Debian Codegraph @

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

raw
1 ########### suite/rpl/t/rpl_atomic_user.test #########################
2 # #
3 # Testing of atomic user ddls. DDLs include #
4 # CREATE USER, ALTER USER, RENAME USER, DROP USER, GRANT, REVOKE, #
5 # CREATE USER IF EXISTS, ALTER USER IF EXISTS, DROP USER IF EXISTS. #
6 # When above ddls are executed, in case of error, none of the command#
7 # will be executed. If error does not occur, whole statement will be #
8 # executed. #
9 # #
10 # #
11 # Creation: #
12 # 2016-08-26 prabprad Implement this test as part of #
13 # WL#9045 Make user management DDLs atomic #
14 # #
15 ######################################################################
16 --source include/not_have_privilege_checks_user.inc
17 --source include/master-slave.inc
18
19 CALL mtr.add_suppression('Following users were specified in CREATE USER');
20 --echo # CREATE USER statement test where user does not exist
21 CREATE USER u1 IDENTIFIED WITH 'sha256_password' BY 'pwd1', u2 IDENTIFIED WITH
22 'mysql_native_password' BY 'pwd2', u3, u4, u5, u6 REQUIRE SSL;
23
24 --echo # CREATE USER statement test where few user exist and few does not
25 --error ER_CANNOT_USER
26 CREATE USER u_ne1, u1 IDENTIFIED WITH 'sha256_password' BY 'auth_string',
27 u_ne2 IDENTIFIED WITH 'sha256_password' REQUIRE SSL;
28 --error ER_PLUGIN_IS_NOT_LOADED
29 CREATE USER u_ne1, u1 IDENTIFIED WITH 'sha256_password' BY 'auth_string',
30 u_ne2 IDENTIFIED WITH 'invalid_plugin' REQUIRE SSL;
31 CREATE ROLE r1, r2;
32
33 --echo # ALTER USER statement test
34 ALTER USER u1 IDENTIFIED WITH 'mysql_native_password' BY 'pwd1', u2 IDENTIFIED
35 WITH 'sha256_password' BY 'pwd2' WITH MAX_USER_CONNECTIONS 11 ;
36 ALTER USER u3 IDENTIFIED WITH 'mysql_native_password' BY 'pwd3', u4 IDENTIFIED
37 WITH 'mysql_native_password' BY 'pwd4' WITH MAX_USER_CONNECTIONS 12;
38
39 --echo # RENAME USER statement test
40 RENAME USER u3 TO u33, u5 TO u55, u4 TO u44;
41 --error ER_CANNOT_USER
42 RENAME USER u3 TO u33, u4 TO u44;
43 --error ER_CANNOT_USER
44 RENAME USER u33 TO uu, u44 TO uu;
45
46 --echo # GRANT USER test for all grant types
47 # u1, u2, u33, u44, u55, u6, r1, r2 can be used.
48 use mysql;
49 CREATE DEFINER=u1 PROCEDURE p() SELECT host, user FROM mysql.user;
50
51 GRANT SELECT ON *.* to u1;
52 GRANT SELECT ON mysql.user to u2;
53 GRANT SELECT ON mysql.* TO u33;
54 GRANT SELECT (user, host) ON mysql.user TO u44;
55 GRANT EXECUTE ON PROCEDURE mysql.p TO u55;
56 GRANT proxy ON u1 TO u2;
57 GRANT ALL ON *.* TO u6;
58 --source include/sync_slave_sql_with_master.inc
59
60 # Bug#27627586: Exclude from the analysis of the content of mysql.user table
61 # the rows which were created during server startup, to prevent mismatches due
62 # to different startup between the master and the slave servers
63
64 --let $rpl_connection_name= master
65 --source include/rpl_connection.inc
66 SET @sql_log_bin_saved = @@sql_log_bin;
67 SET sql_log_bin = 0;
68 CREATE TABLE test.user_1
69 SELECT * FROM mysql.user
70 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
71 ORDER BY user;
72 SET sql_log_bin = @sql_log_bin_saved;
73
74 --let $rpl_connection_name= slave
75 --source include/rpl_connection.inc
76 SET @sql_log_bin_saved = @@sql_log_bin;
77 SET sql_log_bin = 0;
78 CREATE TABLE test.user_1
79 SELECT * FROM mysql.user
80 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
81 ORDER BY user;
82 SET sql_log_bin = @sql_log_bin_saved;
83
84 --echo # check that mysql.user table has same values in both servers
85 --let $diff_tables=master:test.user_1, slave:test.user_1
86 --source include/diff_tables.inc
87
88 --echo # REVOKE FROM USER statement test
89 --let $rpl_connection_name= master
90 --source include/rpl_connection.inc
91 REVOKE SELECT ON *.* FROM u1;
92 REVOKE SELECT ON mysql.user FROM u2;
93 REVOKE SELECT ON mysql.* FROM u33;
94 REVOKE SELECT (user, host) ON mysql.user FROM u44;
95 REVOKE EXECUTE ON PROCEDURE mysql.p FROM u55;
96 REVOKE PROXY ON u1 FROM u2;
97 REVOKE ALL ON *.* FROM u6;
98 DROP PROCEDURE p;
99
100 --echo # CREATE USER IF NOT EXISTS statement test
101 CREATE USER IF NOT EXISTS u7, u_ne3 IDENTIFIED WITH 'sha256_password';
102 CREATE USER IF NOT EXISTS u7, u_ne4, u_ne5 IDENTIFIED WITH 'sha256_password'
103 by 'pwd_u_ne5';
104 --error ER_PLUGIN_IS_NOT_LOADED
105 CREATE USER IF NOT EXISTS u5, u_failed1 IDENTIFIED WITH 'invalid_plugin',
106 u_failed2;
107 CREATE ROLE IF NOT EXISTS r33, r44;
108 CREATE ROLE IF NOT EXISTS r44, r55;
109 CREATE ROLE IF NOT EXISTS r44, r55;
110 CREATE USER IF NOT EXISTS u6, u6, r1, r2 IDENTIFIED WITH 'sha256_password', r6;
111
112 --echo # ALTER USER IF EXISTS statement test
113 ALTER USER IF EXISTS u_failed1, u_failed2 IDENTIFIED WITH 'sha256_password';
114 ALTER USER IF EXISTS u7 IDENTIFIED WITH 'sha256_password', u_failed1, u_failed2,
115 u8 WITH MAX_USER_CONNECTIONS 4;
116 ALTER USER IF EXISTS u7, u_failed1, u_failed2 IDENTIFIED WITH
117 'sha256_password' by 'pwd_failed' WITH MAX_USER_CONNECTIONS 5;
118 ALTER USER IF EXISTS u7, u7 IDENTIFIED WITH 'sha256_password',
119 u8 WITH MAX_USER_CONNECTIONS 6;
120 --error ER_PLUGIN_IS_NOT_LOADED
121 ALTER USER IF EXISTS u6 IDENTIFIED WITH 'sha256_password',
122 u7 IDENTIFIED WITH 'invalid_plugin', u8 WITH MAX_USER_CONNECTIONS 17;
123 ALTER USER IF EXISTS u6 IDENTIFIED WITH 'sha256_password',
124 u_failed1, r6, r7, u9 WITH MAX_USER_CONNECTIONS 7;
125
126 let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
127 # $server_log has to be processed by include/search_pattern.inc which
128 # contains Perl code requiring that the environment variable SEARCH_FILE points
129 # to this file.
130 let SEARCH_FILE= $server_log;
131 --echo # Search warnings in the server log
132 --let SEARCH_PATTERN= Following users were specified in CREATE USER
133 --source include/search_pattern.inc
134
135 --echo # DROP USER IF EXISTS statement test
136 DROP USER IF EXISTS u7, u8, u_failed;
137 DROP USER IF EXISTS u7, u8;
138 DROP USER IF EXISTS u_ne3, u_ne4, r55;
139 DROP USER IF EXISTS u_ne5, u_failed1, r6, u_failed2, r7;
140
141 --echo # DROP USER statement test
142 --error ER_CANNOT_USER
143 DROP USER u1, u2, u_ne1, u_ne2;
144 --error ER_CANNOT_USER
145 DROP ROLE r1, r2, u1, u_ne1;
146 --error ER_CANNOT_USER
147 DROP ROLE r1, r2, u5, u6, u1_failed;
148
149 --source include/sync_slave_sql_with_master.inc
150
151 # Bug#27627586: Exclude from the analysis of the content of mysql.user table
152 # the rows which were created during server startup, to prevent mismatches due
153 # to different startup between the master and the slave servers
154
155 --let $rpl_connection_name= master
156 --source include/rpl_connection.inc
157 SET @sql_log_bin_saved = @@sql_log_bin;
158 SET sql_log_bin = 0;
159 CREATE TABLE test.user_2
160 SELECT * FROM mysql.user
161 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
162 ORDER BY user;
163 SET sql_log_bin = @sql_log_bin_saved;
164
165 --let $rpl_connection_name= slave
166 --source include/rpl_connection.inc
167 SET @sql_log_bin_saved = @@sql_log_bin;
168 SET sql_log_bin = 0;
169 CREATE TABLE test.user_2
170 SELECT * FROM mysql.user
171 WHERE user != 'root' AND user NOT LIKE 'mysql.%'
172 ORDER BY user;
173 SET sql_log_bin = @sql_log_bin_saved;
174
175 --echo # check that mysql.user table has same values in both servers
176 --let $diff_tables=master:test.user_2, slave:test.user_2
177 --source include/diff_tables.inc
178 --echo # Try connecting with users created to ensure that connection successful
179 connect (conssl1,localhost,u1,pwd1,,,,SSL);
180 connect (conssl2,localhost,u2,pwd2,,,,SSL);
181 connect (conssl3,localhost,u33,pwd3,,,,SSL);
182 connect (conssl4,localhost,u44,pwd4,,,,SSL);
183 --echo # Try connecting with user without ssl where user require ssl
184 --echo # Connection will be denied
185 --disable_query_log
186 --error ER_ACCESS_DENIED_ERROR
187 connect (conssl5,localhost,u44,pwd4,,,,);
188 --enable_query_log
189
190 DISCONNECT conssl1;
191 DISCONNECT conssl2;
192 DISCONNECT conssl3;
193 DISCONNECT conssl4;
194
195 --echo # Cleanup
196 --let $rpl_connection_name= master
197 --source include/rpl_connection.inc
198 DROP ROLE IF EXISTS r1, r2, r33, r44, u1, u2, u33, u44, u55, u6;
199 DROP TABLE test.user_1;
200 DROP TABLE test.user_2;
201 --source include/sync_slave_sql_with_master.inc
202 --source include/rpl_end.inc