# Can't test grants with embedded server -- source include/not_embedded.inc let $type= 'MYISAM' ; ################ GRANT/REVOKE/DROP affecting a parallel session ################ --disable_query_log select '------ grant/revoke/drop affects a parallel session test ------' as test_sequence ; --enable_query_log #---------------------------------------------------------------------# # Here we test that: # 1. A new GRANT will be visible within another sessions. # # # # Let's assume there is a parallel session with an already prepared # # statement for a table. # # A DROP TABLE will affect the EXECUTE properties. # # A REVOKE will affect the EXECUTE properties. # #---------------------------------------------------------------------# # Who am I ? # this is different across different systems: # select current_user(), user() ; #### create a new user account #### ## There should be no grants for that non existing user --error 1141 show grants for second_user@localhost ; ## create a new user account by using GRANT statements on t9 create database mysqltest; # create the tables (t1 and t9) used in many tests use mysqltest; --disable_query_log --source include/ps_create.inc --source include/ps_renew.inc --enable_query_log eval use $DB; grant usage on mysqltest.* to second_user@localhost identified by 'looser' ; grant select on mysqltest.t9 to second_user@localhost identified by 'looser' ; show grants for second_user@localhost ; #### establish a second session to the new user account connect (con3,localhost,second_user,looser,mysqltest); ## switch to the second session connection con3; # Who am I ? select current_user(); ## check the access rights show grants for current_user(); prepare s_t9 from 'select c1 as my_col from t9 where c1= 1' ; execute s_t9 ; # check that we cannot do a SELECT on the table t1; --error 1142 select a as my_col from t1; #### give access rights to t1 and drop table t9 ## switch back to the first session connection default; grant select on mysqltest.t1 to second_user@localhost identified by 'looser' ; show grants for second_user@localhost ; drop table mysqltest.t9 ; show grants for second_user@localhost ; #### check the access as new user ## switch to the second session connection con3; ######## Question 1: The table t1 should be now accessible. ######## show grants for second_user@localhost ; prepare s_t1 from 'select a as my_col from t1' ; execute s_t1 ; ######## Question 2: The table t9 does not exist. ######## --error 1146 execute s_t9 ; #### revoke the access rights to t1 ## switch back to the first session connection default; revoke all privileges on mysqltest.t1 from second_user@localhost identified by 'looser' ; show grants for second_user@localhost ; #### check the access as new user ## switch to the second session connection con3; show grants for second_user@localhost ; ######## Question 2: The table t1 should be now not accessible. ######## --error 1142 execute s_t1 ; ## cleanup ## switch back to the first session connection default; ## disconnect the second session disconnect con3 ; ## remove all rights of second_user@localhost revoke all privileges, grant option from second_user@localhost ; show grants for second_user@localhost ; drop user second_user@localhost ; commit ; --error 1141 show grants for second_user@localhost ; drop database mysqltest; # Tested here simply so it is not tested with embedded server prepare stmt4 from ' show full processlist '; --replace_column 1 number 6 time 3 localhost execute stmt4; ## grant/revoke + drop user --error 1295 prepare stmt3 from ' grant all on test.t1 to drop_user@localhost identified by ''looser'' '; grant all on test.t1 to drop_user@localhost identified by 'looser' ; --error 1295 prepare stmt3 from ' revoke all privileges on test.t1 from drop_user@localhost '; revoke all privileges on test.t1 from drop_user@localhost ; --error 1295 prepare stmt3 from ' drop user drop_user@localhost '; drop user drop_user@localhost;