内容简介：MySQL 的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。在权限存取的两个过程中，系统会用到 “mysql” 数据库(安装 MySQL 时被创建，数据库名称叫“mysql”) 中 user、host 和 db 这3个最重要的权限表。在这 3 个表中，最重要的表示 user 表，其次是 db 表，host 表在大多数情况下并不使用。
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
Linux公社的RSS地址 ： https://www.linuxidc.com/rssFeed.aspx
本文永久更新链接地址： https://www.linuxidc.com/Linux/2018-12/155657.htm
以上所述就是小编给大家介绍的《MySQL用户账户管理与权限管理详解》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
- 以太坊源码分析—账户的管理
- Bytom交易说明（账户管理模式）
- MySQL常用高级操作：视图、事务、账户管理
- 兄弟连区块链教程以太源码分析accounts账户管理分析
- Windows严重漏洞披露：允许黑客将管理员权限转移给来宾账户
- 单点登出，你在哪儿？一个基于Web的SSO账户劫持与会话管理的实证分析
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Learning PHP, MySQL, and JavaScript
Robin Nixon / O'Reilly Media / 2009-7-21 / USD 39.99
Learn how to create responsive, data-driven websites with PHP, MySQL, and JavaScript - whether or not you know how to program. This simple, streamlined guide explains how the powerful combination of P......一起来看看 《Learning PHP, MySQL, and JavaScript》 这本书的介绍吧!
