前言

Github:https://github.com/HealerJean

博客:http://blog.healerjean.com

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


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

用户权限管理作用: 

  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'

WX20180319-101525

1.、资源控制列

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

2、db表

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

WX20180319-102641

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:权限的设置者

WX20180319-105812



columns_priv:可以对单个数据列进行权限设置,有7个列,作用同上:    Host、Db、User、Table_name、Column_name、Column_priv、Timestamp。

  WX20180319-105943

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;
s
GRANT SELECT ON *.* TO netUser@'%'



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

GRANT select,insert,update,delete ON talbename.* to 'hlj_sql'@'127.0.0.%' IDENTIFIED BY '123456';

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')

ContactAuthor