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

Mysql:远程连接及用户权限问题
     这种类描述方式的语义定义我很喜欢,能够根据定义很快的了解到语法使用。

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

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版本。

Mysql:远程连接及用户权限问题
    看完这些权限后,是不是感觉有点乱。其实对于使用者来说,只要不是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的用户体系和权限体系,对于排查问题是很有帮助的。

 

本节内容到此。

 

相关推荐