1 2# Can't run with embedded server because we use GRANT 3-- source include/not_embedded.inc 4 5# Save the initial number of concurrent sessions 6--source include/count_sessions.inc 7 8# Test of GRANT commands 9 10SET NAMES binary; 11 12# 13# Remove NO_AUTO_CREATE_USER sql mode (default from 5.7) 14# 15set @orig_sql_mode_session= @@SESSION.sql_mode; 16set @orig_sql_mode_global= @@GLOBAL.sql_mode; 17set GLOBAL sql_mode= (select replace(@@GLOBAL.sql_mode,'NO_AUTO_CREATE_USER','')); 18set SESSION sql_mode= (select replace(@@SESSION.sql_mode,'NO_AUTO_CREATE_USER','')); 19 20connect (master,localhost,root,,); 21connection master; 22 23# Cleanup 24--disable_warnings 25drop table if exists t1; 26--enable_warnings 27 28delete from mysql.user where user like 'mysqltest\_%'; 29delete from mysql.db where user like 'mysqltest\_%'; 30delete from mysql.tables_priv where user like 'mysqltest\_%'; 31delete from mysql.columns_priv where user like 'mysqltest\_%'; 32flush privileges; 33 34create user mysqltest_1@localhost; 35grant create user on *.* to mysqltest_1@localhost; 36grant select on `my\_1`.* to mysqltest_1@localhost with grant option; 37connect (user_a,localhost,mysqltest_1,,); 38connection user_a; 39--error ER_CANT_CREATE_USER_WITH_GRANT 40grant select on `my\_1`.* to mysqltest_2@localhost; 41create user mysqltest_2@localhost; 42disconnect user_a; 43disconnect master; 44connection default; 45 46delete from mysql.user where user like 'mysqltest\_%'; 47delete from mysql.db where user like 'mysqltest\_%'; 48delete from mysql.tables_priv where user like 'mysqltest\_%'; 49delete from mysql.columns_priv where user like 'mysqltest\_%'; 50flush privileges; 51 52# 53# Bug#19828 Case sensitivity in Grant/Revoke 54# 55 56grant select on test.* to CUser@localhost; 57grant select on test.* to CUser@LOCALHOST; 58flush privileges; 59 60SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 61SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2; 62 63REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST'; 64flush privileges; 65 66SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 67SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2; 68 69REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost'; 70flush privileges; 71 72SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 73SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser' order by 1,2; 74 75DROP USER CUser@localhost; 76--error ER_CANNOT_USER 77DROP USER CUser@LOCALHOST; 78 79#### table grants 80create table t1 (a int); 81grant select on test.t1 to CUser@localhost; 82grant select on test.t1 to CUser@LOCALHOST; 83flush privileges; 84 85SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 86SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2; 87 88REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST'; 89flush privileges; 90 91SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 92SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2; 93 94REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost'; 95flush privileges; 96 97SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 98SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2; 99 100DROP USER CUser@localhost; 101--error ER_CANNOT_USER 102DROP USER CUser@LOCALHOST; 103 104### column grants 105 106grant select(a) on test.t1 to CUser@localhost; 107grant select(a) on test.t1 to CUser@LOCALHOST; 108flush privileges; 109 110SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 111SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2; 112 113REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'LOCALHOST'; 114flush privileges; 115 116SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 117SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2; 118 119REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'CUser'@'localhost'; 120flush privileges; 121 122SELECT user, host FROM mysql.user where user = 'CUser' order by 1,2; 123SELECT user, host, db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv where user = 'CUser' order by 1,2; 124 125DROP USER CUser@localhost; 126--error ER_CANNOT_USER 127DROP USER CUser@LOCALHOST; 128 129drop table t1; 130 131# revoke on a specific DB only 132 133grant select on test.* to CUser2@localhost; 134grant select on test.* to CUser2@LOCALHOST; 135flush privileges; 136 137SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2; 138SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2; 139 140REVOKE SELECT ON test.* FROM 'CUser2'@'LOCALHOST'; 141flush privileges; 142 143SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2; 144SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2; 145 146--error ER_NONEXISTING_GRANT 147REVOKE SELECT ON test.* FROM 'CUser2'@'localhost'; 148flush privileges; 149 150SELECT user, host FROM mysql.user where user = 'CUser2' order by 1,2; 151SELECT user, host, db, select_priv FROM mysql.db where user = 'CUser2' order by 1,2; 152 153DROP USER CUser2@localhost; 154--error ER_CANNOT_USER 155DROP USER CUser2@LOCALHOST; 156 157 158# 159# Bug#31194 Privilege ordering does not order properly for wildcard values 160# 161 162CREATE DATABASE mysqltest_1; 163CREATE TABLE mysqltest_1.t1 (a INT); 164CREATE USER 'mysqltest1'@'%'; 165GRANT SELECT, UPDATE ON `mysqltest_1`.* TO 'mysqltest1'@'%'; 166REVOKE SELECT ON `mysqltest_1`.* FROM 'mysqltest1'@'%'; 167GRANT SELECT, UPDATE ON `mysqltest\_1`.* TO 'mysqltest1'@'%'; 168FLUSH PRIVILEGES; 169 170connect (conn1,localhost,mysqltest1,,); 171connection conn1; 172SHOW GRANTS; 173SELECT * FROM mysqltest_1.t1; 174disconnect conn1; 175 176connection default; 177DROP USER 'mysqltest1'@'%'; 178DROP DATABASE mysqltest_1; 179 180--echo # 181--echo # Bug#41597 - After rename of user, there are additional grants 182--echo # when grants are reapplied. 183--echo # 184 185CREATE DATABASE temp; 186CREATE TABLE temp.t1(a INT, b VARCHAR(10)); 187INSERT INTO temp.t1 VALUES(1, 'name1'); 188INSERT INTO temp.t1 VALUES(2, 'name2'); 189INSERT INTO temp.t1 VALUES(3, 'name3'); 190 191 192CREATE USER 'user1'@'%'; 193RENAME USER 'user1'@'%' TO 'user2'@'%'; 194--echo # Show privileges after rename and BEFORE grant 195SHOW GRANTS FOR 'user2'@'%'; 196GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%'; 197--echo # Show privileges after rename and grant 198SHOW GRANTS FOR 'user2'@'%'; 199 200--echo # Connect as the renamed user 201connect (conn1, localhost, user2,,); 202connection conn1; 203SHOW GRANTS; 204SELECT a FROM temp.t1; 205--echo # Check for additional privileges by accessing a 206--echo # non privileged column. We shouldn't be able to 207--echo # access this column. 208--error ER_COLUMNACCESS_DENIED_ERROR 209SELECT b FROM temp.t1; 210disconnect conn1; 211 212connection default; 213DROP USER 'user2'@'%'; 214DROP DATABASE temp; 215 216set GLOBAL sql_mode= @orig_sql_mode_global; 217set SESSION sql_mode= @orig_sql_mode_session; 218 219--echo End of 5.0 tests 220 221# Wait till we reached the initial number of concurrent sessions 222--source include/wait_until_count_sessions.inc 223