1
# Test of GRANT commands
3
# Grant tests not performed with embedded server
4
-- source include/not_embedded.inc
6
# Save the initial number of concurrent sessions
7
--source include/count_sessions.inc
9
SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
10
SET GLOBAL log_bin_trust_function_creators = 1;
14
drop table if exists t1;
15
drop database if exists mysqltest;
18
connect (master,localhost,root,,);
23
# Test that SSL options works properly
26
delete from mysql.user where user='mysqltest_1';
27
delete from mysql.db where user='mysqltest_1';
29
grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
30
show grants for mysqltest_1@localhost;
31
grant delete on mysqltest.* to mysqltest_1@localhost;
32
select * from mysql.user where user="mysqltest_1";
33
show grants for mysqltest_1@localhost;
34
revoke delete on mysqltest.* from mysqltest_1@localhost;
35
show grants for mysqltest_1@localhost;
36
grant select on mysqltest.* to mysqltest_1@localhost require NONE;
37
show grants for mysqltest_1@localhost;
38
grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB";
39
show grants for mysqltest_1@localhost;
40
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
41
show grants for mysqltest_1@localhost;
42
delete from mysql.user where user='mysqltest_1';
46
# Test of GRANTS specifying user limits
48
delete from mysql.user where user='mysqltest_1';
50
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10;
51
select * from mysql.user where user="mysqltest_1";
52
show grants for mysqltest_1@localhost;
53
grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30;
54
select * from mysql.user where user="mysqltest_1";
55
show grants for mysqltest_1@localhost;
56
# This is just to double check that one won't ignore results of selects
58
show grants for mysqltest_1@localhost;
59
delete from mysql.user where user='mysqltest_1';
63
# Test that the new db privileges are stored/retrieved correctly
66
grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
67
show grants for mysqltest_1@localhost;
69
show grants for mysqltest_1@localhost;
70
revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
71
show grants for mysqltest_1@localhost;
72
grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
74
show grants for mysqltest_1@localhost;
75
revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
76
show grants for mysqltest_1@localhost;
77
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
78
delete from mysql.user where user='mysqltest_1';
80
grant usage on test.* to mysqltest_1@localhost with grant option;
81
show grants for mysqltest_1@localhost;
82
delete from mysql.user where user='mysqltest_1';
83
delete from mysql.db where user='mysqltest_1';
84
delete from mysql.tables_priv where user='mysqltest_1';
85
delete from mysql.columns_priv where user='mysqltest_1';
87
--error ER_NONEXISTING_GRANT
88
show grants for mysqltest_1@localhost;
91
# Test what happens when you have same table and colum level grants
94
create table t1 (a int);
95
GRANT select,update,insert on t1 to mysqltest_1@localhost;
96
GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
97
show grants for mysqltest_1@localhost;
98
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
99
REVOKE select (a), update on t1 from mysqltest_1@localhost;
100
show grants for mysqltest_1@localhost;
101
REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
102
show grants for mysqltest_1@localhost;
103
GRANT select,references on t1 to mysqltest_1@localhost;
104
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
105
grant all on test.* to mysqltest_3@localhost with grant option;
106
revoke all on test.* from mysqltest_3@localhost;
107
show grants for mysqltest_3@localhost;
108
revoke grant option on test.* from mysqltest_3@localhost;
109
show grants for mysqltest_3@localhost;
110
grant all on test.t1 to mysqltest_2@localhost with grant option;
111
revoke all on test.t1 from mysqltest_2@localhost;
112
show grants for mysqltest_2@localhost;
113
revoke grant option on test.t1 from mysqltest_2@localhost;
114
show grants for mysqltest_2@localhost;
115
delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
116
delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
117
delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
118
delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
123
# Test some error conditions
125
--error ER_WRONG_USAGE
126
GRANT FILE on mysqltest.* to mysqltest_1@localhost;
127
select 1; # To test that the previous command didn't cause problems
130
# Bug#4898 User privileges depending on ORDER BY Settings of table db
132
insert into mysql.user (host, user) values ('localhost', 'test11');
133
insert into mysql.db (host, db, user, select_priv) values
134
('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
135
alter table mysql.db order by db asc;
137
show grants for test11@localhost;
138
alter table mysql.db order by db desc;
140
show grants for test11@localhost;
141
delete from mysql.user where user='test11';
142
delete from mysql.db where user='test11';
145
# Bug#6123 GRANT USAGE inserts useless Db row
147
create database mysqltest1;
148
grant usage on mysqltest1.* to test6123 identified by 'magic123';
149
select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1";
150
delete from mysql.user where user='test6123';
151
drop database mysqltest1;
154
# Test for 'drop user', 'revoke privileges, grant'
157
create table t1 (a int);
158
grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION;
159
show grants for drop_user2@localhost;
160
revoke all privileges, grant option from drop_user2@localhost;
161
drop user drop_user2@localhost;
163
grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION;
164
grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION;
165
grant select(a) on test.t1 to drop_user@localhost;
166
show grants for drop_user@localhost;
169
# Bug#3086 SHOW GRANTS doesn't follow ANSI_QUOTES
171
set sql_mode=ansi_quotes;
172
show grants for drop_user@localhost;
173
set sql_mode=default;
175
set sql_quote_show_create=0;
176
show grants for drop_user@localhost;
177
set sql_mode="ansi_quotes";
178
show grants for drop_user@localhost;
179
set sql_quote_show_create=1;
180
show grants for drop_user@localhost;
182
show grants for drop_user@localhost;
184
revoke all privileges, grant option from drop_user@localhost;
185
show grants for drop_user@localhost;
186
drop user drop_user@localhost;
187
--error ER_REVOKE_GRANTS
188
revoke all privileges, grant option from drop_user@localhost;
190
grant select(a) on test.t1 to drop_user1@localhost;
191
grant select on test.t1 to drop_user2@localhost;
192
grant select on test.* to drop_user3@localhost;
193
grant select on *.* to drop_user4@localhost;
194
# Drop user now implicitly revokes all privileges.
195
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
196
drop_user4@localhost;
197
--error ER_REVOKE_GRANTS
198
revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,
199
drop_user3@localhost, drop_user4@localhost;
200
--error ER_CANNOT_USER
201
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
202
drop_user4@localhost;
204
grant usage on *.* to mysqltest_1@localhost identified by "password";
205
grant select, update, insert on test.* to mysqltest_1@localhost;
206
show grants for mysqltest_1@localhost;
207
drop user mysqltest_1@localhost;
210
# Bug#3403 Wrong encoding in SHOW GRANTS output
215
CREATE TABLE ��� (��� INT);
217
GRANT SELECT ON ��.* TO ����@localhost;
218
SHOW GRANTS FOR ����@localhost;
219
REVOKE SELECT ON ��.* FROM ����@localhost;
221
GRANT SELECT ON ��.��� TO ����@localhost;
222
SHOW GRANTS FOR ����@localhost;
223
REVOKE SELECT ON ��.��� FROM ����@localhost;
225
GRANT SELECT (���) ON ��.��� TO ����@localhost;
226
SHOW GRANTS FOR ����@localhost;
227
REVOKE SELECT (���) ON ��.��� FROM ����@localhost;
229
# Revoke does not drop user. Leave a clean user table for the next tests.
230
DROP USER ����@localhost;
236
# Bug#5831 REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything
239
CREATE TABLE t1 (a int );
240
CREATE TABLE t2 LIKE t1;
241
CREATE TABLE t3 LIKE t1;
242
CREATE TABLE t4 LIKE t1;
243
CREATE TABLE t5 LIKE t1;
244
CREATE TABLE t6 LIKE t1;
245
CREATE TABLE t7 LIKE t1;
246
CREATE TABLE t8 LIKE t1;
247
CREATE TABLE t9 LIKE t1;
248
CREATE TABLE t10 LIKE t1;
249
CREATE DATABASE testdb1;
250
CREATE DATABASE testdb2;
251
CREATE DATABASE testdb3;
252
CREATE DATABASE testdb4;
253
CREATE DATABASE testdb5;
254
CREATE DATABASE testdb6;
255
CREATE DATABASE testdb7;
256
CREATE DATABASE testdb8;
257
CREATE DATABASE testdb9;
258
CREATE DATABASE testdb10;
259
GRANT ALL ON testdb1.* TO testuser@localhost;
260
GRANT ALL ON testdb2.* TO testuser@localhost;
261
GRANT ALL ON testdb3.* TO testuser@localhost;
262
GRANT ALL ON testdb4.* TO testuser@localhost;
263
GRANT ALL ON testdb5.* TO testuser@localhost;
264
GRANT ALL ON testdb6.* TO testuser@localhost;
265
GRANT ALL ON testdb7.* TO testuser@localhost;
266
GRANT ALL ON testdb8.* TO testuser@localhost;
267
GRANT ALL ON testdb9.* TO testuser@localhost;
268
GRANT ALL ON testdb10.* TO testuser@localhost;
269
GRANT SELECT ON test.t1 TO testuser@localhost;
270
GRANT SELECT ON test.t2 TO testuser@localhost;
271
GRANT SELECT ON test.t3 TO testuser@localhost;
272
GRANT SELECT ON test.t4 TO testuser@localhost;
273
GRANT SELECT ON test.t5 TO testuser@localhost;
274
GRANT SELECT ON test.t6 TO testuser@localhost;
275
GRANT SELECT ON test.t7 TO testuser@localhost;
276
GRANT SELECT ON test.t8 TO testuser@localhost;
277
GRANT SELECT ON test.t9 TO testuser@localhost;
278
GRANT SELECT ON test.t10 TO testuser@localhost;
279
GRANT SELECT (a) ON test.t1 TO testuser@localhost;
280
GRANT SELECT (a) ON test.t2 TO testuser@localhost;
281
GRANT SELECT (a) ON test.t3 TO testuser@localhost;
282
GRANT SELECT (a) ON test.t4 TO testuser@localhost;
283
GRANT SELECT (a) ON test.t5 TO testuser@localhost;
284
GRANT SELECT (a) ON test.t6 TO testuser@localhost;
285
GRANT SELECT (a) ON test.t7 TO testuser@localhost;
286
GRANT SELECT (a) ON test.t8 TO testuser@localhost;
287
GRANT SELECT (a) ON test.t9 TO testuser@localhost;
288
GRANT SELECT (a) ON test.t10 TO testuser@localhost;
289
REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost;
290
SHOW GRANTS FOR testuser@localhost;
291
DROP USER testuser@localhost;
292
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
293
DROP DATABASE testdb1;
294
DROP DATABASE testdb2;
295
DROP DATABASE testdb3;
296
DROP DATABASE testdb4;
297
DROP DATABASE testdb5;
298
DROP DATABASE testdb6;
299
DROP DATABASE testdb7;
300
DROP DATABASE testdb8;
301
DROP DATABASE testdb9;
302
DROP DATABASE testdb10;
305
# Bug#6932 a problem with 'revoke ALL PRIVILEGES'
308
create table t1(a int, b int, c int, d int);
309
grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost;
310
show grants for grant_user@localhost;
311
select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name;
312
revoke ALL PRIVILEGES on t1 from grant_user@localhost;
313
show grants for grant_user@localhost;
314
select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv;
315
drop user grant_user@localhost;
319
# Bug#7391 Cross-database multi-table UPDATE security problem
321
create database mysqltest_1;
322
create database mysqltest_2;
323
create table mysqltest_1.t1 select 1 a, 2 q;
324
create table mysqltest_1.t2 select 1 b, 2 r;
325
create table mysqltest_2.t1 select 1 c, 2 s;
326
create table mysqltest_2.t2 select 1 d, 2 t;
328
# test the column privileges
329
grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
330
grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
331
grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;
332
grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;
333
connect (conn1,localhost,mysqltest_3,,);
335
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
336
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
337
ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;
338
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
339
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
340
ORDER BY TABLE_NAME,PRIVILEGE_TYPE;
341
SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
342
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
343
ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;
344
SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES
345
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
346
ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE;
347
--error ER_COLUMNACCESS_DENIED_ERROR
348
update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1;
349
--error ER_COLUMNACCESS_DENIED_ERROR
350
update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1;
351
--error ER_TABLEACCESS_DENIED_ERROR
352
update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1;
353
--error ER_TABLEACCESS_DENIED_ERROR
354
update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1;
355
--error ER_COLUMNACCESS_DENIED_ERROR
356
update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2;
357
# the following two should work
358
update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;
359
update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20;
361
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
362
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
363
revoke all on mysqltest_1.t1 from mysqltest_3@localhost;
364
revoke all on mysqltest_1.t2 from mysqltest_3@localhost;
365
revoke all on mysqltest_2.t1 from mysqltest_3@localhost;
366
revoke all on mysqltest_2.t2 from mysqltest_3@localhost;
368
# test the db/table level privileges
369
grant all on mysqltest_2.* to mysqltest_3@localhost;
370
grant select on *.* to mysqltest_3@localhost;
371
# Next grant is needed to trigger bug#7391. Do not optimize!
372
grant select on mysqltest_2.t1 to mysqltest_3@localhost;
375
connect (conn2,localhost,mysqltest_3,,);
378
update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600;
379
# the following failed before, should fail now.
380
--error ER_TABLEACCESS_DENIED_ERROR
381
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
383
# the following used to succeed, it must fail now.
384
--error ER_TABLEACCESS_DENIED_ERROR
385
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
386
--error ER_TABLEACCESS_DENIED_ERROR
387
update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200;
388
--error ER_TABLEACCESS_DENIED_ERROR
389
update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200;
390
# lets see the result
392
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
393
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
395
delete from mysql.user where user='mysqltest_3';
396
delete from mysql.db where user="mysqltest_3";
397
delete from mysql.tables_priv where user="mysqltest_3";
398
delete from mysql.columns_priv where user="mysqltest_3";
400
drop database mysqltest_1;
401
drop database mysqltest_2;
405
# just SHOW PRIVILEGES test
410
# Rights for renaming test (Bug#3270)
412
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
415
create database mysqltest;
417
create table mysqltest.t1 (a int,b int,c int);
418
grant all on mysqltest.t1 to mysqltest_1@localhost;
419
connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
421
-- error ER_TABLEACCESS_DENIED_ERROR
422
alter table t1 rename t2;
425
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
426
delete from mysql.user where user=_binary'mysqltest_1';
427
drop database mysqltest;
432
# check all new table privileges
434
CREATE USER dummy@localhost;
435
CREATE DATABASE mysqltest;
436
CREATE TABLE mysqltest.dummytable (dummyfield INT);
437
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
438
GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost;
439
GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost;
440
SHOW GRANTS FOR dummy@localhost;
441
use INFORMATION_SCHEMA;
442
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
443
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
444
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
446
SHOW GRANTS FOR dummy@localhost;
447
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
448
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
449
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
450
SHOW FIELDS FROM mysql.tables_priv;
452
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
453
DROP USER dummy@localhost;
454
DROP DATABASE mysqltest;
455
# check view only privileges
456
CREATE USER dummy@localhost;
457
CREATE DATABASE mysqltest;
458
CREATE TABLE mysqltest.dummytable (dummyfield INT);
459
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
460
GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost;
461
GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost;
462
SHOW GRANTS FOR dummy@localhost;
463
use INFORMATION_SCHEMA;
464
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
465
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
466
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
468
SHOW GRANTS FOR dummy@localhost;
469
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
470
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
471
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
473
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
474
DROP USER dummy@localhost;
475
DROP DATABASE mysqltest;
476
CREATE USER dummy@localhost;
477
CREATE DATABASE mysqltest;
478
CREATE TABLE mysqltest.dummytable (dummyfield INT);
479
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
480
GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost;
481
GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost;
482
SHOW GRANTS FOR dummy@localhost;
483
use INFORMATION_SCHEMA;
484
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
485
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
486
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
488
SHOW GRANTS FOR dummy@localhost;
489
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
490
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
491
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
493
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
494
DROP USER dummy@localhost;
495
DROP DATABASE mysqltest;
497
# Bug#11330 Entry in tables_priv with host = '' causes crash
501
insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select');
503
delete from tables_priv where host = '' and user = 'mysqltest_1';
508
# Bug#10892 user variables not auto cast for comparisons
509
# Check that we don't get illegal mix of collations
511
set @user123="non-existent";
512
select * from mysql.db where user=@user123;
516
grant select on ��.* to root@localhost;
517
select hex(Db) from mysql.db where Db='��';
518
show grants for root@localhost;
520
show grants for root@localhost;
522
revoke all privileges on ��.* from root@localhost;
523
show grants for root@localhost;
527
# Bug#15598 Server crashes in specific case during setting new password
528
# - Caused by a user with host ''
530
create user mysqltest_7@;
531
set password for mysqltest_7@ = password('systpass');
532
show grants for mysqltest_7@;
533
drop user mysqltest_7@;
534
--error ER_NONEXISTING_GRANT
535
show grants for mysqltest_7@;
538
# Bug#14385 GRANT and mapping to correct user account problems
540
create database mysqltest;
542
create table t1(f1 int);
543
GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%';
544
GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%';
545
show grants for mysqltest1@'192.%';
546
show grants for mysqltest1@'%';
547
delete from mysql.user where user='mysqltest1';
548
delete from mysql.db where user='mysqltest1';
549
delete from mysql.tables_priv where user='mysqltest1';
551
drop database mysqltest;
554
# Bug#27515 DROP previlege is not required for RENAME TABLE
557
create database db27515;
559
create table t1 (a int);
560
grant alter on db27515.t1 to user27515@localhost;
561
grant insert, create on db27515.t2 to user27515@localhost;
563
connect (conn27515, localhost, user27515, , db27515);
564
connection conn27515;
565
--error ER_TABLEACCESS_DENIED_ERROR
566
rename table t1 to t2;
567
disconnect conn27515;
570
revoke all privileges, grant option from user27515@localhost;
571
drop user user27515@localhost;
572
drop database db27515;
574
--echo End of 4.1 tests
577
# Bug#16297 In memory grant tables not flushed when users's hostname is ""
580
create table t1 (a int);
582
# Backup anonymous users and remove them. (They get in the way of
583
# the one we test with here otherwise.)
584
create table t2 as select * from mysql.user where user='';
585
delete from mysql.user where user='';
588
# Create some users with different hostnames
589
create user mysqltest_8@'';
590
create user mysqltest_8;
591
create user mysqltest_8@host8;
593
# Try to create them again
594
--error ER_CANNOT_USER
595
create user mysqltest_8@'';
596
--error ER_CANNOT_USER
597
create user mysqltest_8;
598
--error ER_CANNOT_USER
599
create user mysqltest_8@host8;
601
select user, QUOTE(host) from mysql.user where user="mysqltest_8";
603
--echo Schema privileges
604
grant select on mysqltest.* to mysqltest_8@'';
605
show grants for mysqltest_8@'';
606
grant select on mysqltest.* to mysqltest_8@;
607
show grants for mysqltest_8@;
608
grant select on mysqltest.* to mysqltest_8;
609
show grants for mysqltest_8;
610
select * from information_schema.schema_privileges
611
where grantee like "'mysqltest_8'%";
612
connect (conn3,localhost,mysqltest_8,,);
616
revoke select on mysqltest.* from mysqltest_8@'';
617
revoke select on mysqltest.* from mysqltest_8;
618
show grants for mysqltest_8@'';
619
show grants for mysqltest_8;
620
select * from information_schema.schema_privileges
621
where grantee like "'mysqltest_8'%";
623
show grants for mysqltest_8@'';
624
show grants for mysqltest_8@;
625
grant select on mysqltest.* to mysqltest_8@'';
627
show grants for mysqltest_8@;
628
revoke select on mysqltest.* from mysqltest_8@'';
631
--echo Column privileges
632
grant update (a) on t1 to mysqltest_8@'';
633
grant update (a) on t1 to mysqltest_8;
634
show grants for mysqltest_8@'';
635
show grants for mysqltest_8;
637
show grants for mysqltest_8@'';
638
show grants for mysqltest_8;
639
select * from information_schema.column_privileges;
640
connect (conn4,localhost,mysqltest_8,,);
644
revoke update (a) on t1 from mysqltest_8@'';
645
revoke update (a) on t1 from mysqltest_8;
646
show grants for mysqltest_8@'';
647
show grants for mysqltest_8;
648
select * from information_schema.column_privileges;
650
show grants for mysqltest_8@'';
651
show grants for mysqltest_8;
653
--echo Table privileges
654
grant update on t1 to mysqltest_8@'';
655
grant update on t1 to mysqltest_8;
656
show grants for mysqltest_8@'';
657
show grants for mysqltest_8;
659
show grants for mysqltest_8@'';
660
show grants for mysqltest_8;
661
select * from information_schema.table_privileges;
662
connect (conn5,localhost,mysqltest_8,,);
666
revoke update on t1 from mysqltest_8@'';
667
revoke update on t1 from mysqltest_8;
668
show grants for mysqltest_8@'';
669
show grants for mysqltest_8;
670
select * from information_schema.table_privileges;
672
show grants for mysqltest_8@'';
673
show grants for mysqltest_8;
675
--echo "DROP USER" should clear privileges
676
grant all privileges on mysqltest.* to mysqltest_8@'';
677
grant select on mysqltest.* to mysqltest_8@'';
678
grant update on t1 to mysqltest_8@'';
679
grant update (a) on t1 to mysqltest_8@'';
680
grant all privileges on mysqltest.* to mysqltest_8;
681
show grants for mysqltest_8@'';
682
show grants for mysqltest_8;
683
select * from information_schema.user_privileges
684
where grantee like "'mysqltest_8'%";
685
connect (conn5,localhost,mysqltest_8,,);
690
show grants for mysqltest_8@'';
691
show grants for mysqltest_8;
692
drop user mysqltest_8@'';
693
--error ER_NONEXISTING_GRANT
694
show grants for mysqltest_8@'';
695
show grants for mysqltest_8;
696
select * from information_schema.user_privileges
697
where grantee like "'mysqltest_8'%";
698
drop user mysqltest_8;
699
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
700
--error ER_ACCESS_DENIED_ERROR
701
connect (conn6,localhost,mysqltest_8,,);
703
--error ER_NONEXISTING_GRANT
704
show grants for mysqltest_8;
705
drop user mysqltest_8@host8;
706
--error ER_NONEXISTING_GRANT
707
show grants for mysqltest_8@host8;
709
# Restore the anonymous users.
710
insert into mysql.user select * from t2;
716
# Bug#20214 Incorrect error when user calls SHOW CREATE VIEW on non
722
CREATE DATABASE mysqltest3;
725
CREATE TABLE t_nn (c1 INT);
726
CREATE VIEW v_nn AS SELECT * FROM t_nn;
728
CREATE DATABASE mysqltest2;
731
CREATE TABLE t_nn (c1 INT);
732
CREATE VIEW v_nn AS SELECT * FROM t_nn;
733
CREATE VIEW v_yn AS SELECT * FROM t_nn;
734
CREATE VIEW v_gy AS SELECT * FROM t_nn;
735
CREATE VIEW v_ny AS SELECT * FROM t_nn;
736
CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55;
738
GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
739
GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
740
GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
741
GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
743
connect (mysqltest_1, localhost, mysqltest_1, mysqltest_1,);
745
# fail because of missing SHOW VIEW (have generic SELECT)
746
--error ER_TABLEACCESS_DENIED_ERROR
747
SHOW CREATE VIEW mysqltest2.v_nn;
748
--error ER_TABLEACCESS_DENIED_ERROR
749
SHOW CREATE TABLE mysqltest2.v_nn;
751
# fail because of missing SHOW VIEW
752
--error ER_TABLEACCESS_DENIED_ERROR
753
SHOW CREATE VIEW mysqltest2.v_yn;
754
--error ER_TABLEACCESS_DENIED_ERROR
755
SHOW CREATE TABLE mysqltest2.v_yn;
757
# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
758
SHOW CREATE TABLE mysqltest2.v_ny;
760
# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
761
SHOW CREATE VIEW mysqltest2.v_ny;
763
# fail because of missing (specific or generic) SELECT
764
--error ER_TABLEACCESS_DENIED_ERROR
765
SHOW CREATE TABLE mysqltest3.t_nn;
767
# fail because of missing (specific or generic) SELECT (not because it's not a view!)
768
--error ER_TABLEACCESS_DENIED_ERROR
769
SHOW CREATE VIEW mysqltest3.t_nn;
771
# fail because of missing missing (specific or generic) SELECT (and SHOW VIEW)
772
--error ER_TABLEACCESS_DENIED_ERROR
773
SHOW CREATE VIEW mysqltest3.v_nn;
774
--error ER_TABLEACCESS_DENIED_ERROR
775
SHOW CREATE TABLE mysqltest3.v_nn;
777
# succeed thanks to generic SELECT
778
SHOW CREATE TABLE mysqltest2.t_nn;
780
# fail because it's not a view! (have generic SELECT though)
781
--error ER_WRONG_OBJECT
782
SHOW CREATE VIEW mysqltest2.t_nn;
784
# succeed, have SELECT and SHOW VIEW
785
SHOW CREATE VIEW mysqltest2.v_yy;
787
# succeed, have SELECT and SHOW VIEW
788
SHOW CREATE TABLE mysqltest2.v_yy;
793
# succeed, we're root
794
SHOW CREATE TABLE mysqltest2.v_nn;
795
SHOW CREATE VIEW mysqltest2.v_nn;
797
SHOW CREATE TABLE mysqltest2.t_nn;
799
# fail because it's not a view!
800
--error ER_WRONG_OBJECT
801
SHOW CREATE VIEW mysqltest2.t_nn;
803
DROP VIEW mysqltest2.v_nn;
804
DROP VIEW mysqltest2.v_yn;
805
DROP VIEW mysqltest2.v_ny;
806
DROP VIEW mysqltest2.v_yy;
807
DROP TABLE mysqltest2.t_nn;
808
DROP DATABASE mysqltest2;
809
DROP VIEW mysqltest3.v_nn;
810
DROP TABLE mysqltest3.t_nn;
811
DROP DATABASE mysqltest3;
812
disconnect mysqltest_1;
813
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
814
DROP USER 'mysqltest_1'@'localhost';
816
# restore the original database
823
# Bug#10668 CREATE USER does not enforce username length limit
825
--error ER_WRONG_STRING_LENGTH
826
create user mysqltest1_thisisreallytoolong;
829
# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause.
831
# These checks are intended to ensure that appropriate errors are risen when
832
# illegal user name or hostname is specified in user-clause of GRANT/REVOKE
837
# Bug#22369 Alter table rename combined with other alterations causes lost tables
839
CREATE DATABASE mysqltest1;
840
CREATE TABLE mysqltest1.t1 (
841
int_field INTEGER UNSIGNED NOT NULL,
845
CREATE TABLE mysqltest1.t2 (int_field INT);
847
--echo "Now check that we require equivalent grants for "
848
--echo "RENAME TABLE and ALTER TABLE"
849
CREATE USER mysqltest_1@localhost;
850
GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost;
852
connect (conn42,localhost,mysqltest_1,,mysqltest1);
855
--error ER_TABLEACCESS_DENIED_ERROR
856
RENAME TABLE t1 TO t2;
857
--error ER_TABLEACCESS_DENIED_ERROR
858
ALTER TABLE t1 RENAME TO t2;
861
GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost;
863
connect (conn42,localhost,mysqltest_1,,mysqltest1);
864
--error ER_TABLEACCESS_DENIED_ERROR
865
RENAME TABLE t1 TO t2;
866
--error ER_TABLEACCESS_DENIED_ERROR
867
ALTER TABLE t1 RENAME TO t2;
870
GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost;
872
connect (conn42,localhost,mysqltest_1,,mysqltest1);
874
--error ER_TABLEACCESS_DENIED_ERROR
875
RENAME TABLE t1 TO t2;
876
--error ER_TABLEACCESS_DENIED_ERROR
877
ALTER TABLE t1 RENAME TO t2;
880
GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost;
881
connect (conn42,localhost,mysqltest_1,,mysqltest1);
883
--error ER_TABLEACCESS_DENIED_ERROR
886
GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost;
887
DROP TABLE mysqltest1.t2;
889
connect (conn42,localhost,mysqltest_1,,mysqltest1);
891
RENAME TABLE t1 TO t2;
892
RENAME TABLE t2 TO t1;
893
ALTER TABLE t1 RENAME TO t2;
894
ALTER TABLE t2 RENAME TO t1;
897
REVOKE DROP, INSERT ON mysqltest1.t1 FROM mysqltest_1@localhost;
898
REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost;
900
connect (conn42,localhost,mysqltest_1,,mysqltest1);
902
--error ER_TABLEACCESS_DENIED_ERROR
903
RENAME TABLE t1 TO t2;
904
--error ER_TABLEACCESS_DENIED_ERROR
905
ALTER TABLE t1 RENAME TO t2;
909
DROP USER mysqltest_1@localhost;
910
DROP DATABASE mysqltest1;
913
# Working with database-level privileges.
915
--error ER_WRONG_STRING_LENGTH
916
GRANT CREATE ON mysqltest.* TO 1234567890abcdefGHIKL@localhost;
918
--error ER_WRONG_STRING_LENGTH
919
GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
921
--error ER_WRONG_STRING_LENGTH
922
REVOKE CREATE ON mysqltest.* FROM 1234567890abcdefGHIKL@localhost;
924
--error ER_WRONG_STRING_LENGTH
925
REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
927
# Working with table-level privileges.
929
--error ER_WRONG_STRING_LENGTH
930
GRANT CREATE ON t1 TO 1234567890abcdefGHIKL@localhost;
932
--error ER_WRONG_STRING_LENGTH
933
GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
935
--error ER_WRONG_STRING_LENGTH
936
REVOKE CREATE ON t1 FROM 1234567890abcdefGHIKL@localhost;
938
--error ER_WRONG_STRING_LENGTH
939
REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
941
# Working with routine-level privileges.
943
--error ER_WRONG_STRING_LENGTH
944
GRANT EXECUTE ON PROCEDURE p1 TO 1234567890abcdefGHIKL@localhost;
946
--error ER_WRONG_STRING_LENGTH
947
GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
949
--error ER_WRONG_STRING_LENGTH
950
REVOKE EXECUTE ON PROCEDURE p1 FROM 1234567890abcdefGHIKL@localhost;
952
--error ER_WRONG_STRING_LENGTH
953
REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
957
# Bug#23556 TRUNCATE TABLE still maps to DELETE
959
CREATE USER bug23556@localhost;
960
CREATE DATABASE bug23556;
961
GRANT SELECT ON bug23556.* TO bug23556@localhost;
962
connect (bug23556,localhost,bug23556,,bug23556);
966
CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
967
GRANT DELETE ON t1 TO bug23556@localhost;
971
--error ER_TABLEACCESS_DENIED_ERROR
976
REVOKE DELETE ON t1 FROM bug23556@localhost;
977
GRANT DROP ON t1 TO bug23556@localhost;
987
DROP DATABASE bug23556;
988
DROP USER bug23556@localhost;
994
# Bug#6774 Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES
996
# Check if GRANT ... ON * ... fails when no database is selected
997
connect (con1, localhost, root,,*NO-ONE*);
999
--error ER_NO_DB_ERROR
1000
GRANT PROCESS ON * TO user@localhost;
1006
# Bug#9504 Stored procedures: execute privilege doesn't make 'use database'
1013
DROP DATABASE IF EXISTS mysqltest1;
1014
DROP DATABASE IF EXISTS mysqltest2;
1015
DROP DATABASE IF EXISTS mysqltest3;
1016
DROP DATABASE IF EXISTS mysqltest4;
1019
CREATE DATABASE mysqltest1;
1020
CREATE DATABASE mysqltest2;
1021
CREATE DATABASE mysqltest3;
1022
CREATE DATABASE mysqltest4;
1024
CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER
1027
CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER
1030
CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER
1033
CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER
1036
GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost;
1037
GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost;
1038
GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost;
1039
GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost;
1041
GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost;
1045
--connect (bug9504_con1,localhost,mysqltest_1,,)
1047
--echo ---> connection: bug9504_con1
1049
# - Check that we can switch to the db;
1059
# - Check that we can call stored routines;
1063
CALL mysqltest1.p_def();
1065
CALL mysqltest2.p_inv();
1067
SELECT mysqltest3.f_def();
1069
SELECT mysqltest4.f_inv();
1073
--connection default
1075
--echo ---> connection: default
1077
--disconnect bug9504_con1
1079
DROP DATABASE mysqltest1;
1080
DROP DATABASE mysqltest2;
1081
DROP DATABASE mysqltest3;
1082
DROP DATABASE mysqltest4;
1084
DROP USER mysqltest_1@localhost;
1088
# Bug#27337 Privileges are not restored properly.
1090
# Actually, the patch for this bugs fixes two problems. So, here are two test
1093
# Test case 1: privileges are not restored properly after calling a stored
1094
# routine defined with SQL SECURITY INVOKER clause.
1099
DROP DATABASE IF EXISTS mysqltest1;
1100
DROP DATABASE IF EXISTS mysqltest2;
1103
CREATE DATABASE mysqltest1;
1104
CREATE DATABASE mysqltest2;
1106
GRANT ALL PRIVILEGES ON mysqltest1.* TO mysqltest_1@localhost;
1107
GRANT SELECT ON mysqltest2.* TO mysqltest_1@localhost;
1109
CREATE PROCEDURE mysqltest1.p1() SQL SECURITY INVOKER
1114
--connect (bug27337_con1,localhost,mysqltest_1,,mysqltest2)
1116
--echo ---> connection: bug27337_con1
1118
--error ER_TABLEACCESS_DENIED_ERROR
1119
CREATE TABLE t1(c INT);
1121
CALL mysqltest1.p1();
1123
--error ER_TABLEACCESS_DENIED_ERROR
1124
CREATE TABLE t1(c INT);
1126
--disconnect bug27337_con1
1128
--connect (bug27337_con2,localhost,mysqltest_1,,mysqltest2)
1130
--echo ---> connection: bug27337_con2
1132
--error ER_TABLEACCESS_DENIED_ERROR
1133
CREATE TABLE t1(c INT);
1139
--connection default
1141
--echo ---> connection: default
1143
--disconnect bug27337_con2
1145
DROP DATABASE mysqltest1;
1146
DROP DATABASE mysqltest2;
1148
DROP USER mysqltest_1@localhost;
1150
# Test case 2: privileges are not checked properly for prepared statements.
1155
DROP DATABASE IF EXISTS mysqltest1;
1156
DROP DATABASE IF EXISTS mysqltest2;
1159
CREATE DATABASE mysqltest1;
1160
CREATE DATABASE mysqltest2;
1162
CREATE TABLE mysqltest1.t1(c INT);
1163
CREATE TABLE mysqltest2.t2(c INT);
1165
GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost;
1166
GRANT SELECT ON mysqltest2.t2 TO mysqltest_2@localhost;
1170
--connect (bug27337_con1,localhost,mysqltest_1,,mysqltest1)
1172
--echo ---> connection: bug27337_con1
1174
SHOW TABLES FROM mysqltest1;
1176
PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1';
1180
--connect (bug27337_con2,localhost,mysqltest_2,,mysqltest2)
1182
--echo ---> connection: bug27337_con2
1184
SHOW COLUMNS FROM mysqltest2.t2;
1186
PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2';
1190
--connection default
1192
--echo ---> connection: default
1194
REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost;
1195
REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost;
1197
--connection bug27337_con1
1199
--echo ---> connection: bug27337_con1
1201
--error ER_DBACCESS_DENIED_ERROR
1202
SHOW TABLES FROM mysqltest1;
1204
--error ER_DBACCESS_DENIED_ERROR
1207
--connection bug27337_con2
1209
--echo ---> connection: bug27337_con2
1211
--error ER_TABLEACCESS_DENIED_ERROR
1212
SHOW COLUMNS FROM mysqltest2.t2;
1214
--error ER_TABLEACCESS_DENIED_ERROR
1219
--connection default
1221
--echo ---> connection: default
1223
--disconnect bug27337_con1
1224
--disconnect bug27337_con2
1226
DROP DATABASE mysqltest1;
1227
DROP DATABASE mysqltest2;
1229
DROP USER mysqltest_1@localhost;
1230
DROP USER mysqltest_2@localhost;
1233
# Bug#27878 Unchecked privileges on a view referring to a table from another
1237
CREATE TABLE t1 (f1 int, f2 int);
1238
INSERT INTO t1 VALUES(1,1), (2,2);
1239
CREATE DATABASE db27878;
1240
GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
1241
GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
1242
GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
1244
CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
1245
connect (user1,localhost,mysqltest_1,,test);
1249
UPDATE v1 SET f2 = 4;
1250
SELECT * FROM test.t1;
1253
REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost';
1254
REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost';
1255
REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost';
1256
DROP USER mysqltest_1@localhost;
1257
DROP DATABASE db27878;
1262
--echo # Bug#33275 Server crash when creating temporary table mysql.user
1264
CREATE TEMPORARY TABLE mysql.user (id INT);
1266
DROP TABLE mysql.user;
1270
# Bug#33201 Crash occurs when granting update privilege on one column of a view
1272
drop table if exists test;
1273
drop function if exists test_function;
1274
drop view if exists v1;
1275
create table test (col1 varchar(30));
1277
create function test_function() returns varchar(30)
1279
declare tmp varchar(30);
1280
select col1 from test limit 1 into tmp;
1284
create view v1 as select test.* from test where test.col1=test_function();
1285
grant update (col1) on v1 to 'greg'@'localhost';
1286
drop user 'greg'@'localhost';
1289
drop function test_function;
1292
# Bug#41456 SET PASSWORD hates CURRENT_USER()
1294
SELECT CURRENT_USER();
1295
SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin");
1296
SET PASSWORD FOR CURRENT_USER() = PASSWORD("");
1298
--echo End of 5.0 tests
1301
# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte
1304
grant select on test.* to юзер_юзер@localhost;
1305
--exec $MYSQL --default-character-set=utf8 --user=юзер_юзер -e "select user()"
1306
revoke all on test.* from юзер_юзер@localhost;
1307
drop user юзер_юзер@localhost;
1308
--error ER_WRONG_STRING_LENGTH
1309
grant select on test.* to очень_длинный_юзер@localhost;
1313
# Bug#20901 CREATE privilege is enough to insert into a table
1316
create database mysqltest;
1319
grant create on mysqltest.* to mysqltest@localhost;
1320
create table t1 (i INT);
1322
connect (user1,localhost,mysqltest,,mysqltest);
1324
# show we don't have INSERT
1325
--error ER_TABLEACCESS_DENIED_ERROR
1326
insert into t1 values (1);
1327
# show we have CREATE
1328
create table t2 (i INT);
1329
create table t4 (i INT);
1332
grant select, insert on mysqltest.t2 to mysqltest@localhost;
1333
grant insert on mysqltest.t4 to mysqltest@localhost;
1334
# to specify ACLs for non-existent objects, must explictly |CREATE
1335
grant create, insert on mysqltest.t5 to mysqltest@localhost;
1336
grant create, insert on mysqltest.t6 to mysqltest@localhost;
1340
insert into t2 values (1);
1343
# CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail
1344
--error ER_TABLEACCESS_DENIED_ERROR
1345
create table if not exists t1 select * from t2;
1347
# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail
1348
--error ER_TABLEACCESS_DENIED_ERROR
1349
create table if not exists t3 select * from t2;
1351
# CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed
1352
create table if not exists t4 select * from t2;
1354
# CREATE IF NOT EXISTS...SELECT, no t5 yet, have INSERT, must succeed
1355
create table if not exists t5 select * from t2;
1358
# CREATE...SELECT, no t6 yet, have INSERT, must succeed
1359
create table t6 select * from t2;
1361
# CREATE...SELECT, no t7 yet, no INSERT, must fail
1362
--error ER_TABLEACCESS_DENIED_ERROR
1363
create table t7 select * from t2;
1365
# CREATE...SELECT, t4 exists, have INSERT, must still fail (exists)
1367
create table t4 select * from t2;
1369
# CREATE...SELECT, t1 exists, no INSERT, must fail
1370
--error ER_TABLEACCESS_DENIED_ERROR
1371
create table t1 select * from t2;
1375
drop table t1,t2,t4,t5,t6;
1377
revoke create on mysqltest.* from mysqltest@localhost;
1378
revoke select, insert on mysqltest.t2 from mysqltest@localhost;
1379
revoke insert on mysqltest.t4 from mysqltest@localhost;
1380
revoke create, insert on mysqltest.t5 from mysqltest@localhost;
1381
revoke create, insert on mysqltest.t6 from mysqltest@localhost;
1382
drop user mysqltest@localhost;
1385
drop database mysqltest;
1390
# Bug#16470 crash on grant if old grant tables
1392
--echo FLUSH PRIVILEGES without procs_priv table.
1393
RENAME TABLE mysql.procs_priv TO mysql.procs_gone;
1394
--error ER_NO_SUCH_TABLE
1396
--echo Assigning privileges without procs_priv table.
1397
CREATE DATABASE mysqltest1;
1398
CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER
1400
--error ER_NO_SUCH_TABLE
1401
GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost;
1402
GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost;
1403
CALL mysqltest1.test();
1404
DROP DATABASE mysqltest1;
1405
RENAME TABLE mysql.procs_gone TO mysql.procs_priv;
1406
DROP USER mysqltest_1@localhost;
1411
# Bug#33464 DROP FUNCTION caused a crash.
1413
CREATE DATABASE dbbug33464;
1414
CREATE USER 'userbug33464'@'localhost';
1416
GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost';
1418
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1419
connect (connbug33464, localhost, userbug33464, , dbbug33464);
1420
--source suite/funcs_1/include/show_connection.inc
1423
DROP PROCEDURE IF EXISTS sp3;
1424
DROP FUNCTION IF EXISTS fn1;
1428
CREATE PROCEDURE sp3(v1 char(20))
1430
SELECT * from dbbug33464.t6 where t6.f2= 'xyz';
1435
CREATE FUNCTION fn1() returns char(50) SQL SECURITY INVOKER
1442
CREATE FUNCTION fn2() returns char(50) SQL SECURITY DEFINER
1448
disconnect connbug33464;
1453
--source suite/funcs_1/include/show_connection.inc
1458
--error 0, ER_CANNOT_USER
1459
DROP USER 'userbug33464'@'localhost';
1465
--error 0, ER_CANNOT_USER
1466
DROP USER 'userbug33464'@'localhost';
1469
DROP DATABASE dbbug33464;
1472
SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
1475
# Bug#44658 Create procedure makes server crash when user does not have ALL privilege
1479
GRANT CREATE ON db1.* TO 'user1'@'localhost';
1480
GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost';
1481
GRANT CREATE ON db1.* TO 'user2'@'%';
1482
GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%';
1484
SHOW GRANTS FOR 'user1'@'localhost';
1485
connect (con1,localhost,user1,,);
1486
--echo ** Connect as user1 and create a procedure.
1487
--echo ** The creation will imply implicitly assigned
1488
--echo ** EXECUTE and ALTER ROUTINE privileges to
1489
--echo ** the current user user1@localhost.
1490
SELECT @@GLOBAL.sql_mode;
1491
SELECT @@SESSION.sql_mode;
1492
CREATE DATABASE db1;
1494
CREATE PROCEDURE db1.proc1(p1 INT)
1497
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
1501
connect (con2,localhost,user2,,);
1502
--echo ** Connect as user2 and create a procedure.
1503
--echo ** Implicitly assignment of privileges will
1504
--echo ** fail because the user2@localhost is an
1505
--echo ** unknown user.
1507
CREATE PROCEDURE db1.proc2(p1 INT)
1510
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
1515
SHOW GRANTS FOR 'user1'@'localhost';
1516
SHOW GRANTS FOR 'user2';
1519
DROP PROCEDURE db1.proc1;
1520
DROP PROCEDURE db1.proc2;
1521
REVOKE ALL ON db1.* FROM 'user1'@'localhost';
1522
REVOKE ALL ON db1.* FROM 'user2'@'%';
1524
DROP USER 'user1'@'localhost';
1528
# Wait till we reached the initial number of concurrent sessions
1529
--source include/wait_until_count_sessions.inc