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)]>grantselecton*.*toxxx@localhost;ERROR1133(42000):Can'tfindanymatchingrowintheusertableMySQL[(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:localhostUser:xxxSelect_priv:YInsert_priv:NUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NReload_priv:NShutdown_priv:NProcess_priv:NFile_priv:NGrant_priv:NReferences_priv:NIndex_priv:NAlter_priv:NShow_db_priv:NSuper_priv:NCreate_tmp_table_priv:NLock_tables_priv:NExecute_priv:NRepl_slave_priv:NRepl_client_priv:NCreate_view_priv:NShow_view_priv:NCreate_routine_priv:NAlter_routine_priv:NCreate_user_priv:NEvent_priv:NTrigger_priv:NCreate_tablespace_priv:Nssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions:0max_updates:0max_connections:0max_user_connections:0plugin:mysql_native_passwordauthentication_string:password_expired:Npassword_last_changed:2018-12-0317:34:49password_lifetime:NULLaccount_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^CMySQL[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:localhostUser:xxxSelect_priv:NInsert_priv:NUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NReload_priv:N

MySQL[mysql]>select*fromdbwhereuser='xxx'\G;***************************1.row***************************Host:localhostDb:db1User:xxxSelect_priv:YInsert_priv:NUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NGrant_priv:NReferences_priv:NIndex_priv:NAlter_priv:NCreate_tmp_table_priv:NLock_tables_priv:NCreate_view_priv:NShow_view_priv:NCreate_routine_priv:NAlter_routine_priv:NExecute_priv:NEvent_priv:NTrigger_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:localhostUser:mmmSelect_priv:NInsert_priv:NUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NReload_priv:N...MySQL[mysql]>select*fromdbwhereuser='mmm'\G;Emptyset(0.00sec)MySQL[mysql]>select*fromtables_privwhereuser='mmm'\G;***************************1.row***************************Host:localhostDb:db1User:mmmTable_name:t1Grantor:root@localhostTimestamp:0000-00-0000:00:00Table_priv:SelectColumn_priv:1rowinset(0.00sec)ERROR:NoqueryspecifiedMySQL[mysql]>select*fromcolumns_privwhereuser='mmm'\G;Emptyset(0.00sec)

