【MySQL 8.0】角色(二)用户授权角色、激活角色
三、创建用户,并赋予r1、r2角色(r1可以对am数据库做dml操作,r2除了对am数据库dml之外,还可以create、drop)
1 2 3 4 5 6 7 8 9 10 |
mysql> create user 'user1'@'%' identified with mysql_native_password by 'oracle'; Query OK, 0 rows affected (0.00 sec) mysql> grant 'r1' to 'user1'@'%' ; Query OK, 0 rows affected (0.00 sec) mysql> grant 'r2' to 'user1'@'%' ; Query OK, 0 rows affected (0.00 sec) mysql> |
四、验证user1用户的权限
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[root@DB8 ~]# mysql -u user1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 933 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ## 我们用user1用户登录后,显示允许访问的数据库时,并没有看到am数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> ## 我们也可以看到当前用户的当前会话,没有任何角色生效 mysql> SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | NONE | +----------------+ 1 row in set (0.00 sec) mysql> |
五:user1用户拥有了r1角色,但却无法访问am数据库,原因是默认情况下该角色未被激活。
有以下几种方式可使角色生效:
1)使用SET DEFAULT ROLE来设置用户的默认角色。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
# 前提条件,用户需要拥有默认角色 # 虽然user1用户已经拥有了r1,r2两个角色的权限,但是,user1用户并没有默认角色,如下 mysql> select * from mysql.default_roles; Empty set (0.00 sec) mysql> ## 为user1用户设置默认角色 ## SET DEFAULT ROLE ALL,指的是将用户所有的角色都设置为默认角色 mysql> SET DEFAULT ROLE ALL TO 'user1'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> select * from mysql.default_roles; +------+-------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+-------+-------------------+-------------------+ | % | user1 | % | r1 | | % | user1 | % | r2 | +------+-------+-------------------+-------------------+ 2 rows in set (0.00 sec) mysql> ## 也可以指定该用户拥有的某一个角色设置为默认角色 mysql> SET DEFAULT ROLE r1 TO 'user1'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select * from mysql.default_roles; +------+-------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+-------+-------------------+-------------------+ | % | user1 | % | r1 | +------+-------+-------------------+-------------------+ 1 row in set (0.00 sec) mysql> ## 使用user1登录,我们可以看到,已经拥有了r1角色的权限 [root@DB8 ~]# mysql -u user1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2120 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | `r1`@`%` | +----------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | am | | information_schema | +--------------------+ 2 rows in set (0.00 sec) mysql> |
2)通过参数设置角色生效
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
# 利用有权限的用户,如:root用户,先将user1的默认角色删除 mysql> select * from mysql.default_roles; +------+-------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+-------+-------------------+-------------------+ | % | user1 | % | r1 | +------+-------+-------------------+-------------------+ 1 row in set (0.00 sec) mysql> SET DEFAULT ROLE none TO user1; Query OK, 0 rows affected (0.00 sec) mysql> select * from mysql.default_roles; Empty set (0.00 sec) mysql> # 角色相关的参数,当用户登录时,是否自动激活角色,默认值为off mysql> show global variables like 'activate_all_roles_on_login'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | activate_all_roles_on_login | OFF | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql> # 修改参数,启用登录自动激活。(该参数不允许会话级修改) mysql> set global activate_all_roles_on_login = on; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'activate_all_roles_on_login'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | activate_all_roles_on_login | ON | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql> # 我们看,该参数与默认角色无关 mysql> select * from mysql.default_roles; Empty set (0.00 sec) mysql> # 退出root用户登录的会话,重新以user1登录 [root@DB8 ~]# mysql -u user1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2188 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show global variables like 'activate_all_roles_on_login'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | activate_all_roles_on_login | ON | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql> SELECT CURRENT_ROLE(); +-------------------+ | CURRENT_ROLE() | +-------------------+ | `r1`@`%`,`r2`@`%` | +-------------------+ 1 row in set (0.00 sec) mysql> ## 我们可以看到,该用户登录后,自动激活了,user1所拥有的两个角色r1和r2 |
3)使用 SET ROLE 更改当前会话中的活动角色(紧紧更改当前会话)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
## SET ROLE NONE 设置当前会话没有任何角色 mysql> SET ROLE NONE; SELECT CURRENT_ROLE(); Query OK, 0 rows affected (0.00 sec) +----------------+ | CURRENT_ROLE() | +----------------+ | NONE | +----------------+ 1 row in set (0.01 sec) mysql> ## SET ROLE ALL EXCEPT 'r1' 设置当前会话除了'r1'外都生效 mysql> SET ROLE ALL EXCEPT 'r1'; SELECT CURRENT_ROLE(); Query OK, 0 rows affected (0.00 sec) +----------------+ | CURRENT_ROLE() | +----------------+ | `r2`@`%` | +----------------+ 1 row in set (0.00 sec) mysql> # SET ROLE DEFAULT 设置当前会话,该用户的默认角色生效 mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE(); Query OK, 0 rows affected (0.00 sec) +----------------+ | CURRENT_ROLE() | +----------------+ | `r1`@`%` | +----------------+ 1 row in set (0.00 sec) mysql> |
4)mandatory_roles 强制给每一个用户赋予该参数指定的角色(不推荐)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
## 在参数文件中修改参数 [mysqld] mandatory_roles='r1' ## 或者通过命令行方式修改参数 ## 持久化,重启生效 SET PERSIST mandatory_roles = 'r1'; ## 重启后失效 SET GLOBAL mandatory_roles = 'r1'; ## 命令行方式修改参数,要求用户拥有role_admin的权限,如下: mysql> SET GLOBAL mandatory_roles = 'r2'; ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SUPER privileges, as well as the ROLE_ADMIN privilege(s) for this operation mysql> mysql> grant ROLE_ADMIN on *.* to root; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SET GLOBAL mandatory_roles = 'r2'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'mandatory_roles'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | mandatory_roles | r2 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> |
小结:
1)SET DEFAULT ROLE 是 ALTER USER … DEFAULT ROLE 的替代语句,区别在于,SET DEFAULT ROLE可以同时修改多个用户,而ALTER USER … DEFAULT ROLE只能修改一个用户。
2)SET ROLE可以在会话层面修改用户所拥有的权限,这个功能Oracle也有,实际工作中用的不多。
3)role_admin权限,root用户可以自己授权自己。