Mysql用户权限分配详解

文章目录

MySQL 权限介绍

相信对于绝大多数开发人员都接触过数据库，当今最常用的关系型数据库有Mysql、Oracle、PostgreSql、SQLserver，本文重点讲解Mysql用户权限的分配、管理。用户权限在实际开发中有什么用途呢？总结作用如下：

一、可以根据登录用户限制用户访问资源(库、表)
二、可以根据登录用户限制用户的操作权限(能对哪些库、表执行增删改查操作)
三、可以指定用户登录IP或者域名
四、可以限制用户权限分配

一、Mysql权限级别分析

Mysql权限级别分为了五个层级，并且每个级别的权限都对应着不同的表，这些表都存在于mysql库下，在Mysql官方文档中有详细介绍权限的概念MYSQL官方中文文档，以下将简单讲述这五个级别的作用和范围。

（1）全局级别

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*用于授予和撤销全局权限。

在mysql数据库里，存在一张user表，mysql中所有的用户都是存放在user表中，user表的字段可以分为四大类(用户列、权限列、安全例、资源控制列)

（1.1） USER表的组成结构

通过查看user表结构可以看出，user表保存了用户的登录信息和权限，这个权限是全局性的，针对的是全部数据库资源，换句话说，只要user表里的某个权限是Y(Y表示YES,N表NO)，那么这个权限可以作用于全部数据库资源。

（1.1.1） 用户列

user表中的用户列包括字段Host、User、Password:

Host: 登录的主机名，表示允许用户从哪台机器登录接连到Mysql服务器。可以使用IP地址或者域名，%表示允许从任何主机连接到Mysql服务器。
User:登录的用户名
Password: 用户登录数据库的密码，当该字段为空时，表示该用户账号登录不需要密码

（1.1.2） 权限列

user表中的权限列是以_priv结尾的字段，这些字段默认为N，N表示该权限不能用到所有数据库上，Y则与N的效果相反。以下是权限列各个字段的说明:
列名说明Select_priv是否拥有SELECT权限命令查询数据Insert_priv是否拥有INSERT权限命令插入数据Update_priv是否拥有UPDATE权限命令修改数据Delete_priv是否拥有DELETE权限命令删除数据Create_priv是否拥有权限创建新的数据库和表Drop_priv是否拥有权限删除现有数据库和表Reload_priv是否拥有权限执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表Shutdown_priv是否拥有权限关闭MySQL服务器Process_priv是否拥有权限通过SHOW PROCESSLIST命令查看其他用户的进程File_priv是否拥有权限执行SELECT INTO OUTFILE和LOAD DATA INFILE命令Grant_priv是否拥有权限将自己已授权的权限授权给其他用户Index_priv是否拥有权限创建和删除表索引Alter_priv是否拥有权限重命名和修改表结构Show_db_priv是否拥有权限查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库Super_priv是否拥有权限执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令Create_tmp_table_priv是否拥有权限创建临时表Lock_tables_priv是否拥有权限使用LOCK TABLES命令阻止对表的访问/修改Repl_slave_priv是否拥有权限读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信Create_view_priv是否拥有权限创建视图Show_view_priv是否拥有权限查看视图或了解视图如何执行Create_routine_priv是否拥有权限更改或放弃存储过程和函数Alter_routine_priv是否拥有权限修改或删除存储函数及函数Create_user_priv是否拥有权限执行CREATE USER命令，这个命令用于创建新的MySQL账户Event_priv是否拥有权限创建、修改和删除事件Trigger_priv是否拥有权限创建和删除触发器

（1.1.3） 安全列

user表的安全列有4个字段：ssl_type、ssl_cipher、x509_issuer、x509_subject

ssl用于加密；
x509标准可以用来标识用户。普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE 'have_openssl’语句来查看是否具有ssl功能。如果取值为DISABLED，那么则没有ssl加密功能。

（1.1.4） 资源控制列

user表的4个资源控制列是：max_questions、max_updates、max_connections、max_user_connections

max_questions：每小时可以允许执行多少次查询；
max_updates：每小时可以允许执行多少次更新；
max_connections：每小时可以建立多少连接；
max_user_connections：单个用户可以同时具有的连接数。

默认值为0，表示无限制。

（2）数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON
db_name.*和REVOKE ALL ON db_name.*用于授予和撤销数据库权限。

刚刚我们已经讨论了全局级别的权限配置，对user表进行了解析，但是user表上的权限都是针对与所有数据库的，如果我们想将权限力度划分的更细，如果我想让某个数据库用户只能查看某个数据库里的表数据，那么此时就需要用到数据库层级的权限控制，主要分析mysql库里的db表。

db表里的权限列与user表里的含义差不多一致，只是db表的权限只是针对与某个数据库，而非全局数据库。其中DB字段存储的是数据库名称。

（3）表层级

表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON
db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限