可以看见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]>grantallprivilegeson*.*top1@localhost;QueryOK,0rowsaffected,2warnings(0.03sec)MySQL[mysql]>select*fromuserwhereuser='p1'\G***************************1.row***************************Host:localhostUser:p1Select_priv:YInsert_priv:YUpdate_priv:YDelete_priv:YCreate_priv:YDrop_priv:YReload_priv:YShutdown_priv:YProcess_priv:YFile_priv:YGrant_priv:NReferences_priv:YIndex_priv:YAlter_priv:YShow_db_priv:YSuper_priv:YCreate_tmp_table_priv:YLock_tables_priv:YExecute_priv:YRepl_slave_priv:YRepl_client_priv:YCreate_view_priv:YShow_view_priv:YCreate_routine_priv:YAlter_routine_priv:YCreate_user_priv:YEvent_priv:YTrigger_priv:YCreate_tablespace_priv:Yssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions:0max_updates:0max_connections:0max_user_connections:0plugin:mysql_native_passwordauthentication_string:password_expired:Npassword_last_changed:2018-12-0318:11:01password_lifetime:NULLaccount_locked:N1rowinset(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:localhostUser:p1Select_priv:YInsert_priv:YUpdate_priv:YDelete_priv:YCreate_priv:YDrop_priv:YReload_priv:YShutdown_priv:YProcess_priv:YFile_priv:YGrant_priv:YReferences_priv:YIndex_priv:YAlter_priv:YShow_db_priv:YSuper_priv:YCreate_tmp_table_priv:YLock_tables_priv:YExecute_priv:YRepl_slave_priv:YRepl_client_priv:YCreate_view_priv:YShow_view_priv:YCreate_routine_priv:YAlter_routine_priv:YCreate_user_priv:YEvent_priv:YTrigger_priv:YCreate_tablespace_priv:Yssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions:0max_updates:0max_connections:0max_user_connections:0plugin:mysql_native_passwordauthentication_string:password_expired:Npassword_last_changed:2018-12-0318:11:01password_lifetime:NULLaccount_locked:N1rowinset(0.00sec)

设置密码赋予grant权限

MySQL[mysql]>grantallprivilegeson*.*top1@localhostidentifiedby'123'withgrantoption;QueryOK,0rowsaffected,2warnings(0.01sec)MySQL[mysql]>select*fromuserwhereuser='p1'\G***************************1.row***************************Host:localhostUser:p1Select_priv:YInsert_priv:YUpdate_priv:YDelete_priv:YCreate_priv:YDrop_priv:YReload_priv:YShutdown_priv:YProcess_priv:YFile_priv:YGrant_priv:YReferences_priv:YIndex_priv:YAlter_priv:YShow_db_priv:YSuper_priv:YCreate_tmp_table_priv:YLock_tables_priv:YExecute_priv:YRepl_slave_priv:YRepl_client_priv:YCreate_view_priv:YShow_view_priv:YCreate_routine_priv:YAlter_routine_priv:YCreate_user_priv:YEvent_priv:YTrigger_priv:YCreate_tablespace_priv:Yssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions:0max_updates:0max_connections:0max_user_connections:0plugin:mysql_native_passwordauthentication_string:*23AE809DDACAF96AF0FD78ED04B6A265E05AA257password_expired:Npassword_last_changed:2018-12-0318:14:40password_lifetime:NULLaccount_locked:N1rowinset(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:p2Select_priv:NInsert_priv:NUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NReload_priv:NShutdown_priv:N...Create_tablespace_priv:Nssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions:0max_updates:0max_connections:0max_user_connections:0plugin:mysql_native_passwordauthentication_string:*23AE809DDACAF96AF0FD78ED04B6A265E05AA257password_expired:Npassword_last_changed:2018-12-0318:20:44password_lifetime:NULLaccount_locked:N1rowinset(0.00sec)ERROR:NoqueryspecifiedMySQL[mysql]>select*fromdbwhereuser='p2'\G;***************************1.row***************************Host:%Db:db1User:p2Select_priv:YInsert_priv:YUpdate_priv:YDelete_priv:YCreate_priv:NDrop_priv:NGrant_priv:NReferences_priv:NIndex_priv:NAlter_priv:NCreate_tmp_table_priv:NLock_tables_priv:NCreate_view_priv:NShow_view_priv:NCreate_routine_priv:NAlter_routine_priv:NExecute_priv:NEvent_priv:NTrigger_priv:N1rowinset(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):IncorrectusageofDBGRANTandGLOBALPRIVILEGESMySQL[mysql]>grantsuper,process,fileon*.*to'p3'@'%';QueryOK,0rowsaffected(0.03sec)

这几个权限都是属于管理权限，因此不能够指定某个数据库，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用户并不能删除用户。

查看账号权限

账号创建好后，可以通过如下命令查看权限:

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 用户并不能删除用户。

要彻底的删除账号，可以使用 drop user

dropuserp4@'%';

账号资源限制

创建 MySQL 账号时，还有一类选项称为账号资源限制，这类选项的作用是限制每个账号实际具有的资源限制，这里的“资源”主要包括：

max_queries_per_hour count : 单个账号每小时执行的查询次数
max_upodates_per_hour count : 单个账号每小时执行的更新次数
max_connections_per_hour count : 单个账号每小时连接服务器的次数
max_user_connections count : 单个账号并发连接服务器的次数

注意：

添加用户或者权限，使用mysql> flush privileges; 刷新权限

看完上述内容，你们掌握MySQL中怎么实现用户账户管理和权限管理的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！