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
|
grant create user on *.* to foo@localhost;
create role role1;
create role role2 with admin current_user;
create role role3 with admin current_role;
ERROR 0L000: Invalid definer
create role role3 with admin role1;
create role role4 with admin root@localhost;
create role role5 with admin root@localhost;
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
create role role5 with admin role3;
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
create role role5 with admin foo@localhost;
call mtr.add_suppression("Invalid roles_mapping table entry user:'foo@bar', rolename:'role6'");
create role role6 with admin foo@bar;
Warnings:
Note 1449 The user specified as a definer ('foo'@'bar') does not exist
create user bar with admin current_user;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'admin current_user' at line 1
grant role1 to foo@localhost with admin option;
grant role2 to foo@localhost;
grant role2 to role1;
grant role4 to role3 with admin option;
grant select on *.* to foo@localhost with admin option;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'admin option' at line 1
show grants for foo@localhost;
Grants for foo@localhost
GRANT CREATE USER ON *.* TO 'foo'@'localhost'
GRANT role1 TO 'foo'@'localhost' WITH ADMIN OPTION
GRANT role2 TO 'foo'@'localhost'
GRANT role5 TO 'foo'@'localhost' WITH ADMIN OPTION
show grants for role1;
Grants for role1
GRANT USAGE ON *.* TO 'role1'
GRANT USAGE ON *.* TO 'role2'
GRANT USAGE ON *.* TO 'role3'
GRANT USAGE ON *.* TO 'role4'
GRANT role2 TO 'role1'
GRANT role3 TO 'role1' WITH ADMIN OPTION
GRANT role4 TO 'role3' WITH ADMIN OPTION
show grants for role4;
Grants for role4
GRANT USAGE ON *.* TO 'role4'
select * from mysql.roles_mapping;
Host User Role Admin_option
role1 role2 N
role1 role3 Y
role3 role4 Y
bar foo role6 Y
localhost foo role1 Y
localhost foo role2 N
localhost foo role5 Y
localhost root role1 Y
localhost root role2 Y
localhost root role4 Y
flush privileges;
show grants for foo@localhost;
Grants for foo@localhost
GRANT CREATE USER ON *.* TO 'foo'@'localhost'
GRANT role1 TO 'foo'@'localhost' WITH ADMIN OPTION
GRANT role2 TO 'foo'@'localhost'
GRANT role5 TO 'foo'@'localhost' WITH ADMIN OPTION
show grants for role1;
Grants for role1
GRANT USAGE ON *.* TO 'role1'
GRANT USAGE ON *.* TO 'role2'
GRANT USAGE ON *.* TO 'role3'
GRANT USAGE ON *.* TO 'role4'
GRANT role2 TO 'role1'
GRANT role3 TO 'role1' WITH ADMIN OPTION
GRANT role4 TO 'role3' WITH ADMIN OPTION
show grants for role4;
Grants for role4
GRANT USAGE ON *.* TO 'role4'
select * from information_schema.applicable_roles;
GRANTEE ROLE_NAME IS_GRANTABLE
role1 role2 NO
role1 role3 YES
role3 role4 YES
root@localhost role1 YES
root@localhost role2 YES
root@localhost role4 YES
grant role2 to role1 with admin option;
revoke role1 from foo@localhost;
revoke admin option for role4 from role3;
revoke admin option for role2 from foo@localhost;
revoke admin option for role1 from root@localhost;
show grants for foo@localhost;
Grants for foo@localhost
GRANT CREATE USER ON *.* TO 'foo'@'localhost'
GRANT role2 TO 'foo'@'localhost'
GRANT role5 TO 'foo'@'localhost' WITH ADMIN OPTION
show grants for role1;
Grants for role1
GRANT USAGE ON *.* TO 'role1'
GRANT USAGE ON *.* TO 'role2'
GRANT USAGE ON *.* TO 'role3'
GRANT USAGE ON *.* TO 'role4'
GRANT role2 TO 'role1' WITH ADMIN OPTION
GRANT role3 TO 'role1' WITH ADMIN OPTION
GRANT role4 TO 'role3'
show grants for role4;
Grants for role4
GRANT USAGE ON *.* TO 'role4'
select * from mysql.roles_mapping;
Host User Role Admin_option
role1 role2 Y
role1 role3 Y
role3 role4 N
bar foo role6 Y
localhost foo role2 N
localhost foo role5 Y
localhost root role1 N
localhost root role2 Y
localhost root role4 Y
flush privileges;
show grants for foo@localhost;
Grants for foo@localhost
GRANT CREATE USER ON *.* TO 'foo'@'localhost'
GRANT role2 TO 'foo'@'localhost'
GRANT role5 TO 'foo'@'localhost' WITH ADMIN OPTION
show grants for role1;
Grants for role1
GRANT USAGE ON *.* TO 'role1'
GRANT USAGE ON *.* TO 'role2'
GRANT USAGE ON *.* TO 'role3'
GRANT USAGE ON *.* TO 'role4'
GRANT role2 TO 'role1' WITH ADMIN OPTION
GRANT role3 TO 'role1' WITH ADMIN OPTION
GRANT role4 TO 'role3'
show grants for role4;
Grants for role4
GRANT USAGE ON *.* TO 'role4'
select * from information_schema.applicable_roles;
GRANTEE ROLE_NAME IS_GRANTABLE
role1 role2 YES
role1 role3 YES
role3 role4 NO
root@localhost role1 NO
root@localhost role2 YES
root@localhost role4 YES
grant role1 to role4;
ERROR 28000: Access denied for user 'root'@'localhost'
grant role1 to role4 with admin option;
ERROR 28000: Access denied for user 'root'@'localhost'
grant role3 to role2;
revoke role3 from role2;
grant role4 to role2 with admin option;
revoke role2 from current_user;
revoke role4 from current_user;
grant role4 to current_user;
drop role role1, role2, role3, role4, role5, role6;
drop user foo@localhost;
|
