Debian Codegraph @

mysql-test/suite/roles/set_default_role_for.test

raw
1 source include/not_embedded.inc;
2
3 # This test checks setting a default role to a different user;
4
5
6 create user user_a@localhost;
7 create user user_b@localhost;
8
9 create role role_a;
10 create role role_b;
11
12 grant role_a to user_a@localhost;
13 grant role_b to user_b@localhost;
14
15 grant role_a to user_a@localhost;
16 grant select on *.* to role_a;
17
18 grant role_b to user_b@localhost;
19 grant insert, update on *.* to role_b;
20
21 change_user 'user_a';
22
23 # A user should not be a able to set a default role for someone else,
24 # if he hasn't got write access to the database.
25 --error ER_DBACCESS_DENIED_ERROR
26 set default role role_a for user_b@localhost;
27
28 # Should have the same effect as set default role role_a.
29 set default role role_a for user_a@localhost;
30
31 change_user 'root';
32
33 # Not even a 'root' user should be able to set an invalid role for a user.
34 --error ER_INVALID_ROLE
35 set default role invalid_role for user_a@localhost;
36
37 --error ER_INVALID_ROLE
38 set default role role_b for user_a@localhost;
39
40 # Make sure we can set a default role for a different user than the one that
41 # is actually running the command.
42 set default role role_b for user_b@localhost;
43
44 change_user 'user_a';
45
46 show grants;
47 --sorted_result
48 select user, host, default_role from mysql.user where user like 'user_%';
49
50 set default role NONE for current_user;
51 --sorted_result
52 select user, host, default_role from mysql.user where user like 'user_%';
53
54 set default role current_role for current_user;
55 --sorted_result
56 select user, host, default_role from mysql.user where user like 'user_%';
57
58 # Make sure we can't set a default role not granted to us, using current_user
59 --error ER_INVALID_ROLE
60 set default role role_b for current_user;
61
62 change_user 'user_b';
63
64 show grants;
65 --error ER_TABLEACCESS_DENIED_ERROR
66 select user, host, default_role from mysql.user where user like 'user_%';
67
68 # Since we have update privileges on the mysql.user table, we should
69 # be able to set a default role for a different user.
70 set default role NONE for user_a@localhost;
71
72 change_user 'user_a';
73
74 # There is no default role set any more.
75 show grants;
76 --error ER_TABLEACCESS_DENIED_ERROR
77 select user, host, default_role from mysql.user where user like 'user_%';
78
79 change_user 'root';
80
81 drop role role_a;
82 drop role role_b;
83 drop user user_a@localhost;
84 drop user user_b@localhost;