如果你清楚了全局级别的权限和数据库层的权限，那么表层级的权限就相对好理解了，表层级的权限是用于控制用户对某个数据库里的某个表是否有权限进行操作。比如你想指定某个用户只能操作指定数据库里的某张表，此时就需要用到表层级的权限来控制。
tables_priv表的字段相比较与user和db表要少的多，因为它控制的权限更细，它的结构如下：

（1.3） tables_priv表的组成结构

列名说明Host用户登录的主机，可以是IP或者域名Db数据库名称User登录用户名Talbe_name表名称Grantor权限的设置者Table_priv对表进行操作的权限，对表的操作权限有：Select、Insert、Update、Delete、Create、Drop、Grant、Index、Alter、Create View、Show View以及TriggerColumn_priv对表中的数据列进行操作的权限 Insert、Update、References

（4）列层级

列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。

列层级的使用还是比较少，所以这里就不再过多阐述。

（5）子程序层级

REATE ROUTINE, ALTER ROUTINE,EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

（6）权限分配顺序

MySQL权限分配顺序是按照层级由大到小进行解析的，也就是先查看
user表（所有数据库）–>db表(某个数据库)->table_priv(某个表)->columns_pirv(某列)进行查找。
如果发现user表中某个权限是Y，那么就不会继续往下找，如果User表某个权限是N，那么就去找db表，依次往下找。

二、实战用户权限分配

（1）新建用户

创建用户常用的命令是CREATE USER，不过使用CREATE USER语句创建用户时，需要操作员要有相应的权限，也就是user表中的字段Create_priv为Y才能创建用户。比如创建一个名为admin的用户，并且登录密码为123456:

CREATE USER ‘admin’@‘%’ IDENTIFIED BY ‘123456’
一般创建用户的格式为：CREATE USER ‘用户名’@‘登录IP或者域名’ IDENTIFIED BY ‘登录密码’。
其中需要注意的是登录IP或者域名，很多时候会将登录IP设置为**%，%**代表所有主机，表示该用户可以在任何机器上进行登录。192.168.101.%表示允许192.168.101这个网段的主机进行登录。

当你只是完上面的创建admin用户命令后，你可以打开mysql库下的user表，你会发现里面多了一条admin的数据，但是所有的权限都是N，这表明该admin用户没有任何权限，不能查看操作任何数据库资源。

此时你可以尝试使用admin用户登录你现有的数据库，你会发现admin账号只能查看information_schema这个数据库，那是因为我们在创建admin账号时，并没有分配任何权限。

如果你想在创建账号的时候就分配给该账号一定的权限，那么你可以使用 :

grant all privileges on *.* to 'admin'@'%' identified by '123456' with grant option;

该语句表示创建admin账号允许从任何主机登录并且拥有数据库全部的权限，可以将自己的权限授予给别人。我们将这条语句拆分:

创建账号时并赋予全部权限: 
grantallprivilegeson*.*to'用户名'@'登录IP或者域名' identified by'登录密码'withgrantoption;allprivileges: 表示授权用户全部权限,当然你也可以指定赋予具体权限，比如SELECT、UPDATE、CREATE、DROP等。
on:表示这些权限可以作用于哪些数据库和哪些表，*.*表示作用于所有数据库和所有表，格式为 数据库名.表名。比如有一个test库，想让admin拥有查看test库所有表的权限，那么可以这样写：grantSELECTon test.*to'admin'@'%' identified by'登录密码';to：将权限授予哪个用户。格式：'用户名'@'登录IP或域名'。
IDENTIFIED by：指定用户的登录密码。
withgrantoption：表示允许用户将自己的权限授权给其它用户。

但请注意可能由于MySQL的数据库版本问题，有些版本不允许在创建用户时就给予权限，那么需要将创建和权限赋予分为两步:

createuser'admin'@'%' identified by'123456'-- 创建用户GRANTallprivilegesON*.*TO'admin'@'%'-- 将所有权限赋予给admin

一般来说我们不会轻易创建一个用户给他赋予所有权限，也就是 grant all privileges，那么grant后面可以接哪些权限呢？这里简单整理了一部分权限以及其含义：
权限意义ALL [PRIVILEGES]设置除GRANT OPTION之外的所有简单权限ALTER允许使用ALTER TABLEALTER ROUTINE更改或取消已存储的子程序CREATE允许使用CREATE TABLECREATE USER允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。CREATE VIEW允许使用CREATE VIEWDELETE允许使用DELETEDROP允许使用DROP TABLEINDEX允许使用CREATE INDEX和DROP INDEXINSERT允许使用INSERTSELECT允许使用SELECTSHOW DATABASESSHOW DATABASES显示所有数据库SHOW VIEW允许使用SHOW CREATE VIEWSHUTDOWN允许使用mysqladmin shutdownUPDATE允许使用UPDATEGRANT OPTION允许授予权限
以上表格举例了部分权限以及其含义，如果你想给一个账号赋予多个权限，可以用英文逗号分开，比如

Grant SELECT,UPDATE,DROP

