MySQL用户账户管理和权限管理深入讲解
前言
MySQL的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。
下面话不多说了，来一起看看详细的介绍吧
mysql的权限体系大致分为5个层级:
全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANTALLON*.*和REVOKEALLON*.*只授予和撤销全局权限。
数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANTALLONdb_name.*和REVOKEALLONdb_name.*只授予和撤销数据库权限。
表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANTALLONdb_name.tbl_name和REVOKEALLONdb_name.tbl_name只授予和撤销表权限。
列层级
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。
子程序层级
CREATEROUTINE,ALTERROUTINE,EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATEROUTINE外，这些权限可以被授予为子程序层级，并存储在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)]>grantselecton*.*toxxx@localhost; ERROR1133(42000):Can'tfindanymatchingrowintheusertable MySQL[(none)]>SETSESSIONsql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; QueryOK,0rowsaffected,2warnings(0.07sec) MySQL[(none)]>grantselecton*.*toxxx@localhost; QueryOK,0rowsaffected,2warnings(0.10sec)
//sql_mode默认值中有NO_AUTO_CREATE_USER(防止GRANT自动创建新用户，除非还指定了密码) SETSESSIONsql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
1.创建用户
xxx@localhost，并赋予所有数据库上的所有表的select权限
先查看user表显示的权限状态
MySQL[mysql]>select*fromuserwhereuser="xxx"andhost='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-0317:34:49 password_lifetime:NULL account_locked:N
再查看db表的权限状态
MySQL[mysql]>select*fromdbwhereuser="xxx"andhost='localhost'\G; Emptyset(0.03sec)
可以发现user表中Select_priv:Y，其他均为N
DB表中则无记录
也就是说，对所有数据库都具有相同的权限的用户并不需要记录到db表，而仅仅需要将user表中的select_priv改为“Y”即可。换句话说，user表中的每个权限都代表了对所有数据库都有权限。
2.将xxx@localhost上的权限改为只对db1数据库上所有表的select权限。
MySQL[mysql]>createdatabasedb1; QueryOK,1rowaffected(0.01sec) MySQL[mysql]>re^C MySQL[mysql]>revokeselecton*.*fromxxx@localhost; QueryOK,0rowsaffected,1warning(0.06sec) MySQL[mysql]>grantselectondb1.*toxxx@localhost; QueryOK,0rowsaffected,1warning(0.09sec) MySQL[mysql]>select*fromuserwhereuser='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*fromdbwhereuser='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]>createtablet1(idint(10),namechar(10)); QueryOK,0rowsaffected(0.83sec) MySQL[db1]>grantselectondb1.t1tommm@localhost; QueryOK,0rowsaffected,2warnings(0.06sec) MySQL[mysql]>select*fromuserwhereuser='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*fromdbwhereuser='mmm'\G; Emptyset(0.00sec) MySQL[mysql]>select*fromtables_privwhereuser='mmm'\G; ***************************1.row*************************** Host:localhost Db:db1 User:mmm Table_name:t1 Grantor:root@localhost Timestamp:0000-00-0000:00:00 Table_priv:Select Column_priv: 1rowinset(0.00sec) ERROR: Noqueryspecified MySQL[mysql]>select*fromcolumns_privwhereuser='mmm'\G; Emptyset(0.00sec)
可以看见tables_priv表中增加了一条记录，而在userdbcolumns_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用户@主机identifiedby'密码';
创建用户p1，权限为可以在所有数据库上执行所有权限，只能从本地进行连接
MySQL[mysql]>grantallprivilegeson*.*top1@localhost; QueryOK,0rowsaffected,2warnings(0.03sec) MySQL[mysql]>select*fromuserwhereuser='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-0318:11:01 password_lifetime:NULL account_locked:N 1rowinset(0.00sec)
除了grant_priv权限外，所有权限在user表里面都是“Y”。
增加对p1的grant权限
MySQL[mysql]>grantallprivilegeson*.*top1@localhostwithgrantoption; QueryOK,0rowsaffected,1warning(0.03sec) MySQL[mysql]>select*fromuserwhereuser='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-0318:11:01 password_lifetime:NULL account_locked:N 1rowinset(0.00sec)
设置密码赋予grant权限
MySQL[mysql]>grantallprivilegeson*.*top1@localhostidentifiedby'123'withgrantoption; QueryOK,0rowsaffected,2warnings(0.01sec) MySQL[mysql]>select*fromuserwhereuser='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-0318:14:40 password_lifetime:NULL account_locked:N 1rowinset(0.00sec)
在5.7版本后的数据库，密码字段改为authentication_string
创建新用户p2，可以从任何IP连接，权限为对db1数据库里的所有表进行select、update、insert和delete操作，初始密码为“123”
MySQL[mysql]>grantselect,insert,update,deleteondb1.*to'p2'@'%'identifiedby'123'; QueryOK,0rowsaffected,1warning(0.01sec) MySQL[mysql]>select*fromuserwhereuser='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-0318:20:44 password_lifetime:NULL account_locked:N 1rowinset(0.00sec) ERROR: Noqueryspecified MySQL[mysql]>select*fromdbwhereuser='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 1rowinset(0.00sec)
user表中的权限都是“N”，db表中增加的记录权限则都是“Y”,这样只授予用户适当的权限，而不会授予过多的权限。
本例中的IP限制为所有IP都可以连接，因此设置为“%”，mysql数据库中是通过user表的host字段来进行控制，host可以是以下类型的赋值。
注意:mysql数据库的user表中host的值为“%”或者空，表示所有外部IP都可以连接，但是不包括本地服务器localhost，因此，如果要包括本地服务器，必须单独为localhost赋予权限。
授予super、process、file权限给用户p3@%
MySQL[mysql]>grantsuper,process,fileondb1.*to'p3'@'%'; ERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGES MySQL[mysql]>grantsuper,process,fileon*.*to'p3'@'%'; QueryOK,0rowsaffected(0.03sec)
这几个权限都是属于管理权限，因此不能够指定某个数据库，on后面必须跟*.*,否则会提示错误，如上
那这几个权限是干啥的？
process通过这个权限，用户可以执行SHOWPROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOWPROCESSLIST命令，但是只能查询本用户的进程。
拥有file权限才可以执行select..intooutfile和loaddatainfile…操作，但是不要把file,process,super权限授予管理员以外的账号，这样存在严重的安全隐患。
super这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGEMASTER，PURGEMASTERLOGS
另外一个比较特殊的
usage权限
连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。
该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即``REVOKE用户并不能删除用户。
查看账号权限
账号创建好后，可以通过如下命令查看权限:
showgrantsforuser@host; MySQL[mysql]>showgrantsforp2@'%'; +-------------------------------------------------------------+ |Grantsforp2@%| +-------------------------------------------------------------+ |GRANTUSAGEON*.*TO'p2'@'%'| |GRANTSELECT,INSERT,UPDATE,DELETEON`db1`.*TO'p2'@'%'| +-------------------------------------------------------------+ 2rowsinset(0.00sec)
更改账户权限
创建用户账号p4，权限为对db1所有表具有select权限
MySQL[mysql]>grantselectondb1.*top4@'%'; QueryOK,0rowsaffected,1warning(0.01sec) MySQL[mysql]>showgrantsforp4@'%'; +-------------------------------------+ |Grantsforp4@%| +-------------------------------------+ |GRANTUSAGEON*.*TO'p4'@'%'| |GRANTSELECTON`db1`.*TO'p4'@'%'| +-------------------------------------+ 2rowsinset(0.00sec)
增加delete权限
MySQL[mysql]>grantdeleteondb1.*top4@'%'; QueryOK,0rowsaffected(0.01sec) MySQL[mysql]>showgrantsforp4@'%'; +---------------------------------------------+ |Grantsforp4@%| +---------------------------------------------+ |GRANTUSAGEON*.*TO'p4'@'%'| |GRANTSELECT,DELETEON`db1`.*TO'p4'@'%'| +---------------------------------------------+ 2rowsinset(0.00sec)
和已有的select权限进行合并
删除delete权限
revoke语句可以回收已经赋予的权限
MySQL[mysql]>showgrantsforp4@'%'; +---------------------------------------------+ |Grantsforp4@%| +---------------------------------------------+ |GRANTUSAGEON*.*TO'p4'@'%'| |GRANTSELECT,DELETEON`db1`.*TO'p4'@'%'| +---------------------------------------------+ 2rowsinset(0.00sec) MySQL[mysql]>revokedeleteondb1.*fromp4@'%'; QueryOK,0rowsaffected(0.01sec) MySQL[mysql]>showgrantsforp4@'%'; +-------------------------------------+ |Grantsforp4@%| +-------------------------------------+ |GRANTUSAGEON*.*TO'p4'@'%'| |GRANTSELECTON`db1`.*TO'p4'@'%'| +-------------------------------------+ 2rowsinset(0.00sec)
usage能revoke?
MySQL[mysql]>revokeselectondb1.*fromp4@'%'; QueryOK,0rowsaffected(0.02sec) MySQL[mysql]>showgrantsforp4@'%'; +--------------------------------+ |Grantsforp4@%| +--------------------------------+ |GRANTUSAGEON*.*TO'p4'@'%'| +--------------------------------+ 1rowinset(0.00sec) MySQL[mysql]>revokeusageondb1.*fromp4@'%'; ERROR1141(42000):Thereisnosuchgrantdefinedforuser'p4'onhost'%'
usage权限不能被回收，也就是说，revoke用户并不能删除用户。
要彻底的删除账号，可以使用dropuser
dropuserp4@'%';
账号资源限制
创建MySQL账号时，还有一类选项称为账号资源限制，这类选项的作用是限制每个账号实际具有的资源限制，这里的“资源”主要包括：
max_queries_per_hourcount:单个账号每小时执行的查询次数
max_upodates_per_hourcount:单个账号每小时执行的更新次数
max_connections_per_hourcount:单个账号每小时连接服务器的次数
max_user_connectionscount:单个账号并发连接服务器的次数
注意：
添加用户或者权限，使用mysql>flushprivileges;刷新权限
具体权限可以参考官网文档
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。