MySql中如何创建角色_MySQL 8 数据库如何创建角色并为用户赋于角色

使用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。