Debian Codegraph @

mysql-test/suite/roles/set_role-multiple-role.result

raw
1 create user 'test_user'@'localhost';
2 create role r_sel;
3 create role r_ins;
4 create role r_upd;
5 create role r_del;
6 create role r_crt;
7 create role r_drp;
8 create role r_rld;
9 grant select on *.* to r_sel;
10 grant insert on *.* to r_ins;
11 grant update on *.* to r_upd;
12 grant delete on *.* to r_del;
13 grant create on *.* to r_crt;
14 grant drop on *.* to r_drp;
15 grant reload on *.* to r_rld;
16 grant r_sel to test_user@localhost;
17 grant r_ins to test_user@localhost;
18 grant r_upd to test_user@localhost;
19 grant r_del to test_user@localhost;
20 grant r_crt to test_user@localhost;
21 grant r_drp to test_user@localhost;
22 grant r_rld to test_user@localhost;
23 flush privileges;
24 select * from mysql.roles_mapping;
25 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
26 show grants;
27 Grants for test_user@localhost
28 GRANT USAGE ON *.* TO `test_user`@`localhost`
29 GRANT `r_crt` TO `test_user`@`localhost`
30 GRANT `r_del` TO `test_user`@`localhost`
31 GRANT `r_drp` TO `test_user`@`localhost`
32 GRANT `r_ins` TO `test_user`@`localhost`
33 GRANT `r_rld` TO `test_user`@`localhost`
34 GRANT `r_sel` TO `test_user`@`localhost`
35 GRANT `r_upd` TO `test_user`@`localhost`
36 select current_user(), current_role();
37 current_user() current_role()
38 test_user@localhost NULL
39 set role r_sel;
40 select current_user(), current_role();
41 current_user() current_role()
42 test_user@localhost r_sel
43 show grants;
44 Grants for test_user@localhost
45 GRANT SELECT ON *.* TO `r_sel`
46 GRANT USAGE ON *.* TO `test_user`@`localhost`
47 GRANT `r_crt` TO `test_user`@`localhost`
48 GRANT `r_del` TO `test_user`@`localhost`
49 GRANT `r_drp` TO `test_user`@`localhost`
50 GRANT `r_ins` TO `test_user`@`localhost`
51 GRANT `r_rld` TO `test_user`@`localhost`
52 GRANT `r_sel` TO `test_user`@`localhost`
53 GRANT `r_upd` TO `test_user`@`localhost`
54 select * from mysql.roles_mapping;
55 Host User Role Admin_option
56 localhost root r_crt Y
57 localhost root r_del Y
58 localhost root r_drp Y
59 localhost root r_ins Y
60 localhost root r_rld Y
61 localhost root r_sel Y
62 localhost root r_upd Y
63 localhost test_user r_crt N
64 localhost test_user r_del N
65 localhost test_user r_drp N
66 localhost test_user r_ins N
67 localhost test_user r_rld N
68 localhost test_user r_sel N
69 localhost test_user r_upd N
70 set role r_ins;
71 select current_user(), current_role();
72 current_user() current_role()
73 test_user@localhost r_ins
74 show grants;
75 Grants for test_user@localhost
76 GRANT INSERT ON *.* TO `r_ins`
77 GRANT USAGE ON *.* TO `test_user`@`localhost`
78 GRANT `r_crt` TO `test_user`@`localhost`
79 GRANT `r_del` TO `test_user`@`localhost`
80 GRANT `r_drp` TO `test_user`@`localhost`
81 GRANT `r_ins` TO `test_user`@`localhost`
82 GRANT `r_rld` TO `test_user`@`localhost`
83 GRANT `r_sel` TO `test_user`@`localhost`
84 GRANT `r_upd` TO `test_user`@`localhost`
85 select * from mysql.roles_mapping;
86 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
87 insert into mysql.roles_mapping values ('', 'r_sel', 'r_rld', 'N');
88 flush privileges;
89 ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
90 set role r_rld;
91 select current_user(), current_role();
92 current_user() current_role()
93 test_user@localhost r_rld
94 flush privileges;
95 set role r_sel;
96 select current_user(), current_role();
97 current_user() current_role()
98 test_user@localhost r_sel
99 flush privileges;
100 set role none;
101 select current_user(), current_role();
102 current_user() current_role()
103 test_user@localhost NULL
104 flush privileges;
105 ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
106 set role r_ins;
107 select current_user(), current_role();
108 current_user() current_role()
109 test_user@localhost r_ins
110 insert into mysql.roles_mapping values ('', 'r_sel', 'r_upd', 'N');
111 insert into mysql.roles_mapping values ('', 'r_sel', 'r_del', 'N');
112 insert into mysql.roles_mapping values ('', 'r_sel', 'r_crt', 'N');
113 insert into mysql.roles_mapping values ('', 'r_sel', 'r_drp', 'N');
114 insert into mysql.roles_mapping values ('', 'r_del', 'r_ins', 'N');
115 set role r_rld;
116 select current_user(), current_role();
117 current_user() current_role()
118 test_user@localhost r_rld
119 flush privileges;
120 set role r_sel;
121 select current_user(), current_role();
122 current_user() current_role()
123 test_user@localhost r_sel
124 update mysql.roles_mapping set Role='r_ins' where Role='r_ins_wrong';
125 flush privileges;
126 set role r_sel;
127 select current_user(), current_role();
128 current_user() current_role()
129 test_user@localhost r_sel
130 create table mysql.random_test_table (id INT);
131 insert into mysql.random_test_table values (1);
132 select * from mysql.random_test_table;
133 id
134 1
135 delete from mysql.roles_mapping where Role='r_ins';
136 flush privileges;
137 set role r_sel;
138 select current_user(), current_role();
139 current_user() current_role()
140 test_user@localhost r_sel
141 insert into mysql.random_test_table values (1);
142 ERROR 42000: INSERT command denied to user 'test_user'@'localhost' for table `mysql`.`random_test_table`
143 drop table mysql.random_test_table;
144 delete from mysql.user where user like 'r\_%';
145 delete from mysql.roles_mapping where Role like 'r\_%';
146 flush privileges;
147 drop user 'test_user'@'localhost';