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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
-- source include/not_embedded.inc
#
# MDEV-6625 SHOW GRANTS for current_user_name@wrong_host_name
#
--error ER_NONEXISTING_GRANT
SHOW GRANTS FOR root@invalid_host;
#
# MDEV-9580 SHOW GRANTS FOR <current_user> fails
#
create user test;
create user foo;
create role foo;
grant foo to test;
--connect (conn_1, localhost, test,,)
set role foo;
show grants for test; # user
show grants for foo; # role
--error ER_DBACCESS_DENIED_ERROR
show grants for foo@'%'; # user
--connection default
drop user test, foo;
drop role foo;
#
# MDEV-17975 Assertion `! is_set()' or `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed upon REVOKE under LOCK TABLE
#
CREATE TABLE t1 (a INT);
LOCK TABLE t1 WRITE;
--error ER_TABLE_NOT_LOCKED
REVOKE EXECUTE ON PROCEDURE sp FROM u;
--error ER_TABLE_NOT_LOCKED
REVOKE PROCESS ON *.* FROM u;
DROP TABLE t1;
--echo #
--echo # MDEV-20076: SHOW GRANTS does not quote role names properly
--echo #
create role 'role1';
create role 'fetch';
create role 'role-1';
create role 'rock\'n\'roll';
create user 'user1'@'localhost';
create user 'fetch'@'localhost';
create user 'user-1'@'localhost';
create user 'O\'Brien'@'localhost';
grant select on mysql.user to role1;
grant select on mysql.user to 'fetch';
grant select on mysql.user to 'role-1';
grant select on mysql.user to 'rock\'n\'roll';
GRANT 'role1' TO 'user1'@'localhost';
GRANT 'fetch' TO 'fetch'@'localhost';
GRANT 'role-1' TO 'user-1'@'localhost';
GRANT 'rock\'n\'roll' TO 'O\'Brien'@'localhost';
show grants for 'role1';
show grants for 'fetch';
show grants for 'role-1';
show grants for 'rock\'n\'roll';
show grants for 'user1'@'localhost';
show grants for 'fetch'@'localhost';
show grants for 'user-1'@'localhost';
show grants for 'O\'Brien'@'localhost';
set @save_sql_quote_show_create= @@sql_quote_show_create;
set @@sql_quote_show_create= OFF;
show grants for 'role1';
show grants for 'fetch';
show grants for 'role-1';
show grants for 'rock\'n\'roll';
show grants for 'user1'@'localhost';
show grants for 'fetch'@'localhost';
show grants for 'user-1'@'localhost';
show grants for 'O\'Brien'@'localhost';
set @@sql_quote_show_create= @save_sql_quote_show_create;
drop role 'role1';
drop role 'fetch';
drop role 'role-1';
drop role 'rock\'n\'roll';
drop user 'user1'@'localhost';
drop user 'fetch'@'localhost';
drop user 'user-1'@'localhost';
drop user 'O\'Brien'@'localhost';
--echo # End of 10.3 tests
#
# MDEV-12321 authentication plugin: SET PASSWORD support
#
error ER_PASSWD_LENGTH;
create user u1@h identified with 'mysql_native_password' using 'pwd';
create user u1@h identified with 'mysql_native_password' using password('pwd');
let p=`select password('pwd')`;
eval create user u2@h identified with 'mysql_native_password' using '$p';
create user u3@h identified with 'mysql_native_password';
error ER_PASSWD_LENGTH;
set password for u3@h = 'pwd';
set password for u3@h = password('pwd');
create user u4@h identified with 'mysql_native_password';
eval set password for u4@h = '$p';
error ER_PASSWD_LENGTH;
create user u5@h identified with 'mysql_old_password' using 'pwd';
create user u5@h identified with 'mysql_old_password' using password('pwd');
let p=`select old_password('pwd')`;
eval create user u6@h identified with 'mysql_old_password' using '$p';
create user u7@h identified with 'mysql_old_password';
error ER_PASSWD_LENGTH;
set password for u7@h = 'pwd';
set password for u7@h = old_password('pwd');
create user u8@h identified with 'mysql_old_password';
eval set password for u8@h = '$p';
sorted_result;
select user,host,plugin,authentication_string from mysql.user where host='h';
# test with invalid entries
update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u1';
update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u5';
update mysql.global_priv set priv=json_set(priv, '$.plugin', 'nonexistent') where user='u8';
flush privileges;
show create user u1@h;
show create user u2@h;
show create user u3@h;
show create user u4@h;
show create user u5@h;
show create user u6@h;
show create user u7@h;
show create user u8@h;
grant select on *.* to u1@h;
grant select on *.* to u2@h;
grant select on *.* to u3@h;
grant select on *.* to u4@h;
grant select on *.* to u5@h;
grant select on *.* to u6@h;
grant select on *.* to u7@h;
grant select on *.* to u8@h;
select user,select_priv,plugin,authentication_string from mysql.user where user like 'u_';
# but they still can be dropped
drop user u1@h, u2@h, u3@h, u4@h, u5@h, u6@h, u7@h, u8@h;
#
# MDEV-14735 better matching order for grants
# MDEV-14732 mysql.db privileges evaluated on order of grants rather than hierarchically
# MDEV-8269 Correct fix for Bug #20181776 :- ACCESS CONTROL DOESN'T MATCH MOST SPECIFIC HOST WHEN IT CONTAINS WILDCARD
#
create database mysqltest_1;
create user twg@'%' identified by 'test';
create table mysqltest_1.t1(id int);
# MDEV-14732 test case
grant create, drop on `mysqltest_1%`.* to twg@'%';
grant all privileges on `mysqltest_1`.* to twg@'%';
connect conn1,localhost,twg,test,mysqltest_1;
insert into t1 values(1);
disconnect conn1;
connection default;
# prefix%suffix
revoke all privileges, grant option from twg@'%';
grant create, drop on `mysqlt%`.* to twg@'%';
grant all privileges on `mysqlt%1`.* to twg@'%';
connect conn1,localhost,twg,test,mysqltest_1;
insert into t1 values(1);
disconnect conn1;
connection default;
# more specific can even have a shorter prefix
revoke all privileges, grant option from twg@'%';
grant create, drop on `mysqlt%`.* to twg@'%';
grant all privileges on `%mysqltest_1`.* to twg@'%';
connect conn1,localhost,twg,test,mysqltest_1;
insert into t1 values(1);
disconnect conn1;
connection default;
drop database mysqltest_1;
drop user twg@'%';
#
# test the empty db case
#
insert mysql.tables_priv (host,db,user,table_name,grantor,table_priv) values ('localhost','','otto','t1','root@localhost','select');
flush privileges;
delete from mysql.tables_priv where db='';
#
# MDEV-21560 Assertion `grant_table || grant_table_role' failed in check_grant_all_columns
#
create database db;
create table db.t1 (a int);
insert into db.t1 values (1);
create user foo;
grant delete on db.* to foo;
--connect (con1,localhost,foo,,)
show create table db.t1;
--error ER_COLUMNACCESS_DENIED_ERROR
delete from db.t1 returning *;
--disconnect con1
--connection default
drop database db;
drop user foo;
--echo # End of 10.4 tests
|
