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的用户体系和权限体系，对于排查问题是很有帮助的。
本节内容到此。