MySQL角色管理

20阅读 0评论2018-06-22 dbwatcher
分类:Mysql/postgreSQL

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5786506.html

角色(role),MySQL的角色是权限的集合。一个角色就是一组权限。角色,像用户帐户一样,角色可以拥有grant和回收(撤销)的权限。

1、创建角色
我们以test数据库的权限为例创建三类角色,分别对应开发人员、应用用户、管理员三类权限用户:
(1)test数据库的dml权限:insert、delete、update、select
(2)test数据库的只读权限:select
(3)test数据库的管理权限:all privileges

(root@localhost:)[(none)]> create role 'dml_test','readonly_test','admin_test';
Query OK, 0 rows affected (0.04 sec)

2、对角色授权如下:
(root@localhost:)[(none)]> GRANT SELECT ON test.*  to readonly_test;
Query OK, 0 rows affected (0.09 sec)

(root@localhost:)[(none)]> GRANT INSERT,UPDATE,DELETE,SELECT ON test.*  to dml_test;
Query OK, 0 rows affected (0.03 sec)

(root@localhost:)[(none)]>  GRANT all privileges ON test.*  to dml_test;
Query OK, 0 rows affected (0.02 sec)

3、创建用户
(root@localhost:)[(none)]> create user dev_lili@'localhost' identified by 'lili';
Query OK, 0 rows affected (0.02 sec)

(root@localhost:)[(none)]> create user apps_test@'localhost' identified by 'test';
Query OK, 0 rows affected (0.04 sec)

(root@localhost:)[(none)]>  create user dba_test@'localhost' identified by 'test';
Query OK, 0 rows affected (0.11 sec)


4、对用户赋予角色
(root@localhost:)[(none)]> grant readonly_test to dev_lili@'localhost';
Query OK, 0 rows affected (0.04 sec)

(root@localhost:)[(none)]> grant dml_test to apps_test@'localhost';
Query OK, 0 rows affected (0.10 sec)

(root@localhost:)[(none)]> grant admin_test to dba_test@'localhost';
Query OK, 0 rows affected (0.09 sec)

5、验证
(root@localhost:)[(none)]> show grants for dev_lili@'localhost';
+-----------------------------------------------------+
| Grants for dev_lili@localhost                       |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `dev_lili`@`localhost`        |
| GRANT `readonly_test`@`%` TO `dev_lili`@`localhost` |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

(root@localhost:)[(none)]>  show grants for dev_lili@'localhost' using readonly_test;
+-----------------------------------------------------+
| Grants for dev_lili@localhost                       |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `dev_lili`@`localhost`        |
| GRANT SELECT ON `test`.* TO `dev_lili`@`localhost`  |
| GRANT `readonly_test`@`%` TO `dev_lili`@`localhost` |
+-----------------------------------------------------+

6、对用户激活角色
[root@dbwatcher ~]# mysql -udev_lili -plili test
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000): Access denied for user 'dev_lili'@'localhost' to database 'test'

但是还是不能用为什么呢?这是因为role还需要激活。

(dev_lili@localhost:) > SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

(root@localhost:)[(none)]>  SET DEFAULT ROLE ALL TO `dev_lili`@`localhost`,
    -> apps_test@'localhost',
    -> dba_test@'localhost';
Query OK, 0 rows affected (0.06 sec)

7、用户权限验证
[root@dbwatcher ~]# mysql -udev_lili -plili test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.11 MySQL Community Server - GPL


Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


(dev_lili@localhost:)[test]> SELECT CURRENT_ROLE();
+---------------------+
| CURRENT_ROLE()      |
+---------------------+
| `readonly_test`@`%` |
+---------------------+
1 row in set (0.00 sec)


(dev_lili@localhost:)[test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sales_info     |
+----------------+
1 row in set (0.00 sec)
(dev_lili@localhost:)[test]> select count(*) from sales_info;
+----------+
| count(*) |
+----------+
|     8000 |
+----------+
1 row in set (0.01 sec)

(dev_lili@localhost:)[test]> select *  from sales_info limit 2\G
*************************** 1. row ***************************
         id: 1
       name: test0
submit_time: 2018-01-01 00:00:00
*************************** 2. row ***************************
         id: 2
       name: test1
submit_time: 2018-01-01 01:00:00
2 rows in set (0.00 sec)

8、回收角色
(root@localhost:)[(none)]> revoke readonly_test from `dev_lili`@`localhost`;
Query OK, 0 rows affected (0.03 sec)

(root@localhost:)[(none)]> show grants for `dev_lili`@`localhost`;
+----------------------------------------------+
| Grants for dev_lili@localhost              |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `dev_lili`@`localhost` |
+-----------------------------------------------+

--The end
上一篇:Oracle一个update语句的优化
下一篇:MySQL用户管理的变化