Debian Codegraph @

mysql-test/suite/roles/role_grant_propagate.test

raw
1 --source include/not_embedded.inc
2
3 --echo #
4 --echo # MDEV-29458 Role grant commands do not propagate all grants
5 --echo #
6
7 create user foo;
8 create database some_db;
9 create table some_db.t1 (a int, b int, secret int);
10
11 delimiter //;
12 CREATE PROCEDURE some_db.p1 (OUT param1 INT)
13 BEGIN
14 SELECT COUNT(*) INTO param1 FROM some_db.t1;
15 END;
16 //
17 delimiter ;//
18
19 delimiter //;
20 CREATE FUNCTION some_db.f1 (param1 INT) RETURNS INT
21 BEGIN
22 DECLARE c INT;
23 SET c = 100;
24 RETURN param1 + c;
25 END;
26 //
27 delimiter ;//
28
29 --echo #
30 --echo # These roles will form a two level hierarchy.
31 --echo # The "select" role will have the select privilege, while
32 --echo # the active role will inherit the select role.
33 --echo #
34 --echo # The active role will be granted a different privilege but on the same
35 --echo # level (global, database, table, proc respectively) *after* the 'select'
36 --echo # role has been granted its select privilege.
37 --echo #
38
39 create role r_select_global;
40 create role r_active_global;
41
42 create role r_select_database;
43 create role r_active_database;
44
45 create role r_select_table;
46 create role r_active_table;
47
48 create role r_select_column;
49 create role r_active_column;
50
51 create role r_execute_proc;
52 create role r_active_proc;
53
54 create role r_execute_func;
55 create role r_active_func;
56
57 grant r_select_global to r_active_global;
58 grant r_select_database to r_active_database;
59 grant r_select_table to r_active_table;
60 grant r_select_column to r_active_column;
61 grant r_execute_proc to r_active_proc;
62 grant r_execute_func to r_active_func;
63
64 --echo #
65 --echo # These 3 roles form a chain, where only the upper level has select
66 --echo # privileges and the middle level will receive a grant for the same level
67 --echo # privilege, but different kind (for example select on upper and insert
68 --echo # on middle).
69 --echo #
70 --echo # The lower level should inherit both rights.
71 --echo #
72 create role upper_level;
73 create role middle_level;
74 create role lower_level;
75
76 grant upper_level to middle_level;
77 grant middle_level to lower_level;
78
79 grant r_active_global to foo;
80 grant r_active_database to foo;
81 grant r_active_table to foo;
82 grant r_active_column to foo;
83 grant r_active_proc to foo;
84 grant r_active_func to foo;
85 grant lower_level to foo;
86
87 grant select on *.* to r_select_global;
88 grant select on some_db.* to r_select_database;
89 grant select on some_db.t1 to r_select_table;
90 grant select(a) on some_db.t1 to r_select_column;
91 grant select on *.* to upper_level;
92
93 grant execute on procedure some_db.p1 to r_execute_proc;
94 grant execute on function some_db.f1 to r_execute_func;
95
96
97 --echo #
98 --echo # Granting a privilege different than select on the corresponding level.
99 --echo # This tests that the base role correctly inherits its granted roles
100 --echo # privileges.
101 --echo #
102 grant insert on *.* to r_active_global;
103 grant insert on some_db.* to r_active_database;
104 grant insert on some_db.t1 to r_active_table;
105 grant insert(a) on some_db.t1 to r_active_column;
106 grant insert on *.* to middle_level;
107
108 grant alter routine on procedure some_db.p1 to r_active_proc;
109 grant alter routine on function some_db.f1 to r_active_func;
110
111 --connect (con1, localhost, foo,,)
112 --error ER_TABLEACCESS_DENIED_ERROR
113 select * from some_db.t1;
114
115 --echo #
116 --echo # Before MDEV-29458 fix, all these commands would return
117 --echo # ER_TABLEACCESS_DENIED_ERROR
118 --echo #
119 set role r_active_global;
120 select * from some_db.t1;
121 set role r_active_database;
122 select * from some_db.t1;
123 set role r_active_table;
124 select * from some_db.t1;
125 set role r_active_column;
126 select a from some_db.t1;
127 set role lower_level;
128 select * from some_db.t1;
129
130 set role r_active_proc;
131 set @var=100;
132 call some_db.p1(@var);
133
134 set role r_active_func;
135 select some_db.f1(10);
136
137 disconnect con1;
138
139 --echo #
140 --echo # Cleanup.
141 --echo #
142 connection default;
143
144 drop database some_db;
145 drop role r_select_global, r_select_database, r_select_table, r_select_column;
146 drop role r_active_global, r_active_database, r_active_table, r_active_column;
147 drop role r_execute_proc, r_execute_func;
148 drop role r_active_proc, r_active_func;
149 drop role upper_level, middle_level, lower_level;
150 drop user foo;
151
152 --echo #
153 --echo # Test that dropping of roles clears the intermediate generated
154 --echo # (such as an `acl_dbs` element with 0 init_access, but with access != 0)
155 --echo # datastructures.
156 --echo #
157 create role test_role1;
158 create role test_role2;
159
160 grant test_role2 to test_role1;
161 grant select on mysql.* to test_role2;
162 grant select on mysql.user to test_role2;
163 grant select(user) on mysql.user to test_role2;
164 drop role test_role1, test_role2;
165
166 create role test_role1;
167 drop role test_role1;
168
169 --echo #
170 --echo # MDEV-29851 Cached role privileges are not invalidated when needed
171 --echo #
172 create role admin;
173 create role student;
174 create database crm;
175 grant create on crm.* to admin;
176 grant select on crm.* to student;
177 create user intern@localhost;
178 grant student to intern@localhost;
179 set default role student for intern@localhost;
180
181 connect con1, localhost, intern;
182 use crm;
183 disconnect con1;
184
185 connection default;
186 grant admin to student;
187
188 connect con1, localhost, intern;
189 use crm;
190 create table t1 (a int);
191 disconnect con1;
192
193 connection default;
194 drop user intern@localhost;
195 drop role student;
196 drop role admin;
197 drop database crm;
198
199 --echo #
200 --echo # MDEV-30526 Assertion `rights == merged->cols' failed in update_role_columns
201 --echo #
202 create table t1 ( pk int, i int);
203 create role a;
204 grant select (i), update (pk) on t1 to a;
205 revoke update (pk) on t1 from a;
206 show grants for a;
207 drop role a;
208 drop table t1;
209
210 --echo #
211 --echo # End of 10.3 tests
212 --echo #