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
|
#create a user with no privileges
create user 'test_user'@'localhost';
create user 'r_sel'@'';
create user 'r_ins'@'';
create user 'r_upd'@'';
create user 'r_del'@'';
create user 'r_crt'@'';
create user 'r_drp'@'';
create user 'r_rld'@'';
update mysql.user set Select_priv='Y' where user like 'r_sel';
update mysql.user set Insert_priv='Y' where user like 'r_ins';
update mysql.user set Update_priv='Y' where user like 'r_upd';
update mysql.user set Delete_priv='Y' where user like 'r_del';
update mysql.user set Create_priv='Y' where user like 'r_crt';
update mysql.user set Drop_priv ='Y' where user like 'r_drp';
update mysql.user set Reload_priv='Y' where user like 'r_rld';
update mysql.user set is_role='Y' where user like 'r\_%';
select * from mysql.user where user='r_sel';
select * from mysql.user where user='r_ins';
select * from mysql.user where user='r_upd';
select * from mysql.user where user='r_del';
select * from mysql.user where user='r_crt';
select * from mysql.user where user='r_drp';
select * from mysql.user where user='r_rld';
#####################################
#set up roles mapping
#####################################
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
'test_user',
'r_sel');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
'test_user',
'r_ins');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
'test_user',
'r_upd');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
'test_user',
'r_del');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
'test_user',
'r_crt');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
'test_user',
'r_drp');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
'test_user',
'r_rld');
flush privileges;
change_user 'test_user';
--error ER_TABLEACCESS_DENIED_ERROR
select * from mysql.roles_mapping;
--sorted_result
show grants;
select current_user(), current_role();
set role r_sel;
select current_user(), current_role();
--sorted_result
show grants;
--sorted_result
select * from mysql.roles_mapping;
set role r_ins;
select current_user(), current_role();
--sorted_result
show grants;
--error ER_TABLEACCESS_DENIED_ERROR
select * from mysql.roles_mapping;
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
'r_sel',
'r_rld');
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
flush privileges;
set role r_rld;
select current_user(), current_role();
flush privileges;
set role r_sel;
select current_user(), current_role();
flush privileges;
set role none;
select current_user(), current_role();
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
flush privileges;
set role r_ins;
select current_user(), current_role();
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
'r_sel',
'r_upd');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
'r_sel',
'r_del');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
'r_sel',
'r_crt');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
'r_sel',
'r_drp');
insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
'r_del',
'r_ins');
set role r_rld;
select current_user(), current_role();
flush privileges;
set role r_sel;
select current_user(), current_role();
update mysql.roles_mapping set RoleFk='r_ins' where RoleFk='r_ins_wrong';
flush privileges;
set role r_sel;
select current_user(), current_role();
create table mysql.random_test_table (id INT);
insert into mysql.random_test_table values (1);
--sorted_result
select * from mysql.random_test_table;
delete from mysql.roles_mapping where RoleFk='r_ins';
flush privileges;
set role r_sel;
select current_user(), current_role();
--error ER_TABLEACCESS_DENIED_ERROR
insert into mysql.random_test_table values (1);
drop table mysql.random_test_table;
change_user 'root';
delete from mysql.user where user like 'r\_%';
delete from mysql.roles_mapping where RoleFk like 'r\_%';
flush privileges;
drop user 'test_user'@'localhost';
|
