0%

MySQL8.0新特性:角色权限

MySQL5.7

在5.7中想要实现角色功能的话可以借助proxies_priv来简单实现,要想使用proxies_priv需要先开启相关参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
root@(none) 17:28:  set global check_proxy_users =on;
Query OK, 0 rows affected (0.00 sec)

root@(none) 17:29: set global mysql_native_password_proxy_users = on;
Query OK, 0 rows affected (0.00 sec)

root@(none) 17:29: show variables like "%proxy%";
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| check_proxy_users | ON |
| mysql_native_password_proxy_users | ON |
| proxy_user | |
+-----------------------------------+-------+

创建dba组

1
2
root@(none) 17:29:  create user dba;
Query OK, 0 rows affected (0.00 sec)

创建成员

1
2
3
4
5
root@(none) 17:31:  create user tom@'localhost' identified by 'Abcd123#';
Query OK, 0 rows affected (0.01 sec)

root@(none) 17:31: create user jey@'localhost' identified by 'Abcd123#';
Query OK, 0 rows affected (0.01 sec)

将DBA权限映射到成员中

1
2
3
4
5
root@(none) 17:32:  grant proxy on dba to tom@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@(none) 17:33: grant proxy on dba to jey@'localhost';
Query OK, 0 rows affected (0.00 sec)

授予权限给DBA

1
2
root@(none) 17:35:  grant select on test.* to dba;
Query OK, 0 rows affected (0.00 sec)

查看权限

1
2
3
4
5
6
7
8
root@(none) 17:36:  select * from mysql.proxies_priv;
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| localhost | root | | | 1 | boot@connecting host | 0000-00-00 00:00:00 |
| localhost | tom | % | dba | 0 | root@localhost | 0000-00-00 00:00:00 |
| localhost | jey | % | dba | 0 | root@localhost | 0000-00-00 00:00:00 |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+

MySQL8.0

MySQL8.0已经正式提供了角色功能,我们可以通过create role来创建角色

1
2
root@(none) 17:27:  create role db_owner,db_reader,db_writer;
Query OK, 0 rows affected (0.01 sec)

给不同的角色添加不同的权限

1
2
3
4
5
6
7
8
9
10
11
root@(none) 17:39:  grant all on test.* to db_owner;
Query OK, 0 rows affected (0.02 sec)

root@(none) 17:40: grant select on test.* to db_reader;
Query OK, 0 rows affected (0.01 sec)

root@(none) 17:41: grant delete,update,insert on test.* to db_reader;
Query OK, 0 rows affected (0.01 sec)

root@(none) 17:41: grant delete,update,insert on test.* to db_writer;
Query OK, 0 rows affected (0.01 sec)

创建用户并指定对应角色,一个用户可以对应多个角色,必须设置一个默认角色

1
2
3
4
5
6
7
8
root@(none) 17:41:  create user test@'%' identified by 'Abcd123#';
Query OK, 0 rows affected (0.01 sec)

root@(none) 17:43: grant db_owner,db_reader,db_writer to test;
Query OK, 0 rows affected (0.02 sec)

root@(none) 17:44: set default role all to test;
Query OK, 0 rows affected (0.00 sec)

查看当前用户对应的角色

1
2
3
4
5
6
test@(none) 17:47:  select current_role();
+------------------------------------------------+
| current_role() |
+------------------------------------------------+
| `db_owner`@`%`,`db_reader`@`%`,`db_writer`@`%` |
+------------------------------------------------+

切换角色

1
2
3
4
5
6
7
8
9
10
11
test@(none) 17:49:  set role db_reader;
Query OK, 0 rows affected (0.00 sec)

test@(none) 17:49: create table test.f(id int);
ERROR 1142 (42000): CREATE command denied to user 'test'@'localhost' for table 'f'

test@(none) 17:49: set role db_owner;
Query OK, 0 rows affected (0.00 sec)

test@(none) 17:52: create table test.f(id int);
Query OK, 0 rows affected (0.03 sec)

针对角色还有两个参数:activate_all_roles_on_login和mandatory_roles。activate_all_roles_on_login表示是否在连接mysql时自动激活角色,mandatory_roles表示强制用户的默认角色

1
2
3
4
5
root@(none) 18:05:  set global activate_all_roles_on_login=on;
Query OK, 0 rows affected (0.00 sec)

root@(none) 18:05: set global mandatory_roles='db_reader';
Query OK, 0 rows affected (0.00 sec)

实际上MySQL用户也能作为角色将权限授予给其它用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14

root@(none) 18:06: select user,host from mysql.user;
+------------------------+------------+
| user | host |
+------------------------+------------+
| cachecloud | % |
| db_owner | % |
| db_reader | % |
| db_writer | % |
| dba | % |
+------------------------+------------+

root@(none) 18:08: grant dba to test;
Query OK, 0 rows affected (0.01 sec)

回收用户角色权限

1
2
root@(none) 18:09:  revoke dba from test;
Query OK, 0 rows affected (0.00 sec)