1
# Can't test grants with embedded server
2
-- source include/not_embedded.inc
6
################ GRANT/REVOKE/DROP affecting a parallel session ################
8
select '------ grant/revoke/drop affects a parallel session test ------'
12
#---------------------------------------------------------------------#
14
# 1. A new GRANT will be visible within another sessions. #
16
# Let's assume there is a parallel session with an already prepared #
17
# statement for a table. #
18
# A DROP TABLE will affect the EXECUTE properties. #
19
# A REVOKE will affect the EXECUTE properties. #
20
#---------------------------------------------------------------------#
23
# this is different across different systems:
24
# select current_user(), user() ;
26
#### create a new user account ####
27
## There should be no grants for that non existing user
29
show grants for second_user@localhost ;
30
## create a new user account by using GRANT statements on t9
31
create database mysqltest;
32
# create the tables (t1 and t9) used in many tests
35
--source include/ps_create.inc
36
--source include/ps_renew.inc
39
grant usage on mysqltest.* to second_user@localhost
40
identified by 'looser' ;
41
grant select on mysqltest.t9 to second_user@localhost
42
identified by 'looser' ;
43
show grants for second_user@localhost ;
46
#### establish a second session to the new user account
47
connect (con3,localhost,second_user,looser,mysqltest);
48
## switch to the second session
51
select current_user();
52
## check the access rights
53
show grants for current_user();
54
prepare s_t9 from 'select c1 as my_col
55
from t9 where c1= 1' ;
57
# check that we cannot do a SELECT on the table t1;
59
select a as my_col from t1;
62
#### give access rights to t1 and drop table t9
63
## switch back to the first session
65
grant select on mysqltest.t1 to second_user@localhost
66
identified by 'looser' ;
67
show grants for second_user@localhost ;
68
drop table mysqltest.t9 ;
69
show grants for second_user@localhost ;
72
#### check the access as new user
73
## switch to the second session
75
######## Question 1: The table t1 should be now accessible. ########
76
show grants for second_user@localhost ;
77
prepare s_t1 from 'select a as my_col from t1' ;
79
######## Question 2: The table t9 does not exist. ########
82
deallocate prepare s_t9;
85
#### revoke the access rights to t1
86
## switch back to the first session
88
revoke all privileges on mysqltest.t1 from second_user@localhost
89
identified by 'looser' ;
90
show grants for second_user@localhost ;
92
#### check the access as new user
93
## switch to the second session
95
show grants for second_user@localhost ;
96
######## Question 2: The table t1 should be now not accessible. ########
101
## switch back to the first session
103
## disconnect the second session
105
## remove all rights of second_user@localhost
106
revoke all privileges, grant option from second_user@localhost ;
107
show grants for second_user@localhost ;
108
drop user second_user@localhost ;
111
show grants for second_user@localhost ;
113
drop database mysqltest;
118
# grant/revoke + drop user
120
prepare stmt3 from ' grant all on test.t1 to drop_user@localhost
121
identified by ''looser'' ';
122
grant all on test.t1 to drop_user@localhost
123
identified by 'looser' ;
124
prepare stmt3 from ' revoke all privileges on test.t1 from
125
drop_user@localhost ';
126
revoke all privileges on test.t1 from drop_user@localhost ;
127
prepare stmt3 from ' drop user drop_user@localhost ';
128
drop user drop_user@localhost;