# 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 GLOBAL sql_mode=""; set LOCAL sql_mode=""; SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; select priv into @root_priv from mysql.global_priv where user='root' and host='localhost'; 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 max_statement_time 60; 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 max_statement_time 0; 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.global_priv (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,,"*NO-ONE*",); 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; --sorted_result 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; --sorted_result 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; --sorted_result 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; --sorted_result 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); # 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@''; grant select on test.* 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; 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,"*NO-ONE*"); # 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@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; --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@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; # 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@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; --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@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; # 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@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; --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@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; # # 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,,) # - 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 --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) --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) --error ER_TABLEACCESS_DENIED_ERROR CREATE TABLE t1(c INT); SHOW TABLES; # Cleanup. --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) SHOW TABLES FROM mysqltest1; PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1'; EXECUTE stmt1; --connect (bug27337_con2,localhost,mysqltest_2,,mysqltest2) SHOW COLUMNS FROM mysqltest2.t2; PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2'; EXECUTE stmt2; --connection default REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost; REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost; --connection bug27337_con1 --error ER_DBACCESS_DENIED_ERROR SHOW TABLES FROM mysqltest1; --error ER_DBACCESS_DENIED_ERROR EXECUTE stmt1; --connection bug27337_con2 --error ER_TABLEACCESS_DENIED_ERROR SHOW COLUMNS FROM mysqltest2.t2; --error ER_TABLEACCESS_DENIED_ERROR EXECUTE stmt2; # Cleanup. --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 |; --enable_prepare_warnings create function test_function() returns varchar(30) begin declare tmp varchar(30); select col1 from test limit 1 into tmp; return '1'; end| --disable_prepare_warnings 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; --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; --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; --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; --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'; --connect (bug36544_con1,localhost,mysqluser1,,"*NO-ONE*") call mysqltest1.p1(); select mysqltest1.f1(); --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 # --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 --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'; --connect (bug36544_con2,localhost,mysqluser1,,"*NO-ONE*") --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(); --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'; --connection bug36544_con2 call mysqltest1.p1(); select mysqltest1.f1(); select * from mysqltest1.t11; select * from mysqltest1.t22; --connection default rename user mysqluser1@localhost to mysqluser10@localhost; --echo # --echo # Test that there are no privileges left for mysqluser1. --echo # --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 --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'; --connect (bug36544_con3,localhost,mysqluser1,,"*NO-ONE*") --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 --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'; --connect (bug36544_con4,localhost,mysqluser10,,"*NO-ONE*") call mysqltest1.p1(); select mysqltest1.f1(); select * from mysqltest1.t11; select * from mysqltest1.t22; --disconnect bug36544_con4 --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,,"*NO-ONE*"); --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,,"*NO-ONE*"); --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; # Global privileges should be granted to all schemas, not individual DB --error ER_WRONG_USAGE GRANT RELOAD ON mysqltest_db1.* TO mysqltest_u1@localhost; # Select privilege is needed beside RELOAD privilege --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT --error ER_DBACCESS_DENIED_ERROR --connect (con1, localhost, mysqltest_u1, ,db1) 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; GRANT SLAVE MONITOR ON *.* TO mysqltest_u1@localhost; --echo SHOW GRANTS FOR mysqltest_u1@localhost; --connect (con1,localhost,mysqltest_u1,,mysqltest_db1) --connection con1 --echo --error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE t1; --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,,foodb; 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 foodb.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; connect (con1, localhost, untrusted,,"*NO-ONE*"); 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; --connection default disconnect con1; DROP USER untrusted@localhost; DROP DATABASE secret; set GLOBAL sql_mode=default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc --echo # --echo # MDEV-22755 CREATE USER leads to indirect SIGABRT in __stack_chk_fail () from fill_schema_user_privileges + *** stack smashing detected *** (on optimized builds) --echo # SET NAMES utf8; SET SQL_MODE=''; CREATE USER 觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻@localhost; SELECT * FROM INFORMATION_SCHEMA.user_privileges WHERE GRANTEE LIKE "'abcdefghijklmnopqrstuvwxyz'%"; SELECT GRANTEE FROM INFORMATION_SCHEMA.user_privileges WHERE GRANTEE LIKE '%觻%'; DROP USER 觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻@localhost; SET SQL_MODE=DEFAULT; --echo # --echo # Start of 10.2 tests --echo # --echo # --echo # MDEV-10134 Add full support for DEFAULT --echo # CREATE TABLE t1 (a VARCHAR(30) DEFAULT USER()); SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (); GRANT ALL PRIVILEGES ON test.* TO dummy@localhost IDENTIFIED BY 'pwd'; connect (conn1,localhost,dummy,pwd,test); connection conn1; INSERT INTO t1 VALUES (); connection default; disconnect conn1; INSERT INTO t1 VALUES (); SELECT * FROM t1; DROP TABLE t1; DROP USER dummy@localhost; --echo # --echo # MDEV-23082: ER_TABLEACCESS_DENIED_ERROR error message is truncated, and --echo # inaccurately --echo # CREATE USER foo; CREATE DATABASE db; CREATE TABLE db.t (a INT); --connect (con1,localhost,foo,,"*NO-ONE*") --error ER_TABLEACCESS_DENIED_ERROR GRANT ALL ON db.t TO foo; --error ER_TABLEACCESS_DENIED_ERROR GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON db.t TO foo; --connection default --disconnect con1 DROP USER foo; DROP TABLE db.t; DROP DATABASE db; --echo # --echo # Bug#33578113: DROP privilege on performance_schema.* can't be revoked --echo # connection default; CREATE USER bug33578113; GRANT DROP ON performance_schema.* TO bug33578113; REVOKE DROP ON performance_schema.* FROM bug33578113; DROP USER bug33578113; --source include/wait_until_count_sessions.inc --echo # --echo # End of 10.2 tests --echo # --echo # --echo # Start of 10.3 tests --echo # --echo # --echo # MDEV-19948 'show grants' return privileges individually --echo # # Let's cheat server that we are using `10.2` user table # which doesn't have `Delete_history_priv` column source include/switch_to_mysql_user.inc; CREATE USER ten2; GRANT ALL ON *.* TO ten2; # Without any patching, this should show a lot of privileges, # but without delete history. With patch it is showing `all privileges` SHOW GRANTS FOR ten2; FLUSH PRIVILEGES; # Now should show `all privileges` with/without patch SHOW GRANTS FOR ten2; DROP USER ten2; source include/switch_to_mysql_global_priv.inc; --echo # --echo # End of 10.3 tests --echo # --echo # --echo # MDEV-17932 : Assertion upon double RENAME USER --echo # CREATE USER foo@localhost; CREATE USER bar2@localhost; RENAME USER foo@localhost TO bar1@localhost, bar1@localhost TO bar3@localhost; DROP USER bar2@localhost; DROP USER bar3@localhost; --echo # --echo # MDEV-17946 : Unsorted acl_dbs after RENAME USER --echo # CREATE USER a; CREATE USER foo; GRANT SELECT ON test.* TO foo; RENAME USER 'a' TO 'name'; GRANT UPDATE ON test.* TO foo; RENAME USER 'name' to 'a'; DROP USER foo; DROP USER a; --echo # --echo # End of 10.4 tests --echo # update mysql.global_priv set priv=@root_priv where user='root' and host='localhost';