MySQL用户授权、收回权限与查看权限

【图书推荐】《MySQL 9从入门到性能优化（视频教学版）》-CSDN博客

《MySQL 9从入门到性能优化（视频教学版）（数据库技术丛书）》(王英英)【摘要书评试读】- 京东图书 (jd.com)

MySQL9数据库技术_夏天又到了的博客-CSDN博客

MySQL新建和删除普通用户-CSDN博客

13.3.2 授权

授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授予权限。授予的权限可以分为以下5个层级：

1. 全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在MySQL.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。

2. 数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在MySQL.db和MySQL.host表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

3. 表层级

表权限适用于一个给定表中的所有列。这些权限存储在MySQL.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。

4. 列层级

列权限适用于一个给定表中的单一列。这些权限存储在MySQL.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。

5. 子程序层级

CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。除了CREATE ROUTINE之外，这些权限还可以被授予子程序层级，并存储在MySQL.procs_priv表中。CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限用于管理已存储的子程序。这些权限可以被授予为全局层级（GLOBAL）和数据库层级（DATABASE）。

除了CREATE ROUTINE 权限外，其他权限（ALTER ROUTINE、EXECUTE和GRANT）还可以被授予子程序层级（PROCEDURE），并存储在MySQL.procs_priv表中。

在MySQL中，要使用GRANT或REVOKE，必须拥有GRANT OPTION权限，并且必须用于正在授予或撤销的权限。GRANT的语法如下：

GRANT priv_type [(columns)] [, priv_type [(columns)]] ...

ON [object_type]  table1, table2,…, tablen

TO user  [WITH GRANT OPTION]



object_type = TABLE  |  FUNCTION  |  PROCEDURE

其中，priv_type参数表示权限类型；columns参数表示权限作用于哪些列上，不指定该参数，表示作用于整张表；table1,table2,…,tablen表示授予权限的列所在的表；object_type指定授权作用的对象类型包括TABLE（表）、FUNCTION（函数）和PROCEDURE（存储过程），当从旧版本的MySQL升级时，要使用object_tpye子句，必须升级授权表；user参数表示用户账户，由用户名和主机名构成，形式是“'username'@'hostname'”。

WITH关键字后可以跟一个或多个with_option参数。这个参数有5个选项，意义如下：

（1）GRANT OPTION：被授权的用户可以将这些权限赋予别的用户。

（2）MAX_QUERIES_PER_HOUR count：设置每小时可以执行count次查询。

（3）MAX_UPDATES_PER_HOUR count：设置每小时可以执行count次更新。

（4）MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。

（5）MAX_USER_CONNECTIONS count：设置单个用户可以同时建立count个连接。

【例13.9】创建一个新的用户grantUser。使用GRANT语句对用户grantUser赋予所有的表进行数据的查询、插入权限，并授于GRANT权限。GRANT语句及其执行结果如下：

MySQL> CREATE USER 'grantUser'@'localhost' IDENTIFIED BY 'mypass';

MySQL> GRANT SELECT,INSERT ON *.* TO 'grantUser'@'localhost' WITH GRANT OPTION;

Query OK, 0 rows affected (0.03 sec)

结果显示执行成功。使用SELECT语句查询用户grantUser的权限：

MySQL> SELECT Host,User,Select_priv,Insert_priv, Grant_priv FROM mysql.user where user='grantUser';
+-----------+------------+-------------+--------------+-------------+
| Host      | User       | Select_priv | Insert_priv  | Grant_priv  |
+-----------+------------+-------------+--------------+-------------+
| localhost | grantUser  | Y           | Y            | Y           |
+-----------+------------+-------------+--------------+-------------+
1 row in set (0.00 sec)

查询结果显示用户grantUser被创建成功，并被赋予SELECT、INSERT和GRANT权限，其相应字段值均为“Y”。被授予GRANT权限的用户可以登录MySQL并创建其他用户账户。

