diff options
Diffstat (limited to 'mysql-test/t/grant2.test')
| -rw-r--r-- | mysql-test/t/grant2.test | 283 |
1 files changed, 254 insertions, 29 deletions
diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 79ea7f70712..2c62d2f1bd3 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -21,17 +21,61 @@ 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; @@ -75,10 +119,10 @@ connect (mrbad, localhost, mysqltest_1,,mysqltest); connection mrbad; show grants for current_user(); insert into t1 values (1, 'I can''t change it!'); ---error 1044 +--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 1044 +--error 1142 insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; select * from t1; disconnect mrbad; @@ -88,7 +132,8 @@ 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); @@ -110,7 +155,184 @@ flush privileges; drop database mysqltest; use test; + +# +# Bug #15775: "drop user" command does not refresh acl_check_hosts +# + +# Create some test users +create user mysqltest_1@host1; +create user mysqltest_2@host2; +create user mysqltest_3@host3; +create user mysqltest_4@host4; +create user mysqltest_5@host5; +create user mysqltest_6@host6; +create user mysqltest_7@host7; +flush privileges; + +# Drop one user +drop user mysqltest_3@host3; + +# This connect failed before fix since the acl_check_hosts list was corrupted by the "drop user" +connect (con8,127.0.0.1,root,,test,$MASTER_MYPORT,); +disconnect con8; +connection default; + +# Clean up - Drop all of the remaining users at once +drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, + mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; + +# Check that it's still possible to connect +connect (con9,127.0.0.1,root,,test,$MASTER_MYPORT,); +disconnect con9; +connection default; + +# +# 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'; +drop user '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@'%'; +drop user mysqltest_A@'%'; +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); @@ -206,37 +428,40 @@ drop user 'mysqltest_1'@'localhost'; disconnect con2root; disconnect con3root; +# End of 4.1 tests + # -# Bug #15775: "drop user" command does not refresh acl_check_hosts +# Bug#17279 user with no global privs and with create +# priv in db can create databases # -# Create some test users -insert into mysql.user (user, host) values - ('mysqltest_1', 'host1'), - ('mysqltest_2', 'host2'), - ('mysqltest_3', 'host3'), - ('mysqltest_4', 'host4'), - ('mysqltest_5', 'host5'), - ('mysqltest_6', 'host6'), - ('mysqltest_7', 'host7'); -flush privileges; - -# Drop one user -drop user mysqltest_3@host3; +create database TESTDB; +create table t2(a int); +create temporary table t1 as select * from mysql.user; +delete from mysql.user where host='localhost'; +INSERT INTO mysql.user VALUES +('%','mysqltest_1',password('password'),'N','N','N','N','N','N', +'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N', +'','','','',0,0,0,0); +INSERT INTO mysql.db VALUES +('%','TESTDB','mysqltest_1','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','N','Y','Y','Y',' +Y','N'); +FLUSH PRIVILEGES; -# This connect failed before fix since the acl_check_hosts list was corrupted by the "drop user" -connect (con8,127.0.0.1,root,,test,$MASTER_MYPORT,); -disconnect con8; -connection default; +connect (con1,localhost,mysqltest_1,password,TESTDB); -# Clean up - Drop all of the remaining users at once -drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, - mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; +# The user mysqltest_1 should only be allowed access to +# database TESTDB, not TEStdb +# On system with "lowercase names" we get error "1007: Can't create db..." +--error 1044, 1007 +create database TEStdb; -# Check that it's still possible to connect -connect (con9,127.0.0.1,root,,test,$MASTER_MYPORT,); -disconnect con9; +# Clean-up connection default; +delete from mysql.user; +delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB'; +insert into mysql.user select * from t1; +drop table t1, t2; +drop database TESTDB; +flush privileges; - -# End of 4.1 tests |