Debian Codegraph @

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

raw
1 include/save_binlog_position.inc
2 # ----------------------------------------------------------------------
3 # Begin : Tests for CREATE USER
4 CREATE USER userX;
5 SELECT user FROM mysql.user WHERE user LIKE 'user%';
6 user
7 userX
8 # Case 1 : Execute CREATE USER for existing user
9 CREATE USER userY, userZ, userX;
10 ERROR HY000: Operation CREATE USER failed for 'userX'@'%'
11 # Must not show entries for userY and userZ
12 SELECT user FROM mysql.user WHERE user LIKE 'user%';
13 user
14 userX
15 # This event sequence pattern MUST be present in binlog: !Q(CREATE USER .userX.*)
16 include/assert_binlog_events.inc
17 # This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER .*userY.*userZ.*)
18 include/assert_binlog_events.inc
19 include/save_binlog_position.inc
20 # Case 2 : Execute CREATE USER for non-existing users
21 CREATE USER userY, userZ;
22 # Must show entries for userX, userY and userZ
23 SELECT user FROM mysql.user WHERE user LIKE 'user%';
24 user
25 userX
26 userY
27 userZ
28 # This event sequence pattern MUST be present in binlog: !Q(CREATE USER .userY.*userZ.*)
29 include/assert_binlog_events.inc
30 include/save_binlog_position.inc
31 # Case 3 : Try creating anonymous user with expired password
32 CREATE USER ''@localhost,
33 userA IDENTIFIED BY 'abcd'
34 PASSWORD EXPIRE;
35 ERROR HY000: Operation CREATE USER failed for anonymous user
36 CREATE USER ''@'' PASSWORD EXPIRE;
37 ERROR HY000: Operation CREATE USER failed for anonymous user
38 # Must not show ''@localhost or ''@''
39 SELECT user FROM mysql.user WHERE user LIKE 'user%';
40 user
41 userX
42 userY
43 userZ
44 # Case 4 : Try creating user with invalid plugin
45 CREATE USER userA IDENTIFIED BY 'abcd', userB IDENTIFIED WITH 'blahblah';
46 ERROR HY000: Plugin 'blahblah' is not loaded
47 # Case 5 : Try creating user with invalid hash
48 CREATE USER userA IDENTIFIED BY 'abcd',
49 userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha';
50 ERROR HY000: The password hash doesn't have the expected format.
51 # Must not show userA, userB
52 SELECT user FROM mysql.user WHERE user LIKE 'user%';
53 user
54 userX
55 userY
56 userZ
57 # Check binlog for Cases 3, 4 and 5 above
58 # This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
59 include/assert_binlog_events.inc
60 # Case 6 : Add a row in mysql.user without FLUSH PRIVILEGES and
61 # execute CREATE USER for same user
62 # Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
63 CREATE TABLE mysql.tmp_user LIKE mysql.user;
64 INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userX';
65 UPDATE mysql.tmp_user SET user='userW' WHERE user LIKE 'userX';
66 INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
67 DROP TABLE mysql.tmp_user;
68 include/save_binlog_position.inc
69 # Though userW is not present in cache, CREATE USER should fail
70 # because we will encounter duplicate key error while inserting entry
71 # in mysql.user table for userW.
72 CREATE USER userW IDENTIFIED BY 'abcd', userV IDENTIFIED BY 'haha';
73 ERROR HY000: Operation CREATE USER failed for 'userW'@'%'
74 # Must not show userV
75 SELECT user FROM mysql.user WHERE user LIKE 'user%';
76 user
77 userW
78 userX
79 userY
80 userZ
81 # This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
82 include/assert_binlog_events.inc
83 FLUSH PRIVILEGES;
84 DROP USER userW, userX, userY, userZ;
85 include/save_binlog_position.inc
86 # End : Tests for CREATE USER
87 # ----------------------------------------------------------------------
88 # BEGIN : Tests for CREATE USER IF NOT EXISTS
89 CALL mtr.add_suppression("Following users were specified in CREATE USER IF NOT EXISTS but they already exist.");
90 CREATE USER userX;
91 include/save_binlog_position.inc
92 # Case 1 : Execute CREATE USER IF NOT EXISTS for existing user
93 CREATE USER IF NOT EXISTS userX, userY, userZ;
94 Warnings:
95 Note 3163 Authorization ID 'userX'@'%' already exists.
96 # Must show entries for userX, userY, userZ
97 SELECT user FROM mysql.user WHERE user LIKE 'user%';
98 user
99 userX
100 userY
101 userZ
102 # This event sequence pattern MUST be present in binlog: !Q(CREATE USER IF NOT EXISTS.*userX.*userY.*userZ.*)
103 include/assert_binlog_events.inc
104 include/save_binlog_position.inc
105 # Must show warnings about userX, userY and userZ
106 CREATE USER IF NOT EXISTS userX, userY, userZ;
107 Warnings:
108 Note 3163 Authorization ID 'userX'@'%' already exists.
109 Note 3163 Authorization ID 'userY'@'%' already exists.
110 Note 3163 Authorization ID 'userZ'@'%' already exists.
111 # This event sequence pattern MUST be present in binlog: !Q(CREATE USER IF NOT EXISTS.*userX.*userY.*userZ.*)
112 include/assert_binlog_events.inc
113 include/save_binlog_position.inc
114 # Case 2 : Try creating anonymous user with expired password
115 # using CREATE USER IF NOT EXISTS
116 CREATE USER IF NOT EXISTS ''@'' PASSWORD EXPIRE;
117 ERROR HY000: Operation CREATE USER failed for anonymous user
118 CREATE USER IF NOT EXISTS ''@localhost PASSWORD EXPIRE;
119 ERROR HY000: Operation CREATE USER failed for anonymous user
120 # Case 3 : Try creating user with invalid plugin
121 CREATE USER IF NOT EXISTS userA IDENTIFIED BY 'abcd', userB IDENTIFIED WITH 'blahblah', userX;
122 ERROR HY000: Plugin 'blahblah' is not loaded
123 # Case 4 : Try creating user with invalid hash
124 CREATE USER IF NOT EXISTS userA IDENTIFIED BY 'abcd',
125 userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha';
126 ERROR HY000: The password hash doesn't have the expected format.
127 # Must not show userA, userB
128 SELECT user FROM mysql.user WHERE user LIKE 'user%';
129 user
130 userX
131 userY
132 userZ
133 # This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
134 include/assert_binlog_events.inc
135 # Case 6 : Add a row in mysql.user without FLUSH PRIVILEGES and
136 # execute CREATE USER IF NOT for same user
137 # Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
138 CREATE TABLE mysql.tmp_user LIKE mysql.user;
139 INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userX';
140 UPDATE mysql.tmp_user SET user='userW' WHERE user LIKE 'userX';
141 INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
142 DROP TABLE mysql.tmp_user;
143 include/save_binlog_position.inc
144 # Though userW is not present in cache, CREATE USER IF NOT EXISTS
145 # will skip it because we will encounter duplicate key error while
146 # inserting entry in mysql.user table for userW.
147 # Must show warning about userW@%.
148 CREATE USER IF NOT EXISTS userW IDENTIFIED BY 'abcd',
149 userV IDENTIFIED BY 'haha';
150 Warnings:
151 Note 3163 Authorization ID 'userW'@'%' already exists.
152 # Must show userV, userW, userX, userY, userZ
153 SELECT user FROM mysql.user WHERE user LIKE 'user%';
154 user
155 userV
156 userW
157 userX
158 userY
159 userZ
160 # This event sequence pattern MUST be present in binlog: !Q(CREATE USER.*userW.*userV.*)
161 include/assert_binlog_events.inc
162 DROP USER userV, userW, userX, userY, userZ;
163 include/save_binlog_position.inc
164 # END : Tests for CREATE USER IF NOT EXISTS
165 # ----------------------------------------------------------------------
166 # Begin : Tests for CREATE USER with DEFAULT ROLE
167 CREATE USER userX;
168 CREATE ROLE 'roleX', 'roleY', 'roleZ';
169 SELECT user FROM mysql.user WHERE user LIKE 'user%';
170 user
171 userX
172 # Case 1 : Execute CREATE USER for existing user
173 CREATE USER userY, userZ, userX DEFAULT ROLE 'roleX';
174 ERROR HY000: Operation CREATE USER failed for 'userX'@'%'
175 # Must not show entries for userY and userZ
176 SELECT user FROM mysql.user WHERE user LIKE 'user%';
177 user
178 userX
179 SELECT user FROM mysql.default_roles where user LIKE 'user%';
180 user
181 # This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER .*userY.*userZ.*.userX)
182 include/assert_binlog_events.inc
183 include/save_binlog_position.inc
184 # Case 2 : Execute CREATE USER for non-existing users
185 CREATE USER userY, userZ DEFAULT ROLE 'roleX', 'roleY';
186 # Must show entries for userX, userY and userZ
187 SELECT user FROM mysql.user WHERE user LIKE 'user%';
188 user
189 userX
190 userY
191 userZ
192 SELECT user FROM mysql.default_roles where user LIKE 'user%';
193 user
194 userY
195 userY
196 userZ
197 userZ
198 # This event sequence pattern MUST be present in binlog: !Q(CREATE USER .userY.*userZ.*)
199 include/assert_binlog_events.inc
200 include/save_binlog_position.inc
201 # Case 3 : Try creating user with invalid hash
202 CREATE USER userA IDENTIFIED BY 'abcd',
203 userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha'
204 DEFAULT ROLE roleX;
205 ERROR HY000: The password hash doesn't have the expected format.
206 # Must not show userA, userB
207 SELECT user FROM mysql.user WHERE user LIKE 'user%';
208 user
209 userX
210 userY
211 userZ
212 SELECT user FROM mysql.default_roles WHERE user LIKE 'user%';
213 user
214 userY
215 userY
216 userZ
217 userZ
218 # Check binlog for Cases 3
219 # This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
220 include/assert_binlog_events.inc
221 DROP USER userX, userY, userZ;
222 DROP ROLE roleX, roleY, roleZ;
223 include/save_binlog_position.inc
224 # ----------------------------------------------------------------------