提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其他

当前栏目

MySQL 8.0角色与授权

2023-03-14 22:47:54 时间

1、用户

已经存在了employees、employees_read用户，新建一个写用户

mysql>  CREATE USER 'employees_write'@'%' IDENTIFIED BY '123' ;
Query OK, 0 rows affected (0.00 sec)

2、新建角色

（1）admin角色

mysql> grant all on employees.* to employees_admin;
Query OK, 0 rows affected (0.01 sec)

（2）只读角色

mysql> grant select on employees.* to employees_r;
Query OK, 0 rows affected (0.00 sec)

（3）读写角色

mysql> grant select,insert,update,delete on employees.* to employees_rw;
Query OK, 0 rows affected (0.00 sec)

3、用于赋予角色

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）查看角色与用户的关系

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）查看用户权限

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）查看角色对应的权限

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>

相关文章