Debian Codegraph @

mysql-test/suite/rpl/t/rpl_user.test

raw
1 --source include/not_group_replication_plugin.inc
2 --source include/not_have_privilege_checks_user.inc
3
4 # BUG#33862 completely failed DROP USER statement gets replicated
5 --source include/master-slave.inc
6
7 #
8 # remove all users will be used in the test
9 #
10 connection master;
11 set session sql_log_bin=0;
12 delete from mysql.user where Host='fakehost';
13 set session sql_log_bin=1;
14
15 connection slave;
16 set session sql_log_bin=0;
17 delete from mysql.user where Host='fakehost';
18 set session sql_log_bin=1;
19
20 #
21 # Test create user
22 #
23 connection master;
24 create user 'foo'@'fakehost';
25 --error ER_CANNOT_USER
26 create user 'foo'@'fakehost', 'bar'@'fakehost';
27 create user 'bar'@'fakehost';
28
29 # In log event, Plaintext password 'foo1' is replaced by ciphertext.
30 create user 'foo1'@'fakehost' IDENTIFIED WITH 'mysql_native_password' BY 'foo1',
31 'foo2'@'fakehost' IDENTIFIED WITH 'mysql_native_password' AS '*1111111111111111111111111111111111111111',
32 'foo3'@'fakehost';
33 --source include/sync_slave_sql_with_master.inc
34 select Host,User from mysql.user where Host='fakehost';
35 --let $diff_tables= master:mysql.user, slave:mysql.user
36 source include/diff_tables.inc;
37
38 #
39 # Test rename user
40 #
41 connection master;
42 rename user 'foo'@'fakehost' to 'foofoo'@'fakehost';
43 --error ER_CANNOT_USER
44 rename user 'not_exist_user1'@'fakehost' to 'foobar'@'fakehost', 'bar'@'fakehost' to 'barbar'@'fakehost';
45 --error ER_CANNOT_USER
46 rename user 'not_exist_user1'@'fakehost' to 'foobar'@'fakehost', 'not_exist_user2'@'fakehost' to 'barfoo'@'fakehost';
47
48 --source include/sync_slave_sql_with_master.inc
49 select Host,User from mysql.user where Host='fakehost';
50
51 #
52 # Test drop user
53 #
54 connection master;
55 drop user 'foofoo'@'fakehost';
56 drop user 'bar'@'fakehost';
57 drop user 'foo1'@'fakehost', 'foo2'@'fakehost', 'foo3'@'fakehost';
58 --error ER_CANNOT_USER
59 drop user 'not_exist_user1'@'fakehost', 'barbar'@'fakehost';
60 --error ER_CANNOT_USER
61 drop user 'not_exist_user1'@'fakehost', 'not_exist_user2'@'fakehost';
62
63 --source include/sync_slave_sql_with_master.inc
64 select Host,User from mysql.user where Host='fakehost';
65
66
67 --echo #
68 --echo # WL2392: "Change Password at next login" (initial default for root)
69 --echo #
70
71 connection master;
72 CREATE USER must_change2@localhost IDENTIFIED BY 'aha';
73 ALTER USER must_change2@localhost PASSWORD EXPIRE;
74 --source include/sync_slave_sql_with_master.inc
75 select Host,User,password_expired from mysql.user where user='must_change2';
76
77 connect(must_change_con_slave,localhost,must_change2,aha,test,$SLAVE_MYPORT,);
78 connection must_change_con_slave;
79 --echo # must throw an error
80 --error ER_MUST_CHANGE_PASSWORD
81 SELECT USER();
82 connection master;
83 disconnect must_change_con_slave;
84
85 connect(must_change_con_master,localhost,must_change2,aha);
86 connection must_change_con_master;
87 --echo # setting a password unlocks it
88 SET PASSWORD = 'aha2';
89 connection master;
90 disconnect must_change_con_master;
91
92 --source include/sync_slave_sql_with_master.inc
93 connection master;
94
95 connect(must_change_con_slave,localhost,must_change2,aha2,test,$SLAVE_MYPORT,);
96 connection must_change_con_slave;
97 --echo # must not throw an error
98 SELECT USER();
99 connection master;
100 disconnect must_change_con_slave;
101
102 DROP USER must_change2@localhost;
103
104 --source include/sync_slave_sql_with_master.inc
105 connection master;
106
107 --echo #
108 --echo # WL#9591: Caching sha2 authentication plugin
109 --echo #
110
111 connection master;
112 CREATE USER 9591_user@localhost IDENTIFIED WITH caching_sha2_password BY 'abcd' REQUIRE NONE;
113 --source include/sync_slave_sql_with_master.inc
114 SELECT Host, User, Plugin FROM mysql.user WHERE user='9591_user';
115
116 connect(9591_user_con_slave, localhost, 9591_user, abcd, test, $SLAVE_MYPORT,,SSL);
117 connection 9591_user_con_slave;
118 SELECT CURRENT_USER();
119 connection master;
120 disconnect 9591_user_con_slave;
121
122 connect(9591_user_con_master, localhost, 9591_user, abcd,,,,SSL);
123 connection 9591_user_con_master;
124 SELECT CURRENT_USER();
125
126 connection master;
127 disconnect 9591_user_con_master;
128
129 DROP USER 9591_user@localhost;
130
131 --source include/sync_slave_sql_with_master.inc
132 connection master;
133
134 --echo #
135 --echo # WL#11544: Current password required for SET PASSWORD
136 --echo # Create users with current password require clauses.
137 --echo #
138
139 connection master;
140 --echo [connection master]
141 CREATE USER 11544_u1@localhost REQUIRE NONE PASSWORD REQUIRE CURRENT;
142 CREATE USER 11544_u2@localhost REQUIRE NONE PASSWORD REQUIRE CURRENT OPTIONAL;
143 CREATE USER 11544_u3@localhost REQUIRE NONE PASSWORD REQUIRE CURRENT DEFAULT;
144
145 --source include/sync_slave_sql_with_master.inc
146 --echo [connection slave]
147 SELECT user, Password_require_current FROM mysql.user WHERE user like '11544_u%';
148
149 connection master;
150 --echo [connection master]
151 DROP USER 11544_u1@localhost;
152 DROP USER 11544_u2@localhost;
153 DROP USER 11544_u3@localhost;
154
155 #
156 # show the binlog events on the master
157 #
158 connection master;
159 --let $mask_user_password_events=1
160 source include/show_binlog_events.inc;
161 --let $mask_user_password_events=0
162 --source include/rpl_end.inc