Debian Codegraph @

mysql-test/suite/rpl/r/rpl_alter_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 SET @old_sql_mode_master= @@session.sql_mode;
7 SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));
8 SET @old_sql_mode_slave= @@session.sql_mode;
9 SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));
10 SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
11 CREATE USER 'user1'@'localhost' IDENTIFIED WITH sha256_password BY 'auth_string1';
12 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
13 include/show_binlog_events.inc
14 Log_name Pos Event_type Server_id End_log_pos Info
15 master-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>\'a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
16 include/sync_slave_sql_with_master.inc
17 include/show_binlog_events.inc
18 Log_name Pos Event_type Server_id End_log_pos Info
19 slave-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>\'a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
20 SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';
21 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret1';
22 include/sync_slave_sql_with_master.inc
23 SELECT USER();
24 USER()
25 user1@localhost
26 SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
27 SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
28 SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
29 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
30 include/show_binlog_events.inc
31 Log_name Pos Event_type Server_id End_log_pos Info
32 master-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>''a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
33 include/sync_slave_sql_with_master.inc
34 include/show_binlog_events.inc
35 Log_name Pos Event_type Server_id End_log_pos Info
36 slave-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'sha256_password' AS '$5$BVZy9O>''a+2MH]_?$fpWyabcdiHjfCVqId/quykZzjaA7adpkcen/uiQrtmOK4p4'
37 SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';
38 SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret2';
39 include/sync_slave_sql_with_master.inc
40 SELECT USER();
41 USER()
42 user1@localhost
43 #
44 # WL#11544: Current password required for SET PASSWORD
45 # Create users with current password require clauses.
46 #
47 [connection master]
48 CREATE USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
49 ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
50 ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT;
51 SET PASSWORD FOR '11544_u1'@'localhost'='haha';
52 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe';
53 #
54 # To check that failed statements are not replicated to binlog
55 #
56 SET PASSWORD ='ahaha';
57 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
58 SET PASSWORD FOR '11544_u1'@'localhost'='ahaha';
59 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
60 ALTER USER user() IDENTIFIED BY 'ahaha';
61 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
62 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha';
63 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
64 SET PASSWORD='ahaha' REPLACE 'xyz';
65 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
66 SET PASSWORD FOR '11544_u1'@'localhost'='ahaha' REPLACE 'xyz';
67 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
68 ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'xyz';
69 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
70 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha' REPLACE 'xyz';
71 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
72 #
73 # To check that REPLACE clause is not reflected in the binlog
74 #
75 SET PASSWORD='ahaha' REPLACE 'hehe';
76 SET PASSWORD FOR '11544_u1'@'localhost'='hehe' REPLACE 'ahaha';
77 ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'hehe';
78 ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe' REPLACE 'ahaha';
79 # Check the binlog contents on the master
80 include/show_binlog_events.inc
81 Log_name Pos Event_type Server_id End_log_pos Info
82 master-bin.000001 # Query # # use `test`; CREATE USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' PASSWORD REQUIRE CURRENT OPTIONAL
83 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT
84 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT
85 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
86 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
87 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
88 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
89 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
90 master-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
91 # Check the binlog contents on the slave
92 include/sync_slave_sql_with_master.inc
93 [connection slave]
94 include/show_binlog_events.inc
95 Log_name Pos Event_type Server_id End_log_pos Info
96 slave-bin.000001 # Query # # use `test`; CREATE USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' PASSWORD REQUIRE CURRENT OPTIONAL
97 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT
98 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT
99 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
100 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
101 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
102 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
103 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
104 slave-bin.000001 # Query # # use `test`; ALTER USER '11544_u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
105 # Check that we are successfully able to login to the slave from
106 # the user whose password is changed on master
107 SELECT USER();
108 USER()
109 11544_u1@localhost
110 [connection master]
111 DROP USER '11544_u1'@'localhost';
112 * Create a new user with random password
113 include/sync_slave_sql_with_master.inc
114 [connection slave]
115 SELECT CURRENT_USER();
116 CURRENT_USER()
117 u1@localhost
118 [connection master]
119 * Set random password using SET PASSWORD
120 include/sync_slave_sql_with_master.inc
121 [connection slave]
122 SELECT CURRENT_USER();
123 CURRENT_USER()
124 u1@localhost
125 [connection master]
126 * Set random password using ALTER USER
127 include/sync_slave_sql_with_master.inc
128 [connection slave]
129 SELECT CURRENT_USER();
130 CURRENT_USER()
131 u1@localhost
132 [connection master]
133 DROP USER u1@localhost;
134 * Check the binlog contents on the master
135 include/show_binlog_events.inc
136 Log_name Pos Event_type Server_id End_log_pos Info
137 master-bin.000001 # Query # # use `test`; CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
138 master-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
139 master-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
140 master-bin.000001 # Query # # use `test`; DROP USER u1@localhost
141 * Create a new user with an arbitrary attribute
142 CREATE USER u1@localhost IDENTIFIED BY 'foo' ATTRIBUTE '{"trackingId": "12345"}';
143 CREATE USER u2@localhost COMMENT 'This is account is used by my private LAMP project';
144 include/sync_slave_sql_with_master.inc
145 [connection slave]
146 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1' OR USER = 'u2';
147 USER HOST ATTRIBUTE
148 u1 localhost {"trackingId": "12345"}
149 u2 localhost {"comment": "This is account is used by my private LAMP project"}
150 [connection master]
151 ALTER USER u1@localhost COMMENT 'TODO: Delete this user';
152 include/sync_slave_sql_with_master.inc
153 [connection slave]
154 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1';
155 USER HOST ATTRIBUTE
156 u1 localhost {"comment": "TODO: Delete this user", "trackingId": "12345"}
157 include/assert.inc [User JSON comment updated successfully]
158 include/assert.inc [User JSON attribute updated successfully]
159 [connection master]
160 ALTER USER u2@localhost ATTRIBUTE '{ "code": "red" }';
161 ALTER USER u2@localhost COMMENT 'Change of mind';
162 include/sync_slave_sql_with_master.inc
163 [connection slave]
164 SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u2';
165 USER HOST ATTRIBUTE
166 u2 localhost {"code": "red", "comment": "Change of mind"}
167 include/assert.inc [User JSON comment updated successfully]
168 include/assert.inc [User JSON attribute updated successfully]
169 [connection master]
170 DROP USER u1@localhost;
171 DROP USER u2@localhost;
172 * Check the binlog contents on the master
173 include/show_binlog_events.inc
174 Log_name Pos Event_type Server_id End_log_pos Info
175 master-bin.000001 # Query # # use `test`; CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '<non-deterministic-password-hash>'
176 master-bin.000001 # Query # # use `test`; CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'caching_sha2_password' COMMENT 'This is account is used by my private LAMP project'
177 master-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' COMMENT 'TODO: Delete this user'
178 master-bin.000001 # Query # # use `test`; ALTER USER 'u2'@'localhost' ATTRIBUTE '{ "code": "red" }'
179 master-bin.000001 # Query # # use `test`; ALTER USER 'u2'@'localhost' COMMENT 'Change of mind'
180 master-bin.000001 # Query # # use `test`; DROP USER u1@localhost
181 master-bin.000001 # Query # # use `test`; DROP USER u2@localhost
182 SET @@session.sql_mode= @old_sql_mode_slave;
183 SET @@session.sql_mode= @old_sql_mode_master;
184 DROP USER 'user1'@'localhost';
185 include/rpl_end.inc