MySQL 8.0角色与授权

最新推荐文章于 2024-12-30 20:53:16 发布

程裕强

最新推荐文章于 2024-12-30 20:53:16 发布

阅读量727

点赞数

分类专栏：数据库文章标签： mysql 角色

本文链接：https://blog.csdn.net/chengyuqiang/article/details/121292459

版权

数据库专栏收录该内容

32 篇文章 0 订阅

订阅专栏

这篇博客详细介绍了如何在MySQL中创建用户、角色，并进行权限分配。首先，创建了'employees_write'用户和'employees_r'、'employees_admin'角色。接着，分别为这三个角色授予不同的权限，如全权、只读和读写。然后，将角色赋给相应用户。最后，通过查询确认了角色与用户的关联以及各个用户和角色的权限。这展示了数据库权限管理的基本流程。

摘要由CSDN通过智能技术生成

1、用户

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

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

2、新建角色

mysql> create role 'employees_r','employees_rw','employees_admin';

（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>