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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
create user test_user@localhost;
create role test_role1;
create role test_role2;
grant test_role1 to test_user@localhost;
grant test_role2 to test_user@localhost;
grant test_role2 to test_role1;
select user, host from mysql.user where user not like 'root';
user host
test_role1
test_role2
test_user localhost
select * from mysql.roles_mapping;
Host User Role Admin_option
test_role1 test_role2 N
localhost root test_role1 Y
localhost root test_role2 Y
localhost test_user test_role1 N
localhost test_user test_role2 N
select user, host from mysql.db;
user host
%
%
grant select on mysql.* to test_role2;
flush privileges;
select * from information_schema.applicable_roles;
GRANTEE ROLE_NAME IS_GRANTABLE
root@localhost test_role1 YES
root@localhost test_role2 YES
test_role1 test_role2 NO
select * from information_schema.applicable_roles;
GRANTEE ROLE_NAME IS_GRANTABLE
test_role1 test_role2 NO
test_user@localhost test_role1 NO
test_user@localhost test_role2 NO
show grants;
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
select current_user(), current_role();
current_user() current_role()
test_user@localhost NULL
set role test_role1;
select * from information_schema.enabled_roles;
ROLE_NAME
test_role1
test_role2
select current_user(), current_role();
current_user() current_role()
test_user@localhost test_role1
show grants;
Grants for test_user@localhost
GRANT SELECT ON `mysql`.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_role1'
GRANT USAGE ON *.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_role1'
GRANT test_role2 TO 'test_user'@'localhost'
set role none;
select * from information_schema.enabled_roles;
ROLE_NAME
NULL
select current_user(), current_role();
current_user() current_role()
test_user@localhost NULL
show grants;
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for test_user@localhost;
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for test_role1;
ERROR 42000: Access denied for user 'test_user'@'localhost' to database 'mysql'
show grants for test_role2;
ERROR 42000: Access denied for user 'test_user'@'localhost' to database 'mysql'
show grants for CURRENT_USER;
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for CURRENT_USER();
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for CURRENT_ROLE;
ERROR 42000: There is no such grant defined for user 'test_user' on host 'localhost'
show grants for CURRENT_ROLE();
ERROR 42000: There is no such grant defined for user 'test_user' on host 'localhost'
set role test_role2;
select * from information_schema.enabled_roles;
ROLE_NAME
test_role2
select current_user(), current_role();
current_user() current_role()
test_user@localhost test_role2
show grants;
Grants for test_user@localhost
GRANT SELECT ON `mysql`.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for test_user@localhost;
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for test_role1;
Grants for test_role1
GRANT SELECT ON `mysql`.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_role1'
GRANT USAGE ON *.* TO 'test_role2'
GRANT test_role2 TO 'test_role1'
show grants for test_role2;
Grants for test_role2
GRANT SELECT ON `mysql`.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_role2'
show grants for CURRENT_USER;
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for CURRENT_USER();
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT test_role1 TO 'test_user'@'localhost'
GRANT test_role2 TO 'test_user'@'localhost'
show grants for CURRENT_ROLE;
Grants for test_role2
GRANT SELECT ON `mysql`.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_role2'
show grants for CURRENT_ROLE();
Grants for test_role2
GRANT SELECT ON `mysql`.* TO 'test_role2'
GRANT USAGE ON *.* TO 'test_role2'
drop user 'test_user'@'localhost';
revoke select on mysql.* from test_role2;
drop role test_role1;
drop role test_role2;
delete from mysql.roles_mapping where Role='test_role1';
delete from mysql.roles_mapping where Role='test_role2';
flush privileges;
|