13.3.3 收回权限

收回权限就是将赋予用户的某些权限取消。收回用户不必要的权限，可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户拥有的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是仍然在user表中保存（删除user表中的账户记录，要使用DROP USER语句，在13.2.3节已经介绍）。

在将用户账户从user表中删除之前，应该收回相应用户的所有权限。REVOKE语句有两种语法格式。

（1）第一种是收回所有用户的所有权限，用于取消已命名用户的所有全局层级、数据库层级、表层级和列层级的权限，具体如下：

REVOKE ALL PRIVILEGES, GRANT OPTION

FROM 'user'@'host' [, 'user'@'host' ...]

REVOKE语句必须和FROM语句一起使用。FROM语句指明需要收回权限的账户。

（2）第二种为长格式的REVOKE语句，基本语法如下：

REVOKE priv_type [(columns)] [, priv_type [(columns)]] ...

ON  table1, table2,…, tablen

FROM 'user'@'host'[, 'user'@ 'host' ...]

该语法收回指定的权限。其中，priv_type参数表示权限类型；columns参数表示权限作用于哪些列上，如果不指定该参数，则表示作用于整张表；table1,table2,…,tablen表示从哪张表中收回权限；'user'@'host'参数表示用户账户，由用户名和主机名构成。

要使用REVOKE语句，必须拥有MySQL数据库的全局CREATE USER权限或UPDATE权限。

【例13.11】使用REVOKE语句取消用户grantUser的查询权限。REVOKE语句及其执行结果如下：

MySQL> REVOKE Select ON *.* FROM 'grantUser'@'localhost';

Query OK, 0 rows affected (0.00 sec)

执行结果显示执行成功。使用SELECT语句查询用户grantUser的权限：

mysql> SELECT Host,User,Select_priv,Insert_priv,Grant_priv FROM MySQL.user where user='grantUser';
+-----------+-----------+-------------+-------------+------------+
| Host      | User      | Select_priv | Insert_priv | Grant_priv |
+-----------+-----------+-------------+-------------+------------+
| localhost | grantUser | N           | Y           | Y          |
+-----------+-----------+-------------+-------------+------------+

查询结果显示用户grantUser的Select_priv字段值为“N”，说明SELECT权限已经被收回。

13.3.4 查看权限

使用SHOW GRANTS语句可以显示指定用户的权限信息，基本语法格式如下：

SHOW GRANTS FOR 'user'@ 'host' ;

其中，user表示登录用户的名称，host表示登录的主机名称或者IP地址。在使用该语句时，要确保指定的用户名和主机名都要用单引号引起来，并使用“@”符号将两个名字分隔开。

【例13.12】使用SHOW GRANTS语句查询用户grantUser的权限信息。SHOW GRANTS语句及其执行结果如下：

MySQL> SHOW GRANTS FOR 'grantUser'@'localhost';
+------------------------------------------------------------------+
| Grants for grantUser@localhost                                   |
+------------------------------------------------------------------+
| GRANT INSERT ON *.* TO `grantUser`@`localhost` WITH GRANT OPTION |
+------------------------------------------------------------------+

返回的结果显示了grantUser表中的账户信息。接下来的行以“GRANT INSERT ON”关键字开头，表示用户被授予了INSERT权限；*.*表示INSERT权限作用于所有数据库的所有数据表。

在这里，只是定义了个别的用户权限，GRANT可以显示更加详细的权限信息，包括全局级的和非全局级的权限，如果表层级或者列层级的权限被授予用户，那么它们也能在结果中显示出来。

在前面创建用户时，查看新建的账户使用的是SELECT语句，也可以通过SELECT语句查看user表中的各个权限字段以确定用户的权限信息，其基本语法格式如下：

SELECT privileges_list FROM user WHERE user='username', host= 'hostname';

其中，privileges_list为想要查看的权限字段，可以为Select_priv、Insert_priv等。读者可以根据需要选择要查询的字段。