1、用户
已经存在了employees、employees_read用户，新建一个写用户
代码语言：javascript复制mysql> CREATE USER 'employees_write'@'%' IDENTIFIED BY '123' ;
Query OK, 0 rows affected (0.00 sec)2、新建角色
（1）admin角色
代码语言：javascript复制mysql> grant all on employees.* to employees_admin;
Query OK, 0 rows affected (0.01 sec)（2）只读角色
代码语言：javascript复制mysql> grant select on employees.* to employees_r;
Query OK, 0 rows affected (0.00 sec)（3）读写角色
代码语言：javascript复制mysql> grant select,insert,update,delete on employees.* to employees_rw;
Query OK, 0 rows affected (0.00 sec)3、用于赋予角色
代码语言：javascript复制mysql> grant employees_admin to employees@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant employees_r to employees_read@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant employees_rw to 'employees_write'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)4、角色相关查询
（1）查看角色与用户的关系
代码语言：javascript复制mysql> select * from mysql.role_edges;
----------- ----------------- --------- ----------------- -------------------
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
----------- ----------------- --------- ----------------- -------------------
| % | employees_admin | % | employees | N |
| % | employees_r | % | employees_read | N |
| % | employees_rw | % | employees_write | N |
----------- ----------------- --------- ----------------- -------------------
3 rows in set (0.00 sec)
mysql> （2）查看用户权限
代码语言：javascript复制mysql> show grants for 'employees_write'@'%';
---------------------------------------------------
| Grants for employees_write@% |
---------------------------------------------------
| GRANT USAGE ON *.* TO `employees_write`@`%` |
| GRANT `employees_rw`@`%` TO `employees_write`@`%` |
---------------------------------------------------
2 rows in set (0.00 sec)
mysql> show grants for 'employees_read'@'%';
---------------------------------------------------------------------------------------------
| Grants for employees_read@% |
---------------------------------------------------------------------------------------------
| GRANT USAGE ON *.* TO `employees_read`@`%` |
| GRANT SELECT (`first_name`, `last_name`) ON `employees`.`employees` TO `employees_read`@`%` |
| GRANT `employees_r`@`%` TO `employees_read`@`%` |
---------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)
mysql> show grants for 'employees'@'%';
--------------------------------------------------------------------------
| Grants for employees@% |
--------------------------------------------------------------------------
| GRANT USAGE ON *.* TO `employees`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `employees`.* TO `employees`@`%` |
| GRANT `employees_admin`@`%` TO `employees`@`%` |
--------------------------------------------------------------------------
3 rows in set (0.00 sec)
mysql> （3）查看角色对应的权限
代码语言：javascript复制mysql> show grants for employees_admin;
----------------------------------------------------------------
| Grants for employees_admin@% |
----------------------------------------------------------------
| GRANT USAGE ON *.* TO `employees_admin`@`%` |
| GRANT ALL PRIVILEGES ON `employees`.* TO `employees_admin`@`%` |
----------------------------------------------------------------
2 rows in set (0.00 sec)
mysql> show grants for employees_r;
----------------------------------------------------
| Grants for employees_r@% |
----------------------------------------------------
| GRANT USAGE ON *.* TO `employees_r`@`%` |
| GRANT SELECT ON `employees`.* TO `employees_r`@`%` |
----------------------------------------------------
2 rows in set (0.00 sec)
mysql> show grants for employees_rw;
-----------------------------------------------------------------------------
| Grants for employees_rw@% |
-----------------------------------------------------------------------------
| GRANT USAGE ON *.* TO `employees_rw`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `employees`.* TO `employees_rw`@`%` |
-----------------------------------------------------------------------------
2 rows in set (0.00 sec)
mysql> 
