Debian Codegraph @

mysql-test/suite/auth_sec/t/atomic_alter_roles.test

raw
1 --source include/have_log_bin.inc
2 --source include/save_binlog_position.inc
3
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
6 --echo
7
8 --echo # Create a few users
9 CREATE USER userX, userY, userZ;
10 --echo # Create a few roles
11 CREATE ROLE 'administrator', 'qa', 'developer', 'manager';
12 --echo # Grant roles to the created users
13 GRANT 'administrator', 'qa', 'developer', 'manager' to userX, userY, userZ;
14 --echo # Check the number of the roles and users created.
15 SELECT COUNT(*) FROM mysql.user;
16 --echo # Check the number of default roles.
17 SELECT COUNT(*) FROM mysql.default_roles;
18 --echo
19
20 --echo #
21 --echo # 1. Tests to SET list of roles as DEFAULT
22 --echo #
23 --echo
24
25 --echo # 1.1 : Set the default roles for a valid and an invalid user; Must fail.
26 --error ER_ROLE_NOT_GRANTED
27 SET DEFAULT ROLE 'administrator', 'qa' to userX, invalidUser;
28 --echo
29
30 --echo # No default roles must be added for valid user i.e. userX
31 SELECT * FROM mysql.default_roles;
32 --echo
33
34 --let $invert= 1
35 --let $event= !Q(SET DEFAULT ROLE .*userX.*invalidUser.*)
36 --source ../include/auth_sec_assert_binlog_events.inc
37 --let $invert= 0
38 --source include/save_binlog_position.inc
39 --echo
40
41 --echo # 1.2 : Set the default roles for two valid users
42 SET DEFAULT ROLE administrator, qa to userX, userY;
43 --echo
44
45 --echo # Default roles must be added for both users
46 SELECT * FROM mysql.default_roles;
47 --echo
48
49 --let $event= !Q(SET DEFAULT ROLE .*userX.*userY.*)
50 --source ../include/auth_sec_assert_binlog_events.inc
51 --source include/save_binlog_position.inc
52 --echo
53
54 --echo #
55 --echo # 2. Tests to set the default roles to ALL
56 --echo #
57 --echo
58
59 --echo # 2.1 : Set default roles for valid users and an invalid user; Must fail.
60 --error ER_UNKNOWN_AUTHID
61 SET DEFAULT ROLE ALL to userY, userZ, invalidUser;
62 --echo
63
64 --echo # No default roles must be added for valid users i.e. userY, userZ
65 SELECT * FROM mysql.default_roles;
66 --echo
67
68 --let $invert= 1
69 --let $event= !Q(SET DEFAULT ROLE ALL .*userX.*userY.*invalidUser.*)
70 --source ../include/auth_sec_assert_binlog_events.inc
71 --let $invert= 0
72 --source include/save_binlog_position.inc
73 --echo
74
75 --echo # 2.2 : Set the default roles for multiple users
76 SET DEFAULT ROLE ALL to userX, userY, userZ;
77 --echo
78
79 --echo # All default roles must be added/updated for users
80 SELECT * FROM mysql.default_roles;
81 --echo
82
83 --let $event= !Q(SET DEFAULT ROLE ALL .*userX.*userY.*userZ.*)
84 --source ../include/auth_sec_assert_binlog_events.inc
85 --source include/save_binlog_position.inc
86 --echo
87
88 --echo #
89 --echo # 3. Tests to set the default roles to NONE
90 --echo #
91 --echo
92
93 --echo # 3.1 : Set default roles to NONE for valid users and ignore invalid user;
94 SET DEFAULT ROLE NONE to userY, invalidUser;
95 --echo
96
97 --echo # Default roles must be removed from valid users
98 SELECT * FROM mysql.default_roles;
99 --echo
100
101 --let $event= !Q(SET DEFAULT ROLE NONE .*userY.*invalidUser.*)
102 --source ../include/auth_sec_assert_binlog_events.inc
103 --source include/save_binlog_position.inc
104 --echo
105
106 --echo # 3.2 : Set the default roles to NONE for valid users
107 SET DEFAULT ROLE NONE to userX, userY, userZ;
108 --echo
109
110 --echo # All default roles must be removed for users
111 SELECT * FROM mysql.default_roles;
112 --echo
113
114 --let $event= !Q(SET DEFAULT ROLE NONE .*userX.*userY.*userZ.*)
115 --source ../include/auth_sec_assert_binlog_events.inc
116 --source include/save_binlog_position.inc
117 --echo
118
119 --echo #
120 --echo # End of tests
121 --echo #
122 --echo
123
124 --echo # Drop the roles
125 DROP ROLE 'administrator', 'qa', 'developer', 'manager';
126 --echo # Drop the users
127 DROP USER userX, userY, userZ;
128
129 # Wait till we reached the initial number of concurrent sessions
130 --source include/wait_until_count_sessions.inc