diff options
Diffstat (limited to 'mysql-test/main/grant2.test')
| -rw-r--r-- | mysql-test/main/grant2.test | 81 |
1 files changed, 44 insertions, 37 deletions
diff --git a/mysql-test/main/grant2.test b/mysql-test/main/grant2.test index 1f7450df6c1..f98af8d8630 100644 --- a/mysql-test/main/grant2.test +++ b/mysql-test/main/grant2.test @@ -4,7 +4,7 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc - +select priv into @root_priv from mysql.global_priv where user='root' and host='localhost'; set GLOBAL sql_mode=""; set LOCAL sql_mode=""; SET NAMES binary; @@ -231,28 +231,40 @@ 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; +--sorted_result +select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%'; +--sorted_result +select host,db,user from mysql.db where user like 'mysqltest_%'; +--sorted_result +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%'; +--sorted_result +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%'; 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; +--sorted_result +select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%'; +--sorted_result +select host,db,user from mysql.db where user like 'mysqltest_%'; +--sorted_result +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%'; +--sorted_result +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%'; --error ER_NONEXISTING_GRANT 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; +--sorted_result +select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ; +--sorted_result +select host,db,user from mysql.db where user like 'mysqltest_%' ; +--sorted_result +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' ; +--sorted_result +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' ; show grants for 'mysqltest_1'; drop user 'mysqltest_1', 'mysqltest_3'; --error ER_CANNOT_USER @@ -269,7 +281,8 @@ show grants for 'mysqltest_1'; --error ER_REVOKE_GRANTS 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; +--sorted_result +select host,db,user from mysql.db where user = 'mysqltest_1' ; # # Add a stray record insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1'; @@ -277,7 +290,8 @@ flush privileges; --error ER_NONEXISTING_GRANT 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; +--sorted_result +select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' ; # # Add a stray record insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1'; @@ -285,7 +299,8 @@ flush privileges; --error ER_NONEXISTING_GRANT 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; +--sorted_result +select host,db,user,table_name,column_name from mysql.columns_priv where user = 'mysqltest_1' ; # # Handle multi user lists create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; @@ -330,7 +345,7 @@ grant create user on *.* to mysqltest_2@localhost; connect (user3,localhost,mysqltest_2,,); connection user3; --error ER_TABLEACCESS_DENIED_ERROR -select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ; create user mysqltest_A@'%'; rename user mysqltest_A@'%' to mysqltest_B@'%'; drop user mysqltest_B@'%'; @@ -345,8 +360,8 @@ connect (user4,localhost,mysqltest_3,,); connection user4; show grants; --error ER_TABLEACCESS_DENIED_ERROR -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'; +select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ; +insert into mysql.global_priv set host='%', user='mysqltest_B'; create user mysqltest_A@'%'; rename user mysqltest_B@'%' to mysqltest_C@'%'; drop user mysqltest_C@'%'; @@ -466,10 +481,10 @@ disconnect con3root; 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 (host, user, password) VALUES -('%','mysqltest_1',password('password')); +create temporary table t1 as select * from mysql.global_priv; +delete from mysql.global_priv where host='localhost'; +INSERT INTO mysql.global_priv (host, user, priv) VALUES +('%','mysqltest_1',json_object('authentication_string', password('password'))); INSERT INTO mysql.db (host, db, user, select_priv) VALUES ('%','TESTDB','mysqltest_1','Y'); FLUSH PRIVILEGES; @@ -485,9 +500,9 @@ create database TEStdb; # Clean-up connection default; disconnect con1; -delete from mysql.user; +delete from mysql.global_priv; delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB'; -insert into mysql.user select * from t1; +insert into mysql.global_priv select * from t1; drop table t1, t2; drop database TESTDB; flush privileges; @@ -642,27 +657,19 @@ DROP DATABASE db1; USE mysql; SELECT LEFT(CURRENT_USER(),INSTR(CURRENT_USER(),'@')-1) INTO @u; SELECT MID(CURRENT_USER(),INSTR(CURRENT_USER(),'@')+1) INTO @h; -SELECT password FROM user WHERE user=@u AND host=@h INTO @pwd; # show current privs. -SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; - -# toggle INSERT -UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; -SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h; # show that GRANT ... TO CURRENT_USER() no longer crashes GRANT INSERT ON *.* TO CURRENT_USER(); -SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; -UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; +SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h; # show that GRANT ... TO CURRENT_USER() IDENTIFIED BY ... works now GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose'; -SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; - -UPDATE user SET password=@pwd WHERE user=@u AND host=@h; -SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h; +UPDATE global_priv SET priv=@root_priv where user='root' and host='localhost'; FLUSH PRIVILEGES; USE test; |