Mysql：远程连接及用户权限问题

Mysql：远程连接及用户权限问题

 

今天开发程序，新建用户后，从本机连接服务器时报异常：

1103 - host xxx.xxx.xxx.xx is not allowed to connec to this mysql server.

 返回异常不允许连接，这个比较怪，各种倒腾后，发现是Mysql的用户体系和权限体系问题、这个只是以前没有认真学习过，这次根据学习过程，将用户体系和权限体系系统学习，记录以供后来查阅。

 

Mysql安装之后，有个mysql数据库，里面保存着Mysql的用户(user)、权限(privilege)、帮助(help)、数据库(db)、时区(timezone)等信息，我们这次连接出问题是由于用户(user)表限定了登陆数据库的IP地址。需要修改数据库或者重新更新用户权限。

 

我们先看下user表的结构：

mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------
| Field                 | Type                              | Null | Key | Default 
+-----------------------+-----------------------------------+------+-----+---------
| Host                  | char(60)                          | NO   | PRI |                 
| User                  | char(16)                          | NO   | PRI |                 
| Password              | char(41)                          | NO   |     |                 
| Select_priv           | enum('N','Y')                     | NO   |     | N               
| Insert_priv           | enum('N','Y')                     | NO   |     | N               
| Update_priv           | enum('N','Y')                     | NO   |     | N               
| Delete_priv           | enum('N','Y')                     | NO   |     | N               
| Create_priv           | enum('N','Y')                     | NO   |     | N               
| Drop_priv             | enum('N','Y')                     | NO   |     | N               
| Reload_priv           | enum('N','Y')                     | NO   |     | N               
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N               
| Process_priv          | enum('N','Y')                     | NO   |     | N               
| File_priv             | enum('N','Y')                     | NO   |     | N               
| Grant_priv            | enum('N','Y')                     | NO   |     | N               
| References_priv       | enum('N','Y')                     | NO   |     | N               
| Index_priv            | enum('N','Y')                     | NO   |     | N               
| Alter_priv            | enum('N','Y')                     | NO   |     | N               
| Show_db_priv          | enum('N','Y')                     | NO   |     | N               
| Super_priv            | enum('N','Y')                     | NO   |     | N               
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N               
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N               
| Execute_priv          | enum('N','Y')                     | NO   |     | N               
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N               
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N               
| Create_view_priv      | enum('N','Y')                     | NO   |     | N               
| Show_view_priv        | enum('N','Y')                     | NO   |     | N               
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N               
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N               
| Create_user_priv      | enum('N','Y')                     | NO   |     | N               
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |                 
| ssl_cipher            | blob                              | NO   |     | NULL            
| x509_issuer           | blob                              | NO   |     | NULL            
| x509_subject          | blob                              | NO   |     | NULL            
| max_questions         | int(11) unsigned                  | NO   |     | 0               
| max_updates           | int(11) unsigned                  | NO   |     | 0               
| max_connections       | int(11) unsigned                  | NO   |     | 0               
| max_user_connections  | int(11) unsigned                  | NO   |     | 0               
+-----------------------+-----------------------------------+------+-----+---------
37 rows in set (0.00 sec)

 其中以priv结尾的字段都是权限；查询下当前用户Host的User、Password字段，看看是否允许远端登陆。如果不允许的话，我们可以直接修改Host字段，使其支持从远端登陆，这样太过粗暴，通常都不会这么做。

 

我们通常的解决方法是创建用户，赋予用户的密码、Host、权限，甚至可以直接将这些内容赋予到具体某张表上，能够达到细致入微的程度。

 

Mysql中能够授予用户权限的命令是grant，我们先看下grant的用法： http://dev.mysql.com/doc/refman/5.1/en/grant.html

     这种类描述方式的语义定义我很喜欢，能够根据定义很快的了解到语法使用。

我将上面那张图精简一下，语法可以修改为：

grant `privilege list` on `db.table` to `user`@`host` idenfied by `password` require `ssl_option` with `with_option`

 

我们逐个解释上面的字段

privilege list：

Mysql支持的privilege如下所示，注意有些权限不在user表里面，如columns_priv就是单独一张表；还有些是数据库版本的问题，自己的机器上装的是5.0版本。

    看完这些权限后，是不是感觉有点乱。其实对于使用者来说，只要不是root权限，也可以得到这些权限。其中ALL可以在某种程度上得到这些权限(这么说是因为GRANT OPTIN也可以对权限做控制)。这些权限中最为常用的是select、insert、update、delete、create、drop、index、alter、event、trigger等。

 

db.table

支持*、*.*、db.*、db.table、table等各种形式的控制，表示将某个库的某张表赋予权限，最细致能够到达数据库表的列字段。其中*表示所有的数据库或者表。对于数据库表列权限控制的语法如下：

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

     注意支持列权限控制的命令有Select、Insert、Update、References这四个，其它命令不支持列权限控制。

 

user：用户名称，user名称不支持匹配符，所以*并不是指任何用户，而是单独指*用户。

host：用户地址，这个是支持%匹配符的。

password：标准的password就行，复杂度越大越好。密码在mysql.user表中是加密存在，所以想直接修改密码的同学可能要要失望。

 

ssl_option：连接方式支持，SSL或者X509等支持。

with_option：额外对授权用户提出的限制，如

grant option：权限级联，如果当前用户被取消权限，授予的用户权限取消。
MAX_QUERIES_PER_HOUR：最大查询数/小时
MAX_UPDATES_PER_HOUR：最大更新次数/小时
MAX_CONNECTIONS_PER_HOUR：最大连接数/小时
MAX_USER_CONNECTIONS：最大用户连接数

 

熟悉了grant的语法和字段含义，我们来学习下grant的用法：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY '123456';

     创建用户jeffery，密码123456，仅允许在本地登录

GRANT ALL ON db1.tbl1 TO 'jeffrey'@'%';

     允许jeffery在任何机器连接，并且授予db1.tbl1的所有操作权限

GRANT SELECT ON db2.* TO 'jeffrey'@'10.11.11.123';

     仅允许jeffery从10.11.11.123连接，授予db2里面所有表的查询权限

GRANT USAGE ON *.* TO 'jeffrey'@'%' WITH MAX_QUERIES_PER_HOUR 90;

     给jeffery的权限添加MAX_QUERIES_PER_HOUR限制

注意这个grant是USAGE，USAGE的用法是给用户添加新的资源限制而不影响用户已经有的权限。

 

对于最开始不能连接的问题，我们可以通过直接新建用户连接地址改正：

CREATE USER 'jeffrey'@'%' IDENTIFIED BY '123456';  //创建新用户jeffery，允许从任意机器连接数据库
GRANT ALL ON *.* TO 'jeffrey'@'%';  //授予jeffery全部数据库操作权限，你可以根据需要授予部分权限

 

有授予GRANT权限，肯定也有回收(REVOKE)权限。

REVOKE `privilege list` on `db.table` from `user`@`host`

     语法类似，不在细解释。

 

注意REVOKE中能够回收GRANT OPTION，如果想确定收回该用户授权出去的用户权限，可以将GRANT OPTION也添加在privilege list中，该用户授权出去的权限自动失效。

 

如果遇到无法连接的问题，除去网络问题外，最有可能就是数据库的设置不正确。了解下Mysql的用户体系和权限体系，对于排查问题是很有帮助的。

 

本节内容到此。