使用CREATE ROLE创建角色(app_read_only,app_writes,app_developer):mysql> CREATE ROLE 'app_read_only',
'app_writes', 'app_developer';
Query OK, 0 rows affected (0.01 sec)
使用GRANT为角色分配权限:# 仅为app_read_only角色分配employees数据库的SELECT权限
mysql> GRANT SELECT ON employees.* TO
'app_read_only';
Query OK, 0 rows affected (0.00 sec)# 为app_writes分配employees数据库的INSERT,UPDATE,DELETE权限
mysql> GRANT INSERT, UPDATE, DELETE ON
employees.* TO 'app_writes';
Query OK, 0 rows affected (0.00 sec)# 为app_developer角色分配employees数据库上的所有权限
mysql> GRANT ALL ON employees.* TO
'app_developer';
Query OK, 0 rows affected (0.04 sec)
创建用户(黙认host为'%'):mysql> CREATE user emp_read_only IDENTIFIED BY
'emp_pass';
Query OK, 0 rows affected (0.06 sec)mysql> CREATE user emp_writes IDENTIFIED BY
'emp_pass';
Query OK, 0 rows affected (0.04 sec)mysql> CREATE user emp_developer IDENTIFIED BY
'emp_pass';
Query OK, 0 rows affected (0.01 sec)
为这些用户指定角色:mysql> GRANT 'app_read_only' TO
'emp_read_only'@'%';
Query OK, 0 rows affected (0.04 sec)mysql> GRANT 'app_writes' TO 'emp_writes'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT 'app_developer' TO
'emp_developer'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT 'app_read_only', 'app_writes' TO
'emp_read_write'@'%';
Query OK, 0 rows affected (0.05 sec)
PS:为了安全性考虑,应该尽量避免使用'%'作为host,应该限制用户访问的IP。