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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
create role r1;
create role r2;
create role r3;
create role r4;
create user u1;
grant r2 to r1;
grant r3 to r2;
grant r4 to r3;
grant r1 to u1;
grant r4 to r1;
show grants for u1;
Grants for u1@%
GRANT USAGE ON *.* TO `u1`@`%`
GRANT `r1` TO `u1`@`%`
show grants for r1;
Grants for r1
GRANT USAGE ON *.* TO `r1`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r2` TO `r1`
GRANT `r3` TO `r2`
GRANT `r4` TO `r1`
GRANT `r4` TO `r3`
grant SELECT on *.* to u1;
grant INSERT on mysql.* to r1;
grant DELETE on mysql.roles_mapping to r2;
grant UPDATE on mysql.user to r3;
create function mysql.test_func (s CHAR(20))
returns CHAR(50) DETERMINISTIC
return concat('Test string: ',s);
create procedure mysql.test_proc (OUT param1 INT)
begin
select COUNT(*) into param1 from mysql.roles_mapping;
end|
grant execute on function mysql.test_func to r2;
grant execute on procedure mysql.test_proc to r3;
grant execute on mysql.* to r4;
show grants for r1;
Grants for r1
GRANT DELETE ON `mysql`.`roles_mapping` TO `r2`
GRANT EXECUTE ON FUNCTION `mysql`.`test_func` TO `r2`
GRANT EXECUTE ON PROCEDURE `mysql`.`test_proc` TO `r3`
GRANT EXECUTE ON `mysql`.* TO `r4`
GRANT INSERT ON `mysql`.* TO `r1`
GRANT UPDATE ON `mysql`.`user` TO `r3`
GRANT USAGE ON *.* TO `r1`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r2` TO `r1`
GRANT `r3` TO `r2`
GRANT `r4` TO `r1`
GRANT `r4` TO `r3`
show grants for r2;
Grants for r2
GRANT DELETE ON `mysql`.`roles_mapping` TO `r2`
GRANT EXECUTE ON FUNCTION `mysql`.`test_func` TO `r2`
GRANT EXECUTE ON PROCEDURE `mysql`.`test_proc` TO `r3`
GRANT EXECUTE ON `mysql`.* TO `r4`
GRANT UPDATE ON `mysql`.`user` TO `r3`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r3` TO `r2`
GRANT `r4` TO `r3`
show grants for r3;
Grants for r3
GRANT EXECUTE ON PROCEDURE `mysql`.`test_proc` TO `r3`
GRANT EXECUTE ON `mysql`.* TO `r4`
GRANT UPDATE ON `mysql`.`user` TO `r3`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r4` TO `r3`
show grants for r4;
Grants for r4
GRANT EXECUTE ON `mysql`.* TO `r4`
GRANT USAGE ON *.* TO `r4`
revoke all privileges, grant option from r4;
show grants for r1;
Grants for r1
GRANT DELETE ON `mysql`.`roles_mapping` TO `r2`
GRANT EXECUTE ON FUNCTION `mysql`.`test_func` TO `r2`
GRANT EXECUTE ON PROCEDURE `mysql`.`test_proc` TO `r3`
GRANT INSERT ON `mysql`.* TO `r1`
GRANT UPDATE ON `mysql`.`user` TO `r3`
GRANT USAGE ON *.* TO `r1`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r2` TO `r1`
GRANT `r3` TO `r2`
GRANT `r4` TO `r1`
GRANT `r4` TO `r3`
show grants for r2;
Grants for r2
GRANT DELETE ON `mysql`.`roles_mapping` TO `r2`
GRANT EXECUTE ON FUNCTION `mysql`.`test_func` TO `r2`
GRANT EXECUTE ON PROCEDURE `mysql`.`test_proc` TO `r3`
GRANT UPDATE ON `mysql`.`user` TO `r3`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r3` TO `r2`
GRANT `r4` TO `r3`
show grants for r3;
Grants for r3
GRANT EXECUTE ON PROCEDURE `mysql`.`test_proc` TO `r3`
GRANT UPDATE ON `mysql`.`user` TO `r3`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r4` TO `r3`
show grants for r4;
Grants for r4
GRANT USAGE ON *.* TO `r4`
revoke all privileges, grant option from r3;
show grants for r1;
Grants for r1
GRANT DELETE ON `mysql`.`roles_mapping` TO `r2`
GRANT EXECUTE ON FUNCTION `mysql`.`test_func` TO `r2`
GRANT INSERT ON `mysql`.* TO `r1`
GRANT USAGE ON *.* TO `r1`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r3`
GRANT USAGE ON *.* TO `r4`
GRANT `r2` TO `r1`
GRANT `r3` TO `r2`
GRANT `r4` TO `r1`
show grants for r2;
Grants for r2
GRANT DELETE ON `mysql`.`roles_mapping` TO `r2`
GRANT EXECUTE ON FUNCTION `mysql`.`test_func` TO `r2`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r3`
GRANT `r3` TO `r2`
show grants for r3;
Grants for r3
GRANT USAGE ON *.* TO `r3`
show grants for r4;
Grants for r4
GRANT USAGE ON *.* TO `r4`
revoke all privileges, grant option from r2;
show grants for r1;
Grants for r1
GRANT INSERT ON `mysql`.* TO `r1`
GRANT USAGE ON *.* TO `r1`
GRANT USAGE ON *.* TO `r2`
GRANT USAGE ON *.* TO `r4`
GRANT `r2` TO `r1`
GRANT `r4` TO `r1`
show grants for r2;
Grants for r2
GRANT USAGE ON *.* TO `r2`
show grants for r3;
Grants for r3
GRANT USAGE ON *.* TO `r3`
show grants for r4;
Grants for r4
GRANT USAGE ON *.* TO `r4`
revoke all privileges, grant option from r1;
show grants for r1;
Grants for r1
GRANT USAGE ON *.* TO `r1`
show grants for r2;
Grants for r2
GRANT USAGE ON *.* TO `r2`
show grants for r3;
Grants for r3
GRANT USAGE ON *.* TO `r3`
show grants for r4;
Grants for r4
GRANT USAGE ON *.* TO `r4`
revoke all privileges, grant option from u1;
show grants for u1;
Grants for u1@%
GRANT USAGE ON *.* TO `u1`@`%`
drop function mysql.test_func;
drop procedure mysql.test_proc;
show grants for r1;
Grants for r1
GRANT USAGE ON *.* TO `r1`
drop role r1, r2, r3, r4;
drop user u1;
|
