1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
# 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
use test;
grant usage on mysqltest.* to second_user@localhost
identified by 'looser' ;
grant select on mysqltest.t9 to second_user@localhost
identified by 'looser' ;
--sorted_result
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' ;
--sorted_result
show grants for second_user@localhost ;
drop table mysqltest.t9 ;
--sorted_result
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. ########
--sorted_result
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 ;
deallocate prepare 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;
--sorted_result
show grants for second_user@localhost ;
#### check the access as new user
## switch to the second session
connection con3;
--sorted_result
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 ;
--sorted_result
show grants for second_user@localhost ;
drop user second_user@localhost ;
commit ;
--error 1141
show grants for second_user@localhost ;
drop database mysqltest;
# End of 4.1 tests
#
# grant/revoke + drop user
#
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' ;
prepare stmt3 from ' revoke all privileges on test.t1 from
drop_user@localhost ';
revoke all privileges on test.t1 from drop_user@localhost ;
prepare stmt3 from ' drop user drop_user@localhost ';
drop user drop_user@localhost;
|