等，如果想连接更多信息，欢迎点击Mysql账号管理查看

（2）删除用户

刚刚我们已经创建了一个admin账号并且没有为其设置任何权限，此时我们可以使用

DROP USER

命令删除某个用户，例如我们要删除之前创建的admin用户，那么我们可以使用命令:

DROPUSER'admin'@'%'-- 删除admin用户，用户名(User)和主机名(Host)构成
FLUSH PRIVILEGES​;-- 刷新权限

此时user表中的admin用户就删除了。

（3）禁用用户、启用用户

有时候我们只想禁用某个用户的登录，并不想将该用户进行删除，那么可以使用MySQL的禁用功能。在演示之前我们现在创建一个admin用户，该用户拥有访问数据库(db_cztl_dev)的权限，请注意db_cztl_dev是在我MySQL中存在的一个数据库，您可以使用您自己的数据库。执行命令:

createuser'admin'@'%' identified by'123456'-- 创建用户GRANTSELECTON`db_cztl_dev`.*TO'admin'@'%'-- 将db_cztl_dev数据库的读权限赋予admin

此时user表会有admin账号的信息，我们可以看到user表里的admin的select_priv是为N，因为admin现在的权限只是能访问某一个数据库，而非全部数据库，所以user表里的select_pirv是为N，但是db表(

数据库层级

)会在db_cztl_dev那条数据的select_priv为N。


表示admin可以访问数据库db_cztl_dev，拥有该数据库下的所有SELECT权限，让我们用admin账号登录，就可以看到db_cztl_dev数据库。

如果我们要禁用admin账号，可以输入命令:

ALTERUSER'admin'@'%' ACCOUNT lock;-- 禁用admin账号
FLUSH PRIVILEGES;-- 刷新权限

此时重新使用admin用户登录，就可以提示以下信息:

解除admin禁用可以使用命令:

ALTERUSER'admin'@'%' ACCOUNT UNLOCK;--解除admin用户
FLUSH PRIVILEGES;-- 刷新权限

（4）重命名用户名

修改用户名的格式如下：

renameuser'用户名'@'IP或者域名'to'新用户名'@'IP或者域名';

比如我们要将admin用户重名为king，那么可以这样写：

renameuser'admin'@'%'to'king'@'%';

（5）修改用户密码

set password for'用户名'@'IP或者域名'=password('新密码')

（6）收回权限(revoke)

根据上面几小节我们指定如何给一个用户赋予权限可以用Grant命令，相对应的撤销用户的某个权限可以使用REVOKE。

（6.1）查看数据库中所有用户信息

我们知道用户的基本信息都存放在user表中，那么查看所有用户信息可以使用以下语句:

SELECTuser,host FROM mysql.`user`;

（6.2）查看用户拥有的所有权限

查看某个用户的权限可以使用

show grants for 用户名

，比如我们要查看admin用户的权限有哪些:

SHOW GRANTS FOR'admin'@'%'

可以看到admin用户拥有查看数据库db_cztl_dev下所有资源的权限。

（6.3）使用REVOKE撤销权限

由4.2小节我们可以看到admin拥有访问db_cztl_dev数据库的权限，假如我们此时想撤销admin的权限，可以这样写:

REVOKESELECTON db_cztl_dev.*FROM'admin'@'%';-- 撤销admin在数据db_cztl_dev的权限

此时我们再使用

SHOW GRANTS FOR 'admin'@'%'

查看admin权限，可以看到admin访问db_cztl_dev数据库的权限已被撤销。

（7）修改用户权限(Grant 权限类型 ON 数据库.表 TO 用户)

通过6.3我们已经撤销了admin查看db_cztl_dev的权限，如果此时我们想赋予admin用户可以访问并且修改db_cztl_dev库下cztl_line表的权限，可以这样做:

GRANTSELECT,UPDATEON db_cztl_dev.cztl_line TO'admin'@'%';
FLUSH PRIVILEGES;

重新使用admin账号登录数据库，就可以访问和修改db_cztl_dev库下的cztl_line表。

如果想让admin用户能访问整个db_cztl_dev库下所有资源，可以追加权限：

GRANTSELECTON`db_cztl_dev`.*TO'admin'@'%'

重新admin登录就可以查到所有表

（8） 设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户，可以通过show variables like ‘default_password_lifetime’;查看密码过期策略，0表示用不过期，可以通过 set global default_password_lifetime=180;设置密码过期时间为180天。

（8.1）手动强制某个用户密码过期

ALTERUSER'用户名'@'IP或者域名' PASSWORD EXPIRE;

假如我们要将admin账号强制密码过期，可以这样做:

ALTERUSER'admin'@'%' PASSWORD EXPIRE;--强制admin用户密码过期

重启使用admin登录时，会弹出重置密码界面。

总结

以上就是我对MySQL用户权限分配的总结和分享，如果有任何意见或者错误欢迎大家及时提出，如果有疑问也欢迎留言一起讨论交流！