mysql用户管理权限分配详解

前言

如果想成为一个技术总监，技术经理。我觉得对于数据库用户的权限分配还是很重要的。比如，阿里巴巴，一个刚来的实习生，他可能连真实数据库都接触不到。等到终于熬成可以操作下数据库了，但是发现自己还是只有读取权限，并没有其他任何对数据库造成影响的权限。

---

这就是技术经理，技术总监。技术组长。所做的事情，维护好公司的利益，最为重要。

用户权限管理作用：

1. 可以限制用户访问哪些库、哪些表
2. 可以限制用户对哪些表执行CREATE、DELETE、(ALTER、UPDATE)、SELECT等操作
3. 可以限制用户登录的IP或域名
4. 可以限制用户自己的权限是否可以授权给别的用户

1、user表的组成

mysql中所有的用户都是存放在user表中的，user表有39个字段。这些字段可以分为4类：
• 用户列；
• 权限列；
• 安全列；
• 资源控制列；

1.1、用户列

用户列包括Host、User、Password，
　　User：要创建用户的名字。
　　Host：主机名：表示要这个新创建的用户允许从哪台机登陆
　　Password：新创建用户的登陆数据库密码，如果没密码可以不写。

1.2、权限列

user表的权限列是以priv结尾的字段。
　　这些字段的值只有Y和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服务器。在将此权限提供给root账户之外的任何用户时，都应当非常谨慎
Process_priv	确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv	确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv	确定用户是否可以将已经授予给该用户自己的权限再授予其他用户
References_priv	目前只是某些未来功能的占位符；现在没有作用
Index_priv	确定用户是否可以创建和删除表索引
Alter_priv	确定用户是否可以重命名和修改表结构
Show_db_priv	确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库
Super_priv	确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令
Create_tmp_table_priv	确定用户是否可以创建临时表
Lock_tables_priv	确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv	确定用户是否可以执行存储过程
Repl_slave_priv	确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信
Repl_client_priv	确定用户是否可以确定复制从服务器和主服务器的位置
Create_view_priv	确定用户是否可以创建视图
Show_view_priv	确定用户是否可以查看视图或了解视图如何执行
Create_routine_priv	确定用户是否可以更改或放弃存储过程和函数
Alter_routine_priv	确定用户是否可以修改或删除存储函数及函数
Create_user_priv	确定用户是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户
Event_priv	确定用户能否创建、修改和删除事件
Trigger_priv	确定用户能否创建和删除触发器

1.3、安全列

user表的安全列有4个字段：
• ssl_type；
• ssl_cipher；
• x509_issuer；
• x509_subject；
　　ssl用于加密；x509标准可以用来标识用户。普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE 'have_openssl’语句来查看是否具有ssl功能。如果取值为DISABLED，那么则没有ssl加密功能。

SHOW VARIABLES LIKE 'have_openssl'

1.、资源控制列

user表的4个资源控制列是：
• max_questions：每小时可以允许执行多少次查询；
• max_updates：每小时可以允许执行多少次更新；
• max_connections：每小时可以建立多少连接；
• max_user_connections：单个用户可以同时具有的连接数。
默认值为0，表示无限制。

2、db表

db表存储了某个用户对一个数据库的权限。
主要分为2个，用户列和权限列

2.1、用户列

db表的用户列有3个字段：
• Host：主机名；
• Db：数据库名；
• User：用户名；

2.2、权限列

举例
Create_routine_priv：是否具有创建存储过程权限；
Alter_routine_priv：是否具有修改存储过程权限；

2.3、user表和db表的关联以及级别关系

user表中的权限是针对所有数据库的，如果user表中的Select_priv字段取值为Y，正常情况下该用户可以查询所有数据库中的表；
　　假如最终结果是为某个用户值设置了查询一个库中表的权限，但是user表的Select_priv字段的取值为N。那么db表中的Select_priv字段的取值将会是Y。
　　
原因：用户先根据user表的内容获取权限，然后再根据db表的内容获取权限。

