MySQL中的权限管理
权限信息根据其作用范围，分别存储在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权限可以被授予到全局（任意数据库下的任意内容）、数据库（指定数据库下的任意内容）、表（指定数据库下的指定数据表）、列（指定数据库下的指定数据表中的指定字段）。
语法
1 GRANT 权限类型 [字段列表][, 权限类型 [字段列表]] ... 2 ON [目标类型] 权限级别 3 TO 账户名 [用户身份验证选项] [, 账户名 [用户身份验证选项]] ... 4 [REQUIRE 连接方式] 5 [WITH {GRANT OPTION | 资源控制选项}]
权限类型：指的就是SELECT、DROP、CREATE等权限。
字段列表：用于设置列权限。
目标类型：默认为TABLE，表示将全局、数据库、表或列中的某些权限授予给指定的用户。其他值为FUNCTION（函数）或PROCEDURE（存储过程）。
权限级别：用于定义全局权限、数据库权限和表权限。
添加GRANT OPTION：表示当前账户可以为其他账户进行授权。
其余各参数均与CREATE USER中的用户选项相同，这里不再赘述。
查看root用户和test1用户的授权情况
1 SHOW GRANTS FOR 'root'@'localhost';
1 SHOW GRANTS FOR 'test1'@'%';
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字段的插入权限
1 GRANT SELECT,INSERT (id, name) 2 ON mahaiwuji.stu 3 TO 'test1'@'%';
查看权限的保存情况
1 SELECT db,table_name,table_priv,column_priv 2 FROM mysql.tables_priv WHERE user = 'test1';
1 SELECT db,table_name,column_name,column_priv 2 FROM mysql.columns_priv WHERE user='test1';
默认在MySQL5.7中，当GRANT 语句中指定的帐户不存在时，系统不支持自动创建用户，它会报一个在user表中找不到用户的提示信息。
例如，在为user表中不存在的test9用户授予SELECT权限。
1 GRANT SELECT ON *.* TO 'test9'@'localhost';
为了解决上述的问题，只需确保MySQL中 NO_AUTO_CREATE_USER模式未开启，就可以利用GRANT自动创建一个不存在的用户。
清空默认SQL模式
1 SET sql_mode = '';
授予权限时，创建不存在的用户
1 GRANT SELECT ON *.* TO 'test9'@'localhost';
将SQL模式修改为MySQL默认的值：
1 SET sql_mode = @@global.sql_mode;
使用GRANT创建新用户的方式已被废弃，并在未来会被移出。因此，MySQL官方推荐使用CREATE USER语句创建用户，使用ALTER USER语句修改用户的非权限选项（如验证插件、资源控制选项等），使用GRANT为新用户授予权限。
2.回收权限
在MySQL中，为了保证数据库的安全性，需要将用户不必要的权限回收。
例如，数据管理员发现某个用户不应该具有DELETE权限，就应该及时将其收回。为此，MySQL专门提供了一个REVOKE语句用于回收指定用户的权限。
1 # 回收指定用户的指定权限 2 REVOKE 权限类型 [(字段列表)] [, 权限类型[(字段列表)]] … 3 ON [目标类型] 权限级别 FROM 账户名 [, 账户名] … 4 # 回收表7-7中的所有权限以及可为其他用户授权的权限 5 REVOKE ALL [PRIVILEGES], GRANT OPTION FROM 账户名 [, 账户名] … 6 # 回收用户的代理权限 7 REVOKE PROXY ON 账户名 FROM 账户名1 [, 账户名2] …
回收test1用户的插入权限
1 REVOKE INSERT (name, price) 2 ON mahaiwuji.stu FROM 'test1'@'%';
3.刷新权限
刷新权限：指的是从系统数据库mysql中的权限表中重新加载用户的权限。
原因在于：GRANT、CREATE USER等操作会将服务器的缓存信息保存到内存中，而REVOKE、DROP USER操作并不会同步到内存中，因此可能会造成服务器内存的消耗，所以在REVOKE、DROP USER后推荐读者使用MySQL提供的“FLUSH PRIVILEGES”重新加载用户的权限。
1 FLUSH PRIVILEGES;