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表一样，不同之处是它提供的是针对某些表的特定列的权限 |
权限表的处理过程
- 先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证。
- 通过权限验证，进行权限分配时，按照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线程权限 |
