# Grant tests not performed with embedded server -- source include/not_embedded.inc SET NAMES binary; # # GRANT tests that require several connections # (usually it's GRANT, reconnect as another user, try something) # # prepare playground before tests --disable_warnings drop database if exists mysqltest; drop database if exists mysqltest_1; --enable_warnings 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; grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option; grant create user on *.* to mysqltest_1@localhost; create user mysqltest_2@localhost; connect (user_a,localhost,mysqltest_1,,); connection user_a; grant select on `my\_1`.* to mysqltest_2@localhost; --error 1132 grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; disconnect user_a; connection default; grant update on mysql.* to mysqltest_1@localhost; connect (user_b,localhost,mysqltest_1,,); connection user_b; grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; grant select on `my\_1`.* to mysqltest_3@localhost; disconnect user_b; connection default; grant insert on mysql.* to mysqltest_1@localhost; connect (user_c,localhost,mysqltest_1,,); connection user_c; grant select on `my\_1`.* to mysqltest_3@localhost; grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass'; disconnect user_c; connection default; 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; # # wild_compare fun # grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; grant create user on *.* to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,); connection user1; select current_user(); grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option; --error 1044 grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option; # # NO_AUTO_CREATE_USER mode # set @@sql_mode='NO_AUTO_CREATE_USER'; select @@sql_mode; # # GRANT without IDENTIFIED BY does not create new users # --error 1133 grant select on `my\_1`.* to mysqltest_4@localhost with grant option; grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass' with grant option; disconnect user1; connection default; show grants for mysqltest_1@localhost; show grants for mysqltest_2@localhost; --error 1141 show grants for mysqltest_3@localhost; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; flush privileges; # # wild_compare part two - acl_cache # create database mysqltest_1; grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option; connect (user2,localhost,mysqltest_1,,); connection user2; select current_user(); show databases; --error 1044 grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option; disconnect user2; connection default; show grants for mysqltest_1@localhost; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; drop database mysqltest_1; flush privileges; # # Bug #6173: One can circumvent missing UPDATE privilege if he has SELECT # and INSERT privilege for table with primary key # create database mysqltest; grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost; flush privileges; use mysqltest; create table t1 (id int primary key, data varchar(255)); connect (mrbad, localhost, mysqltest_1,,mysqltest); connection mrbad; show grants for current_user(); insert into t1 values (1, 'I can''t change it!'); --error 1142 update t1 set data='I can change it!' where id = 1; # This should not be allowed since it too require UPDATE privilege. --error 1142 insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; select * from t1; disconnect mrbad; connection default; drop table t1; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; flush privileges; # # create table t1 (a int, b int); grant select (a) on t1 to mysqltest_1@localhost with grant option; connect (mrugly, localhost, mysqltest_1,,mysqltest); connection mrugly; --error 1143 grant select (a,b) on t1 to mysqltest_2@localhost; --error 1142 grant select on t1 to mysqltest_3@localhost; disconnect mrugly; connection default; drop table t1; 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; drop database mysqltest; use test; # # Create and drop user # set sql_mode='maxdb'; --disable_warnings drop table if exists t1, t2; --enable_warnings create table t1(c1 int); create table t2(c1 int, c2 int); # # Three forms of CREATE USER create user 'mysqltest_1'; --error 1396 create user 'mysqltest_1'; create user 'mysqltest_2' identified by 'Mysqltest-2'; create user 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; grant select on *.* to 'mysqltest_2'; grant insert on test.* to 'mysqltest_2'; grant update on test.t1 to 'mysqltest_2'; grant update (c2) on test.t2 to 'mysqltest_2'; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; show grants for 'mysqltest_1'; show grants for 'mysqltest_2'; # # Drop drop user 'mysqltest_1'; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; --error 1141 show grants for 'mysqltest_1'; # # Rename rename user 'mysqltest_2' to 'mysqltest_1'; select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; show grants for 'mysqltest_1'; drop user 'mysqltest_1', 'mysqltest_3'; --error 1396 drop user 'mysqltest_1'; # # Cleanup drop table t1, t2; # # Add a stray record insert into mysql.db set user='mysqltest_1', db='%', host='%'; flush privileges; --error 1141 show grants for 'mysqltest_1'; --error 1269 revoke all privileges, grant option from 'mysqltest_1'; drop user 'mysqltest_1'; select host,db,user from mysql.db where user = 'mysqltest_1' order by host,db,user; # # Add a stray record insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1'; flush privileges; --error 1141 show grants for 'mysqltest_1'; drop user 'mysqltest_1'; select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' order by host,db,user,table_name; # # Add a stray record insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1'; flush privileges; --error 1141 show grants for 'mysqltest_1'; drop user 'mysqltest_1'; select host,db,user,table_name,column_name from mysql.columns_priv where user = 'mysqltest_1' order by host,db,user,table_name,column_name; # # Handle multi user lists create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; create user 'mysqltest_1', 'mysqltest_2' identified by 'Mysqltest-2', 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; rename user 'mysqltest_1' to 'mysqltest_1a', 'mysqltest_2' TO 'mysqltest_2a', 'mysqltest_3' TO 'mysqltest_3a'; --error 1396 drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a'; # # Let one of multiple users fail create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; --error 1396 create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a'; --error 1396 rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b'; drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; --error 1396 drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b'; # # Obsolete syntax has been dropped create user 'mysqltest_2' identified by 'Mysqltest-2'; --error 1064 drop user 'mysqltest_2' identified by 'Mysqltest-2'; # # Strange user names create user '%@b'@'b'; show grants for '%@b'@'b'; grant select on mysql.* to '%@b'@'b'; show grants for '%@b'@'b'; rename user '%@b'@'b' to '%@a'@'a'; --error 1141 show grants for '%@b'@'b'; show grants for '%@a'@'a'; drop user '%@a'@'a'; # # CREATE USER privilege is enough # create user mysqltest_2@localhost; grant create user on *.* to mysqltest_2@localhost; connect (user3,localhost,mysqltest_2,,); connection user3; --error 1142 select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; create user mysqltest_A@'%'; rename user mysqltest_A@'%' to mysqltest_B@'%'; drop user mysqltest_B@'%'; disconnect user3; connection default; drop user mysqltest_2@localhost; # # INSERT/UPDATE/DELETE is ok too create user mysqltest_3@localhost; grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; connect (user4,localhost,mysqltest_3,,); connection user4; show grants; --error 1142 select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; insert into mysql.user set host='%', user='mysqltest_B'; create user mysqltest_A@'%'; rename user mysqltest_B@'%' to mysqltest_C@'%'; drop user mysqltest_C@'%'; disconnect user4; connection default; drop user mysqltest_3@localhost; # # Bug #3309: Test IP addresses with netmask set @@sql_mode=''; create database mysqltest_1; create table mysqltest_1.t1 (i int); insert into mysqltest_1.t1 values (1),(2),(3); GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0'; connect (n1,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,$MASTER_MYSOCK); connection n1; show grants for current_user(); select * from t1; disconnect n1; connection default; REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0'; delete from mysql.user where user like 'mysqltest\_1'; flush privileges; drop table mysqltest_1.t1; # # Bug #12302: 'SET PASSWORD = ...' didn't work if connecting hostname != # hostname the current user is authenticated as. Note that a test for this # was also added to the test above. # grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1'; connect (b12302,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,); connection b12302; select current_user(); set password = password('changed'); disconnect b12302; connection default; select host, length(password) from mysql.user where user like 'mysqltest\_1'; revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1'; delete from mysql.user where user like 'mysqltest\_1'; flush privileges; grant all on mysqltest_1.* to mysqltest_1@'127.0.0.0/255.0.0.0'; connect (b12302_2,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,); connection b12302_2; select current_user(); set password = password('changed'); disconnect b12302_2; connection default; select host, length(password) from mysql.user where user like 'mysqltest\_1'; revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0'; delete from mysql.user where user like 'mysqltest\_1'; flush privileges; drop database mysqltest_1; # But anonymous users can't change their password connect (n5,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection n5; --error 1044 set password = password("changed"); disconnect n5; connection default; # Bug #12423 "Deadlock when doing FLUSH PRIVILEGES and GRANT in # multi-threaded environment". We should be able to execute FLUSH # PRIVILEGES and SET PASSWORD simultaneously with other account # management commands (such as GRANT and REVOKE) without causing # deadlocks. To achieve this we should ensure that all account # management commands take table and internal locks in the same order. connect (con2root,localhost,root,,); connect (con3root,localhost,root,,); # Check that we can execute FLUSH PRIVILEGES and GRANT simultaneously # This will check that locks are taken in proper order during both # user/db-level and table/column-level privileges reloading. connection default; lock table mysql.user write; connection con2root; send flush privileges; connection con3root; send grant all on *.* to 'mysqltest_1'@'localhost'; connection default; unlock tables; connection con2root; reap; connection con3root; reap; # Check for simultaneous SET PASSWORD and REVOKE. connection default; lock table mysql.user write; connection con2root; send set password for 'mysqltest_1'@'localhost' = password(''); connection con3root; send revoke all on *.* from 'mysqltest_1'@'localhost'; connection default; unlock tables; connection con2root; reap; connection con3root; reap; connection default; # Clean-up drop user 'mysqltest_1'@'localhost'; disconnect con2root; disconnect con3root; # End of 4.1 tests