DBA Hub

📋Steps in this guide1/11

Create Roles in MySQL

Create Roles in MySQL

mysql configurationintermediate
by MYSQL
14 views
1

Overview

Create Roles in MySQL 1. Overview A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them. 2. How to Create Roles?

Code/Command (click line numbers to comment):

1
A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.
2

Section 2

2. How to Create Roles? 3. How to Grant Privileges to Role? 4. Create new users and Grant Roles

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql>
CREATE ROLE 'DELL_APP_RO', 'DELL_APP_RW';
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql>
GRANT SELECT ON dell.* TO 'DELL_APP_RO';
Query OK, 0 rows affected (0.01 sec)

mysql>
GRANT SELECT, INSERT, UPDATE, DELETE ON dell.* TO 'DELL_APP_RW';
Query OK, 0 rows affected (0.01 sec)

mysql>
3

Section 3

4. Create new users and Grant Roles -- Create new users -- Grants Roles 5. How to find what roles granted to any users?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Create new users
mysql>
CREATE USER 'raj_ro_user'@'localhost' IDENTIFIED BY 'Raj_ro_user1%';
Query OK, 0 rows affected (0.01 sec)

mysql>
CREATE USER 'raj_rw_user'@'localhost' IDENTIFIED BY 'Raj_rw_user1%';
Query OK, 0 rows affected (0.01 sec)

mysql>
-- Grants Roles
mysql>
GRANT DELL_APP_RO TO 'raj_ro_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>
GRANT DELL_APP_RW TO 'raj_rw_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>
4

Section 4

------------ OR ------------- 6. How to set Granted ROLE manually if not set? mysql -u raj_ro_user -h localhost -pRaj_ro_user1%

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
mysql>
SELECT * FROM mysql.role_edges;
+-----------+-------------+-----------+-------------+-------------------+
| FROM_HOST | FROM_USER   | TO_HOST   | TO_USER     | WITH_ADMIN_OPTION |
+-----------+-------------+-----------+-------------+-------------------+
| %         | DELL_APP_RO | localhost | raj_ro_user | N                 |
| %         | DELL_APP_RW | localhost | raj_rw_user | N                 |
+-----------+-------------+-----------+-------------+-------------------+
2 rows in set (0.00 sec)

mysql>
------------ OR -------------
-- TO list ALL Roles  (you can notice roles also)
select * from mysql.user;

[root@rac1 ~]#
mysql -u raj_ro_user -h localhost -pRaj_ro_user1%
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 30
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

mysql>
select current_user ();
+-----------------------+
| current_user ()       |
+-----------------------+
| raj_ro_user@localhost |
+-----------------------+
1 row in set (0.00 sec)

mysql>
SHOW GRANTS;
+------------------------------------------------------+
| Grants for raj_ro_user@localhost                     |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `raj_ro_user`@`localhost`      |
| GRANT `DELL_APP_RO`@`%` TO `raj_ro_user`@`localhost` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

mysql>
set role DELL_APP_RO;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+-------------------+
| CURRENT_ROLE()    |
+-------------------+
| `DELL_APP_RO`@`%` |
+-------------------+
1 row in set (0.00 sec)

mysql>
SHOW GRANTS;
+-------------------------------------------------------+
| Grants for raj_ro_user@localhost                      |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `raj_ro_user`@`localhost`       |
| GRANT SELECT ON `dell`.* TO `raj_ro_user`@`localhost` |
| GRANT `DELL_APP_RO`@`%` TO `raj_ro_user`@`localhost`  |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

############################################################
-- This command can set permanatly, no need to set manually
mysql>
select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql>
set global activate_all_roles_on_login=1;
Query OK, 0 rows affected (0.00 sec)

mysql> 

#############################################################
-- Set multiple default roles for one user
SET DEFAULT ROLE ROLE1,ROLE2,ROLE3 TO TO 'username'@'localhost';
-- A default role can be assigned to multiple users in one command as above
SET DEFAULT ROLE ROLE1 TO 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost';
5

Section 5

-- This command can set permanatly, no need to set manually set global activate_all_roles_on_login=1; -- Set multiple default roles for one user SET DEFAULT ROLE ROLE1,ROLE2,ROLE3 TO TO 'username'@'localhost'; SET DEFAULT ROLE ROLE1 TO 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost'; 7. How To Define Mandatory Roles?
6

Section 6

-- if you create new user, then automatically this role will be assigned. -- these changes are fixed ever after restart MySQL. GRANT `MY_MANDATORY_ROLE`@`%` TO `lenovo`@`localhost` | 8. How to Find assigned privileges from Role?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
To set and persist mandatory_roles at runtime,use a statement like this:
-- if you create new user, then
automatically
this role will be assigned.
-- these changes are fixed ever after restart MySQL.
mysql>
CREATE ROLE MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.01 sec)

mysql>
GRANT DELL_APP_RO,DELL_APP_RW TO MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.01 sec)

mysql>
GRANT SELECT ON sys.version to MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.00 sec)

mysql>
SET PERSIST mandatory_roles = 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.00 sec)

mysql>
select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
| MY_MANDATORY_ROLE |
+-------------------+
1 row in set (0.00 sec)

mysql>
create user lenovo@localhost identified by 'Lenovo123%';
Query OK, 0 rows affected (0.01 sec)

mysql>

[root@rac1 ~]#
mysql -u lenovo -h localhost -pLenovo123%
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 31
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

