summaryrefslogtreecommitdiff
path: root/mysql-test/suite/roles/set_role-multiple-role.result
blob: fca53b4d6455d1dcfd2e60cdc365ff965abdc62c (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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
create user 'test_user'@'localhost';
create role r_sel;
create role r_ins;
create role r_upd;
create role r_del;
create role r_crt;
create role r_drp;
create role r_rld;
grant select on *.* to r_sel;
grant insert on *.* to r_ins;
grant update on *.* to r_upd;
grant delete on *.* to r_del;
grant create on *.* to r_crt;
grant drop   on *.* to r_drp;
grant reload on *.* to r_rld;
grant r_sel to test_user@localhost;
grant r_ins to test_user@localhost;
grant r_upd to test_user@localhost;
grant r_del to test_user@localhost;
grant r_crt to test_user@localhost;
grant r_drp to test_user@localhost;
grant r_rld to test_user@localhost;
flush privileges;
select * from mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping'
show grants;
Grants for test_user@localhost
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT r_crt TO 'test_user'@'localhost'
GRANT r_del TO 'test_user'@'localhost'
GRANT r_drp TO 'test_user'@'localhost'
GRANT r_ins TO 'test_user'@'localhost'
GRANT r_rld TO 'test_user'@'localhost'
GRANT r_sel TO 'test_user'@'localhost'
GRANT r_upd TO 'test_user'@'localhost'
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	NULL
set role r_sel;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_sel
show grants;
Grants for test_user@localhost
GRANT SELECT ON *.* TO 'r_sel'
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT r_crt TO 'test_user'@'localhost'
GRANT r_del TO 'test_user'@'localhost'
GRANT r_drp TO 'test_user'@'localhost'
GRANT r_ins TO 'test_user'@'localhost'
GRANT r_rld TO 'test_user'@'localhost'
GRANT r_sel TO 'test_user'@'localhost'
GRANT r_upd TO 'test_user'@'localhost'
select * from mysql.roles_mapping;
Host	User	Role	Admin_option
localhost	root	r_crt	Y
localhost	root	r_del	Y
localhost	root	r_drp	Y
localhost	root	r_ins	Y
localhost	root	r_rld	Y
localhost	root	r_sel	Y
localhost	root	r_upd	Y
localhost	test_user	r_crt	N
localhost	test_user	r_del	N
localhost	test_user	r_drp	N
localhost	test_user	r_ins	N
localhost	test_user	r_rld	N
localhost	test_user	r_sel	N
localhost	test_user	r_upd	N
set role r_ins;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_ins
show grants;
Grants for test_user@localhost
GRANT INSERT ON *.* TO 'r_ins'
GRANT USAGE ON *.* TO 'test_user'@'localhost'
GRANT r_crt TO 'test_user'@'localhost'
GRANT r_del TO 'test_user'@'localhost'
GRANT r_drp TO 'test_user'@'localhost'
GRANT r_ins TO 'test_user'@'localhost'
GRANT r_rld TO 'test_user'@'localhost'
GRANT r_sel TO 'test_user'@'localhost'
GRANT r_upd TO 'test_user'@'localhost'
select * from mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping'
insert into mysql.roles_mapping values ('', 'r_sel', 'r_rld', 'N');
flush privileges;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
set role r_rld;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_rld
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_sel
flush privileges;
set role none;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	NULL
flush privileges;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
set role r_ins;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_ins
insert into mysql.roles_mapping values ('', 'r_sel', 'r_upd', 'N');
insert into mysql.roles_mapping values ('', 'r_sel', 'r_del', 'N');
insert into mysql.roles_mapping values ('', 'r_sel', 'r_crt', 'N');
insert into mysql.roles_mapping values ('', 'r_sel', 'r_drp', 'N');
insert into mysql.roles_mapping values ('', 'r_del', 'r_ins', 'N');
set role r_rld;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_rld
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_sel
update mysql.roles_mapping set Role='r_ins' where Role='r_ins_wrong';
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_sel
create table mysql.random_test_table (id INT);
insert into mysql.random_test_table values (1);
select * from mysql.random_test_table;
id
1
delete from mysql.roles_mapping where Role='r_ins';
flush privileges;
set role r_sel;
select current_user(), current_role();
current_user()	current_role()
test_user@localhost	r_sel
insert into mysql.random_test_table values (1);
ERROR 42000: INSERT command denied to user 'test_user'@'localhost' for table 'random_test_table'
drop table mysql.random_test_table;
delete from mysql.user where user like 'r\_%';
delete from mysql.roles_mapping where Role like 'r\_%';
flush privileges;
drop user 'test_user'@'localhost';