[玩转MySQL之三]MySQL用户及许可权
引言
资料库保存著应用程序日积夜累记录下来的数据资产，安全级别特别高，所以只能让授权的用户可以访问，其他用户需一律拒绝。MySQL是一个多用户资料库，拥有功能强大的访问控制系统，可以为不同的用户指定不同的许可权。 小编一直对MySQL的用户及许可权管理都是一知半解，存有疑问，具体疑问如下:
1.MySQL如何认证一个用户？
小编认为"用户认证"就是为了解决一个问题:你是谁？。在国内，中国公民要证明他是谁，只要拿出身份证就可以，因为身份证上的照片，姓名，家庭住址，性别，出生年月，身份证号码等信息，是中国政府为了说明你就是中国大地上某个地方的某某某而制定的。那么在MySQL Server中，一个用户是如何认证的？
2.MySQL的许可权分哪几种及存储在什么地方？
小编认为"MySQL许可权"是为了解决一个问题: 你能在MySQL Server内干哪些事情？就好比图书馆一样，只有办了卡的人才允许进入，不同的卡可以进入不同的图书馆区域，可以做不同的事情，即拥有不一样的许可权，那么MySQL的许可权有哪些？并且这些许可权存储在哪里？
3. MySQL是如何控制用户访问的？
继续使用图书馆的栗子，当你要进图书馆的时候，需要刷卡或者与管理员沟通，如果无效，那么将会出现谢绝参阅的礼貌回复；假如你有许可权进入图书馆，但是你没有借书的权利，那么在你借书的时候，会借书失败。在MySQL Server中， 一个用户想要对MySQL Server进行操作，MySQL Server是如何控制用户行为的？
一、MySQL用户认证
MySQL的用户认证形式是: 用户名+主机。比如[email protected]和[email protected]是不一样的用户。就好比现实中的牛家村的张三和马家村的张三是分别两个人一样。MySQL中的许可权分配都是分配到用户+主机的实体上。MySQL的主机信息可以是本地(localhost)，某个IP，某个IP段，以及任何地方等，即用户的地址可以限制到某个具体的IP，或者某个IP范围，或者任意地方。MySQL用户分为普通用户和root用户。root用户是超级管理员，拥有所有许可权，普通用户只拥有被授予的各种许可权。
二、MySQL的许可权分类及存储
1.MySQL用户许可权层级
- 全局层级：全局许可权适用于一个给定MySQL Server中的所有资料库，这些许可权存储在mysql.user表中。
GRANT ALL ON *.* TO user@host; # *.* 表示资料库库的所有库和表，对应许可权存储在mysql.user表中
- 资料库层级：资料库许可权适用于一个给定资料库中的所有目标，这些许可权存储在mysql.db表中。
GRANT ALL ON mydb.* TO user@host; #mydb.* 表示mysql资料库下的所有表，对应许可权存储在mysql.db表中
- 表层级：表许可权适用于一个给定表中的所有列，这些许可权存储在mysql.tables_priv表中。
GRANT ALL ON mydb.mytable TO user@host; #mydb.mytable 表示mysql资料库下的mytable表，对应许可权存储在mysql.tables_priv表
- 列层级：列许可权使用于一个给定表中的单一列，这些许可权存储在mysql.columns_priv表中。
GRANT ALL (col1， col2， col3) ON mydb.mytable TO user@host; #mydb.mytable 表示mysql资料库下的mytable表， col1, col2, col3表示mytable表中的列名
- 子程序层级：CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT许可权适用于已存储的子程序。这些许可权可以被授予为全局层级和资料库层级。而且，除了CREATE ROUTINE外，这些许可权可以被授予子程序层级，并存储在mysql.procs_priv表中。
GRANT EXECUTE ON PROCEDURE mydb.myproc TO user@host; #mydb.mytable 表示mysql资料库下的mytable表，PROCEDUR表示存储过程
2. MySQL许可权简单分类
- 数据许可权分为：库、表和栏位三种级别
- 管理许可权主要是管理员要使用到的许可权，包括：资料库创建，临时表创建、主从部署、进程管理等
- 程序许可权主要是触发器、存储过程、函数等许可权。
2. MySQL许可权详情
第一列表示可以在grant命令中制定的许可权第二列对应著几张许可权表(user,db,tables_priv, columns_priv, procs_priv)中的列第三列表示许可权的作用范围，其中Global（Server administration）对应 mysql.user 表，Database 对应 mysql.db 表，Tables 对应 mysql.tables_priv 表，Columns 对应 mysql.columns_priv 表，Stored routines 对应 mysql.procs_priv 表。
MYSQL的许可权如何分布，就是针对表可以设置什么许可权，针对列可以设置什么许可权等等，这个可以从官方文档中的一个表来说明：
许可权分布可能设置的许可权表许可权Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter列许可权Select, Insert, Update, References程序许可权Execute, Alter Routine, Grant
三、 MySQL访问控制
MySQL访问控制分为两个阶段:
- 用户连接检查阶段
- 执行SQL语句时检查阶段
1、用户连接时的检查
1）当用户连接时，MySQL伺服器首先从user表里匹配host, user, password，匹配不到则拒绝该连接2）接著检查user表的max_connections和max_user_connections，如果超过上限则拒绝连接3）检查user表的SSL安全连接，如果有配置SSL，则需确认用户提供的证书是否合法只有上面3个检查都通过后，伺服器才建立连接，连接建立后，当用户执行SQL语句时，需要做SQL语句执行检查。
2、执行SQL语句时的检查
1）从user表里检查max_questions和max_updates，如果超过上限则拒绝执行SQL下面几步是进行许可权检查：2）首先检查user表，看是否具有相应的全局性许可权，如果有，则执行，没有则继续下一步检查3）接著到db表，看是否具有资料库级别的许可权，如果有，则执行，没有则继续下一步检查4）最后到tables_priv, columns_priv, procs_priv表里查看是否具有相应对象的许可权从以上的过程我们可以知道，MySQL检查许可权是一个比较复杂的过程，所以为了提高性能，MySQL的启动时就会把这5张许可权表载入到内存。
四、许可权表栏位详解
1.user表user表的许可权是基于伺服器范围的所有许可权，比如用户拥有伺服器中所有资料库的select许可权那么在user表中的Select_priv列为Y,如果用户单单只拥有某个一资料库的select许可权那么user表中的Select_priv为N,会在DB表中记录一条信息在DB表中的select_priv为Y。
desc mysql.user;
2.db表如果授予一个用户单独某个资料库的许可权，就会在db表中记录一条相关信息。
desc mysql.db;
3.tables_priv表
desc mysql.tables_priv;
上面的Column_priv比较奇怪，因为照理说tables_priv只显示表级别的许可权，列级别的许可权应该在columns_priv里显示才对。后来查了资料才知道，原来这是为了提高许可权检查时的性能，试想一下，许可权检查时，如果发现tables_priv.Column_priv为空，就不需要再检查columns_priv表了，这种情况在现实中往往占大多数。
4. columns_priv表
desc mysql.columns_priv;
5. procs_priv表
desc mysql.procs_priv;
五、用户管理实践
1.用户创建
- 通过create user语句创建用户
在执行CREATE USER或CRANT语句后，MySQL伺服器会修改相应的用户许可权表，添加或修改用户及许可权。
create user USERNAME@HOST identified by PASSWORD;
HOST的形式：1. IP地址，如172.16.16.1；2. 主机名，如localhost；3. 网路地址，如172.16.0.04. 通配符，如%：匹配任意字元_：匹配任意单个字元如172.16.16._(允许172.16.16.1-172.16.16.9)
eg:
mysql> CREATE USER jeffrey@localhost IDENTIFIED BY mypass;
Query OK, 0 rows affected (0.00 sec)
- 通过grant语句创建新用户
GRANT语句是添加新用户并授权它们访问MySQL对象的首选方法，其语法格式为：
grant all on DB_NAME.TABLE_NAME to USERNAME@HOST identified by PASSWORD;
HOST的表现形式和create user一样
eg：
# 用户 testUser对所有的数据有查询和更新许可权，并授于对所有数据表的SELECT和UPDATE许可权
mysql> GRANT SELECT,UPDATE ON *.* TO testUser@localhost IDENTIFIED BY testpwd;
Query OK, 0 rows affected (0.00 sec)
1) 创建root用户
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY root WITH GRANT OPTION;
mysql> flush privileges;
2). 创建一个基本的增删改查用户
mysql> GRANT UPDATE, DELETE, INSERT, SELECT ON *.* TO test@% identified by test WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
mysql> flush privileges;
# MAX_QUERIES_PER_HOUR，MAX_CONNECTIONS_PER_HOUR，MAX_UPDATES_PER_HOUR设置为0表示不限制
3). 创建资料库基本的增删改查用户
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, EXECUTE ON `db_name`.* TO test@% identified by test;
mysql> flush privileges;
4). 授予资料库名以db开头的资料库的许可权
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, EXECUTE ON `db%`.* TO perform@%;
mysql> flush privileges;
5). 创建备份用户许可权
mysql> GRANT SELECT,EVENT,SHOW DATABASES,LOCK TABLES,SUPER,REPLICATION CLIENT ON *.* TO backup@localhost identified by backup;
mysql> flush privileges;
6). 备份恢复用户许可权
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO restore@localhost identified by 123456;
mysql> flush privileges;
7). 复制用户许可权
mysql> GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@% IDENTIFIED BY 123456;
mysql> flush privileges;
2.用户删除
mysql> drop user USERNAME@HOST;
# 删除MySQL默认的无用账户;
mysql> drop user [email protected];
# 删除MySQL默认的无用账户;
mysql> drop user [email protected];
3. 更改用户名
mysql> rename user OLD_NAME to NEW_NAME;
4. 修改用户密码
- 通过mysqladmin工具
# 给root@localhost用户登录mysql设置密码为"redhat";
$ mysqladmin -u root -h localhost password "redhat"
# 修改root@localhost用户登录mysql资料库的密码;
$ mysqladmin -u root -h localhost password "new passwd" -p "old passwd"
- 通过直接修改mysql.user表的用户记录
# MySQL 5.6
mysql> update mysql.user set password=PASSWORD(redhat) where user=root;
mysql> flush privileges;
# MySQL 5.7
mysql> update mysql.user set authentication_string=PASSWORD(redhat) where user=root;
mysql> flush privileges;
- set password语句
mysql> set password for root@localhost=PASSWORD(redhat);
mysql> flush privileges;
- ALTER USER语句(MYSQL5.7版本)
mysql> use mysql
mysql> alter user root@localhost identified by 123456;
mysql> flush privileges;
六、MySQL管理员密码找回
1.修改配置文件，跳过授权表在配置文件中[mysqld]栏位添加skip-grant-tables指令
$ cat /etc/my.cnf
[mysqld]
skip-grant-tables
2. 重启MySQL Server
service mysqld restart
3. 给root用户登录mysql设置密码为helloWORD并以加密方式
mysql> use mysql;
# MySQL5.6
mysql> update user set password=PASSWORD(helloWORD) where user=root;
# MySQL5.7
mysql> update mysql.user set authentication_string=PASSWORD(helloWORD) where user=root;
mysql> flush privileges;
4.修改配置文件，注释刚才添加的配置项
$ cat /etc/my.cnf
[mysqld]
#skip-grant-tables
5.重启MySQL Server
service mysqld restart
七、 MySQL 许可权管理实践
账户许可权信息被存储在MySQL资料库的几张许可权表中，在MySQL启动时，伺服器将这些资料库表中许可权信息的内容读入内存。其中GRANT和REVOKE语句所涉及的常用许可权大致如下这些：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE等，还有一个特殊的proxy许可权，是用来赋予某个用户具有给他人赋予许可权的许可权。1. grant 所有许可权
mysql> grant all privileges on *.* to USERNAME@HOST;
mysql> flush privileges;
2. grant super许可权在*.*上(super许可权可以对全局变数更改)；
mysql> grant super on *.* to USERNAME@HOST;
mysql> flush privileges;
3. grant某个库下所有表的所有许可权
mysql> grant all privileges on DB_NAME.* to USERNAME@HOST;
mysql> flush privileges;
4. grant某个库下所有表的select许可权
mysql>grant select on DB_NAME.* to USERNAME@HOST;
mysql> flush privileges;
5. grant某个库下某个表的insert许可权
mysql> grant insert on DB_NAME.TABLE_NAME to USERNAME@HOST;
mysql> flush privileges;
6. grant某个库下某个表的update许可权
mysql>grant update on DB_NAME.TABLE_NAME to USERNAME@HOST;
mysql> flush privileges;
7. grant某个库下某个表的某个栏位update许可权
mysql> grant update(COLUMN_NAME) on DB_NAME.TABLE_NAME to USERNAME@HOST;
mysql> flush privileges;
8.通过GRANT语句中的USAGE许可权，可以创建账户而不授予任何许可权
mysql> grant usage on *.* to USERNAME@HOST;
mysql> flush privileges;
9. grant创建、修改、删除MySQL数据表结构许可权
mysql> grant create on testdb.* to [email protected].%;
mysql> grant alter on testdb.* to [email protected].%;
mysql> grant drop on testdb.* to [email protected].%;
mysql> flush privileges;
10. grant操作MySQL外键许可权
mysql> grant references on testdb.* to [email protected].%;
mysql> flush privileges;
11. grant操作MySQL临时表许可权
mysql> grant create temporary tables on testdb.* to [email protected].%;
mysql> flush privileges;
12. grant操作MySQL索引许可权
mysql> grant index on testdb.* to [email protected].%;
mysql> flush privileges;
13.grant操作MySQL视图、查看视图源代码许可权
mysql> grant create view on testdb.* to [email protected].%;
mysql> grant show view on testdb.* to [email protected].%;
mysql> flush privileges;
14. grant操作MySQL存储过程、存储函数许可权
mysql> grant create routine on testdb.* to [email protected].%;
mysql> grant alter routine on testdb.* to [email protected].%;
mysql> grant execute on testdb.* to [email protected].%;
mysql> flush privileges;
15.PROXY特殊许可权如果想让某个用户具有给他人赋予许可权的能力，那么就需要proxy许可权了。当你给一个用户赋予all许可权之后，你查看mysql.user表会发现Grant_priv栏位还是为N，表示其没有给他人赋予许可权的许可权。
我们可以查看一下系统默认的超级管理员许可权：
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO root@localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root@localhost WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到其本身有PROXY许可权，并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予许可权的能力，就需要给此用户PROXY许可权，如下：
mysql> grant all on *.* to test@% identified by helloWORD;
mysql> GRANT PROXY ON @ TO test@% WITH GRANT OPTION;
mysql> flush privileges;
16. 查看用户的许可权
Mysql> show grants for USERNAME@HOST;
17. 移除用户许可权
# 移除tom用户对于db.xsb的许可权;
Mysql> revoke all on db.xsb from tom@localhost;
# 刷新授权表;
Mysql> flush privileges;
使用REVOKE收回许可权之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号依然在user表中保存。
八、MySQL 用户和许可权管理经验
1. 用户管理经验
- 1)、尽量使用create user, grant等语句，而不要直接修改许可权表。
虽然create user, grant等语句底层也是修改许可权表，和直接修改许可权表的效果是一样的，但是，对于非高手来说，采用封装好的语句肯定不会出错，而如果直接修改许可权表，难免会漏掉某些表。而且，修改完许可权表之后，还需要执行flush privileges重新载入到内存，否则不会生效。
- 2). 把匿名用户删除掉。
匿名用户没有密码，不但不安全，还会产生一些莫名其妙的问题，强烈建议删除。
2. 许可权管理经验
- 1)、只授予能满足需要的最小许可权，防止用户干坏事。比如用户只是需要查询，那就只给select许可权就可以了，不要给用户赋予update、insert或者delete许可权。
- 2)、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
- 3)、初始化资料库的时候删除没有密码的用户。安装完资料库的时候会自动创建一些用户，这些用户默认没有密码。
- 4)、为每个用户设置满足密码复杂度的密码。
- 5)、定期清理不需要的用户，回收许可权或者删除用户。
九、参考文献
MySQL之许可权管理MySQL用户和许可权管理MySQL许可权探索许可权表MySQL 许可权机制和许可权存储
更多内容请关注公众号: 云小秘
推荐阅读：
