Debian Codegraph @

mysql-test/suite/auth_sec/r/atomic_alter_roles.result

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