共计 12663 个字符，预计需要花费 32 分钟才能阅读完成。
MySQL 的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。
mysql 的权限体系大致分为 5 个层级:
全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在 mysql.user 表中。GRANT ALL ON *.* 和 REVOKE ALL ON *.* 只授予和撤销全局权限。数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在 mysql.db 和 mysql.host 表中。GRANT ALL ON db_name.* 和 REVOKE ALL ON db_name.* 只授予和撤销数据库权限。表层级
表权限适用于一个给定表中的所有列。这些权限存储在 mysql.talbes_priv 表中。GRANT ALL ON db_name.tbl_name 和 REVOKE ALL ON db_name.tbl_name 只授予和撤销表权限。列层级
列权限适用于一个给定表中的单一列。这些权限存储在 mysql.columns_priv 表中。当使用 REVOKE 时，您必须指定与被授权列相同的列。子程序层级
CREATE ROUTINE, ALTER ROUTINE, EXECUTE 和 GRANT 权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了 CREATE ROUTINE 外，这些权限可以被授予为子程序层级，并存储在 mysql.procs_priv 表中。这些权限信息存储在下面的系统表中:
mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv
当用户连接进来，mysqld 会通过上面的这些表对用户权限进行验证！一、权限表的存取
在权限存取的两个过程中，系统会用到“mysql”数据库(安装 MySQL 时被创建，数据库名称叫“mysql”) 中 user、host 和 db 这 3 个最重要的权限表。
在这 3 个表中，最重要的表示 user 表，其次是 db 表，host 表在大多数情况下并不使用。
user 中的列主要分为 4 个部分：用户列、权限列、安全列和资源控制列。
通常用的最多的是用户列和权限列，其中权限列又分为普通权限和管理权限。普通权限用于数据库的操作，比如 select_priv、super_priv 等。
当用户进行连接时，权限表的存取过程有以下两个过程：
先从 user 表中的 host、user 和 password 这 3 个字段中判断连接的 IP、用户名、和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。
如果通过身份验证、则按照以下权限表的顺序得到数据库权限：user -> db -> tables_priv -> columns_priv。
在这几个权限表中，权限范围依次递减，全局权限覆盖局部权限。上面的第一阶段好理解，下面以一个例子来详细解释一下第二阶段。
为了方便测试，需要修改变量 sql_mode，不然会报错，如下
MySQL [(none)]> grant select on *.* to xxx@localhost;
ERROR 1133 (42000): Can't find any matching row in the user table
MySQL [(none)]> SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 2 warnings (0.07 sec)
MySQL [(none)]> grant select on *.* to xxx@localhost;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
// sql_mode 默认值中有 NO_AUTO_CREATE_USER (防止 GRANT 自动创建新用户，除非还指定了密码)
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';1. 创建用户
xxx@localhost，并赋予所有数据库上的所有表的 select 权限
先查看 user 表显示的权限状态
MySQL [mysql]> select * from user where user="xxx" and host='localhost' \G;
*************************** 1. row ***************************
Host: localhost
User: xxx
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
password_last_changed: 2018-12-03 17:34:49
password_lifetime: NULL
account_locked: N
再查看 db 表的权限状态
MySQL [mysql]> select * from db where user="xxx" and host='localhost' \G;
Empty set (0.03 sec)
可以发现 user 表中Select_priv: Y，其他均为N
DB表中则无记录
也就是说，对所有数据库都具有相同的权限的用户并不需要记录到 db 表，而仅仅需要将 user 表中的 select_priv 改为“Y”即可。换句话说，user 表中的每个权限都代表了对所有数据库都有权限。
2. 将 xxx@localhost 上的权限改为只对 db1 数据库上所有表的 select 权限。
MySQL [mysql]> create database db1;
Query OK, 1 row affected (0.01 sec)
MySQL [mysql]> re^C
MySQL [mysql]> revoke select on *.* from xxx@localhost;
Query OK, 0 rows affected, 1 warning (0.06 sec)
MySQL [mysql]> grant select on db1.* to xxx@localhost;
Query OK, 0 rows affected, 1 warning (0.09 sec)
MySQL [mysql]> select * from user where user='xxx'\G;
*************************** 1. row ***************************
Host: localhost
User: xxx
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
MySQL [mysql]> select * from db where user='xxx'\G;
*************************** 1. row ***************************
Host: localhost
Db: db1
User: xxx
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
这时候发现，user 表中的 select_priv 变为“N”，而 db 表中增加了 db 为 xxx 的一条记录。也就是说，当只授予部分数据库某些权限时，user 表中的相应权限列保持“N”，而将具体的数据库权限写入 db 表。table 和 column 的权限机制和 db 类似。
3. tables_priv 记录表权限
MySQL [db1]> create table t1(id int(10),name char(10));
Query OK, 0 rows affected (0.83 sec)
MySQL [db1]> grant select on db1.t1 to mmm@localhost;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
MySQL [mysql]> select * from user where user='mmm'\G;
*************************** 1. row ***************************
Host: localhost
User: mmm
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
...
MySQL [mysql]> select * from db where user='mmm'\G;
Empty set (0.00 sec)
MySQL [mysql]> select * from tables_priv where user='mmm'\G;
*************************** 1. row ***************************
Host: localhost
Db: db1
User: mmm
Table_name: t1
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv:
1 row in set (0.00 sec)
ERROR:
No query specified
MySQL [mysql]> select * from columns_priv where user='mmm'\G;
Empty set (0.00 sec)
可以看见 tables_priv 表中增加了一条记录，而在 user db columns_priv 三个表中没有记录
从上例可以看出，当用户通过权限认证，进行权限分配时，将按照 ==user -> db -> tables_priv -> columns_priv== 的顺序进行权限分配，即先检查全局权限表 user，如果 user 中对应 权限为“Y”，则此用户对所有数据库的权限都为“Y”，将不再检查 db、tables_priv 和 columns_priv；如果为“N”，则到 db 表中检查此用户对应的具体数据库，并得到 db 中为“Y”的权限；如果 db 中相应权限为“N”，则再依次检查tables_priv 和 columns_priv 中的权限，如果所有的都为“N”，则判断为不具备权限。
二. 账户管理
授权 grant
grant 不仅可以用来授权，还可以用来创建用户。
授权的语法：
grant 权限列表 on 库名. 表名 to 用户 @主机 identified by '密码';创建用户 p1，权限为可以在所有数据库上执行所有权限，只能从本地进行连接
MySQL [mysql]> grant all privileges on *.* to p1@localhost;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
MySQL [mysql]> select * from user where user='p1'\G
*************************** 1. row ***************************
Host: localhost
User: p1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
password_last_changed: 2018-12-03 18:11:01
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
除了 grant_priv 权限外，所有权限在user 表里面都是“Y”。
增加对 p1 的 grant 权限
MySQL [mysql]> grant all privileges on *.* to p1@localhost with grant option;
Query OK, 0 rows affected, 1 warning (0.03 sec)
MySQL [mysql]> select * from user where user='p1'\G
*************************** 1. row ***************************
Host: localhost
User: p1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
password_last_changed: 2018-12-03 18:11:01
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
设置密码赋予 grant 权限
MySQL [mysql]> grant all privileges on *.* to p1@localhost identified by '123' with grant option;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
MySQL [mysql]> select * from user where user='p1'\G
*************************** 1. row ***************************
Host: localhost
User: p1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2018-12-03 18:14:40
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
在 5.7 版本后的数据库，密码字段改为authentication_string
创建新用户 p2，可以从任何 IP 连接，权限为对 db1 数据库里的所有表进行 select、update、insert 和 delete 操作，初始密码为“123”
MySQL [mysql]> grant select,insert,update,delete on db1.* to 'p2'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
MySQL [mysql]> select * from user where user='p2'\G;
*************************** 1. row ***************************
Host: %
User: p2
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
...
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2018-12-03 18:20:44
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
ERROR:
No query specified
MySQL [mysql]> select * from db where user='p2'\G;
*************************** 1. row ***************************
Host: %
Db: db1
User: p2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)
user 表中的权限都是“N”，db 表中增加的记录权限则都是“Y”, 这样只授予用户适当的权限，而不会授予过多的权限。
本例中的 IP 限制为所有 IP 都可以连接，因此设置为“%”，mysql 数据库中是通过 user 表的 host 字段来进行控制，host 可以是以下类型的赋值。
注意: mysql 数据库的 user 表中 host 的值为“%”或者空，表示所有外部 IP 都可以连接，但是不包括本地服务器 localhost，因此，如果要包括本地服务器，必须单独为 localhost 赋予权限。
授予 super、process、file 权限给用户 p3@%
MySQL [mysql]> grant super,process,file on db1.* to 'p3'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
MySQL [mysql]> grant super,process,file on *.* to 'p3'@'%';
Query OK, 0 rows affected (0.03 sec)
这几个权限都是属于管理权限，因此不能够指定某个数据库，on 后面必须跟 *.*, 否则会提示错误，如上
那这几个权限是干啥的？process通过这个权限，用户可以执行 SHOW PROCESSLIST 和KILL命令。默认情况下，每个用户都可以执行 SHOW PROCESSLIST 命令，但是只能查询本用户的进程。
拥有 file 权限才可以执行 select ..into outfile和 load data infile… 操作，但是不要把 file, process, super 权限授予管理员以外的账号，这样存在严重的安全隐患。
super这个权限允许用户终止任何查询；修改全局变量的 SET 语句；使用CHANGE MASTER，PURGE MASTER LOGS
另外一个比较特殊的
usage权限
连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。
该权限只能用于数据库登陆，不能执行任何操作；且 usage 权限不能被回收，也即 “REVOKE 用户并不能删除用户。
查看账号权限
账号创建好后，可以通过如下命令查看权限:
show grants for user@host;MySQL [mysql]> show grants for p2@'%';
+-------------------------------------------------------------+
| Grants for p2@% |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'p2'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO 'p2'@'%' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)
更改账户权限
创建用户账号 p4，权限为对db1 所有表具有 select 权限
MySQL [mysql]> grant select on db1.* to p4@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)
MySQL [mysql]> show grants for p4@'%';
+-------------------------------------+
| Grants for p4@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT ON `db1`.* TO 'p4'@'%' |
+-------------------------------------+
2 rows in set (0.00 sec)
增加 delete 权限
MySQL [mysql]> grant delete on db1.* to p4@'%';
Query OK, 0 rows affected (0.01 sec)
MySQL [mysql]> show grants for p4@'%';
+---------------------------------------------+
| Grants for p4@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)
和已有的 select 权限进行合并
删除 delete 权限
revoke 语句可以回收已经赋予的权限
MySQL [mysql]> show grants for p4@'%';
+---------------------------------------------+
| Grants for p4@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)
MySQL [mysql]> revoke delete on db1.* from p4@'%';
Query OK, 0 rows affected (0.01 sec)
MySQL [mysql]> show grants for p4@'%';
+-------------------------------------+
| Grants for p4@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT ON `db1`.* TO 'p4'@'%' |
+-------------------------------------+
2 rows in set (0.00 sec)
usage能revoke?
MySQL [mysql]> revoke select on db1.* from p4@'%';
Query OK, 0 rows affected (0.02 sec)
MySQL [mysql]> show grants for p4@'%';
+--------------------------------+
| Grants for p4@% |
+--------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
+--------------------------------+
1 row in set (0.00 sec)
MySQL [mysql]> revoke usage on db1.* from p4@'%';
ERROR 1141 (42000): There is no such grant defined for user 'p4' on host '%'
usage 权限不能被回收，也就是说，revoke 用户并不能删除用户。
要彻底的删除账号，可以使用 drop user
drop user p4@'%';账号资源限制
创建 MySQL 账号时，还有一类选项称为账号资源限制，这类选项的作用是限制每个账号实际具有的资源限制，这里的“资源”主要包括：
max_queries_per_hour count : 单个账号每小时执行的查询次数
max_upodates_per_hour count : 单个账号每小时执行的更新次数
max_connections_per_hour count : 单个账号每小时连接服务器的次数
max_user_connections count : 单个账号并发连接服务器的次数注意：
添加用户或者权限，使用mysql> flush privileges; 刷新权限
具体权限可以参考官网文档
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
：
正文完
星哥玩云-微信公众号
