Debian Codegraph @

mysql-test/suite/auth_sec/r/mandatory_roles.result

raw
1 call mtr.add_suppression("Can't set mandatory_role.*");
2 SHOW VARIABLES LIKE 'mandatory_roles';
3 Variable_name Value
4 mandatory_roles PUBLIC
5 CREATE ROLE PUBLIC;
6 CREATE DATABASE test2;
7 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON test2.* TO PUBLIC@`%`;
8 SHOW GRANTS FOR PUBLIC;
9 Grants for PUBLIC@%
10 GRANT USAGE ON *.* TO `PUBLIC`@`%`
11 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test2`.* TO `PUBLIC`@`%`
12 CREATE USER u1@localhost IDENTIFIED BY 'foo';
13 SET DEFAULT ROLE PUBLIC TO u1@localhost;
14 SHOW GRANTS FOR u1@localhost USING PUBLIC;
15 Grants for u1@localhost
16 GRANT USAGE ON *.* TO `u1`@`localhost`
17 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test2`.* TO `u1`@`localhost`
18 # Current default role should be the mandatory role PUBLIC
19 SELECT current_role();
20 current_role()
21 `PUBLIC`@`%`
22 # SHOW GRANTS should show the granted roles
23 SHOW GRANTS;
24 Grants for u1@localhost
25 GRANT USAGE ON *.* TO `u1`@`localhost`
26 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test2`.* TO `u1`@`localhost`
27 GRANT `PUBLIC`@`%` TO `u1`@`localhost`
28 use test2;
29 CREATE TABLE t1 (c1 INT);
30 SET ROLE PUBLIC;
31 DROP TABLE t1;
32 # Make sure we're actually checking for privileges
33 CREATE DATABASE test3;
34 ERROR 42000: Access denied for user 'u1'@'localhost' to database 'test3'
35 CREATE ROLE team_share;
36 SET GLOBAL mandatory_roles='PUBLIC@%,team_share';
37 # restart
38 # Restarting the server will reset the mandatory_roles variable
39 SHOW VARIABLES LIKE 'mandatory_roles';
40 Variable_name Value
41 mandatory_roles PUBLIC
42 SET global mandatory_roles='PUBLIC@%,team_share';
43 # Active role is still PUBLIC; this is determined by default role
44 # settings.
45 SELECT CURRENT_ROLE();
46 CURRENT_ROLE()
47 `PUBLIC`@`%`
48 # But u1 is also granted team_share
49 SHOW GRANTS;
50 Grants for u1@localhost
51 GRANT USAGE ON *.* TO `u1`@`localhost`
52 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test2`.* TO `u1`@`localhost`
53 GRANT `PUBLIC`@`%`,`team_share`@`%` TO `u1`@`localhost`
54 use test2;
55 CREATE TABLE t1 (c1 INT);
56 SET ROLE PUBLIC;
57 DROP TABLE t1;
58 REVOKE PUBLIC FROM u1@localhost;
59 ERROR HY000: The role `PUBLIC`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.
60 DROP ROLE PUBLIC;
61 ERROR HY000: The role `PUBLIC`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.
62 DROP USER u1@localhost;
63 SET GLOBAL mandatory_roles= '';
64 DROP ROLE team_share;
65 DROP DATABASE test2;
66 SET GLOBAL mandatory_roles= 'PUBLIC';
67 #
68 # Tests for activate_all_roles_on_login
69 #
70 CREATE USER u1@localhost IDENTIFIED BY 'foo';
71 CREATE ROLE r1;
72 GRANT r1 TO u1@localhost;
73 GRANT SELECT ON *.* TO r1;
74 SET GLOBAL activate_all_roles_on_login= 'ON';
75 # Active role are PUBLIC, r1
76 SELECT CURRENT_ROLE();
77 CURRENT_ROLE()
78 `PUBLIC`@`%`,`r1`@`%`
79 SHOW GRANTS;
80 Grants for u1@localhost
81 GRANT SELECT ON *.* TO `u1`@`localhost`
82 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test2`.* TO `u1`@`localhost`
83 GRANT `PUBLIC`@`%`,`r1`@`%` TO `u1`@`localhost`
84 CREATE ROLE r2,r3,r4,r5;
85 GRANT r2,r3,r4,r5 TO u1@localhost;
86 # Grants for u1@localhost should include PUBLIC.
87 SELECT CURRENT_ROLE();
88 CURRENT_ROLE()
89 `PUBLIC`@`%`,`r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%`
90 SHOW GRANTS;
91 Grants for u1@localhost
92 GRANT SELECT ON *.* TO `u1`@`localhost`
93 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test2`.* TO `u1`@`localhost`
94 GRANT `PUBLIC`@`%`,`r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
95 SET ROLE NONE;
96 SHOW GRANTS;
97 Grants for u1@localhost
98 GRANT USAGE ON *.* TO `u1`@`localhost`
99 GRANT `PUBLIC`@`%`,`r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
100 # SHOW GRANTS FOR won't show mandatory roles (PUBLIC in this case)
101 # because we need to avoid breaking the mysqlpump protocol which rely
102 # on SHOW GRANTS FOR to dump user privileges.
103 SHOW GRANTS FOR u1@localhost;
104 Grants for u1@localhost
105 GRANT USAGE ON *.* TO `u1`@`localhost`
106 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
107 SHOW GRANTS FOR u1@localhost USING `PUBLIC`@`%`,`r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%`;
108 Grants for u1@localhost
109 GRANT SELECT ON *.* TO `u1`@`localhost`
110 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test2`.* TO `u1`@`localhost`
111 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
112 SET GLOBAL mandatory_roles= '';
113 DROP ROLE PUBLIC;
114 SET GLOBAL mandatory_roles= 'PUBLIC';
115 SET GLOBAL activate_all_roles_on_login= 'OFF';
116 REVOKE ALL ON *.* from r1,r2;
117 GRANT SELECT ON *.* TO r1;
118 GRANT INSERT ON *.* TO r2;
119 GRANT r2 TO u1@localhost;
120 SET GLOBAL mandatory_roles='r1';
121 FLUSH PRIVILEGES;
122 SHOW GRANTS FOR u1@localhost USING r1;
123 Grants for u1@localhost
124 GRANT SELECT ON *.* TO `u1`@`localhost`
125 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
126 SHOW GRANTS FOR u1@localhost USING r2;
127 Grants for u1@localhost
128 GRANT INSERT ON *.* TO `u1`@`localhost`
129 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
130 SHOW GRANTS FOR u1@localhost USING r1,r2;
131 Grants for u1@localhost
132 GRANT SELECT, INSERT ON *.* TO `u1`@`localhost`
133 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
134 # We need to disconnect here to be sure that the u1 session has correct
135 # values for activate_all_roles_on_login and mandatory_roles.
136 # Current role should be None because ther are no activate roles per
137 # default even though r1 is granted as a mandatory role.
138 SELECT CURRENT_ROLE();
139 CURRENT_ROLE()
140 NONE
141 SHOW GRANTS;
142 Grants for u1@localhost
143 GRANT USAGE ON *.* TO `u1`@`localhost`
144 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
145 SET ROLE r1;
146 SHOW GRANTS;
147 Grants for u1@localhost
148 GRANT SELECT ON *.* TO `u1`@`localhost`
149 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
150 SET ROLE r2;
151 SHOW GRANTS;
152 Grants for u1@localhost
153 GRANT INSERT ON *.* TO `u1`@`localhost`
154 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
155 SET ROLE r1,r2;
156 SHOW GRANTS;
157 Grants for u1@localhost
158 GRANT SELECT, INSERT ON *.* TO `u1`@`localhost`
159 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
160 SET ROLE ALL;
161 SHOW GRANTS;
162 Grants for u1@localhost
163 GRANT SELECT, INSERT ON *.* TO `u1`@`localhost`
164 GRANT `r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%` TO `u1`@`localhost`
165 #
166 # Reparse the mandatory role after each CREATE/DROP USER/ROLE
167 #
168 CREATE USER u2@localhost IDENTIFIED BY 'foo';
169 SET GLOBAL mandatory_roles="rr1";
170 FLUSH PRIVILEGES;
171 DROP ROLE rr1;
172 ERROR HY000: Operation DROP ROLE failed for 'rr1'@'%'
173 # Creating the role will cause mandatory_roles to be reparsed
174 CREATE ROLE rr1;
175 GRANT SELECT ON test.* TO rr1;
176 SET GLOBAL activate_all_roles_on_login = 'ON';
177 SELECT CURRENT_USER();
178 CURRENT_USER()
179 u2@localhost
180 # SHOW GRANTS should show that rr1 is granted and effective privileges
181 # should contain SELECT ON test.*
182 SHOW GRANTS;
183 Grants for u2@localhost
184 GRANT USAGE ON *.* TO `u2`@`localhost`
185 GRANT SELECT ON `test`.* TO `u2`@`localhost`
186 GRANT `rr1`@`%` TO `u2`@`localhost`
187 SET GLOBAL activate_all_roles_on_login = 'OFF';
188 # Don't show the same role twice.
189 SET GLOBAL mandatory_roles="r1, r1";
190 SHOW GRANTS;
191 Grants for root@localhost
192 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
193 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
194 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
195 GRANT `r1`@`%` TO `root`@`localhost`
196 DROP USER u1@localhost;
197 DROP USER u2@localhost;
198 SET GLOBAL mandatory_roles='PUBLIC';
199 DROP ROLE r1,r2,r3,r4,r5,rr1;
200 CREATE USER u1@localhost IDENTIFIED BY 'foo';
201 CREATE ROLE r1, r2, r3;
202 SET GLOBAL mandatory_roles="foo,";
203 ERROR 42000: Variable 'mandatory_roles' can't be set to the value of 'foo,'
204 SET GLOBAL mandatory_roles="aa ; bb : r3, r1, (r2), *r2";
205 SHOW GRANTS;
206 Grants for u1@localhost
207 GRANT USAGE ON *.* TO `u1`@`localhost`
208 GRANT `r1`@`%` TO `u1`@`localhost`
209 SET GLOBAL mandatory_roles="PUBLIC";
210 DROP USER u1@localhost, r1, r2, r3;
211
212 # Anonymous roles aren't allowed
213 SET GLOBAL mandatory_roles='';
214 CREATE USER ''@'localhost';
215 GRANT ALL ON sys.* TO ''@'localhost';
216 SET GLOBAL mandatory_roles='``@`localhost`';
217 ERROR 42000: Variable 'mandatory_roles' can't be set to the value of '``@`localhost`'
218 SHOW GRANTS;
219 Grants for root@localhost
220 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
221 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
222 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
223 CREATE USER u1@localhost identified by 'foo';
224 GRANT SELECT ON *.* To u1@localhost;
225 SELECT CURRENT_USER();
226 CURRENT_USER()
227 u1@localhost
228 # Should not show any anonymous mandatory role
229 SHOW GRANTS;
230 Grants for u1@localhost
231 GRANT SELECT ON *.* TO `u1`@`localhost`
232 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
233 SELECT CURRENT_USER();
234 CURRENT_USER()
235 u1@localhost
236 SHOW GRANTS;
237 Grants for u1@localhost
238 GRANT SELECT ON *.* TO `u1`@`localhost`
239 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO `u1`@`localhost`
240 SET GLOBAL mandatory_roles='';
241 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SUPER privileges, as well as the ROLE_ADMIN privilege(s) for this operation
242 SET GLOBAL mandatory_roles='foo@localhost';
243 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SUPER privileges, as well as the ROLE_ADMIN privilege(s) for this operation
244 GRANT ''@'localhost' TO u1@localhost;
245 ERROR HY000: Failed to grant ``@`localhost`` to `u1`@`localhost`
246 SET GLOBAL mandatory_roles='';
247 DROP USER u1@localhost, ''@'localhost';
248 # SET ROLE DEFAULT ALL should include mandatory roles
249 CREATE ROLE r1, r2, r3;
250 GRANT SELECT ON *.* TO r1;
251 CREATE USER paul@localhost IDENTIFIED BY 'foo';
252 GRANT r3 TO paul@localhost;
253 GRANT INSERT ON test.* TO r3;
254 SET GLOBAL mandatory_roles='r1,r2,r3';
255 SET DEFAULT ROLE ALL TO paul@localhost;
256 # Should show roles r1,r2,r3
257 SELECT CURRENT_ROLE();
258 CURRENT_ROLE()
259 `r1`@`%`,`r2`@`%`,`r3`@`%`
260 SET ROLE ALL;
261 # Should still show all mandatory roles
262 SELECT CURRENT_ROLE();
263 CURRENT_ROLE()
264 `r1`@`%`,`r2`@`%`,`r3`@`%`
265 ALTER USER paul@localhost DEFAULT ROLE ALL;
266 SET ROLE DEFAULT;
267 # Should show mandatory roles
268 SELECT CURRENT_ROLE();
269 CURRENT_ROLE()
270 `r1`@`%`,`r2`@`%`,`r3`@`%`
271 SET ROLE ALL;
272 # Should show roles r1,r2,r3
273 SELECT CURRENT_ROLE();
274 CURRENT_ROLE()
275 `r1`@`%`,`r2`@`%`,`r3`@`%`
276 # no duplicates
277 SET ROLE r1,r1,r1,r1,r1,r2,r2,r2,r2,r3,r3,r3,r3;
278 SELECT CURRENT_ROLE();
279 CURRENT_ROLE()
280 `r1`@`%`,`r2`@`%`,`r3`@`%`
281 SET GLOBAL mandatory_roles="PUBLIC";
282 DROP USER paul@localhost;
283 DROP ROLE r1,r2,r3;