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
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
SHOW GRANTS FOR root@invalid_host;
ERROR 42000: There is no such grant defined for user 'root' on host 'invalid_host'
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;
Grants for test@%
GRANT `foo` TO `test`@`%`
GRANT USAGE ON *.* TO `test`@`%`
show grants for foo;
Grants for foo
GRANT USAGE ON *.* TO `foo`
show grants for foo@'%';
ERROR 42000: Access denied for user 'test'@'%' to database 'mysql'
connection default;
drop user test, foo;
drop role foo;
CREATE TABLE t1 (a INT);
LOCK TABLE t1 WRITE;
REVOKE EXECUTE ON PROCEDURE sp FROM u;
ERROR HY000: Table 'procs_priv' was not locked with LOCK TABLES
REVOKE PROCESS ON *.* FROM u;
ERROR HY000: Table 'db' was not locked with LOCK TABLES
DROP TABLE t1;
#
# MDEV-20076: SHOW GRANTS does not quote role names properly
#
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';
Grants for role1
GRANT USAGE ON *.* TO `role1`
GRANT SELECT ON `mysql`.`user` TO `role1`
show grants for 'fetch';
Grants for fetch
GRANT USAGE ON *.* TO `fetch`
GRANT SELECT ON `mysql`.`user` TO `fetch`
show grants for 'role-1';
Grants for role-1
GRANT USAGE ON *.* TO `role-1`
GRANT SELECT ON `mysql`.`user` TO `role-1`
show grants for 'rock\'n\'roll';
Grants for rock'n'roll
GRANT USAGE ON *.* TO `rock'n'roll`
GRANT SELECT ON `mysql`.`user` TO `rock'n'roll`
show grants for 'user1'@'localhost';
Grants for user1@localhost
GRANT `role1` TO `user1`@`localhost`
GRANT USAGE ON *.* TO `user1`@`localhost`
show grants for 'fetch'@'localhost';
Grants for fetch@localhost
GRANT `fetch` TO `fetch`@`localhost`
GRANT USAGE ON *.* TO `fetch`@`localhost`
show grants for 'user-1'@'localhost';
Grants for user-1@localhost
GRANT `role-1` TO `user-1`@`localhost`
GRANT USAGE ON *.* TO `user-1`@`localhost`
show grants for 'O\'Brien'@'localhost';
Grants for O'Brien@localhost
GRANT `rock'n'roll` TO `O'Brien`@`localhost`
GRANT USAGE ON *.* TO `O'Brien`@`localhost`
set @save_sql_quote_show_create= @@sql_quote_show_create;
set @@sql_quote_show_create= OFF;
show grants for 'role1';
Grants for role1
GRANT USAGE ON *.* TO role1
GRANT SELECT ON `mysql`.`user` TO role1
show grants for 'fetch';
Grants for fetch
GRANT USAGE ON *.* TO `fetch`
GRANT SELECT ON `mysql`.`user` TO `fetch`
show grants for 'role-1';
Grants for role-1
GRANT USAGE ON *.* TO `role-1`
GRANT SELECT ON `mysql`.`user` TO `role-1`
show grants for 'rock\'n\'roll';
Grants for rock'n'roll
GRANT USAGE ON *.* TO `rock'n'roll`
GRANT SELECT ON `mysql`.`user` TO `rock'n'roll`
show grants for 'user1'@'localhost';
Grants for user1@localhost
GRANT role1 TO user1@localhost
GRANT USAGE ON *.* TO user1@localhost
show grants for 'fetch'@'localhost';
Grants for fetch@localhost
GRANT `fetch` TO `fetch`@localhost
GRANT USAGE ON *.* TO `fetch`@localhost
show grants for 'user-1'@'localhost';
Grants for user-1@localhost
GRANT `role-1` TO `user-1`@localhost
GRANT USAGE ON *.* TO `user-1`@localhost
show grants for 'O\'Brien'@'localhost';
Grants for O'Brien@localhost
GRANT `rock'n'roll` TO `O'Brien`@localhost
GRANT USAGE ON *.* TO `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';
# End of 10.3 tests
create user u1@h identified with 'mysql_native_password' using 'pwd';
ERROR HY000: Password hash should be a 41-digit hexadecimal number
create user u1@h identified with 'mysql_native_password' using password('pwd');
create user u2@h identified with 'mysql_native_password' using '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';
create user u3@h identified with 'mysql_native_password';
set password for u3@h = 'pwd';
ERROR HY000: Password hash should be a 41-digit hexadecimal number
set password for u3@h = password('pwd');
create user u4@h identified with 'mysql_native_password';
set password for u4@h = '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';
create user u5@h identified with 'mysql_old_password' using 'pwd';
ERROR HY000: Password hash should be a 16-digit hexadecimal number
create user u5@h identified with 'mysql_old_password' using password('pwd');
create user u6@h identified with 'mysql_old_password' using '78a302dd267f6044';
create user u7@h identified with 'mysql_old_password';
set password for u7@h = 'pwd';
ERROR HY000: Password hash should be a 41-digit hexadecimal number
set password for u7@h = old_password('pwd');
create user u8@h identified with 'mysql_old_password';
set password for u8@h = '78a302dd267f6044';
select user,host,plugin,authentication_string from mysql.user where host='h';
User Host plugin authentication_string
u1 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
u2 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
u3 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
u4 h mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
u5 h mysql_old_password 78a302dd267f6044
u6 h mysql_old_password 78a302dd267f6044
u7 h mysql_old_password 78a302dd267f6044
u8 h mysql_old_password 78a302dd267f6044
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;
CREATE USER for u1@h
CREATE USER `u1`@`h` IDENTIFIED BY PASSWORD 'bad'
show create user u2@h;
CREATE USER for u2@h
CREATE USER `u2`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
show create user u3@h;
CREATE USER for u3@h
CREATE USER `u3`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
show create user u4@h;
CREATE USER for u4@h
CREATE USER `u4`@`h` IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
show create user u5@h;
CREATE USER for u5@h
CREATE USER `u5`@`h` IDENTIFIED BY PASSWORD 'bad'
show create user u6@h;
CREATE USER for u6@h
CREATE USER `u6`@`h` IDENTIFIED BY PASSWORD '78a302dd267f6044'
show create user u7@h;
CREATE USER for u7@h
CREATE USER `u7`@`h` IDENTIFIED BY PASSWORD '78a302dd267f6044'
show create user u8@h;
CREATE USER for u8@h
CREATE USER `u8`@`h` IDENTIFIED VIA nonexistent USING '78a302dd267f6044'
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_';
User Select_priv plugin authentication_string
u1 Y mysql_native_password bad
u2 Y mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
u3 Y mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
u4 Y mysql_native_password *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
u5 Y mysql_old_password bad
u6 Y mysql_old_password 78a302dd267f6044
u7 Y mysql_old_password 78a302dd267f6044
u8 Y nonexistent 78a302dd267f6044
drop user u1@h, u2@h, u3@h, u4@h, u5@h, u6@h, u7@h, u8@h;
create database mysqltest_1;
create user twg@'%' identified by 'test';
create table mysqltest_1.t1(id int);
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;
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;
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@'%';
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='';
# End of 10.4 tests
|
