# Test of GRANT commands # Grant tests not performed with embedded server -- source include/not_embedded.inc # Cleanup --disable_warnings drop table if exists t1; --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; 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 "MySQL 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; 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; 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 1141 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 1221 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; # # Bug3086 # 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 1269 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; --error 1268 drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; 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@localhost; show grants for mysqltest_1@localhost; drop user mysqltest_1@localhost; # # Bug #3403 Wrong encodin 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; 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; 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; show grants for mysqltest_3@localhost; --error 1143 update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; --error 1142 update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1; --error 1143 update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1; --error 1143 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 1143 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 1142 update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; --error 1142 update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200; --error 1142 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; # # 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 1142 alter table t1 rename t2; connection root; revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; delete from mysql.user where user=_binary'mysqltest_1'; 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; # # 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 '' # insert into mysql.user (host, user) values ('', 'mysqltest_7'); flush privileges; set password for mysqltest_7@ = password('systpass'); show grants for mysqltest_7@; drop user mysqltest_7@; --error 1141 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 1142 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