Mysql之用户认证授权管理

概述

Mysql的认证采用账号密码方式，其中账号由两个部分组成:Host和User；Host为允许登录的客户端Ip，User为当前登录的用户名。

授权没有采用典型的RBAC(基于角色的访问控制),而是每个用户关联一个资源权限列表。

认证信息存放在mysql.user表中，其中还包含了用户的全局权限。

授权信息主要存放在mysql.*_priv系列表中，提供了数据库，表，行等各个粒度的权限控制。

本文使用的Mysql版本为5.7.24。

认证

用户认证信息存放在mysql.user表中，每条记录为一个用户，(Host,User)为唯一索引,除此之外还有用户全局权限和一些控制数据如密码过期时间等。

可以像操作普通表一样修改user表数据，但是密码字段需要额外的命令修改。

新增用户

创建用户同时设置密码：CREATE USER 'userName'@'host' IDENTIFIED BY 'password'

创建用户但是不设置密码：CREATE USER 'userName'

认证信息四元组为：host,userName,pwd,authentication_string。

(host,userName)受唯一索引约束,两者共同标识唯一用户。

host为当前用户登录的终端主机ip，支持通配符_和%；通配符使用规则和like模糊匹配中用法一致。不限制登录终端ip设为%。

HOST的形式：1. IP地址，如172.16.16.1；2. 主机名，如localhost；3. 网络地址，如172.16.0._ 允许的host区间为172.16.0.1-172.16.0.9

删除用户

直接删除user表记录:Delete FROM user Where User='test' and Host='localhost';

使用drop命令：drop user 'jack'@'localhost';

修改密码

用户密码使用密文保存，散列后存在user表中。可以用password('passwd')函数来生成密文密码。

直接操作数据库：set authentication_string=password('plaintext_password') where User = 'userNmae' and Host = 'host'

使用修改密码命令：SET PASSWORD FOR 'fred'@'localhost' = 'plaintext_password';

用户重命名

rename user 'jack'@'%' to 'jim'@'%';

授权

授权检查流程 :

第一阶段：服务器首先会检查你是否允许连接。因为创建用户的时候会加上主机限制，可以限制成本地、某个IP、某个IP段、以及任何地方等，只允许你从配置的指定地方登陆。

第二阶段：如果你能连接，Mysql会检查你发出的每个请求，看你是否有足够的权限实施它。比如你要更新某个表、或者查询某个表，Mysql会查看你对哪个表或者某个列是否有权限。再比如，你要运行某个存储过程，Mysql会检查你对存储过程是否有执行权限等

用户授权信息分为两部分：全局授权在user表中,各个类型数据库对象的授权在db、tables_priv、columns_priv等表中。

表	说明	详细
user	用户的全局权限	该表决定是否允许用户连接到服务器。如果允许连接，权限字段则为该用户的全局权限
db	用户对数据的权限	用于决定哪些用户可以从哪些主机访问哪些数据库。包含在db表中的权限适用于这个表标识的数据库
tables_priv	用户对数据表的权限	该表与db表相似，不同之处是它用于表而不是数据库。这个表还包含一个其他字段类型，包括timestamp和grantor两个字段，用于存储时间戳和授权方。在本文后面我们会对这个表做进一步的讲解
columns_priv	用户对数据列的权限	该表作用几乎与db和tables_priv表一样，不同之处是它提供的是针对某些表的特定列的权限

权限表的处理过程

1. 先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证。
2. 通过权限验证，进行权限分配时，按照user、db、tables_priv、columns_priv的顺序进行分配。
   即先检查全局权限表 user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；
   如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检 查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

grant授权语句

grant all privileges on db.table to 'fred'@'localhost'

ALL PRIVILEGES 是表示所有权限，你也可以使用select、update等权限,见下文权限类型。

ON 用来指定权限针对哪些库和表。

. 中前面的号用来指定数据库名，后面的号用来指定表名。

TO 表示将权限赋予某个用户。

jack@’localhost’ 表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。
注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。
注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

备注：可以使用GRANT重复给用户添加权限，权限叠加。
比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。

刷新权限

flush privileges;

使用这个命令使权限生效，尤其是你对那些权限表user、db、host等做了update或者delete更新的时候。以前遇到过使用grant后权限没有更新的情况，只要对权限做了更改就使用FLUSH PRIVILEGES命令来刷新权限。

查看权限

show grants 查看当前用户的权限

show grants for 'jack'@'%' 查看某个用户的权限

回收权限

revoke delete on *.* from 'jack'@'localhost';

权限分布

权限分布	可能的设置的权限
表权限	‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’, ‘Grant’, ‘References’, ‘Index’, ‘Alter’
列权限	‘Select’, ‘Insert’, ‘Update’, ‘References’
过程权限	‘Execute’, ‘Alter Routine’, ‘Grant’

权限类型

权限	权限级别	权限说明
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
REFERENCES	数据库或表
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	服务器主机上的文件访问	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
PROCESS	服务器管理	查看进程权限
RELOAD	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT	服务器管理	复制权限
REPLICATION SLAVE	服务器管理	复制权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限

引用

MySQL之权限管理

[玩转MySQL之三]MySQL用户及权限

MySQL用户管理