Debian Codegraph @

mysql-test/suite/roles/admin.test

raw
1 source include/not_embedded.inc;
2
3 create user foo@localhost;
4 grant create user on *.* to foo@localhost;
5
6 ########################################
7 # syntax tests
8 ########################################
9
10 create role role1;
11 create role role2 with admin current_user;
12 --error ER_MALFORMED_DEFINER
13 create role role3 with admin current_role;
14 create role role3 with admin role1;
15 create role role4 with admin root@localhost;
16
17 # privilege checks, one needs SUPER to specify an arbitrary admin
18 connect (c1, localhost, foo,,);
19 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
20 create role role5 with admin root@localhost;
21 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
22 create role role5 with admin role3;
23 create role role5 with admin foo@localhost;
24
25 connection default;
26 # non-existing admin. works. warning. error in the log on acl_load.
27 call mtr.add_suppression("Invalid roles_mapping table entry user:'foo@bar', rolename:'role6'");
28 create role role6 with admin foo@bar;
29
30 --error ER_PARSE_ERROR
31 create user bar with admin current_user;
32
33 grant role1 to foo@localhost with admin option;
34 grant role2 to foo@localhost;
35 grant role2 to role1;
36 grant role4 to role3 with admin option;
37 --error ER_PARSE_ERROR
38 grant select on *.* to foo@localhost with admin option;
39
40 --sorted_result
41 show grants for foo@localhost;
42 --sorted_result
43 show grants for role1;
44 --sorted_result
45 show grants for role4;
46 --sorted_result
47 select * from mysql.roles_mapping;
48 flush privileges;
49 --sorted_result
50 show grants for foo@localhost;
51 --sorted_result
52 show grants for role1;
53 --sorted_result
54 show grants for role4;
55 --sorted_result
56 select * from information_schema.applicable_roles;
57
58 grant role2 to role1 with admin option;
59 revoke role1 from foo@localhost;
60 revoke admin option for role4 from role3;
61 revoke admin option for role2 from foo@localhost;
62 revoke admin option for role1 from root@localhost;
63
64 --sorted_result
65 show grants for foo@localhost;
66 --sorted_result
67 show grants for role1;
68 --sorted_result
69 show grants for role4;
70 --sorted_result
71 select * from mysql.roles_mapping;
72 flush privileges;
73 --sorted_result
74 show grants for foo@localhost;
75 --sorted_result
76 show grants for role1;
77 --sorted_result
78 show grants for role4;
79 --sorted_result
80 select * from information_schema.applicable_roles;
81
82 # Now, root@localhost don't have admin option for role1:
83 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
84 grant role1 to role4;
85 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
86 grant role1 to role4 with admin option;
87 # but role3 is grantable
88 grant role3 to role2;
89 revoke role3 from role2;
90
91 # now, a diamond
92 grant role4 to role2 with admin option;
93 revoke role2 from current_user;
94 revoke role4 from current_user;
95 grant role4 to current_user;
96
97
98 ########################################
99 # cleanup
100 ########################################
101 drop role role1, role2, role3, role4, role5, role6;
102 drop user foo@localhost;