博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5786506.html
角色(role)，MySQL的角色是权限的集合。一个角色就是一组权限。角色，像用户帐户一样，角色可以拥有grant和回收(撤销)的权限。
1、创建角色
我们以test数据库的权限为例创建三类角色，分别对应开发人员、应用用户、管理员三类权限用户：
(1)test数据库的dml权限：insert、delete、update、select
(2)test数据库的只读权限：select
(3)test数据库的管理权限：all privileges
(root@localhost:)[(none)]> create role 'dml_test','readonly_test','admin_test';
Query OK, 0 rows affected (0.04 sec)
2、对角色授权如下：
(root@localhost:)[(none)]> GRANT SELECT ON test.* to readonly_test;
Query OK, 0 rows affected (0.09 sec)
(root@localhost:)[(none)]> GRANT INSERT,UPDATE,DELETE,SELECT ON test.* to dml_test;
Query OK, 0 rows affected (0.03 sec)
(root@localhost:)[(none)]> GRANT all privileges ON test.* to dml_test;
Query OK, 0 rows affected (0.02 sec)
3、创建用户
(root@localhost:)[(none)]> create user dev_lili@'localhost' identified by 'lili';
Query OK, 0 rows affected (0.02 sec)
(root@localhost:)[(none)]> create user apps_test@'localhost' identified by 'test';
Query OK, 0 rows affected (0.04 sec)
(root@localhost:)[(none)]> create user dba_test@'localhost' identified by 'test';
Query OK, 0 rows affected (0.11 sec)
4、对用户赋予角色
(root@localhost:)[(none)]> grant readonly_test to dev_lili@'localhost';
Query OK, 0 rows affected (0.04 sec)
(root@localhost:)[(none)]> grant dml_test to apps_test@'localhost';
Query OK, 0 rows affected (0.10 sec)
(root@localhost:)[(none)]> grant admin_test to dba_test@'localhost';
Query OK, 0 rows affected (0.09 sec)
5、验证
(root@localhost:)[(none)]> show grants for dev_lili@'localhost';
+-----------------------------------------------------+
| Grants for dev_lili@localhost |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `dev_lili`@`localhost` |
| GRANT `readonly_test`@`%` TO `dev_lili`@`localhost` |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
(root@localhost:)[(none)]> show grants for dev_lili@'localhost' using readonly_test;
+-----------------------------------------------------+
| Grants for dev_lili@localhost |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `dev_lili`@`localhost` |
| GRANT SELECT ON `test`.* TO `dev_lili`@`localhost` |
| GRANT `readonly_test`@`%` TO `dev_lili`@`localhost` |
+-----------------------------------------------------+
6、对用户激活角色
[root@dbwatcher ~]# mysql -udev_lili -plili test
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000): Access denied for user 'dev_lili'@'localhost' to database 'test'
但是还是不能用为什么呢？这是因为role还需要激活。
(dev_lili@localhost:) > SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
(root@localhost:)[(none)]> SET DEFAULT ROLE ALL TO `dev_lili`@`localhost`,
-> apps_test@'localhost',
-> dba_test@'localhost';
Query OK, 0 rows affected (0.06 sec)
7、用户权限验证
[root@dbwatcher ~]# mysql -udev_lili -plili test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.
(dev_lili@localhost:)[test]> SELECT CURRENT_ROLE();
+---------------------+
| CURRENT_ROLE() |
+---------------------+
| `readonly_test`@`%` |
+---------------------+
1 row in set (0.00 sec)
(dev_lili@localhost:)[test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sales_info |
+----------------+
1 row in set (0.00 sec)
(dev_lili@localhost:)[test]> select count(*) from sales_info;
+----------+
| count(*) |
+----------+
| 8000 |
+----------+
1 row in set (0.01 sec)
(dev_lili@localhost:)[test]> select * from sales_info limit 2\G
*************************** 1. row ***************************
id: 1
name: test0
submit_time: 2018-01-01 00:00:00
*************************** 2. row ***************************
id: 2
name: test1
submit_time: 2018-01-01 01:00:00
2 rows in set (0.00 sec)
8、回收角色
(root@localhost:)[(none)]> revoke readonly_test from `dev_lili`@`localhost`;
Query OK, 0 rows affected (0.03 sec)
(root@localhost:)[(none)]> show grants for `dev_lili`@`localhost`;
+----------------------------------------------+
| Grants for dev_lili@localhost |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `dev_lili`@`localhost` |
+-------------------------
----------------------+
--The end