3、tables_priv表和columns_priv表

tables_priv：可以对单个表进行权限设置：
• tables_priv表包含8个字段：
• Host：主机名；
• DB：数据库名；
• User：用户名；
• Table_name：表名
• Table_priv：对表进行操作的权限

Table_priv：
set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')

• Column_priv：对表中的数据列进行操作的权限

set('Select','Insert','Update','References')

• Timestamp：修改权限的事件
• Grantor：权限的设置者

---

---

columns_priv：可以对单个数据列进行权限设置，有7个列，作用同上：

　　Host、Db、User、Table_name、Column_name、Column_priv、Timestamp。

　

　　

3.1、真正的权限分配顺序

MySQL权限分配是按照
user表(所有数据库)-> db表 (某个数据库)-> table_priv表(某个表) -> columns_priv表(某表的某列)的顺序进行分配的。

在数据库系统中，先判断user表中的值是否为’Y’，如果user表中的值是’Y’，就不需要检查后面的表。如果user表为N，则一次检查后面的表。

4、开始真正的账户管理吧，朋友们

4.1、新建普通用户

正常来说一共有三种方式
• 使用create user语句来创建新的用户；
• 直接在mysql.user表中INSERT用户；
• 使用grant语句来新建用户；

4.1.1、create user （建议使用）

使用create user语句创建用户，必须要拥有create user权限。

---

示例：

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin'

其中，user参数表示新建用户的账户，user由用户名(User)和主机名(Host)构成；IDENTIFIED BY关键字用来设置用户的密码；password参数表示用户的密码；
　　格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0 IP段登录
　　
　　执行之后user表会增加一行记录**，但权限暂时全部为‘N’。**

4.1.2、用INSERT语句新建普通用户（一般不用）

可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。
　　另外，ssl_cipher、x509_issuer、x509_subject没有值，必须要设置值，否则INSERT语句无法执行。
　　示例：

INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES('%','newuser1',PASSWORD('123456'),'','','')

执行INSERT之后，要使用下面的命令：来使用户生效。

FLUSH PRIVILEGES

4.1.3、用GRANT语句来新建普通用户 （一般用不到）

个人认为一般也用不上。如果需要用的话，百度下呗，或者后期如果博主用到的话，会继续再这里讲解的哦

4.2 删除用户

1、DROP USER语句删除普通用户

需要拥有DROP USER权限。语法如下：,user是需要删除的用户，由用户名(User)和主机名(Host)构成。

DROP USER 'admin'@'%'

2、DELETE语句删除普通用户

可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。

DELETE FROM mysql.user WHERE Host = '%' AND User = 'admin'

删除完成后，​​FLUSH PRIVILEGES​​ 进行生效

5、真实场景

5.1 查看数据库中所有的用户

muysql> select u.host,u.user from user u;

5.2 创建用户并授权

正常情况下实习生，只给select权限即可。

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

GRANT SELECT ON *.* TO netUser@'%'

all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。

on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的test表中，可以这么写：test.test。这里是针对的某个库的某个表。可以​​test.*​​ 表示test库中的所有表

to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。
identified by：指定用户的登录密码
with grant option：表示允许用户将自己的权限授权给其它用户

5.3 、刷新权限(flush)

对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

 mysql> flush privileges;

5.4、回收权限(revoke)

删除yangxin这个用户的create权限，该用户将不能创建数据库和表。

mysql> revoke create on *.* from 'yangxin@localhost';
mysql> flush privileges;

5.5、删除用户

mysql> select host,user from user;
+---------------+---------+
| host          | user    |
+---------------+---------+
localhost HealerJean
localhost mysql.session
localhost mysql.sys
localhost root

8 rows in set (0.00 sec)
mysql> drop user 'HealerJean'@'localhost';

5.6、用户重命名

mysql> rename user 'test3'@'%' to 'test1'@'%';

5.7、修改密码

mysql> set password for 'root'@'localhost'=password('123456')