summaryrefslogtreecommitdiff
path: root/mysql-test/main/ps_grant.test
blob: 3d59cc36de077a2312f3bec830eda22ea9429198 (plain)
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
# 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' ;
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 ;
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;
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;

# 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;