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
|
create user 'test_user'@'localhost';
create role r_sel;
create role r_ins;
create role r_upd;
create role r_del;
create role r_crt;
create role r_drp;
create role r_rld;
grant select on *.* to r_sel;
grant insert on *.* to r_ins;
grant update on *.* to r_upd;
grant delete on *.* to r_del;
grant create on *.* to r_crt;
grant drop on *.* to r_drp;
grant reload on *.* to r_rld;
grant r_sel to test_user@localhost;
grant r_ins to test_user@localhost;
grant r_upd to test_user@localhost;
grant r_del to test_user@localhost;
grant r_crt to test_user@localhost;
grant r_drp to test_user@localhost;
grant r_rld to test_user@localhost;
flush privileges;
select * from mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
show grants;
Grants for test_user@localhost
GRANT USAGE ON *.* TO `test_user`@`localhost`
GRANT `r_crt` TO `test_user`@`localhost`
GRANT `r_del` TO `test_user`@`localhost`
GRANT `r_drp` TO `test_user`@`localhost`
GRANT `r_ins` TO `test_user`@`localhost`
GRANT `r_rld` TO `test_user`@`localhost`
GRANT `r_sel` TO `test_user`@`localhost`
GRANT `r_upd` TO `test_user`@`localhost`
select current_user(), current_role();
current_user() current_role()
test_user@localhost NULL
set role r_sel;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_sel
show grants;
Grants for test_user@localhost
GRANT SELECT ON *.* TO `r_sel`
GRANT USAGE ON *.* TO `test_user`@`localhost`
GRANT `r_crt` TO `test_user`@`localhost`
GRANT `r_del` TO `test_user`@`localhost`
GRANT `r_drp` TO `test_user`@`localhost`
GRANT `r_ins` TO `test_user`@`localhost`
GRANT `r_rld` TO `test_user`@`localhost`
GRANT `r_sel` TO `test_user`@`localhost`
GRANT `r_upd` TO `test_user`@`localhost`
select * from mysql.roles_mapping;
Host User Role Admin_option
localhost root r_crt Y
localhost root r_del Y
localhost root r_drp Y
localhost root r_ins Y
localhost root r_rld Y
localhost root r_sel Y
localhost root r_upd Y
localhost test_user r_crt N
localhost test_user r_del N
localhost test_user r_drp N
localhost test_user r_ins N
localhost test_user r_rld N
localhost test_user r_sel N
localhost test_user r_upd N
set role r_ins;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_ins
show grants;
Grants for test_user@localhost
GRANT INSERT ON *.* TO `r_ins`
GRANT USAGE ON *.* TO `test_user`@`localhost`
GRANT `r_crt` TO `test_user`@`localhost`
GRANT `r_del` TO `test_user`@`localhost`
GRANT `r_drp` TO `test_user`@`localhost`
GRANT `r_ins` TO `test_user`@`localhost`
GRANT `r_rld` TO `test_user`@`localhost`
GRANT `r_sel` TO `test_user`@`localhost`
GRANT `r_upd` TO `test_user`@`localhost`
select * from mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
insert into mysql.roles_mapping values ('', 'r_sel', 'r_rld', 'N');
flush privileges;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
set role r_rld;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_rld
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_sel
flush privileges;
set role none;
select current_user(), current_role();
current_user() current_role()
test_user@localhost NULL
flush privileges;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
set role r_ins;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_ins
insert into mysql.roles_mapping values ('', 'r_sel', 'r_upd', 'N');
insert into mysql.roles_mapping values ('', 'r_sel', 'r_del', 'N');
insert into mysql.roles_mapping values ('', 'r_sel', 'r_crt', 'N');
insert into mysql.roles_mapping values ('', 'r_sel', 'r_drp', 'N');
insert into mysql.roles_mapping values ('', 'r_del', 'r_ins', 'N');
set role r_rld;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_rld
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_sel
update mysql.roles_mapping set Role='r_ins' where Role='r_ins_wrong';
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_sel
create table mysql.random_test_table (id INT);
insert into mysql.random_test_table values (1);
select * from mysql.random_test_table;
id
1
delete from mysql.roles_mapping where Role='r_ins';
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user() current_role()
test_user@localhost r_sel
insert into mysql.random_test_table values (1);
ERROR 42000: INSERT command denied to user 'test_user'@'localhost' for table `mysql`.`random_test_table`
drop table mysql.random_test_table;
delete from mysql.user where user like 'r\_%';
delete from mysql.roles_mapping where Role like 'r\_%';
flush privileges;
drop user 'test_user'@'localhost';
|