mysql>
select user();
+------------------+
| user()           |
+------------------+
| lenovo@localhost |
+------------------+
1 row in set (0.00 sec)

mysql>
show grants;
+-------------------------------------------------------+
| Grants for lenovo@localhost                           |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `lenovo`@`localhost`            |
|
GRANT `MY_MANDATORY_ROLE`@`%` TO `lenovo`@`localhost` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

mysql>
show grants for DELL_APP_RO;
+-----------------------------------------------+
| Grants for DELL_APP_RO@%                      |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RO`@`%`       |
|
GRANT SELECT ON `dell`.* TO `DELL_APP_RO`@`%`
|
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql>
show grants for DELL_APP_RW;
+-----------------------------------------------------------------------+
| Grants for DELL_APP_RW@%                                              |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RW`@`%`                               |
|
GRANT SELECT, INSERT, UPDATE, DELETE ON `dell`.* TO `DELL_APP_RW`@`%`
|
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
7

Section 7

9. How to Revoke Privileges from Role? and Revoke Roles from User? 10. Revoke Roles from User (OR) Role?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
mysql>
REVOKE SELECT ON dell.* FROM DELL_APP_RO;
Query OK, 0 rows affected (0.01 sec)

mysql>
REVOKE SELECT,INSERT,UPDATE,DELETE ON dell.* FROM DELL_APP_RW;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql>
show grants for DELL_APP_RO;
+-----------------------------------------+
| Grants for DELL_APP_RO@%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RO`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>
show grants for DELL_APP_RW;
+-----------------------------------------+
| Grants for DELL_APP_RW@%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RW`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql>
SELECT * FROM mysql.role_edges;
+-----------+-------------+-----------+-------------------+-------------------+
| FROM_HOST | FROM_USER   | TO_HOST   | TO_USER           | WITH_ADMIN_OPTION |
+-----------+-------------+-----------+-------------------+-------------------+
| %         | DELL_APP_RO | %         | MY_MANDATORY_ROLE | N                 |
| %         | DELL_APP_RO | localhost | raj_ro_user       | N                 |
| %         | DELL_APP_RW | %         | MY_MANDATORY_ROLE | N                 |
| %         | DELL_APP_RW | localhost | raj_rw_user       | N                 |
+-----------+-------------+-----------+-------------------+-------------------+
4 rows in set (0.00 sec)
mysql>
-- Revoke Roles from user
mysql>
REVOKE DELL_APP_RO FROM 'raj_ro_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>
REVOKE DELL_APP_RW FROM 'raj_rw_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql>
-- Revoke Roles from Roles
mysql>
REVOKE DELL_APP_RO FROM 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.01 sec)

mysql>
REVOKE DELL_APP_RW FROM 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql>
SELECT * FROM mysql.role_edges;
Empty set (0.00 sec)

mysql>
8

Section 8

SELECT * FROM mysql.role_edges; -- Revoke Roles from user -- Revoke Roles from Roles 11. How to Drop Roles?
9

Section 9

select user from mysql.user; DELL_APP_RO | | DELL_APP_RW | | MY_MANDATORY_ROLE | DROP ROLE DELL_APP_RO; DROP ROLE DELL_APP_RW; DROP ROLE MY_MANDATORY_ROLE; ERROR 3628 (HY000): The role `MY_MANDATORY_ROLE`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
mysql>
select user from mysql.user;
+-------------------+
| user              |
+-------------------+
|
DELL_APP_RO       |
| DELL_APP_RW       |
| MY_MANDATORY_ROLE |
| TEST_RO           |
| raj               |
| scott1            |
| srinu             |
| scott             |
| scott             |
| sugi              |
| scott             |
| teja              |
| bose              |
| lenovo            |
| lori              |
| may               |
| mike              |
| mysql.infoschema  |
| mysql.session     |
| mysql.sys         |
| raj               |
| raj_ro_user       |
| raj_rw_user       |
| root              |
| sunil             |
+-------------------+
25 rows in set (0.00 sec)

mysql>

mysql>
DROP ROLE DELL_APP_RO;
Query OK, 0 rows affected (0.00 sec)

mysql>
DROP ROLE DELL_APP_RW;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
DROP ROLE MY_MANDATORY_ROLE;
ERROR 3628 (HY000): The role `MY_MANDATORY_ROLE`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.
mysql>
mysql>
select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
|
MY_MANDATORY_ROLE
|
+-------------------+
1 row in set (0.00 sec)

mysql>

mysql>
SET PERSIST mandatory_roles = '
NONE
';
Query OK, 0 rows affected (0.00 sec)

mysql>
select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
|
NONE
|
+-------------------+
1 row in set (0.00 sec)

mysql>
DROP ROLE MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
select user from mysql.user;
+------------------+
| user             |
+------------------+
| TEST_RO          |
| raj              |
| scott1           |
| srinu            |
| scott            |
| scott            |
| sugi             |
| scott            |
| teja             |
| bose             |
| lenovo           |
| lori             |
| may              |
| mike             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| raj              |
| raj_ro_user      |
| raj_rw_user      |
| root             |
| sunil            |
+------------------+
22 rows in set (0.00 sec)

mysql>
10

Section 10

MY_MANDATORY_ROLE SET PERSIST mandatory_roles = ' NONE '; DROP ROLE MY_MANDATORY_ROLE; select user from mysql.user; Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution:
11

Section 11

Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Reference: https://dev.mysql.com/doc/refman/8.0/en/roles.html Reference:

Comments (0)

Please to add comments

No comments yet. Be the first to comment!