32.MySQL中的权限管理

1.授予权限

权限信息根据其作用范围，分别存储在mysql数据库中的不同数据表中。

当MySQL启动时会自动加载这些权限信息，并将这些权限信息读取到内存中。

数据表	描述
user	保存用户被授予的全局权限
db	保存用户被授予的数据库权限
tables_priv	保存用户被授予的表权限
columns_priv	保存用户被授予的列权限
procs_priv	保存用户被授予的存储过程权限
proxies_priv	保存用户被授予的代理权限

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

数据权限

权限	权限级别	描述
SELECT	全局、数据库、表、列	SELECT
UPDATE	全局、数据库、表、列	UPDATE
DELETE	全局、数据库、表	DELETE
INSERT	全局、数据库、表、列	INSERT
SHOW DATABASES	全局	SHOW DATABASES
SHOW VIEW	全局、数据库、表	SHOW CREATE VIEW
PROCESS	全局	SHOW PROCESSLIST

结构权限

权限	权限级别	描述
DROP	全局、数据库、表	允许删除数据库、表和视图
CREATE	全局、数据库、表	创建数据库、表
CREATE ROUTINE	全局、数据库	创建存储过程
CREATE TABLESPACE	全局	允许创建、修改或删除表空间和日志文件组
CREATE TEMPORARY TABLES	全局、数据库	CREATE TEMPORARY TABLE
CREATE VIEW	全局、数据库、表	允许创建或修改视图
ALTER	全局、数据库、表	ALTER TABLE
ALTER ROUTINE	全局、数据库、存储过程	允许删除或修改存储过程
INDEX	全局、数据库、表	允许创建或删除索引
TRIGGER	全局、数据库、表	允许触发器的所有操作
REFERENCES	全局、数据库、表、列	允许创建外键

管理权限

权限	权限级别	描述
SUPER	全局	允许使用其他管理操作，如CHANGE MASTER TO等
CREATE USER	全局	CREATE USER、DROP USER、 RENAME USER 和REVOKEALL PRIVILEGES
GRANT OPTION	全局、数据库、表、存储过程、代理	允许授予或删除用户权限
RELOAD	全局	FLUSH操作
PROXY	全局	与代理的用户权限相同
REPLICATION CLIENT	全局	允许用户访问主服务器或从服务器
REPLICATION SLAVE	全局	允许复制从服务器读取的主服务器二进制日志事件
SHUTDOWN	全局	允许使用mysqladmin shutdown
LOCK TABLES	全局、数据库	允许在有SELECT表权限上使用LOCK TABLES

权限级别指的就是权限可以被应用在哪些数据库的内容中。

例如，SELECT权限可以被授予到全局（任意数据库下的任意内容）、数据库（指定数据库下的任意内容）、表（指定数据库下的指定数据表）、列（指定数据库下的指定数据表中的指定字段）。

语法

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

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

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

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

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

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

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

查看root用户和test1用户的授权情况

SHOW GRANTS FOR 'root'@'localhost';

s32_1.png

SHOW GRANTS FOR 'test1'@'%';

s32_2.png

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

s32_3.png

SELECT db,table_name,column_name,column_priv
FROM mysql.columns_priv WHERE user='test1';

s32_4.png

默认在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 mahaiwuji.stu FROM 'test1'@'%';

3.刷新权限

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

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

FLUSH PRIVILEGES;