119
123
# Test some error conditions
125
--error ER_WRONG_USAGE
122
126
GRANT FILE on mysqltest.* to mysqltest_1@localhost;
123
select 1; # To test that the previous command didn't cause problems
127
select 1; # To test that the previous command didn't cause problems
126
# Bug #4898: User privileges depending on ORDER BY Settings of table db
130
# Bug#4898 User privileges depending on ORDER BY Settings of table db
128
132
insert into mysql.user (host, user) values ('localhost', 'test11');
129
insert into mysql.db (host, db, user, select_priv) values
133
insert into mysql.db (host, db, user, select_priv) values
130
134
('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
131
135
alter table mysql.db order by db asc;
132
136
flush privileges;
190
194
# Drop user now implicitly revokes all privileges.
191
195
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
192
196
drop_user4@localhost;
197
--error ER_REVOKE_GRANTS
194
198
revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,
195
199
drop_user3@localhost, drop_user4@localhost;
200
--error ER_CANNOT_USER
197
201
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
198
202
drop_user4@localhost;
203
207
drop user mysqltest_1@localhost;
206
# Bug #3403 Wrong encodin in SHOW GRANTS output
210
# Bug#3403 Wrong encoding in SHOW GRANTS output
209
213
CREATE DATABASE ��;
211
CREATE TABLE ��� (��� int);
215
CREATE TABLE ��� (��� INT);
213
217
GRANT SELECT ON ��.* TO ����@localhost;
214
218
SHOW GRANTS FOR ����@localhost;
321
325
create table mysqltest_2.t1 select 1 c, 2 s;
322
326
create table mysqltest_2.t2 select 1 d, 2 t;
324
#test the column privileges
328
# test the column privileges
325
329
grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
326
330
grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
327
331
grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;
328
332
grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;
329
333
connect (conn1,localhost,mysqltest_3,,);
330
334
connection conn1;
331
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
332
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
335
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
336
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
333
337
ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;
334
338
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
335
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
339
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
336
340
ORDER BY TABLE_NAME,PRIVILEGE_TYPE;
337
341
SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
338
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
342
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
339
343
ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;
340
344
SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES
341
345
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
342
346
ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE;
347
--error ER_COLUMNACCESS_DENIED_ERROR
344
348
update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1;
349
--error ER_COLUMNACCESS_DENIED_ERROR
346
350
update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1;
351
--error ER_TABLEACCESS_DENIED_ERROR
348
352
update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1;
353
--error ER_TABLEACCESS_DENIED_ERROR
350
354
update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1;
355
--error ER_COLUMNACCESS_DENIED_ERROR
352
356
update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2;
353
#the following two should work
357
# the following two should work
354
358
update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;
355
359
update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20;
356
360
connection master;
361
365
revoke all on mysqltest_2.t1 from mysqltest_3@localhost;
362
366
revoke all on mysqltest_2.t2 from mysqltest_3@localhost;
364
#test the db/table level privileges
368
# test the db/table level privileges
365
369
grant all on mysqltest_2.* to mysqltest_3@localhost;
366
370
grant select on *.* to mysqltest_3@localhost;
367
371
# Next grant is needed to trigger bug#7391. Do not optimize!
374
378
update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600;
375
379
# the following failed before, should fail now.
380
--error ER_TABLEACCESS_DENIED_ERROR
377
381
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
379
#the following used to succeed, it must fail now.
383
# the following used to succeed, it must fail now.
384
--error ER_TABLEACCESS_DENIED_ERROR
381
385
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
386
--error ER_TABLEACCESS_DENIED_ERROR
383
387
update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200;
388
--error ER_TABLEACCESS_DENIED_ERROR
385
389
update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200;
390
# lets see the result
387
391
connection master;
388
392
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
389
393
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
413
418
grant all on mysqltest.t1 to mysqltest_1@localhost;
414
419
connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
415
420
connection user1;
421
-- error ER_TABLEACCESS_DENIED_ERROR
416
422
alter table t1 rename t2;
417
423
disconnect user1;
419
425
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
420
426
delete from mysql.user where user=_binary'mysqltest_1';
421
427
drop database mysqltest;
424
# check all new table priveleges
432
# check all new table privileges
426
434
CREATE USER dummy@localhost;
427
435
CREATE DATABASE mysqltest;
517
524
set names latin1;
520
# Bug #15598 Server crashes in specific case during setting new password
527
# Bug#15598 Server crashes in specific case during setting new password
521
528
# - Caused by a user with host ''
523
530
create user mysqltest_7@;
524
531
set password for mysqltest_7@ = password('systpass');
525
532
show grants for mysqltest_7@;
526
533
drop user mysqltest_7@;
534
--error ER_NONEXISTING_GRANT
528
535
show grants for mysqltest_7@;
531
# Bug#14385: GRANT and mapping to correct user account problems
538
# Bug#14385 GRANT and mapping to correct user account problems
533
540
create database mysqltest;
683
690
show grants for mysqltest_8@'';
684
691
show grants for mysqltest_8;
685
692
drop user mysqltest_8@'';
693
--error ER_NONEXISTING_GRANT
687
694
show grants for mysqltest_8@'';
688
695
show grants for mysqltest_8;
689
696
select * from information_schema.user_privileges
690
697
where grantee like "'mysqltest_8'%";
691
698
drop user mysqltest_8;
692
699
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
700
--error ER_ACCESS_DENIED_ERROR
694
701
connect (conn6,localhost,mysqltest_8,,);
695
702
connection master;
703
--error ER_NONEXISTING_GRANT
697
704
show grants for mysqltest_8;
698
705
drop user mysqltest_8@host8;
706
--error ER_NONEXISTING_GRANT
700
707
show grants for mysqltest_8@host8;
702
709
# Restore the anonymous users.
703
710
insert into mysql.user select * from t2;
704
711
flush privileges;
710
# Bug#20214: Incorrect error when user calls SHOW CREATE VIEW on non
716
# Bug#20214 Incorrect error when user calls SHOW CREATE VIEW on non
714
720
connection master;
716
722
CREATE DATABASE mysqltest3;
719
725
CREATE TABLE t_nn (c1 INT);
720
726
CREATE VIEW v_nn AS SELECT * FROM t_nn;
722
728
CREATE DATABASE mysqltest2;
725
731
CREATE TABLE t_nn (c1 INT);
726
732
CREATE VIEW v_nn AS SELECT * FROM t_nn;
742
748
--error ER_TABLEACCESS_DENIED_ERROR
743
749
SHOW CREATE TABLE mysqltest2.v_nn;
747
751
# fail because of missing SHOW VIEW
748
752
--error ER_TABLEACCESS_DENIED_ERROR
749
753
SHOW CREATE VIEW mysqltest2.v_yn;
750
754
--error ER_TABLEACCESS_DENIED_ERROR
751
755
SHOW CREATE TABLE mysqltest2.v_yn;
755
757
# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
756
758
SHOW CREATE TABLE mysqltest2.v_ny;
758
760
# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
759
761
SHOW CREATE VIEW mysqltest2.v_ny;
763
763
# fail because of missing (specific or generic) SELECT
764
764
--error ER_TABLEACCESS_DENIED_ERROR
765
765
SHOW CREATE TABLE mysqltest3.t_nn;
768
768
--error ER_TABLEACCESS_DENIED_ERROR
769
769
SHOW CREATE VIEW mysqltest3.t_nn;
773
771
# fail because of missing missing (specific or generic) SELECT (and SHOW VIEW)
774
772
--error ER_TABLEACCESS_DENIED_ERROR
775
773
SHOW CREATE VIEW mysqltest3.v_nn;
776
774
--error ER_TABLEACCESS_DENIED_ERROR
777
775
SHOW CREATE TABLE mysqltest3.v_nn;
781
777
# succeed thanks to generic SELECT
782
778
SHOW CREATE TABLE mysqltest2.t_nn;
808
800
--error ER_WRONG_OBJECT
809
801
SHOW CREATE VIEW mysqltest2.t_nn;
813
803
DROP VIEW mysqltest2.v_nn;
814
804
DROP VIEW mysqltest2.v_yn;
815
805
DROP VIEW mysqltest2.v_ny;
816
806
DROP VIEW mysqltest2.v_yy;
818
807
DROP TABLE mysqltest2.t_nn;
820
808
DROP DATABASE mysqltest2;
824
809
DROP VIEW mysqltest3.v_nn;
825
810
DROP TABLE mysqltest3.t_nn;
827
811
DROP DATABASE mysqltest3;
812
disconnect mysqltest_1;
829
813
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
830
814
DROP USER 'mysqltest_1'@'localhost';
832
816
# restore the original database
836
# Bug #10668: CREATE USER does not enforce username length limit
823
# Bug#10668 CREATE USER does not enforce username length limit
838
825
--error ER_WRONG_STRING_LENGTH
839
826
create user mysqltest1_thisisreallytoolong;
842
# Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause.
829
# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause.
844
831
# These checks are intended to ensure that appropriate errors are risen when
845
832
# illegal user name or hostname is specified in user-clause of GRANT/REVOKE
1000
987
DROP DATABASE bug23556;
1001
988
DROP USER bug23556@localhost;
1003
# Bug #6774: Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES
994
# Bug#6774 Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES
1005
996
# Check if GRANT ... ON * ... fails when no database is selected
1006
997
connect (con1, localhost, root,,*NO-ONE*);
1238
1230
DROP USER mysqltest_2@localhost;
1241
# Bug#27878: Unchecked privileges on a view referring to a table from another
1233
# Bug#27878 Unchecked privileges on a view referring to a table from another
1245
1237
CREATE TABLE t1 (f1 int, f2 int);
1246
1238
INSERT INTO t1 VALUES(1,1), (2,2);
1247
1239
CREATE DATABASE db27878;
1248
1240
GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost';
1249
1241
GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost';
1250
1242
GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost';
1252
1244
CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1;
1253
1245
connect (user1,localhost,mysqltest_1,,test);
1254
1246
connection user1;
1257
1249
UPDATE v1 SET f2 = 4;
1258
1250
SELECT * FROM test.t1;
1349
1343
# CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail
1344
--error ER_TABLEACCESS_DENIED_ERROR
1351
1345
create table if not exists t1 select * from t2;
1353
1347
# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail
1348
--error ER_TABLEACCESS_DENIED_ERROR
1355
1349
create table if not exists t3 select * from t2;
1357
1351
# CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed
1365
1359
create table t6 select * from t2;
1367
1361
# CREATE...SELECT, no t7 yet, no INSERT, must fail
1362
--error ER_TABLEACCESS_DENIED_ERROR
1369
1363
create table t7 select * from t2;
1371
1365
# CREATE...SELECT, t4 exists, have INSERT, must still fail (exists)
1471
1465
--error 0, ER_CANNOT_USER
1472
1466
DROP USER 'userbug33464'@'localhost';
1475
1469
DROP DATABASE dbbug33464;
1478
--echo End of 5.1 tests
1472
SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
1474
# Wait till we reached the initial number of concurrent sessions
1475
--source include/wait_until_count_sessions.inc