Debian Codegraph @

mysql-test/suite/roles/role_grant_propagate.result

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