32.MySQL中的权限管理

1.授予权限

权限信息根据其作用范围，分别存储在mysql数据库中的不同数据表中。
当MySQL启动时会自动加载这些权限信息并将这些权限信息读取到内存中。

根据权限的操作内容可将权限大致分为数据权限、结构权限以及管理权限。

数据权限

结构权限

管理权限

权限级别指的就是权限可以被应用在哪些数据库的内容中。
例如，SELECT权限可以被授予到全局（任意数据库下的任意内容）、数据库（指定数据库下的任意内容）、表（指定数据库下的指定数据表）、列（指定数据库下的指定数据表中的指定字段）。
语法：

GRANT 权限类型 [字段列表][, 权限类型 [字段列表]] ...
ON [目标类型] 权限级别
TO 账户名 [用户身份验证选项] [, 账户名 [用户身份验证选项]] ...
[REQUIRE 连接方式]
[WITH {GRANT OPTION | 资源控制选项}]

权限类型：指的就是SELECT、DROP、CREATE等权限。

字段列表：用于设置列权限。

目标类型：默认为TABLE，表示将全局、数据库、表或列中的某些权限授予给指定的用户。其他值为FUNCTION（函数）或PROCEDURE（存储过程）。

权限级别：用于定义全局权限、数据库权限和表权限。

添加GRANT OPTION：表示当前账户可以为其他账户进行授权。

其余各参数均与CREATE USER中的用户选项相同，这里不再赘述。

查看root用户的授权情况

SHOW GRANTS FOR 'root'@'localhost';

ALL PRIVILEGES表示除GRANT OPTION（授权权限）和PROXY（代理权限）外的所有权限。

USAGE表示没有任何权限。

ON后的*.*表示全局级别的权限，即MySQL服务器下的所有数据库下的所有表，‘@‘表示任何主机中的匿名用户。

在为用户授予权限时，可以分为6个不同的级别的语法。

全局权限：GRANT权限列表 ON . TO 账户名[WITH GRANT OPTION];

数据库级权限：GRANT权限列表 ON 数据库名.* TO 账户名[WITH GRANT OPTION];

表级权限：GRANT权限列表 ON 数据库名.表名 TO 账户名[WITH GRANT OPTION];

列级权限：GRANT 权限类型 (字段列表) [,…]ON 数据库名.表名 TO 账户名[WITH GRANT OPTION];

存储过程权限：GRANT EXECUTE|ALTER ROUTINE|CREATE ROUTINE ON {[.|数据库名.* ]|PROCEDURE 数据库名.存储过程} TO 账户名 [WITH GRANT OPTION];

代理权限：GRANT PROXY ON 账户名 TO 账户名1 [, 账户名2] …[WITH GRANT OPTION]

要想使用GRANT语句为用户授权，必须要拥有GRANT OPTION权限；且在启用read_only系统变量时，还必须要拥有SUPER权限。

授予test1用户 mahaiwuji.stu表的SELECT权限，以及对id和name字段的插入权限

GRANT SELECT,INSERT (id, name)
ON mahaiwuji.stu
TO 'test1'@'%';

查看权限的保存情况

SELECT db,table_name,table_priv,column_priv 
FROM mysql.tables_priv WHERE user = 'test1';

默认在MySQL5.7中，当GRANT 语句中指定的帐户不存在时，系统不支持自动创建用户，它会报一个在user表中找不到用户的提示信息。

例如，在为user表中不存在的test9用户授予SELECT权限。

GRANT SELECT ON *.* TO 'test9'@'localhost';

为了解决上述的问题，只需确保MySQL中 NO_AUTO_CREATE_USER模式未开启，就可以利用GRANT自动创建一个不存在的用户。

清空默认SQL模式

SET sql_mode = '';

授予权限时，创建不存在的用户

GRANT SELECT ON *.* TO 'test9'@'localhost';

将SQL模式修改为MySQL默认的值：

SET sql_mode = @@global.sql_mode;

使用GRANT创建新用户的方式已被废弃，并在未来会被移出。因此，MySQL官方推荐使用CREATE USER语句创建用户，使用ALTER USER语句修改用户的非权限选项（如验证插件、资源控制选项等），使用GRANT为新用户授予权限。

2.回收权限

在MySQL中，为了保证数据库的安全性，需要将用户不必要的权限回收。

例如，数据管理员发现某个用户不应该具有DELETE权限，就应该及时将其收回。为此，MySQL专门提供了一个REVOKE语句用于回收指定用户的权限。

# 回收指定用户的指定权限
REVOKE 权限类型 [(字段列表)] [, 权限类型[(字段列表)]] …
ON [目标类型] 权限级别 FROM 账户名 [, 账户名] …
# 回收表7-7中的所有权限以及可为其他用户授权的权限
REVOKE ALL [PRIVILEGES], GRANT OPTION FROM 账户名 [, 账户名] …
# 回收用户的代理权限
REVOKE PROXY ON 账户名 FROM 账户名1 [, 账户名2] …

回收test1用户的插入权限

REVOKE INSERT (name, price)
ON my_sql.stu FROM 'test1'@'%';

3.刷新权限

刷新权限：指的是从系统数据库mysql中的权限表中重新加载用户的权限。

原因在于：GRANT、CREATE USER等操作会将服务器的缓存信息保存到内存中，而REVOKE、DROP USER操作并不会同步到内存中，因此可能会造成服务器内存的消耗，所以在REVOKE、DROP USER后推荐读者使用MySQL提供的“FLUSH PRIVILEGES”重新加载用户的权限。