MySQL中的权限管理
- 1.授予权限
- 2.回收权限
- 3.刷新权限
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”重新加载用户的权限。