Debian Codegraph @

mysql-test/suite/roles/set_default_role_invalid.test

raw
1 source include/not_embedded.inc;
2
3 # This test checks the error paths possible during set default role.
4
5 # Create a user with no privileges
6 create user test_user@localhost;
7
8 create role test_role;
9 create role not_granted_role;
10
11 grant select on *.* to test_role;
12 grant test_role to test_user@localhost;
13
14 change_user 'test_user';
15 show grants;
16 --error ER_TABLEACCESS_DENIED_ERROR
17 select user, host, default_role from mysql.user;
18
19 # A user can not set a default role that does not exist in the database.
20 --error ER_INVALID_ROLE
21 set default role invalid_role;
22
23 # A user can not set a default role if he can not call set role <role>.
24 --error ER_INVALID_ROLE
25 set default role not_granted_role;
26
27 set default role test_role;
28
29 # Even though a user has the default role set, without reconnecting, we should
30 # not already have the roles privileges.
31 --error ER_TABLEACCESS_DENIED_ERROR
32 select user, host, default_role from mysql.user;
33
34 change_user 'root';
35 select user, host, default_role from mysql.user where user='test_user';
36
37 change_user 'test_user';
38 # This should show that the new test_user has the role's grants enabled.
39 show grants;
40 select user, host, default_role from mysql.user where user='test_user';
41
42 # If we have a failed set default role attempt, don't change the already set
43 # default role.
44 --error ER_INVALID_ROLE
45 set default role invalid_role;
46 select user, host, default_role from mysql.user where user='test_user';
47
48 change_user 'root';
49 # Now, even though a default role is still set for test_user, make sure the
50 # user does not get the rights, if he can not set the role.
51 revoke test_role from test_user@localhost;
52
53 change_user 'test_user';
54 --error ER_TABLEACCESS_DENIED_ERROR
55 select user, host, default_role from mysql.user where user='test_user';
56
57 change_user 'root';
58
59 # Cleanup
60 drop role test_role;
61 drop role not_granted_role;
62 drop user test_user@localhost;
63
64 --echo #
65 --echo # MDEV-22312: Bad error message for SET DEFAULT ROLE when user account
66 --echo # is not granted the role
67 --echo #
68
69 CREATE USER a;
70 CREATE USER b;
71 CREATE ROLE r1;
72 CREATE ROLE r2;
73 --error ER_INVALID_ROLE
74 SET DEFAULT ROLE r1 FOR a;
75 # Granting roles to user b
76 GRANT r1 TO b;
77 GRANT r2 TO b;
78 # After granting the role, role can be set as default
79 SET DEFAULT ROLE r1 FOR b;
80
81 --echo # Change user b
82 change_user b;
83 SELECT CURRENT_ROLE;
84 SET ROLE r2;
85 SELECT CURRENT_ROLE;
86 # User b has no UPDATE_PRIV for mysql.user
87 --error ER_DBACCESS_DENIED_ERROR
88 SET DEFAULT ROLE r1 FOR a;
89 SET DEFAULT ROLE r2;
90
91 --echo # Change user root (session 1: select_priv to b)
92 change_user root;
93 # Let's grant select_priv to user b
94 GRANT SELECT ON mysql.* TO b;
95
96 --echo # Change user b (session 1: select_priv)
97 change_user b;
98 SHOW GRANTS FOR b;
99 # User must have update_priv before setting the role
100 --error ER_DBACCESS_DENIED_ERROR
101 SET DEFAULT ROLE r1 FOR a;
102 # Testing the `CURRENT_ROLE` as a special case
103 SELECT CURRENT_ROLE;
104 SET DEFAULT ROLE NONE;
105 SELECT CURRENT_ROLE;
106 SET DEFAULT ROLE current_role FOR current_user;
107 # Testing of non-existing role
108 --error ER_INVALID_ROLE
109 SET DEFAULT ROLE invalid_role;
110 # Testing of non-existing role for different user
111 --error ER_DBACCESS_DENIED_ERROR
112 SET DEFAULT ROLE invalid_role FOR a;
113 # Testing the `None` role for different user
114 -- error ER_DBACCESS_DENIED_ERROR
115 SET DEFAULT ROLE none FOR a;
116
117 --echo # Change user root (session 2: adding update_priv to user b)
118 change_user root;
119 # update_priv are enough
120 GRANT UPDATE ON mysql.* TO b;
121
122 --echo # Change user b
123 change_user b;
124 SHOW GRANTS FOR b;
125 # In all tests in session user a has not been granted the role
126 # Testing setting role for different user, should fail with new error
127 --error ER_INVALID_ROLE
128 SET DEFAULT ROLE r1 FOR a;
129 # Testing of non-existing role
130 --error ER_INVALID_ROLE
131 SET DEFAULT ROLE invalid_role;
132 # Testing of non-existing role for different user with update_priv
133 --error ER_INVALID_ROLE
134 SET DEFAULT ROLE invalid_role FOR a;
135 # Testing the `None` role for different user with update_priv
136 SET DEFAULT ROLE none FOR a;
137
138 --echo # Change user root (session 3: Grant role to user a)
139 change_user root;
140 # After granting the privilege for a, user b can set default role
141 GRANT r1 TO a;
142 SET DEFAULT ROLE r1 FOR a;
143
144 --echo # Change user a (verify session 3)
145 change_user a;
146 SELECT CURRENT_ROLE;
147 SET DEFAULT ROLE None;
148
149 --echo # Change user b (session 3: role granted to user a)
150 change_user b;
151 # This should set role because b has update_priv
152 SET DEFAULT ROLE r1 FOR a;
153 # Testing non-granted role r2 still should fail
154 -- error ER_INVALID_ROLE
155 SET DEFAULT ROLE r2 FOR a;
156 # Testing of non-existing role
157 --error ER_INVALID_ROLE
158 SET DEFAULT ROLE invalid_role;
159 # Testing of non-existing role for different user
160 --error ER_INVALID_ROLE
161 SET DEFAULT ROLE invalid_role FOR a;
162
163 # Clear the workspace
164 change_user root;
165 --sorted_result
166 SELECT user, host, default_role FROM mysql.user where user='a' or user='b';
167 DROP ROLE r1, r2;
168 DROP USER a, b;