Debian Codegraph @

mysql-test/suite/rpl/r/rpl_atomic_user.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 CALL mtr.add_suppression('Following users were specified in CREATE USER');
7 # CREATE USER statement test where user does not exist
8 CREATE USER u1 IDENTIFIED WITH 'sha256_password' BY 'pwd1', u2 IDENTIFIED WITH
9 'mysql_native_password' BY 'pwd2', u3, u4, u5, u6 REQUIRE SSL;
10 # CREATE USER statement test where few user exist and few does not
11 CREATE USER u_ne1, u1 IDENTIFIED WITH 'sha256_password' BY 'auth_string',
12 u_ne2 IDENTIFIED WITH 'sha256_password' REQUIRE SSL;
13 ERROR HY000: Operation CREATE USER failed for 'u1'@'%'
14 CREATE USER u_ne1, u1 IDENTIFIED WITH 'sha256_password' BY 'auth_string',
15 u_ne2 IDENTIFIED WITH 'invalid_plugin' REQUIRE SSL;
16 ERROR HY000: Plugin 'invalid_plugin' is not loaded
17 CREATE ROLE r1, r2;
18 # ALTER USER statement test
19 ALTER USER u1 IDENTIFIED WITH 'mysql_native_password' BY 'pwd1', u2 IDENTIFIED
20 WITH 'sha256_password' BY 'pwd2' WITH MAX_USER_CONNECTIONS 11 ;
21 ALTER USER u3 IDENTIFIED WITH 'mysql_native_password' BY 'pwd3', u4 IDENTIFIED
22 WITH 'mysql_native_password' BY 'pwd4' WITH MAX_USER_CONNECTIONS 12;
23 # RENAME USER statement test
24 RENAME USER u3 TO u33, u5 TO u55, u4 TO u44;
25 RENAME USER u3 TO u33, u4 TO u44;
26 ERROR HY000: Operation RENAME USER failed for 'u3'@'%','u4'@'%'
27 RENAME USER u33 TO uu, u44 TO uu;
28 ERROR HY000: Operation RENAME USER failed for 'u44'@'%'
29 # GRANT USER test for all grant types
30 use mysql;
31 CREATE DEFINER=u1 PROCEDURE p() SELECT host, user FROM mysql.user;
32 GRANT SELECT ON *.* to u1;
33 GRANT SELECT ON mysql.user to u2;
34 GRANT SELECT ON mysql.* TO u33;
35 GRANT SELECT (user, host) ON mysql.user TO u44;
36 GRANT EXECUTE ON PROCEDURE mysql.p TO u55;
37 GRANT proxy ON u1 TO u2;
38 GRANT ALL ON *.* TO u6;
39 include/sync_slave_sql_with_master.inc
40 [connection master]
41 SET @sql_log_bin_saved = @@sql_log_bin;
42 SET sql_log_bin = 0;
43 CREATE TABLE test.user_1
44 SELECT * FROM mysql.user
45 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
46 ORDER BY user;
47 SET sql_log_bin = @sql_log_bin_saved;
48 [connection slave]
49 SET @sql_log_bin_saved = @@sql_log_bin;
50 SET sql_log_bin = 0;
51 CREATE TABLE test.user_1
52 SELECT * FROM mysql.user
53 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
54 ORDER BY user;
55 SET sql_log_bin = @sql_log_bin_saved;
56 # check that mysql.user table has same values in both servers
57 include/diff_tables.inc [master:test.user_1, slave:test.user_1]
58 # REVOKE FROM USER statement test
59 [connection master]
60 REVOKE SELECT ON *.* FROM u1;
61 REVOKE SELECT ON mysql.user FROM u2;
62 REVOKE SELECT ON mysql.* FROM u33;
63 REVOKE SELECT (user, host) ON mysql.user FROM u44;
64 REVOKE EXECUTE ON PROCEDURE mysql.p FROM u55;
65 REVOKE PROXY ON u1 FROM u2;
66 REVOKE ALL ON *.* FROM u6;
67 DROP PROCEDURE p;
68 # CREATE USER IF NOT EXISTS statement test
69 CREATE USER IF NOT EXISTS u7, u_ne3 IDENTIFIED WITH 'sha256_password';
70 CREATE USER IF NOT EXISTS u7, u_ne4, u_ne5 IDENTIFIED WITH 'sha256_password'
71 by 'pwd_u_ne5';
72 Warnings:
73 Note 3163 Authorization ID 'u7'@'%' already exists.
74 CREATE USER IF NOT EXISTS u5, u_failed1 IDENTIFIED WITH 'invalid_plugin',
75 u_failed2;
76 ERROR HY000: Plugin 'invalid_plugin' is not loaded
77 CREATE ROLE IF NOT EXISTS r33, r44;
78 CREATE ROLE IF NOT EXISTS r44, r55;
79 Warnings:
80 Note 3163 Authorization ID 'r44'@'%' already exists.
81 CREATE ROLE IF NOT EXISTS r44, r55;
82 Warnings:
83 Note 3163 Authorization ID 'r44'@'%' already exists.
84 Note 3163 Authorization ID 'r55'@'%' already exists.
85 CREATE USER IF NOT EXISTS u6, u6, r1, r2 IDENTIFIED WITH 'sha256_password', r6;
86 Warnings:
87 Note 3163 Authorization ID 'u6'@'%' already exists.
88 Note 3163 Authorization ID 'u6'@'%' already exists.
89 Note 3163 Authorization ID 'r1'@'%' already exists.
90 Note 3163 Authorization ID 'r2'@'%' already exists.
91 # ALTER USER IF EXISTS statement test
92 ALTER USER IF EXISTS u_failed1, u_failed2 IDENTIFIED WITH 'sha256_password';
93 Warnings:
94 Note 3162 Authorization ID 'u_failed1'@'%' does not exist.
95 Note 3162 Authorization ID 'u_failed2'@'%' does not exist.
96 ALTER USER IF EXISTS u7 IDENTIFIED WITH 'sha256_password', u_failed1, u_failed2,
97 u8 WITH MAX_USER_CONNECTIONS 4;
98 Warnings:
99 Note 3162 Authorization ID 'u_failed1'@'%' does not exist.
100 Note 3162 Authorization ID 'u_failed2'@'%' does not exist.
101 Note 3162 Authorization ID 'u8'@'%' does not exist.
102 ALTER USER IF EXISTS u7, u_failed1, u_failed2 IDENTIFIED WITH
103 'sha256_password' by 'pwd_failed' WITH MAX_USER_CONNECTIONS 5;
104 Warnings:
105 Note 3162 Authorization ID 'u_failed1'@'%' does not exist.
106 Note 3162 Authorization ID 'u_failed2'@'%' does not exist.
107 ALTER USER IF EXISTS u7, u7 IDENTIFIED WITH 'sha256_password',
108 u8 WITH MAX_USER_CONNECTIONS 6;
109 Warnings:
110 Note 3162 Authorization ID 'u8'@'%' does not exist.
111 ALTER USER IF EXISTS u6 IDENTIFIED WITH 'sha256_password',
112 u7 IDENTIFIED WITH 'invalid_plugin', u8 WITH MAX_USER_CONNECTIONS 17;
113 ERROR HY000: Plugin 'invalid_plugin' is not loaded
114 ALTER USER IF EXISTS u6 IDENTIFIED WITH 'sha256_password',
115 u_failed1, r6, r7, u9 WITH MAX_USER_CONNECTIONS 7;
116 Warnings:
117 Note 3162 Authorization ID 'u_failed1'@'%' does not exist.
118 Note 3162 Authorization ID 'r7'@'%' does not exist.
119 Note 3162 Authorization ID 'u9'@'%' does not exist.
120 # Search warnings in the server log
121 Pattern "Following users were specified in CREATE USER" found
122 # DROP USER IF EXISTS statement test
123 DROP USER IF EXISTS u7, u8, u_failed;
124 Warnings:
125 Note 3162 Authorization ID 'u8'@'%' does not exist.
126 Note 3162 Authorization ID 'u_failed'@'%' does not exist.
127 DROP USER IF EXISTS u7, u8;
128 Warnings:
129 Note 3162 Authorization ID 'u7'@'%' does not exist.
130 Note 3162 Authorization ID 'u8'@'%' does not exist.
131 DROP USER IF EXISTS u_ne3, u_ne4, r55;
132 DROP USER IF EXISTS u_ne5, u_failed1, r6, u_failed2, r7;
133 Warnings:
134 Note 3162 Authorization ID 'u_failed1'@'%' does not exist.
135 Note 3162 Authorization ID 'u_failed2'@'%' does not exist.
136 Note 3162 Authorization ID 'r7'@'%' does not exist.
137 # DROP USER statement test
138 DROP USER u1, u2, u_ne1, u_ne2;
139 ERROR HY000: Operation DROP USER failed for 'u_ne1'@'%','u_ne2'@'%'
140 DROP ROLE r1, r2, u1, u_ne1;
141 ERROR HY000: Operation DROP ROLE failed for 'u_ne1'@'%'
142 DROP ROLE r1, r2, u5, u6, u1_failed;
143 ERROR HY000: Operation DROP ROLE failed for 'u5'@'%','u1_failed'@'%'
144 include/sync_slave_sql_with_master.inc
145 [connection master]
146 SET @sql_log_bin_saved = @@sql_log_bin;
147 SET sql_log_bin = 0;
148 CREATE TABLE test.user_2
149 SELECT * FROM mysql.user
150 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
151 ORDER BY user;
152 SET sql_log_bin = @sql_log_bin_saved;
153 [connection slave]
154 SET @sql_log_bin_saved = @@sql_log_bin;
155 SET sql_log_bin = 0;
156 CREATE TABLE test.user_2
157 SELECT * FROM mysql.user
158 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
159 ORDER BY user;
160 SET sql_log_bin = @sql_log_bin_saved;
161 # check that mysql.user table has same values in both servers
162 include/diff_tables.inc [master:test.user_2, slave:test.user_2]
163 # Try connecting with users created to ensure that connection successful
164 # Try connecting with user without ssl where user require ssl
165 # Connection will be denied
166 ERROR 28000: Access denied for user 'u44'@'localhost' (using password: YES)
167 # Cleanup
168 [connection master]
169 DROP ROLE IF EXISTS r1, r2, r33, r44, u1, u2, u33, u44, u55, u6;
170 DROP TABLE test.user_1;
171 DROP TABLE test.user_2;
172 include/sync_slave_sql_with_master.inc
173 include/rpl_end.inc