diff options
Diffstat (limited to 'mysql-test/r/grant.test')
| -rw-r--r-- | mysql-test/r/grant.test | 2291 |
1 files changed, 0 insertions, 2291 deletions
diff --git a/mysql-test/r/grant.test b/mysql-test/r/grant.test deleted file mode 100644 index f2dfb01cc39..00000000000 --- a/mysql-test/r/grant.test +++ /dev/null @@ -1,2291 +0,0 @@ -# Test of GRANT commands - -# Grant tests not performed with embedded server --- source include/not_embedded.inc - -# Save the initial number of concurrent sessions ---source include/count_sessions.inc - -SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; -SET GLOBAL log_bin_trust_function_creators = 1; - -# Cleanup ---disable_warnings -drop table if exists t1; -drop database if exists mysqltest; ---enable_warnings - -connect (master,localhost,root,,); -connection master; -SET NAMES binary; - -# -# Test that SSL options works properly -# - -delete from mysql.user where user='mysqltest_1'; -delete from mysql.db where user='mysqltest_1'; -flush privileges; -grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; -show grants for mysqltest_1@localhost; -grant delete on mysqltest.* to mysqltest_1@localhost; -query_vertical select * from mysql.user where user="mysqltest_1"; -show grants for mysqltest_1@localhost; -revoke delete on mysqltest.* from mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -grant select on mysqltest.* to mysqltest_1@localhost require NONE; -show grants for mysqltest_1@localhost; -grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "Monty Program Ab"; -show grants for mysqltest_1@localhost; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -flush privileges; - -# -# Test of GRANTS specifying user limits -# -delete from mysql.user where user='mysqltest_1'; -flush privileges; -grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10; -query_vertical select * from mysql.user where user="mysqltest_1"; -show grants for mysqltest_1@localhost; -grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30; -query_vertical select * from mysql.user where user="mysqltest_1"; -show grants for mysqltest_1@localhost; -# This is just to double check that one won't ignore results of selects -flush privileges; -show grants for mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -flush privileges; - -# -# Test that the new db privileges are stored/retrieved correctly -# - -grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -flush privileges; -show grants for mysqltest_1@localhost; -revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; -flush privileges; -show grants for mysqltest_1@localhost; -revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -revoke all privileges on mysqltest.* from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -flush privileges; -grant usage on test.* to mysqltest_1@localhost with grant option; -show grants for mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; -delete from mysql.db where user='mysqltest_1'; -delete from mysql.tables_priv where user='mysqltest_1'; -delete from mysql.columns_priv where user='mysqltest_1'; -flush privileges; ---error ER_NONEXISTING_GRANT -show grants for mysqltest_1@localhost; - -# -# Test what happens when you have same table and colum level grants -# - -create table t1 (a int); -GRANT select,update,insert on t1 to mysqltest_1@localhost; -GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; -REVOKE select (a), update on t1 from mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -GRANT select,references on t1 to mysqltest_1@localhost; -select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1"; -grant all on test.* to mysqltest_3@localhost with grant option; -revoke all on test.* from mysqltest_3@localhost; -show grants for mysqltest_3@localhost; -revoke grant option on test.* from mysqltest_3@localhost; -show grants for mysqltest_3@localhost; -grant all on test.t1 to mysqltest_2@localhost with grant option; -revoke all on test.t1 from mysqltest_2@localhost; -show grants for mysqltest_2@localhost; -revoke grant option on test.t1 from mysqltest_2@localhost; -show grants for mysqltest_2@localhost; -delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; -delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; -delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; -delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3"; -flush privileges; -drop table t1; - -# -# Test some error conditions -# ---error ER_WRONG_USAGE -GRANT FILE on mysqltest.* to mysqltest_1@localhost; -select 1; # To test that the previous command didn't cause problems - -# -# Bug#4898 User privileges depending on ORDER BY Settings of table db -# -insert into mysql.user (host, user) values ('localhost', 'test11'); -insert into mysql.db (host, db, user, select_priv) values -('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y'); -alter table mysql.db order by db asc; -flush privileges; -show grants for test11@localhost; -alter table mysql.db order by db desc; -flush privileges; -show grants for test11@localhost; -delete from mysql.user where user='test11'; -delete from mysql.db where user='test11'; - -# -# Bug#6123 GRANT USAGE inserts useless Db row -# -create database mysqltest1; -grant usage on mysqltest1.* to test6123 identified by 'magic123'; -select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1"; -delete from mysql.user where user='test6123'; -drop database mysqltest1; - -# -# Test for 'drop user', 'revoke privileges, grant' -# - -create table t1 (a int); -grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION; -show grants for drop_user2@localhost; -revoke all privileges, grant option from drop_user2@localhost; -drop user drop_user2@localhost; - -grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION; -grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION; -grant select(a) on test.t1 to drop_user@localhost; -show grants for drop_user@localhost; - -# -# Bug#3086 SHOW GRANTS doesn't follow ANSI_QUOTES -# -set sql_mode=ansi_quotes; -show grants for drop_user@localhost; -set sql_mode=default; - -set sql_quote_show_create=0; -show grants for drop_user@localhost; -set sql_mode="ansi_quotes"; -show grants for drop_user@localhost; -set sql_quote_show_create=1; -show grants for drop_user@localhost; -set sql_mode=""; -show grants for drop_user@localhost; - -revoke all privileges, grant option from drop_user@localhost; -show grants for drop_user@localhost; -drop user drop_user@localhost; ---error ER_REVOKE_GRANTS -revoke all privileges, grant option from drop_user@localhost; - -grant select(a) on test.t1 to drop_user1@localhost; -grant select on test.t1 to drop_user2@localhost; -grant select on test.* to drop_user3@localhost; -grant select on *.* to drop_user4@localhost; -# Drop user now implicitly revokes all privileges. -drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, -drop_user4@localhost; ---error ER_REVOKE_GRANTS -revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, -drop_user3@localhost, drop_user4@localhost; ---error ER_CANNOT_USER -drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, -drop_user4@localhost; -drop table t1; -grant usage on *.* to mysqltest_1@localhost identified by "password"; -grant select, update, insert on test.* to mysqltest_1@localhost; -show grants for mysqltest_1@localhost; -drop user mysqltest_1@localhost; - -# -# Bug#3403 Wrong encoding in SHOW GRANTS output -# -SET NAMES koi8r; -CREATE DATABASE ÂÄ; -USE ÂÄ; -CREATE TABLE ÔÁ (ËÏÌ INT); - -GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -REVOKE SELECT ON ÂÄ.* FROM ÀÚÅÒ@localhost; - -GRANT SELECT ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -REVOKE SELECT ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; - -GRANT SELECT (ËÏÌ) ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -REVOKE SELECT (ËÏÌ) ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; - -# Revoke does not drop user. Leave a clean user table for the next tests. -DROP USER ÀÚÅÒ@localhost; - -DROP DATABASE ÂÄ; -SET NAMES latin1; - -# -# Bug#5831 REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything -# -USE test; -CREATE TABLE t1 (a int ); -CREATE TABLE t2 LIKE t1; -CREATE TABLE t3 LIKE t1; -CREATE TABLE t4 LIKE t1; -CREATE TABLE t5 LIKE t1; -CREATE TABLE t6 LIKE t1; -CREATE TABLE t7 LIKE t1; -CREATE TABLE t8 LIKE t1; -CREATE TABLE t9 LIKE t1; -CREATE TABLE t10 LIKE t1; -CREATE DATABASE testdb1; -CREATE DATABASE testdb2; -CREATE DATABASE testdb3; -CREATE DATABASE testdb4; -CREATE DATABASE testdb5; -CREATE DATABASE testdb6; -CREATE DATABASE testdb7; -CREATE DATABASE testdb8; -CREATE DATABASE testdb9; -CREATE DATABASE testdb10; -GRANT ALL ON testdb1.* TO testuser@localhost; -GRANT ALL ON testdb2.* TO testuser@localhost; -GRANT ALL ON testdb3.* TO testuser@localhost; -GRANT ALL ON testdb4.* TO testuser@localhost; -GRANT ALL ON testdb5.* TO testuser@localhost; -GRANT ALL ON testdb6.* TO testuser@localhost; -GRANT ALL ON testdb7.* TO testuser@localhost; -GRANT ALL ON testdb8.* TO testuser@localhost; -GRANT ALL ON testdb9.* TO testuser@localhost; -GRANT ALL ON testdb10.* TO testuser@localhost; -GRANT SELECT ON test.t1 TO testuser@localhost; -GRANT SELECT ON test.t2 TO testuser@localhost; -GRANT SELECT ON test.t3 TO testuser@localhost; -GRANT SELECT ON test.t4 TO testuser@localhost; -GRANT SELECT ON test.t5 TO testuser@localhost; -GRANT SELECT ON test.t6 TO testuser@localhost; -GRANT SELECT ON test.t7 TO testuser@localhost; -GRANT SELECT ON test.t8 TO testuser@localhost; -GRANT SELECT ON test.t9 TO testuser@localhost; -GRANT SELECT ON test.t10 TO testuser@localhost; -GRANT SELECT (a) ON test.t1 TO testuser@localhost; -GRANT SELECT (a) ON test.t2 TO testuser@localhost; -GRANT SELECT (a) ON test.t3 TO testuser@localhost; -GRANT SELECT (a) ON test.t4 TO testuser@localhost; -GRANT SELECT (a) ON test.t5 TO testuser@localhost; -GRANT SELECT (a) ON test.t6 TO testuser@localhost; -GRANT SELECT (a) ON test.t7 TO testuser@localhost; -GRANT SELECT (a) ON test.t8 TO testuser@localhost; -GRANT SELECT (a) ON test.t9 TO testuser@localhost; -GRANT SELECT (a) ON test.t10 TO testuser@localhost; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost; -SHOW GRANTS FOR testuser@localhost; -DROP USER testuser@localhost; -DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; -DROP DATABASE testdb1; -DROP DATABASE testdb2; -DROP DATABASE testdb3; -DROP DATABASE testdb4; -DROP DATABASE testdb5; -DROP DATABASE testdb6; -DROP DATABASE testdb7; -DROP DATABASE testdb8; -DROP DATABASE testdb9; -DROP DATABASE testdb10; - -# -# Bug#6932 a problem with 'revoke ALL PRIVILEGES' -# - -create table t1(a int, b int, c int, d int); -grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost; -show grants for grant_user@localhost; -select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name; -revoke ALL PRIVILEGES on t1 from grant_user@localhost; -show grants for grant_user@localhost; -select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; -drop user grant_user@localhost; -drop table t1; - -# -# Bug#7391 Cross-database multi-table UPDATE security problem -# -create database mysqltest_1; -create database mysqltest_2; -create table mysqltest_1.t1 select 1 a, 2 q; -create table mysqltest_1.t2 select 1 b, 2 r; -create table mysqltest_2.t1 select 1 c, 2 s; -create table mysqltest_2.t2 select 1 d, 2 t; - -# test the column privileges -grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost; -grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost; -grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; -grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; -connect (conn1,localhost,mysqltest_3,,); -connection conn1; -SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES - WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; -SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES - WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_NAME,PRIVILEGE_TYPE; -SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES - WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; -SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES - WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE; ---error ER_COLUMNACCESS_DENIED_ERROR -update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; ---error ER_COLUMNACCESS_DENIED_ERROR -update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1; ---error ER_TABLEACCESS_DENIED_ERROR -update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1; ---error ER_TABLEACCESS_DENIED_ERROR -update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1; ---error ER_COLUMNACCESS_DENIED_ERROR -update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2; -# the following two should work -update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10; -update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20; -connection master; -select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; -select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; -revoke all on mysqltest_1.t1 from mysqltest_3@localhost; -revoke all on mysqltest_1.t2 from mysqltest_3@localhost; -revoke all on mysqltest_2.t1 from mysqltest_3@localhost; -revoke all on mysqltest_2.t2 from mysqltest_3@localhost; - -# test the db/table level privileges -grant all on mysqltest_2.* to mysqltest_3@localhost; -grant select on *.* to mysqltest_3@localhost; -# Next grant is needed to trigger bug#7391. Do not optimize! -grant select on mysqltest_2.t1 to mysqltest_3@localhost; -flush privileges; -disconnect conn1; -connect (conn2,localhost,mysqltest_3,,); -connection conn2; -use mysqltest_1; -update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600; -# the following failed before, should fail now. ---error ER_TABLEACCESS_DENIED_ERROR -update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; -use mysqltest_2; -# the following used to succeed, it must fail now. ---error ER_TABLEACCESS_DENIED_ERROR -update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; ---error ER_TABLEACCESS_DENIED_ERROR -update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200; ---error ER_TABLEACCESS_DENIED_ERROR -update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200; -# lets see the result -connection master; -select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; -select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; - -delete from mysql.user where user='mysqltest_3'; -delete from mysql.db where user="mysqltest_3"; -delete from mysql.tables_priv where user="mysqltest_3"; -delete from mysql.columns_priv where user="mysqltest_3"; -flush privileges; -drop database mysqltest_1; -drop database mysqltest_2; -disconnect conn2; - -# -# just SHOW PRIVILEGES test -# -SHOW PRIVILEGES; - -# -# Rights for renaming test (Bug#3270) -# -connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connection root; ---disable_warnings -create database mysqltest; ---enable_warnings -create table mysqltest.t1 (a int,b int,c int); -grant all on mysqltest.t1 to mysqltest_1@localhost; -connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); -connection user1; --- error ER_TABLEACCESS_DENIED_ERROR -alter table t1 rename t2; -disconnect user1; -connection root; -revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; -delete from mysql.user where user=_binary'mysqltest_1'; -drop database mysqltest; -connection default; -disconnect root; - -# -# check all new table privileges -# -CREATE USER dummy@localhost; -CREATE DATABASE mysqltest; -CREATE TABLE mysqltest.dummytable (dummyfield INT); -CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; -GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost; -GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost; -SHOW GRANTS FOR dummy@localhost; -use INFORMATION_SCHEMA; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -FLUSH PRIVILEGES; -SHOW GRANTS FOR dummy@localhost; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -SHOW FIELDS FROM mysql.tables_priv; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; -DROP USER dummy@localhost; -DROP DATABASE mysqltest; -# check view only privileges -CREATE USER dummy@localhost; -CREATE DATABASE mysqltest; -CREATE TABLE mysqltest.dummytable (dummyfield INT); -CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; -GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost; -GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost; -SHOW GRANTS FOR dummy@localhost; -use INFORMATION_SCHEMA; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -FLUSH PRIVILEGES; -SHOW GRANTS FOR dummy@localhost; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; -DROP USER dummy@localhost; -DROP DATABASE mysqltest; -CREATE USER dummy@localhost; -CREATE DATABASE mysqltest; -CREATE TABLE mysqltest.dummytable (dummyfield INT); -CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; -GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost; -GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost; -SHOW GRANTS FOR dummy@localhost; -use INFORMATION_SCHEMA; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -FLUSH PRIVILEGES; -SHOW GRANTS FOR dummy@localhost; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; -DROP USER dummy@localhost; -DROP DATABASE mysqltest; -# -# Bug#11330 Entry in tables_priv with host = '' causes crash -# -connection default; -use mysql; -insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select'); -flush privileges; -delete from tables_priv where host = '' and user = 'mysqltest_1'; -flush privileges; -use test; - -# -# Bug#10892 user variables not auto cast for comparisons -# Check that we don't get illegal mix of collations -# -set @user123="non-existent"; -select * from mysql.db where user=@user123; - -set names koi8r; -create database ÂÄ; -grant select on ÂÄ.* to root@localhost; -select hex(Db) from mysql.db where Db='ÂÄ'; -show grants for root@localhost; -flush privileges; -show grants for root@localhost; -drop database ÂÄ; -revoke all privileges on ÂÄ.* from root@localhost; -show grants for root@localhost; -set names latin1; - -# -# Bug#15598 Server crashes in specific case during setting new password -# - Caused by a user with host '' -# -create user mysqltest_7@; -set password for mysqltest_7@ = password('systpass'); -show grants for mysqltest_7@; -drop user mysqltest_7@; ---error ER_NONEXISTING_GRANT -show grants for mysqltest_7@; - -# -# Bug#14385 GRANT and mapping to correct user account problems -# -create database mysqltest; -use mysqltest; -create table t1(f1 int); -GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%'; -GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%'; -show grants for mysqltest1@'192.%'; -show grants for mysqltest1@'%'; -delete from mysql.user where user='mysqltest1'; -delete from mysql.db where user='mysqltest1'; -delete from mysql.tables_priv where user='mysqltest1'; -flush privileges; -drop database mysqltest; - -# -# Bug#27515 DROP previlege is not required for RENAME TABLE -# -connection master; -create database db27515; -use db27515; -create table t1 (a int); -grant alter on db27515.t1 to user27515@localhost; -grant insert, create on db27515.t2 to user27515@localhost; - -connect (conn27515, localhost, user27515, , db27515); -connection conn27515; ---error ER_TABLEACCESS_DENIED_ERROR -rename table t1 to t2; -disconnect conn27515; - -connection master; -revoke all privileges, grant option from user27515@localhost; -drop user user27515@localhost; -drop database db27515; - ---echo End of 4.1 tests - -# -# Bug#16297 In memory grant tables not flushed when users's hostname is "" -# -use test; -create table t1 (a int); - -# Backup anonymous users and remove them. (They get in the way of -# the one we test with here otherwise.) -create table t2 as select * from mysql.user where user=''; -delete from mysql.user where user=''; -flush privileges; - -# Create some users with different hostnames -create user mysqltest_8@''; -create user mysqltest_8@host8; - -# Try to create them again ---error ER_CANNOT_USER -create user mysqltest_8@''; ---error ER_CANNOT_USER -create user mysqltest_8; ---error ER_CANNOT_USER -create user mysqltest_8@host8; - -select user, QUOTE(host) from mysql.user where user="mysqltest_8"; - ---echo Schema privileges -grant select on mysqltest.* to mysqltest_8@''; -show grants for mysqltest_8@''; -grant select on mysqltest.* to mysqltest_8@; -show grants for mysqltest_8@; -grant select on mysqltest.* to mysqltest_8; -show grants for mysqltest_8; -select * from information_schema.schema_privileges -where grantee like "'mysqltest_8'%"; -connect (conn3,localhost,mysqltest_8,,); -select * from t1; -disconnect conn3; -connection master; -revoke select on mysqltest.* from mysqltest_8@''; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -select * from information_schema.schema_privileges -where grantee like "'mysqltest_8'%"; -flush privileges; -show grants for mysqltest_8@''; -show grants for mysqltest_8@; -grant select on mysqltest.* to mysqltest_8@''; -flush privileges; -show grants for mysqltest_8@; -revoke select on mysqltest.* from mysqltest_8@''; -flush privileges; - ---echo Column privileges -grant update (a) on t1 to mysqltest_8@''; -grant update (a) on t1 to mysqltest_8; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -flush privileges; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -select * from information_schema.column_privileges; -connect (conn4,localhost,mysqltest_8,,); -select * from t1; -disconnect conn4; -connection master; -revoke update (a) on t1 from mysqltest_8@''; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -select * from information_schema.column_privileges; -flush privileges; -show grants for mysqltest_8@''; -show grants for mysqltest_8; - ---echo Table privileges -grant update on t1 to mysqltest_8@''; -grant update on t1 to mysqltest_8; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -flush privileges; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -select * from information_schema.table_privileges; -connect (conn5,localhost,mysqltest_8,,); -select * from t1; -disconnect conn5; -connection master; -revoke update on t1 from mysqltest_8@''; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -select * from information_schema.table_privileges; -flush privileges; -show grants for mysqltest_8@''; -show grants for mysqltest_8; - ---echo "DROP USER" should clear privileges -grant all privileges on mysqltest.* to mysqltest_8@''; -grant select on mysqltest.* to mysqltest_8@''; -grant update on t1 to mysqltest_8@''; -grant update (a) on t1 to mysqltest_8@''; -grant all privileges on mysqltest.* to mysqltest_8; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -select * from information_schema.user_privileges -where grantee like "'mysqltest_8'%"; -connect (conn5,localhost,mysqltest_8,,); -select * from t1; -disconnect conn5; -connection master; -flush privileges; -show grants for mysqltest_8@''; -show grants for mysqltest_8; -drop user mysqltest_8@''; ---error ER_NONEXISTING_GRANT -show grants for mysqltest_8@''; ---replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error ER_ACCESS_DENIED_ERROR -connect (conn6,localhost,mysqltest_8,,); -connection master; ---error ER_NONEXISTING_GRANT -show grants for mysqltest_8; -drop user mysqltest_8@host8; ---error ER_NONEXISTING_GRANT -show grants for mysqltest_8@host8; - -# Restore the anonymous users. -insert into mysql.user select * from t2; -flush privileges; -drop table t2; -drop table t1; - -# -# Bug#20214 Incorrect error when user calls SHOW CREATE VIEW on non -# privileged view -# - -connection master; - -CREATE DATABASE mysqltest3; -USE mysqltest3; - -CREATE TABLE t_nn (c1 INT); -CREATE VIEW v_nn AS SELECT * FROM t_nn; - -CREATE DATABASE mysqltest2; -USE mysqltest2; - -CREATE TABLE t_nn (c1 INT); -CREATE VIEW v_nn AS SELECT * FROM t_nn; -CREATE VIEW v_yn AS SELECT * FROM t_nn; -CREATE VIEW v_gy AS SELECT * FROM t_nn; -CREATE VIEW v_ny AS SELECT * FROM t_nn; -CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55; - -GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; -GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; -GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; -GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; - -connect (mysqltest_1, localhost, mysqltest_1, mysqltest_1,); - -# fail because of missing SHOW VIEW (have generic SELECT) ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE VIEW mysqltest2.v_nn; ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE TABLE mysqltest2.v_nn; - -# fail because of missing SHOW VIEW ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE VIEW mysqltest2.v_yn; ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE TABLE mysqltest2.v_yn; - -# succeed (despite of missing SELECT, having SHOW VIEW bails us out) -SHOW CREATE TABLE mysqltest2.v_ny; - -# succeed (despite of missing SELECT, having SHOW VIEW bails us out) -SHOW CREATE VIEW mysqltest2.v_ny; - -# fail because of missing (specific or generic) SELECT ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE TABLE mysqltest3.t_nn; - -# fail because of missing (specific or generic) SELECT (not because it's not a view!) ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE VIEW mysqltest3.t_nn; - -# fail because of missing missing (specific or generic) SELECT (and SHOW VIEW) ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE VIEW mysqltest3.v_nn; ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE TABLE mysqltest3.v_nn; - -# succeed thanks to generic SELECT -SHOW CREATE TABLE mysqltest2.t_nn; - -# fail because it's not a view! (have generic SELECT though) ---error ER_WRONG_OBJECT -SHOW CREATE VIEW mysqltest2.t_nn; - -# succeed, have SELECT and SHOW VIEW -SHOW CREATE VIEW mysqltest2.v_yy; - -# succeed, have SELECT and SHOW VIEW -SHOW CREATE TABLE mysqltest2.v_yy; - -# clean-up -connection master; - -# succeed, we're root -SHOW CREATE TABLE mysqltest2.v_nn; -SHOW CREATE VIEW mysqltest2.v_nn; - -SHOW CREATE TABLE mysqltest2.t_nn; - -# fail because it's not a view! ---error ER_WRONG_OBJECT -SHOW CREATE VIEW mysqltest2.t_nn; - -DROP VIEW mysqltest2.v_nn; -DROP VIEW mysqltest2.v_yn; -DROP VIEW mysqltest2.v_ny; -DROP VIEW mysqltest2.v_yy; -DROP TABLE mysqltest2.t_nn; -DROP DATABASE mysqltest2; -DROP VIEW mysqltest3.v_nn; -DROP TABLE mysqltest3.t_nn; -DROP DATABASE mysqltest3; -disconnect mysqltest_1; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost'; -DROP USER 'mysqltest_1'@'localhost'; - -# restore the original database -USE test; -connection default; -disconnect master; - - -# -# Bug#10668 CREATE USER does not enforce username length limit -# ---error ER_WRONG_STRING_LENGTH -create user longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789; - -# -# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause. -# -# These checks are intended to ensure that appropriate errors are risen when -# illegal user name or hostname is specified in user-clause of GRANT/REVOKE -# statements. -# - -# -# Bug#22369 Alter table rename combined with other alterations causes lost tables -# -CREATE DATABASE mysqltest1; -CREATE TABLE mysqltest1.t1 ( - int_field INTEGER UNSIGNED NOT NULL, - char_field CHAR(10), - INDEX(`int_field`) -); -CREATE TABLE mysqltest1.t2 (int_field INT); - ---echo "Now check that we require equivalent grants for " ---echo "RENAME TABLE and ALTER TABLE" -CREATE USER mysqltest_1@localhost; -GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; - -connect (conn42,localhost,mysqltest_1,,mysqltest1); -SELECT USER(); -SHOW GRANTS; ---error ER_TABLEACCESS_DENIED_ERROR -RENAME TABLE t1 TO t2; ---error ER_TABLEACCESS_DENIED_ERROR -ALTER TABLE t1 RENAME TO t2; ---disconnect conn42 ---connection default -GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost; - -connect (conn42,localhost,mysqltest_1,,mysqltest1); ---error ER_TABLEACCESS_DENIED_ERROR -RENAME TABLE t1 TO t2; ---error ER_TABLEACCESS_DENIED_ERROR -ALTER TABLE t1 RENAME TO t2; ---disconnect conn42 ---connection default -GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost; - -connect (conn42,localhost,mysqltest_1,,mysqltest1); -SHOW GRANTS; ---error ER_TABLEACCESS_DENIED_ERROR -RENAME TABLE t1 TO t2; ---error ER_TABLEACCESS_DENIED_ERROR -ALTER TABLE t1 RENAME TO t2; ---disconnect conn42 ---connection default -GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost; -connect (conn42,localhost,mysqltest_1,,mysqltest1); -SHOW GRANTS; ---error ER_TABLEACCESS_DENIED_ERROR ---disconnect conn42 ---connection default -GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost; -DROP TABLE mysqltest1.t2; - -connect (conn42,localhost,mysqltest_1,,mysqltest1); -SHOW GRANTS; -RENAME TABLE t1 TO t2; -RENAME TABLE t2 TO t1; -ALTER TABLE t1 RENAME TO t2; -ALTER TABLE t2 RENAME TO t1; ---disconnect conn42 ---connection default -REVOKE DROP, INSERT ON mysqltest1.t1 FROM mysqltest_1@localhost; -REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost; - -connect (conn42,localhost,mysqltest_1,,mysqltest1); -SHOW GRANTS; ---error ER_TABLEACCESS_DENIED_ERROR -RENAME TABLE t1 TO t2; ---error ER_TABLEACCESS_DENIED_ERROR -ALTER TABLE t1 RENAME TO t2; ---disconnect conn42 ---connection default - -DROP USER mysqltest_1@localhost; -DROP DATABASE mysqltest1; -USE test; - -# Working with database-level privileges. - ---error ER_WRONG_STRING_LENGTH -GRANT CREATE ON mysqltest.* TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; - ---error ER_WRONG_STRING_LENGTH -GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; - ---error ER_WRONG_STRING_LENGTH -REVOKE CREATE ON mysqltest.* FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; - ---error ER_WRONG_STRING_LENGTH -REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; - -# Working with table-level privileges. - ---error ER_WRONG_STRING_LENGTH -GRANT CREATE ON t1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; - ---error ER_WRONG_STRING_LENGTH -GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; - ---error ER_WRONG_STRING_LENGTH -REVOKE CREATE ON t1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; - ---error ER_WRONG_STRING_LENGTH -REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; - -# Working with routine-level privileges. - ---error ER_WRONG_STRING_LENGTH -GRANT EXECUTE ON PROCEDURE p1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; - ---error ER_WRONG_STRING_LENGTH -GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; - ---error ER_WRONG_STRING_LENGTH -REVOKE EXECUTE ON PROCEDURE p1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; - ---error ER_WRONG_STRING_LENGTH -REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; - - -# -# Bug#23556 TRUNCATE TABLE still maps to DELETE -# -CREATE USER bug23556@localhost; -CREATE DATABASE bug23556; -GRANT SELECT ON bug23556.* TO bug23556@localhost; -connect (bug23556,localhost,bug23556,,bug23556); - -connection default; -USE bug23556; -CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); -GRANT DELETE ON t1 TO bug23556@localhost; - -connection bug23556; -USE bug23556; ---error ER_TABLEACCESS_DENIED_ERROR -TRUNCATE t1; - -connection default; -USE bug23556; -REVOKE DELETE ON t1 FROM bug23556@localhost; -GRANT DROP ON t1 TO bug23556@localhost; - -connection bug23556; -USE bug23556; -TRUNCATE t1; - -connection default; -USE bug23556; -DROP TABLE t1; -USE test; -DROP DATABASE bug23556; -DROP USER bug23556@localhost; -connection default; -disconnect bug23556; - - -# -# Bug#6774 Replication fails with Wrong usage of DB GRANT and GLOBAL PRIVILEGES -# -# Check if GRANT ... ON * ... fails when no database is selected -connect (con1, localhost, root,,*NO-ONE*); -connection con1; ---error ER_NO_DB_ERROR -GRANT PROCESS ON * TO user@localhost; -disconnect con1; -connection default; - - -# -# Bug#9504 Stored procedures: execute privilege doesn't make 'use database' -# okay. -# - -# Prepare. - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -DROP DATABASE IF EXISTS mysqltest3; -DROP DATABASE IF EXISTS mysqltest4; ---enable_warnings - -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; -CREATE DATABASE mysqltest3; -CREATE DATABASE mysqltest4; - -CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER - SELECT 1; - -CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER - SELECT 1; - -CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER - RETURN 1; - -CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER - RETURN 1; - -GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost; -GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost; -GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost; -GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost; - -GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; - -# Test. - ---connect (bug9504_con1,localhost,mysqltest_1,,) ---echo ---echo ---> connection: bug9504_con1 - -# - Check that we can switch to the db; - -use mysqltest1; - -use mysqltest2; - -use mysqltest3; - -use mysqltest4; - -# - Check that we can call stored routines; - -use test; - -CALL mysqltest1.p_def(); - -CALL mysqltest2.p_inv(); - -SELECT mysqltest3.f_def(); - -SELECT mysqltest4.f_inv(); - -# Cleanup. - ---connection default ---echo ---echo ---> connection: default - ---disconnect bug9504_con1 - -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP DATABASE mysqltest3; -DROP DATABASE mysqltest4; - -DROP USER mysqltest_1@localhost; - - -# -# Bug#27337 Privileges are not restored properly. -# -# Actually, the patch for this bugs fixes two problems. So, here are two test -# cases. - -# Test case 1: privileges are not restored properly after calling a stored -# routine defined with SQL SECURITY INVOKER clause. - -# Prepare. - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; ---enable_warnings - -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; - -GRANT ALL PRIVILEGES ON mysqltest1.* TO mysqltest_1@localhost; -GRANT SELECT ON mysqltest2.* TO mysqltest_1@localhost; - -CREATE PROCEDURE mysqltest1.p1() SQL SECURITY INVOKER - SELECT 1; - -# Test. - ---connect (bug27337_con1,localhost,mysqltest_1,,mysqltest2) ---echo ---echo ---> connection: bug27337_con1 - ---error ER_TABLEACCESS_DENIED_ERROR -CREATE TABLE t1(c INT); - -CALL mysqltest1.p1(); - ---error ER_TABLEACCESS_DENIED_ERROR -CREATE TABLE t1(c INT); - ---disconnect bug27337_con1 - ---connect (bug27337_con2,localhost,mysqltest_1,,mysqltest2) ---echo ---echo ---> connection: bug27337_con2 - ---error ER_TABLEACCESS_DENIED_ERROR -CREATE TABLE t1(c INT); - -SHOW TABLES; - -# Cleanup. - ---connection default ---echo ---echo ---> connection: default - ---disconnect bug27337_con2 - -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; - -DROP USER mysqltest_1@localhost; - -# Test case 2: privileges are not checked properly for prepared statements. - -# Prepare. - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; ---enable_warnings - -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; - -CREATE TABLE mysqltest1.t1(c INT); -CREATE TABLE mysqltest2.t2(c INT); - -GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; -GRANT SELECT ON mysqltest2.t2 TO mysqltest_2@localhost; - -# Test. - ---connect (bug27337_con1,localhost,mysqltest_1,,mysqltest1) ---echo ---echo ---> connection: bug27337_con1 - -SHOW TABLES FROM mysqltest1; - -PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1'; - -EXECUTE stmt1; - ---connect (bug27337_con2,localhost,mysqltest_2,,mysqltest2) ---echo ---echo ---> connection: bug27337_con2 - -SHOW COLUMNS FROM mysqltest2.t2; - -PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2'; - -EXECUTE stmt2; - ---connection default ---echo ---echo ---> connection: default - -REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost; -REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost; - ---connection bug27337_con1 ---echo ---echo ---> connection: bug27337_con1 - ---error ER_DBACCESS_DENIED_ERROR -SHOW TABLES FROM mysqltest1; - ---error ER_DBACCESS_DENIED_ERROR -EXECUTE stmt1; - ---connection bug27337_con2 ---echo ---echo ---> connection: bug27337_con2 - ---error ER_TABLEACCESS_DENIED_ERROR -SHOW COLUMNS FROM mysqltest2.t2; - ---error ER_TABLEACCESS_DENIED_ERROR -EXECUTE stmt2; - -# Cleanup. - ---connection default ---echo ---echo ---> connection: default - ---disconnect bug27337_con1 ---disconnect bug27337_con2 - -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; - -DROP USER mysqltest_1@localhost; -DROP USER mysqltest_2@localhost; - -# -# Bug#27878 Unchecked privileges on a view referring to a table from another -# database. -# -USE test; -CREATE TABLE t1 (f1 int, f2 int); -INSERT INTO t1 VALUES(1,1), (2,2); -CREATE DATABASE db27878; -GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost'; -GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost'; -GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost'; -USE db27878; -CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1; -connect (user1,localhost,mysqltest_1,,test); -connection user1; -USE db27878; ---error 1356 -UPDATE v1 SET f2 = 4; -SELECT * FROM test.t1; -disconnect user1; -connection default; -REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost'; -REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost'; -REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost'; -DROP USER mysqltest_1@localhost; -DROP DATABASE db27878; -USE test; -DROP TABLE t1; - ---echo # ---echo # Bug#33275 Server crash when creating temporary table mysql.user ---echo # -CREATE TEMPORARY TABLE mysql.user (id INT); -FLUSH PRIVILEGES; -DROP TABLE mysql.user; - - -# -# Bug#33201 Crash occurs when granting update privilege on one column of a view -# -drop table if exists test; -drop function if exists test_function; -drop view if exists v1; -create table test (col1 varchar(30)); -delimiter |; -create function test_function() returns varchar(30) -begin - declare tmp varchar(30); - select col1 from test limit 1 into tmp; - return '1'; -end| -delimiter ;| -create view v1 as select test.* from test where test.col1=test_function(); -grant update (col1) on v1 to 'greg'@'localhost'; -drop user 'greg'@'localhost'; -drop view v1; -drop table test; -drop function test_function; - -# -# Bug#41456 SET PASSWORD hates CURRENT_USER() -# -SELECT CURRENT_USER(); -SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin"); -SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); - -# -# Bug#57952: privilege change is not taken into account by EXECUTE. -# - ---echo ---echo # Bug#57952 ---echo - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; ---enable_warnings - -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; - -use mysqltest1; -CREATE TABLE t1(a INT, b INT); -INSERT INTO t1 VALUES (1, 1); - -CREATE TABLE t2(a INT); -INSERT INTO t2 VALUES (2); - -CREATE TABLE mysqltest2.t3(a INT); -INSERT INTO mysqltest2.t3 VALUES (4); - -CREATE USER testuser@localhost; -GRANT CREATE ROUTINE, EXECUTE ON mysqltest1.* TO testuser@localhost; -GRANT SELECT(b) ON t1 TO testuser@localhost; -GRANT SELECT ON t2 TO testuser@localhost; -GRANT SELECT ON mysqltest2.* TO testuser@localhost; - ---echo ---echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1) ---connect (bug57952_con1,localhost,testuser,,mysqltest1) -PREPARE s1 FROM 'SELECT b FROM t1'; -PREPARE s2 FROM 'SELECT a FROM t2'; -PREPARE s3 FROM 'SHOW TABLES FROM mysqltest2'; - -CREATE PROCEDURE p1() SELECT b FROM t1; -CREATE PROCEDURE p2() SELECT a FROM t2; -CREATE PROCEDURE p3() SHOW TABLES FROM mysqltest2; - -CALL p1; -CALL p2; -CALL p3; - ---echo ---echo # Connection: default ---connection default -REVOKE SELECT ON t1 FROM testuser@localhost; -GRANT SELECT(a) ON t1 TO testuser@localhost; -REVOKE SELECT ON t2 FROM testuser@localhost; -REVOKE SELECT ON mysqltest2.* FROM testuser@localhost; - ---echo ---echo # Connection: bug57952_con1 (testuser@localhost, db: mysqltest1) ---connection bug57952_con1 ---echo # - Check column-level privileges... ---error ER_COLUMNACCESS_DENIED_ERROR -EXECUTE s1; - ---error ER_COLUMNACCESS_DENIED_ERROR -SELECT b FROM t1; - ---error ER_COLUMNACCESS_DENIED_ERROR -EXECUTE s1; - ---error ER_COLUMNACCESS_DENIED_ERROR -CALL p1; - ---echo # - Check table-level privileges... ---error ER_TABLEACCESS_DENIED_ERROR -SELECT a FROM t2; - ---error ER_TABLEACCESS_DENIED_ERROR -EXECUTE s2; - ---error ER_TABLEACCESS_DENIED_ERROR -CALL p2; - ---echo # - Check database-level privileges... ---error ER_DBACCESS_DENIED_ERROR -SHOW TABLES FROM mysqltest2; - ---error ER_DBACCESS_DENIED_ERROR -EXECUTE s3; - ---error ER_DBACCESS_DENIED_ERROR -CALL p3; - ---echo ---echo # Connection: default ---connection default ---disconnect bug57952_con1 -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP USER testuser@localhost; -use test; ---echo - - ---echo # ---echo # Test for bug #36544 "DROP USER does not remove stored function ---echo # privileges". ---echo # -create database mysqltest1; -create function mysqltest1.f1() returns int return 0; -create procedure mysqltest1.p1() begin end; ---echo # ---echo # 1) Check that DROP USER properly removes privileges on both ---echo # stored procedures and functions. ---echo # -create user mysqluser1@localhost; -grant execute on function mysqltest1.f1 to mysqluser1@localhost; -grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; - ---echo # Quick test that granted privileges are properly reflected ---echo # in privilege tables and in in-memory structures. -show grants for mysqluser1@localhost; -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; ---echo # ---echo # Create connection 'bug_36544_con1' as 'mysqluser1@localhost'. ---connect (bug36544_con1,localhost,mysqluser1,,) -call mysqltest1.p1(); -select mysqltest1.f1(); - ---echo # ---echo # Switch to connection 'default'. ---connection default -drop user mysqluser1@localhost; - ---echo # ---echo # Test that dropping of user is properly reflected in ---echo # both privilege tables and in in-memory structures. ---echo # ---echo # Switch to connection 'bug36544_con1'. ---connection bug36544_con1 ---echo # The connection cold be alive but should not be able to ---echo # access to any of the stored routines. ---error ER_PROCACCESS_DENIED_ERROR -call mysqltest1.p1(); ---error ER_PROCACCESS_DENIED_ERROR -select mysqltest1.f1(); ---disconnect bug36544_con1 - ---echo # ---echo # Switch to connection 'default'. ---connection default ---echo # ---echo # Now create user with the same name and check that he ---echo # has not inherited privileges. -create user mysqluser1@localhost; -show grants for mysqluser1@localhost; -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; ---echo # ---echo # Create connection 'bug_36544_con2' as 'mysqluser1@localhost'. ---connect (bug36544_con2,localhost,mysqluser1,,) ---echo # Newly created user should not be able to access any of the routines. ---error ER_PROCACCESS_DENIED_ERROR -call mysqltest1.p1(); ---error ER_PROCACCESS_DENIED_ERROR -select mysqltest1.f1(); ---echo # ---echo # Switch to connection 'default'. ---connection default - ---echo # ---echo # 2) Check that RENAME USER properly updates privileges on both ---echo # stored procedures and functions. ---echo # -grant execute on function mysqltest1.f1 to mysqluser1@localhost; -grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; ---echo # ---echo # Create one more user to make in-memory hashes non-trivial. ---echo # User names 'mysqluser11' and 'mysqluser10' were selected ---echo # to trigger bug discovered during code inspection. -create user mysqluser11@localhost; -grant execute on function mysqltest1.f1 to mysqluser11@localhost; -grant execute on procedure mysqltest1.p1 to mysqluser11@localhost; ---echo # Also create a couple of tables to test for another bug ---echo # discovered during code inspection (again table names were ---echo # chosen especially to trigger the bug). -create table mysqltest1.t11 (i int); -create table mysqltest1.t22 (i int); -grant select on mysqltest1.t22 to mysqluser1@localhost; -grant select on mysqltest1.t11 to mysqluser1@localhost; - ---echo # Quick test that granted privileges are properly reflected ---echo # in privilege tables and in in-memory structures. -show grants for mysqluser1@localhost; -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; -select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; ---echo # ---echo # Switch to connection 'bug36544_con2'. ---connection bug36544_con2 -call mysqltest1.p1(); -select mysqltest1.f1(); -select * from mysqltest1.t11; -select * from mysqltest1.t22; - ---echo # ---echo # Switch to connection 'default'. ---connection default -rename user mysqluser1@localhost to mysqluser10@localhost; - ---echo # ---echo # Test that there are no privileges left for mysqluser1. ---echo # ---echo # Switch to connection 'bug36544_con2'. ---connection bug36544_con2 ---echo # The connection cold be alive but should not be able to ---echo # access to any of the stored routines or tables. ---error ER_PROCACCESS_DENIED_ERROR -call mysqltest1.p1(); ---error ER_PROCACCESS_DENIED_ERROR -select mysqltest1.f1(); ---error ER_TABLEACCESS_DENIED_ERROR -select * from mysqltest1.t11; ---error ER_TABLEACCESS_DENIED_ERROR -select * from mysqltest1.t22; ---disconnect bug36544_con2 - ---echo # ---echo # Switch to connection 'default'. ---connection default ---echo # ---echo # Now create user with the old name and check that he ---echo # has not inherited privileges. -create user mysqluser1@localhost; -show grants for mysqluser1@localhost; -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; -select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; ---echo # ---echo # Create connection 'bug_36544_con3' as 'mysqluser1@localhost'. ---connect (bug36544_con3,localhost,mysqluser1,,) ---echo # Newly created user should not be able to access to any of the ---echo # stored routines or tables. ---error ER_PROCACCESS_DENIED_ERROR -call mysqltest1.p1(); ---error ER_PROCACCESS_DENIED_ERROR -select mysqltest1.f1(); ---error ER_TABLEACCESS_DENIED_ERROR -select * from mysqltest1.t11; ---error ER_TABLEACCESS_DENIED_ERROR -select * from mysqltest1.t22; ---disconnect bug36544_con3 - ---echo # ---echo # Switch to connection 'default'. ---connection default ---echo # ---echo # Now check that privileges became associated with a new user ---echo # name - mysqluser10. ---echo # -show grants for mysqluser10@localhost; -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost'; -select db, table_name, table_priv from mysql.tables_priv where user='mysqluser10' and host='localhost'; ---echo # ---echo # Create connection 'bug_36544_con4' as 'mysqluser10@localhost'. ---connect (bug36544_con4,localhost,mysqluser10,,) -call mysqltest1.p1(); -select mysqltest1.f1(); -select * from mysqltest1.t11; -select * from mysqltest1.t22; ---disconnect bug36544_con4 - ---echo # ---echo # Switch to connection 'default'. ---connection default ---echo # ---echo # Clean-up. -drop user mysqluser1@localhost; -drop user mysqluser10@localhost; -drop user mysqluser11@localhost; -drop database mysqltest1; - - ---echo End of 5.0 tests -set names utf8; ---error ER_WRONG_STRING_LENGTH -grant select on test.* to очень_длинный_юзер890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890@localhost; -set names default; - -# -# Bug#20901 CREATE privilege is enough to insert into a table -# - -create database mysqltest; -use mysqltest; - -grant create on mysqltest.* to mysqltest@localhost; -create table t1 (i INT); - -connect (user1,localhost,mysqltest,,mysqltest); -connection user1; -# show we don't have INSERT ---error ER_TABLEACCESS_DENIED_ERROR -insert into t1 values (1); -# show we have CREATE -create table t2 (i INT); -create table t4 (i INT); - -connection default; -grant select, insert on mysqltest.t2 to mysqltest@localhost; -grant insert on mysqltest.t4 to mysqltest@localhost; -# to specify ACLs for non-existent objects, must explictly |CREATE -grant create, insert on mysqltest.t5 to mysqltest@localhost; -grant create, insert on mysqltest.t6 to mysqltest@localhost; -flush privileges; - -connection user1; -insert into t2 values (1); - - -# CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail ---error ER_TABLEACCESS_DENIED_ERROR -create table if not exists t1 select * from t2; - -# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail ---error ER_TABLEACCESS_DENIED_ERROR -create table if not exists t3 select * from t2; - -# CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed -create table if not exists t4 select * from t2; - -# CREATE IF NOT EXISTS...SELECT, no t5 yet, have INSERT, must succeed -create table if not exists t5 select * from t2; - - -# CREATE...SELECT, no t6 yet, have INSERT, must succeed -create table t6 select * from t2; - -# CREATE...SELECT, no t7 yet, no INSERT, must fail ---error ER_TABLEACCESS_DENIED_ERROR -create table t7 select * from t2; - -# CREATE...SELECT, t4 exists, have INSERT, must still fail (exists) ---error 1050 -create table t4 select * from t2; - -# CREATE...SELECT, t1 exists, no INSERT, must fail ---error ER_TABLEACCESS_DENIED_ERROR -create table t1 select * from t2; - - -connection default; -drop table t1,t2,t4,t5,t6; - -revoke create on mysqltest.* from mysqltest@localhost; -revoke select, insert on mysqltest.t2 from mysqltest@localhost; -revoke insert on mysqltest.t4 from mysqltest@localhost; -revoke create, insert on mysqltest.t5 from mysqltest@localhost; -revoke create, insert on mysqltest.t6 from mysqltest@localhost; -drop user mysqltest@localhost; - -disconnect user1; -drop database mysqltest; -use test; - - -# -# Bug#16470 crash on grant if old grant tables -# - -call mtr.add_suppression("Can't open and lock privilege tables"); - ---echo FLUSH PRIVILEGES without procs_priv table. -RENAME TABLE mysql.procs_priv TO mysql.procs_gone; -FLUSH PRIVILEGES; ---echo Assigning privileges without procs_priv table. -CREATE DATABASE mysqltest1; -CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER - SELECT 1; -CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 1; ---error ER_NO_SUCH_TABLE -GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost; -GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; -CALL mysqltest1.test(); -DROP DATABASE mysqltest1; -RENAME TABLE mysql.procs_gone TO mysql.procs_priv; -DROP USER mysqltest_1@localhost; -FLUSH PRIVILEGES; - - -# -# Bug#33464 DROP FUNCTION caused a crash. -# -CREATE DATABASE dbbug33464; -CREATE USER 'userbug33464'@'localhost'; - -GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost'; - ---replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK -connect (connbug33464, localhost, userbug33464, , dbbug33464); ---source suite/funcs_1/include/show_connection.inc - -delimiter //; -CREATE PROCEDURE sp3(v1 char(20)) -BEGIN - SELECT * from dbbug33464.t6 where t6.f2= 'xyz'; -END// -delimiter ;// - -delimiter //; -CREATE FUNCTION fn1() returns char(50) SQL SECURITY INVOKER -BEGIN - return 1; -END// -delimiter ;// - -delimiter //; -CREATE FUNCTION fn2() returns char(50) SQL SECURITY DEFINER -BEGIN - return 2; -END// -delimiter ;// - -disconnect connbug33464; - -# cleanup -connection default; -USE dbbug33464; ---source suite/funcs_1/include/show_connection.inc - -SELECT fn1(); -SELECT fn2(); - ---error 0, ER_CANNOT_USER -DROP USER 'userbug33464'@'localhost'; - -DROP FUNCTION fn1; -DROP FUNCTION fn2; -DROP PROCEDURE sp3; - ---error 0, ER_CANNOT_USER -DROP USER 'userbug33464'@'localhost'; - -USE test; -DROP DATABASE dbbug33464; - - -SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; - -# -# Bug#44658 Create procedure makes server crash when user does not have ALL privilege -# -CREATE USER user1; -CREATE USER user2; -GRANT CREATE ON db1.* TO 'user1'@'localhost'; -GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost'; -GRANT CREATE ON db1.* TO 'user2'@'%'; -GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%'; -FLUSH PRIVILEGES; -SHOW GRANTS FOR 'user1'@'localhost'; -connect (con1,localhost,user1,,); ---echo ** Connect as user1 and create a procedure. ---echo ** The creation will imply implicitly assigned ---echo ** EXECUTE and ALTER ROUTINE privileges to ---echo ** the current user user1@localhost. -SELECT @@GLOBAL.sql_mode; -SELECT @@SESSION.sql_mode; -CREATE DATABASE db1; -DELIMITER ||; -CREATE PROCEDURE db1.proc1(p1 INT) - BEGIN - SET @x = 0; - REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; - END ;|| -DELIMITER ;|| - -connect (con2,localhost,user2,,); ---echo ** Connect as user2 and create a procedure. ---echo ** Implicitly assignment of privileges will ---echo ** fail because the user2@localhost is an ---echo ** unknown user. -DELIMITER ||; -CREATE PROCEDURE db1.proc2(p1 INT) - BEGIN - SET @x = 0; - REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; - END ;|| -DELIMITER ;|| - -connection default; -SHOW GRANTS FOR 'user1'@'localhost'; -SHOW GRANTS FOR 'user2'; -disconnect con1; -disconnect con2; -DROP PROCEDURE db1.proc1; -DROP PROCEDURE db1.proc2; -REVOKE ALL ON db1.* FROM 'user1'@'localhost'; -REVOKE ALL ON db1.* FROM 'user2'@'%'; -DROP USER 'user1'; -DROP USER 'user1'@'localhost'; -DROP USER 'user2'; -DROP DATABASE db1; - - ---echo # ---echo # Bug #25863 No database selected error, but documentation ---echo # says * for global allowed ---echo # - -connect(conn1,localhost,root,,*NO-ONE*); - ---error ER_NO_DB_ERROR -GRANT ALL ON * TO mysqltest_1; - -GRANT ALL ON *.* TO mysqltest_1; -SHOW GRANTS FOR mysqltest_1; -DROP USER mysqltest_1; - -USE test; - -GRANT ALL ON * TO mysqltest_1; -SHOW GRANTS FOR mysqltest_1; -DROP USER mysqltest_1; - -GRANT ALL ON *.* TO mysqltest_1; -SHOW GRANTS FOR mysqltest_1; -DROP USER mysqltest_1; - -connection default; -disconnect conn1; - - -# -# Bug #53371: COM_FIELD_LIST can be abused to bypass table level grants. -# - -CREATE DATABASE db1; -CREATE DATABASE db2; -GRANT SELECT ON db1.* to 'testbug'@localhost; -USE db2; -CREATE TABLE t1 (a INT); -USE test; -connect (con1,localhost,testbug,,db1); ---error ER_NO_SUCH_TABLE -SELECT * FROM `../db2/tb2`; ---error ER_TABLEACCESS_DENIED_ERROR -SELECT * FROM `../db2`.tb2; ---error ER_WRONG_TABLE_NAME -SELECT * FROM `#mysql50#/../db2/tb2`; -connection default; -disconnect con1; -DROP USER 'testbug'@localhost; -DROP TABLE db2.t1; -DROP DATABASE db1; -DROP DATABASE db2; - ---echo # ---echo # Bug #36742 ---echo # -grant usage on Foo.* to myuser@Localhost identified by 'foo'; -grant select on Foo.* to myuser@localhost; -select host,user from mysql.user where User='myuser'; -revoke select on Foo.* from myuser@localhost; -delete from mysql.user where User='myuser'; -flush privileges; - ---echo ######################################################################### ---echo # ---echo # Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE. ---echo # ---echo ######################################################################### - ---echo ---echo # -- ---echo # -- Prepare the environment. ---echo # -- - -DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; -DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; -DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; -DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; -FLUSH PRIVILEGES; - ---disable_warnings -DROP DATABASE IF EXISTS mysqltest_db1; ---enable_warnings - -CREATE DATABASE mysqltest_db1; - -CREATE TABLE mysqltest_db1.t1(a INT); - ---echo ---echo # -- ---echo # -- Check that global privileges don't allow SHOW CREATE TABLE. ---echo # -- - -GRANT EVENT ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT LOCK TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT ALTER ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT CREATE ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; - -GRANT FILE ON *.* TO mysqltest_u1@localhost; -GRANT CREATE USER ON *.* TO mysqltest_u1@localhost; -GRANT PROCESS ON *.* TO mysqltest_u1@localhost; -GRANT RELOAD ON *.* TO mysqltest_u1@localhost; -GRANT REPLICATION CLIENT ON *.* TO mysqltest_u1@localhost; -GRANT REPLICATION SLAVE ON *.* TO mysqltest_u1@localhost; -GRANT SHOW DATABASES ON *.* TO mysqltest_u1@localhost; -GRANT SHUTDOWN ON *.* TO mysqltest_u1@localhost; -GRANT USAGE ON *.* TO mysqltest_u1@localhost; - ---echo -SHOW GRANTS FOR mysqltest_u1@localhost; - ---echo ---echo # connection: con1 (mysqltest_u1@mysqltest_db1) ---connect (con1,localhost,mysqltest_u1,,mysqltest_db1) ---connection con1 - ---echo ---error ER_TABLEACCESS_DENIED_ERROR -SHOW CREATE TABLE t1; - ---echo ---echo # connection: default ---connection default - ---disconnect con1 - ---echo -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; - ---echo ---echo # -- ---echo # -- Check that global SELECT allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global INSERT allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global UPDATE allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global DELETE allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global CREATE allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global DROP allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global ALTER allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global INDEX allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global REFERENCES allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global GRANT OPTION allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global CREATE VIEW allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that global SHOW VIEW allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level SELECT allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level INSERT allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level UPDATE allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level DELETE allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level CREATE allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level DROP allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level ALTER allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level INDEX allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level REFERENCES allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE. ---echo # -- - ---echo -GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - ---source include/bug38347.inc - ---echo ---echo # -- ---echo # -- Cleanup. ---echo # -- - ---echo -DROP DATABASE mysqltest_db1; - -DROP USER mysqltest_u1@localhost; - ---echo ---echo # End of Bug#38347. ---echo - - ---echo # ---echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES ---echo # DIFFERENTLY'. ---echo # ---disable_warnings -drop database if exists mysqltest_db1; ---enable_warnings -create database mysqltest_db1; -create user mysqltest_u1; ---echo # Both GRANT statements below should fail with the same error. ---error ER_SP_DOES_NOT_EXIST -grant execute on function mysqltest_db1.f1 to mysqltest_u1; ---error ER_SP_DOES_NOT_EXIST -grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; ---echo # Let us show that GRANT behaviour for routines is consistent ---echo # with GRANT behaviour for tables. Attempt to grant privilege ---echo # on non-existent table also results in an error. ---error ER_NO_SUCH_TABLE -grant select on mysqltest_db1.t1 to mysqltest_u1; -show grants for mysqltest_u1; -drop database mysqltest_db1; -drop user mysqltest_u1; - - ---echo # ---echo # Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - ---echo # REQUIRES FLUSH PRIVILEGES ---echo # - -CREATE USER foo@'127.0.0.1'; -GRANT ALL ON *.* TO foo@'127.0.0.1'; - ---echo # First attempt, should connect successfully -connect (conn1, '127.0.0.1', foo,,test); -SELECT user(), current_user(); - ---echo # Rename the user -RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0'; - ---echo # Second attempt, should connect successfully as its valid mask ---echo # This was failing without fix -connect (conn2, '127.0.0.1', foo,,test); -SELECT user(), current_user(); - ---echo # Rename the user back to original -RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1'; - ---echo # Third attempt, should connect successfully -connect (conn3, '127.0.0.1', foo,,test); -SELECT user(), current_user(); - ---echo # Clean-up -connection default; -disconnect conn1; -disconnect conn2; -disconnect conn3; -DROP USER foo@'127.0.0.1'; - ---echo # End of Bug#12766319 - -# -# Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS -# -create user foo@localhost; -create database foodb; -grant create routine on foodb.* to foo@localhost; -connect con1,localhost,foo; -create procedure fooproc() select 'i am fooproc'; -show grants; -disconnect con1; -connection default; -rename table mysql.procs_priv to mysql.procs_priv1; -flush privileges; -show grants for foo@localhost; -rename table mysql.procs_priv1 to mysql.procs_priv; -show grants for foo@localhost; -flush privileges; -show grants for foo@localhost; -drop user foo@localhost; -drop procedure fooproc; -drop database foodb; - - ---echo # ---echo # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS ---echo # DATABASE SECURITY ---echo # - ---disable_warnings -DROP DATABASE IF EXISTS secret; -DROP DATABASE IF EXISTS no_such_db; ---enable_warnings - -CREATE DATABASE secret; -GRANT USAGE ON *.* TO untrusted@localhost; - ---echo # Connection con1 -connect (con1, localhost, untrusted); -SHOW GRANTS; -SHOW DATABASES; - ---echo # Both statements below should fail with the same error. ---echo # They used to give different errors, thereby ---echo # hinting that the secret database exists. ---error ER_DBACCESS_DENIED_ERROR -CREATE PROCEDURE no_such_db.foo() BEGIN END; ---error ER_DBACCESS_DENIED_ERROR -CREATE PROCEDURE secret.peek_at_secret() BEGIN END; - ---echo # Connection default ---connection default -disconnect con1; -DROP USER untrusted@localhost; -DROP DATABASE secret; - -# Wait till we reached the initial number of concurrent sessions ---source include/wait_until_count_sessions.inc |