5.2 管理权限
新创建的用户默认只有 USAGE 权限，只能连接数据库，而没有任何操作权限。使用 SHOW GRANTS 命令可以查看用户的权限：
mysql> SHOW GRANTS FOR dev01@localhost; ±------------------------------------------+ | Grants for dev01@localhost | ±------------------------------------------+ | GRANT USAGE ON . TO dev01@localhost | ±------------------------------------------+ 1 row in set (0.00 sec)
使用 GRANT 语句可以为用户授予权限。
5.2.1 授予权限
GRANT 语句基本语法如下：
GRANT privilege, …
ON privilege_level
TO account_name;
GRANT 语句支持一次授予多个权限，使用逗号进行分隔。
privilege_level 指定权限的作用级别，包括：
- 全局权限，作用于 MySQL 服务器中的所有数据库。全局权限使用
*.*表示，例如，以下语句授予 dev01@localhost 用户查询所有数据库中的所有表的权限：
mysql> GRANT SELECT -> ON . -> TO dev01@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±-------------------------------------------+ | Grants for dev01@localhost | ±-------------------------------------------+ | GRANT SELECT ON . TO dev01@localhost | ±-------------------------------------------+ 1 row in set (0.00 sec)
全局权限存储在 mysql.user 表中。
- 数据库权限，作用于指定数据库中的所有对象。数据库权限使用
db_name.*表示，例如，以下语句授予 dev01@localhost 用户查询数据库 world 中的所有表的权限：
mysql> GRANT ALL -> ON world.* -> TO dev01@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±---------------------------------------------------------+ | Grants for dev01@localhost | ±---------------------------------------------------------+ | GRANT SELECT ON . TO dev01@localhost | | GRANT ALL PRIVILEGES ON world.* TO dev01@localhost | ±---------------------------------------------------------+ 2 rows in set (0.00 sec)
数据库权限存储在 mysql.db 表中。
- 表权限，作用于指定表的所有列。数据库权限使用
db_name.table_name表示；如果不指定 db_name，使用默认数据库；如果没有默认数据库，将会返回错误。例如，以下语句授予 dev01@localhost 用户数据库 world 中country 表的增删改查权限：
mysql> GRANT SELECT, INSERT, UPDATE, DELETE -> ON world.country -> TO dev01@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±---------------------------------------------------------------------------------+ | Grants for dev01@localhost | ±---------------------------------------------------------------------------------+ | GRANT SELECT ON . TO dev01@localhost | | GRANT ALL PRIVILEGES ON world.* TO dev01@localhost | | GRANT SELECT, INSERT, UPDATE, DELETE ON world.country TO dev01@localhost | ±---------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
表权限存储在 mysql.tables_priv 表中。
- 列权限，作用于指定表的指定列。每个列权限都需要指定具体的列名。例如，以下语句授予 dev01@localhost 用户在 world.country 表中 code 和 name 字段的查询权限，以及 population 字段的修改权限：
mysql> GRANT SELECT(code, name), UPDATE(population) -> ON world.country -> TO dev01@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±---------------------------------------------------------------------------------------------------------------------------------+ | Grants for dev01@localhost | ±---------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT ON . TO dev01@localhost | | GRANT ALL PRIVILEGES ON world.* TO dev01@localhost | | GRANT SELECT, SELECT (code, name), INSERT, UPDATE, UPDATE (population), DELETE ON world.country TO dev01@localhost | ±---------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
列权限存储在 mysql.columns_priv 表中。
- 存储例程权限，作用于存储例程（函数和过程）。存储例程权限可以基于全局、数据库或者单个例程进行指定。以下语句授予 dev01@localhost 用户在数据库 world.country 中创建存储例程的权限：
mysql> GRANT CREATE ROUTINE -> ON world.* -> TO dev01@localhost; Query OK, 0 rows affected (0.02 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for dev01@localhost | ±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT ON . TO dev01@localhost | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, EVENT, TRIGGER ON world.* TO dev01@localhost | | GRANT SELECT, SELECT (code, name), INSERT, UPDATE, UPDATE (population), DELETE ON world.country TO dev01@localhost | ±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
存储例程权限存储在 mysql.procs_priv 表中。
- 代理用户权限，允许用户作为其他用户的代理。代理用户拥有被代理用户的所有权限。以下语句将 dev01@localhost 用户设置为 root 用的代理：
mysql> GRANT PROXY -> ON root -> TO dev01@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for dev01@localhost | ±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT ON . TO dev01@localhost | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, EVENT, TRIGGER ON world.* TO dev01@localhost | | GRANT SELECT, SELECT (code, name), INSERT, UPDATE, UPDATE (population), DELETE ON world.country TO dev01@localhost | | GRANT PROXY ON ‘root’@‘%’ TO ‘dev01’@‘localhost’ | ±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
代理用户权限存储在 mysql.proxies_priv 表中。
5.2.2 撤销权限
REVOKE 语句执行与 GRANT 语句相反的操作，撤销授予用户的权限。
REVOKE privilegee, … ON privilege_level FROM account_name;
撤销权限的参数与授予权限时类似，以下语句撤销用户 dev01@localhost 所有的权限：
mysql> REVOKE ALL, GRANT OPTION -> FROM dev01@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±-------------------------------------------------+ | Grants for dev01@localhost | ±-------------------------------------------------+ | GRANT USAGE ON . TO dev01@localhost | | GRANT PROXY ON ‘root’@‘%’ TO ‘dev01’@‘localhost’ | ±-------------------------------------------------+ 2 rows in set (0.00 sec)
代理用户权限需要单独撤销：
mysql> REVOKE PROXY -> ON root -> FROM dev01@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR dev01@localhost; ±------------------------------------------+ | Grants for dev01@localhost | ±------------------------------------------+ | GRANT USAGE ON . TO dev01@localhost | ±------------------------------------------+ 1 row in set (0.00 sec)
用户 dev01@localhost 又恢复了初始的权限。
对于全局级别的权限，REVOKE 的效果在用户下次登录时生效；对于数据库级别的权限，REVOKE 的效果在执行 USE 命令后生效；对于表级或者字段级别的权限，REVOKE 的效果随后的查询立即生效。
5.3 管理角色
当用户越来越多时，权限的管理也越来越复杂；而实际上，许多用户需要相同或类似的权限。为此，MySQL 8.0 引入了一个新的特性：角色（Role）。角色是一组权限的集合。
与账户类似，角色也可以授予权限；但是角色不能用于登录数据库。通过角色为用户授权的步骤如下：
- 创建一个角色；
- 为角色授权权限；
- 为用户指定角色。
5.3.1 创建角色
假设我们的应用需要使用 world 数据库。开发人员需要该数据库的完全访问权限，测试人员需要表的读写权限，业务分析人员需要查询数据的权限。
首先，使用CREATE ROLE语句创建 3 个角色：
mysql> CREATE ROLE devp_role, read_role, write_role;
Query OK, 0 rows affected (0.02 sec)
角色名称和账户名称类似，也可以包含 role_name 和 host_name 两部分，使用 @ 连接。
此时如果查询用户表：
mysql> SELECT host,user,authentication_string FROM mysql.user; ±----------±-----------------±-----------------------------------------------------------------------+ | host | user | authentication_string | ±----------±-----------------±-----------------------------------------------------------------------+ | % | devp_role | | | % | read_role | | | % | write_role | | | localhost | dev01 | $A005 005005lw58QcU;QI|L`ktULChFhIVFxy5dsYrYmEhJkJqko4mezqefUFyT0zgyE2 | | localhost | mysql.infoschema | $A005 005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A005 005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A005 005005THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A005 005005kDqbW(q*0Uev;TyKgUe56D9KXiFzPtrSGVxKjvM23CYN5pgE9dLrO0eT8 | ±----------±-----------------±-----------------------------------------------------------------------+ 8 rows in set (0.00 sec)
可以看出，角色实际上也是一个用户，但是没有密码。
5.3.2 为角色授权
为角色授权和用户授权类似，也是使用 GRANT 语句。我们分别为上面的 3 个角色分配权限：
mysql> GRANT ALL ON world.* TO devp_role; Query OK, 0 rows affected (0.01 sec) mysql> GRANT SELECT ON world.* TO read_role; Query OK, 0 rows affected (0.01 sec) mysql> GRANT INSERT, UPDATE, DELETE ON world.* TO write_role; Query OK, 0 rows affected (0.01 sec) 复制代码 查看角色的权限和查询用户的权限类似： mysql> SHOW GRANTS FOR devp_role; ±-----------------------------------------------------+ | Grants for devp_role@% | ±-----------------------------------------------------+ | GRANT USAGE ON . TO devp_role@% | | GRANT ALL PRIVILEGES ON world.* TO devp_role@% | ±-----------------------------------------------------+ 2 rows in set (0.00 sec)
5.3.2 为用户指定角色
接下来我们创建几个用户，然后为他们分别指定角色。
mysql> CREATE USER devp1 IDENTIFIED BY ‘Devp1@2019’; Query OK, 0 rows affected (0.01 sec) mysql> CREATE USER read1 IDENTIFIED BY ‘Read1@2019’; Query OK, 0 rows affected (0.01 sec) mysql> CREATE USER test1 IDENTIFIED BY ‘Test1@2019’; Query OK, 0 rows affected (0.04 sec)
为用户指定角色和授予权限类似，也是使用GRANT语句：
mysql> GRANT devp_role TO devp1; Query OK, 0 rows affected (0.01 sec) mysql> GRANT read_role TO read1; Query OK, 0 rows affected (0.01 sec) mysql> GRANT read_role, write_role TO test1; Query OK, 0 rows affected (0.01 sec) 复制代码 再次查询用户的权限： mysql> SHOW GRANTS FOR devp1; ±-------------------------------------+ | Grants for devp1@% | ±-------------------------------------+ | GRANT USAGE ON . TO devp1@% | | GRANT devp_role@% TO devp1@% | ±-------------------------------------+ 2 rows in set (0.00 sec)
如果想要知道用户通过角色获得的具体权限，可以使用USING选项：
mysql> SHOW GRANTS FOR devp1 USING devp_role; ±-------------------------------------------------+ | Grants for devp1@% | ±-------------------------------------------------+ | GRANT USAGE ON . TO devp1@% | | GRANT ALL PRIVILEGES ON world.* TO devp1@% | | GRANT devp_role@% TO devp1@% | ±-------------------------------------------------+ 3 rows in set (0.00 sec)
另外，也可以通过将一个用户授予另一个用户，实现权限的复制：
mysql> GRANT read1 TO test1; Query OK, 0 rows affected (0.09 sec)
用户是具有登录权限的角色，角色是不能登录的用户。
