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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
#
# MDEV-29458 Role grant commands do not propagate all grants
#
create user foo;
create database some_db;
create table some_db.t1 (a int, b int, secret int);
CREATE PROCEDURE some_db.p1 (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM some_db.t1;
END;
//
CREATE FUNCTION some_db.f1 (param1 INT) RETURNS INT
BEGIN
DECLARE c INT;
SET c = 100;
RETURN param1 + c;
END;
//
#
# These roles will form a two level hierarchy.
# The "select" role will have the select privilege, while
# the active role will inherit the select role.
#
# The active role will be granted a different privilege but on the same
# level (global, database, table, proc respectively) *after* the 'select'
# role has been granted its select privilege.
#
create role r_select_global;
create role r_active_global;
create role r_select_database;
create role r_active_database;
create role r_select_table;
create role r_active_table;
create role r_select_column;
create role r_active_column;
create role r_execute_proc;
create role r_active_proc;
create role r_execute_func;
create role r_active_func;
grant r_select_global to r_active_global;
grant r_select_database to r_active_database;
grant r_select_table to r_active_table;
grant r_select_column to r_active_column;
grant r_execute_proc to r_active_proc;
grant r_execute_func to r_active_func;
#
# These 3 roles form a chain, where only the upper level has select
# privileges and the middle level will receive a grant for the same level
# privilege, but different kind (for example select on upper and insert
# on middle).
#
# The lower level should inherit both rights.
#
create role upper_level;
create role middle_level;
create role lower_level;
grant upper_level to middle_level;
grant middle_level to lower_level;
grant r_active_global to foo;
grant r_active_database to foo;
grant r_active_table to foo;
grant r_active_column to foo;
grant r_active_proc to foo;
grant r_active_func to foo;
grant lower_level to foo;
grant select on *.* to r_select_global;
grant select on some_db.* to r_select_database;
grant select on some_db.t1 to r_select_table;
grant select(a) on some_db.t1 to r_select_column;
grant select on *.* to upper_level;
grant execute on procedure some_db.p1 to r_execute_proc;
grant execute on function some_db.f1 to r_execute_func;
#
# Granting a privilege different than select on the corresponding level.
# This tests that the base role correctly inherits its granted roles
# privileges.
#
grant insert on *.* to r_active_global;
grant insert on some_db.* to r_active_database;
grant insert on some_db.t1 to r_active_table;
grant insert(a) on some_db.t1 to r_active_column;
grant insert on *.* to middle_level;
grant alter routine on procedure some_db.p1 to r_active_proc;
grant alter routine on function some_db.f1 to r_active_func;
connect con1, localhost, foo,,;
select * from some_db.t1;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `some_db`.`t1`
#
# Before MDEV-29458 fix, all these commands would return
# ER_TABLEACCESS_DENIED_ERROR
#
set role r_active_global;
select * from some_db.t1;
a b secret
set role r_active_database;
select * from some_db.t1;
a b secret
set role r_active_table;
select * from some_db.t1;
a b secret
set role r_active_column;
select a from some_db.t1;
a
set role lower_level;
select * from some_db.t1;
a b secret
set role r_active_proc;
set @var=100;
call some_db.p1(@var);
set role r_active_func;
select some_db.f1(10);
some_db.f1(10)
110
disconnect con1;
#
# Cleanup.
#
connection default;
drop database some_db;
drop role r_select_global, r_select_database, r_select_table, r_select_column;
drop role r_active_global, r_active_database, r_active_table, r_active_column;
drop role r_execute_proc, r_execute_func;
drop role r_active_proc, r_active_func;
drop role upper_level, middle_level, lower_level;
drop user foo;
#
# Test that dropping of roles clears the intermediate generated
# (such as an `acl_dbs` element with 0 init_access, but with access != 0)
# datastructures.
#
create role test_role1;
create role test_role2;
grant test_role2 to test_role1;
grant select on mysql.* to test_role2;
grant select on mysql.user to test_role2;
grant select(user) on mysql.user to test_role2;
drop role test_role1, test_role2;
create role test_role1;
drop role test_role1;
#
# MDEV-29851 Cached role privileges are not invalidated when needed
#
create role admin;
create role student;
create database crm;
grant create on crm.* to admin;
grant select on crm.* to student;
create user intern@localhost;
grant student to intern@localhost;
set default role student for intern@localhost;
connect con1, localhost, intern;
use crm;
disconnect con1;
connection default;
grant admin to student;
connect con1, localhost, intern;
use crm;
create table t1 (a int);
disconnect con1;
connection default;
drop user intern@localhost;
drop role student;
drop role admin;
drop database crm;
#
# MDEV-30526 Assertion `rights == merged->cols' failed in update_role_columns
#
create table t1 ( pk int, i int);
create role a;
grant select (i), update (pk) on t1 to a;
revoke update (pk) on t1 from a;
show grants for a;
Grants for a
GRANT USAGE ON *.* TO `a`
GRANT SELECT (`i`) ON `test`.`t1` TO `a`
drop role a;
drop table t1;
#
# End of 10.3 tests
#
|
