mysql用户管理权限分配详解
前言
如果想成为一个技术总监，技术经理。我觉得对于数据库用户的权限分配还是很重要的。比如，阿里巴巴，一个刚来的实习生，他可能连真实数据库都接触不到。等到终于熬成可以操作下数据库了，但是发现自己还是只有读取权限，并没有其他任何对数据库造成影响的权限。
这就是技术经理，技术总监。技术组长。所做的事情，维护好公司的利益，最为重要。
用户权限管理作用：
- 可以限制用户访问哪些库、哪些表
- 可以限制用户对哪些表执行CREATE、DELETE、(ALTER、UPDATE)、SELECT等操作
- 可以限制用户登录的IP或域名
- 可以限制用户自己的权限是否可以授权给别的用户
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 PRIVILEGES4.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')
如果满意，请打赏博主任意金额，感兴趣的请下方留言吧。可与博主自由讨论哦
| 支付包 | 微信 | 微信公众号 |
|---|---|---|
 |
 |
 |
