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
|
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
mariadb.sys localhost
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 IS_DEFAULT
root@localhost test_role1 YES NO
root@localhost test_role2 YES NO
test_role1 test_role2 NO NULL
select * from information_schema.applicable_roles;
GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT
test_role1 test_role2 NO NULL
test_user@localhost test_role1 NO NO
test_user@localhost test_role2 NO 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;
#
# MDEV-24289: show grants missing with grant option
#
create role anel;
GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO 'anel';
SHOW GRANTS for 'anel';
Grants for anel
GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO `anel`
create role MariaDB_admin;
GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO 'MariaDB_admin' WITH GRANT OPTION;
SHOW GRANTS for 'MariaDB_admin';
Grants for MariaDB_admin
GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO `MariaDB_admin` WITH GRANT OPTION
drop role MariaDB_admin;
drop role anel;
|
