Debian Codegraph @

mysql-test/suite/auth_sec/t/mandatory_roles.test

raw
1 call mtr.add_suppression("Can't set mandatory_role.*");
2 SHOW VARIABLES LIKE 'mandatory_roles';
3 CREATE ROLE PUBLIC;
4 CREATE DATABASE test2;
5 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON test2.* TO PUBLIC@`%`;
6 SHOW GRANTS FOR PUBLIC;
7 CREATE USER u1@localhost IDENTIFIED BY 'foo';
8 SET DEFAULT ROLE PUBLIC TO u1@localhost;
9 SHOW GRANTS FOR u1@localhost USING PUBLIC;
10 connect(con1,localhost,u1,foo,test2,,,);
11 connection con1;
12 --echo # Current default role should be the mandatory role PUBLIC
13 SELECT current_role();
14 --echo # SHOW GRANTS should show the granted roles
15 SHOW GRANTS;
16 use test2;
17 CREATE TABLE t1 (c1 INT);
18 SET ROLE PUBLIC;
19 DROP TABLE t1;
20 --echo # Make sure we're actually checking for privileges
21 --error ER_DBACCESS_DENIED_ERROR
22 CREATE DATABASE test3;
23 connection default;
24 CREATE ROLE team_share;
25 SET GLOBAL mandatory_roles='PUBLIC@%,team_share';
26 disconnect con1;
27 --source include/restart_mysqld.inc
28 --echo # Restarting the server will reset the mandatory_roles variable
29 SHOW VARIABLES LIKE 'mandatory_roles';
30 SET global mandatory_roles='PUBLIC@%,team_share';
31 connect(con1,localhost,u1,foo,test2,,,);
32 connection con1;
33 --echo # Active role is still PUBLIC; this is determined by default role
34 --echo # settings.
35 SELECT CURRENT_ROLE();
36 --echo # But u1 is also granted team_share
37 SHOW GRANTS;
38 use test2;
39 CREATE TABLE t1 (c1 INT);
40 SET ROLE PUBLIC;
41 DROP TABLE t1;
42 connection default;
43 disconnect con1;
44 --error ER_MANDATORY_ROLE
45 REVOKE PUBLIC FROM u1@localhost;
46 --error ER_MANDATORY_ROLE
47 DROP ROLE PUBLIC;
48 DROP USER u1@localhost;
49 SET GLOBAL mandatory_roles= '';
50 DROP ROLE team_share;
51 DROP DATABASE test2;
52 SET GLOBAL mandatory_roles= 'PUBLIC';
53 --echo #
54 --echo # Tests for activate_all_roles_on_login
55 --echo #
56 CREATE USER u1@localhost IDENTIFIED BY 'foo';
57 CREATE ROLE r1;
58 GRANT r1 TO u1@localhost;
59 GRANT SELECT ON *.* TO r1;
60 SET GLOBAL activate_all_roles_on_login= 'ON';
61 connect(con1,localhost,u1,foo,test,,,);
62 connection con1;
63 --echo # Active role are PUBLIC, r1
64 SELECT CURRENT_ROLE();
65 SHOW GRANTS;
66 connection default;
67 CREATE ROLE r2,r3,r4,r5;
68 GRANT r2,r3,r4,r5 TO u1@localhost;
69 disconnect con1;
70 connect(con1,localhost,u1,foo,test,,,);
71 connection con1;
72 --echo # Grants for u1@localhost should include PUBLIC.
73 SELECT CURRENT_ROLE();
74 SHOW GRANTS;
75 SET ROLE NONE;
76 SHOW GRANTS;
77 connection default;
78 --echo # SHOW GRANTS FOR won't show mandatory roles (PUBLIC in this case)
79 --echo # because we need to avoid breaking the mysqlpump protocol which rely
80 --echo # on SHOW GRANTS FOR to dump user privileges.
81 SHOW GRANTS FOR u1@localhost;
82 SHOW GRANTS FOR u1@localhost USING `PUBLIC`@`%`,`r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%`;
83 SET GLOBAL mandatory_roles= '';
84 DROP ROLE PUBLIC;
85 SET GLOBAL mandatory_roles= 'PUBLIC';
86 SET GLOBAL activate_all_roles_on_login= 'OFF';
87 connection default;
88 REVOKE ALL ON *.* from r1,r2;
89 GRANT SELECT ON *.* TO r1;
90 GRANT INSERT ON *.* TO r2;
91 GRANT r2 TO u1@localhost;
92 SET GLOBAL mandatory_roles='r1';
93 FLUSH PRIVILEGES;
94 SHOW GRANTS FOR u1@localhost USING r1;
95 SHOW GRANTS FOR u1@localhost USING r2;
96 SHOW GRANTS FOR u1@localhost USING r1,r2;
97 --echo # We need to disconnect here to be sure that the u1 session has correct
98 --echo # values for activate_all_roles_on_login and mandatory_roles.
99 disconnect con1;
100 connect(con1,localhost,u1,foo,test,,,);
101 --echo # Current role should be None because ther are no activate roles per
102 --echo # default even though r1 is granted as a mandatory role.
103 SELECT CURRENT_ROLE();
104 SHOW GRANTS;
105 SET ROLE r1;
106 SHOW GRANTS;
107 SET ROLE r2;
108 SHOW GRANTS;
109 SET ROLE r1,r2;
110 SHOW GRANTS;
111 SET ROLE ALL;
112 SHOW GRANTS;
113 connection default;
114 --echo #
115 --echo # Reparse the mandatory role after each CREATE/DROP USER/ROLE
116 --echo #
117 CREATE USER u2@localhost IDENTIFIED BY 'foo';
118 SET GLOBAL mandatory_roles="rr1";
119 FLUSH PRIVILEGES;
120 --error ER_CANNOT_USER
121 DROP ROLE rr1;
122 --echo # Creating the role will cause mandatory_roles to be reparsed
123 CREATE ROLE rr1;
124 GRANT SELECT ON test.* TO rr1;
125 SET GLOBAL activate_all_roles_on_login = 'ON';
126 connect(con2,localhost,u2,foo,test,,,);
127 SELECT CURRENT_USER();
128 --echo # SHOW GRANTS should show that rr1 is granted and effective privileges
129 --echo # should contain SELECT ON test.*
130 SHOW GRANTS;
131 connection default;
132 SET GLOBAL activate_all_roles_on_login = 'OFF';
133 --echo # Don't show the same role twice.
134 SET GLOBAL mandatory_roles="r1, r1";
135 SHOW GRANTS;
136 disconnect con1;
137 disconnect con2;
138
139 DROP USER u1@localhost;
140 DROP USER u2@localhost;
141 SET GLOBAL mandatory_roles='PUBLIC';
142 DROP ROLE r1,r2,r3,r4,r5,rr1;
143
144 CREATE USER u1@localhost IDENTIFIED BY 'foo';
145 CREATE ROLE r1, r2, r3;
146 --error ER_WRONG_VALUE_FOR_VAR
147 SET GLOBAL mandatory_roles="foo,";
148 SET GLOBAL mandatory_roles="aa ; bb : r3, r1, (r2), *r2";
149 connect(con1,localhost,u1,foo,test,,,);
150 SHOW GRANTS;
151 connection default;
152 SET GLOBAL mandatory_roles="PUBLIC";
153 DROP USER u1@localhost, r1, r2, r3;
154
155 --echo
156 --echo # Anonymous roles aren't allowed
157 SET GLOBAL mandatory_roles='';
158 CREATE USER ''@'localhost';
159 GRANT ALL ON sys.* TO ''@'localhost';
160 --error ER_WRONG_VALUE_FOR_VAR
161 SET GLOBAL mandatory_roles='``@`localhost`';
162 SHOW GRANTS;
163 CREATE USER u1@localhost identified by 'foo';
164 GRANT SELECT ON *.* To u1@localhost;
165 connect(con2,localhost,u1,foo,test,,,);
166 SELECT CURRENT_USER();
167 --echo # Should not show any anonymous mandatory role
168 SHOW GRANTS;
169 connection default;
170 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
171 connection con2;
172 SELECT CURRENT_USER();
173 SHOW GRANTS;
174 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
175 SET GLOBAL mandatory_roles='';
176 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
177 SET GLOBAL mandatory_roles='foo@localhost';
178 connection default;
179 disconnect con2;
180 --error ER_FAILED_ROLE_GRANT
181 GRANT ''@'localhost' TO u1@localhost;
182 SET GLOBAL mandatory_roles='';
183 DROP USER u1@localhost, ''@'localhost';
184
185 --echo # SET ROLE DEFAULT ALL should include mandatory roles
186 CREATE ROLE r1, r2, r3;
187 GRANT SELECT ON *.* TO r1;
188 CREATE USER paul@localhost IDENTIFIED BY 'foo';
189 GRANT r3 TO paul@localhost;
190 GRANT INSERT ON test.* TO r3;
191 SET GLOBAL mandatory_roles='r1,r2,r3';
192 SET DEFAULT ROLE ALL TO paul@localhost;
193 connect(con3,localhost,paul,foo,test,,,);
194 --echo # Should show roles r1,r2,r3
195 SELECT CURRENT_ROLE();
196 SET ROLE ALL;
197 --echo # Should still show all mandatory roles
198 SELECT CURRENT_ROLE();
199 connection default;
200 ALTER USER paul@localhost DEFAULT ROLE ALL;
201 connection con3;
202 SET ROLE DEFAULT;
203 --echo # Should show mandatory roles
204 SELECT CURRENT_ROLE();
205 SET ROLE ALL;
206 --echo # Should show roles r1,r2,r3
207 SELECT CURRENT_ROLE();
208 --echo # no duplicates
209 SET ROLE r1,r1,r1,r1,r1,r2,r2,r2,r2,r3,r3,r3,r3;
210 SELECT CURRENT_ROLE();
211 connection default;
212 SET GLOBAL mandatory_roles="PUBLIC";
213 DROP USER paul@localhost;
214 DROP ROLE r1,r2,r3;
215 disconnect con3;
216