WL#8326: Make NO_AUTO_CREATE_USER sql_mode behavior the default in 5.7

This work changes the default of sql_mode to include "NO_AUTO_CREATE_USER" (was: not included).

This is to prevent the GRANT statement from automatically creating new user accounts unless authentication information is specified.

Functional Requirements

F-1 NO_AUTO_CREATE_USER mode added to the default set (compiled-in) of the sql_mode global variable.

F-2 Server reports missing NO_AUTO_CREATE_USER mode of the sql_mode variable during boot time. The initial value of the sql_mode is stored in the configuration file (*.cnf).

F-3 Switching on and off NO_AUTO_CREATE_USER mode of the sql_mode variable reports warning.

MySQL default sql_mode value

S-1 Initializing sql_mode to its default value sets NO_AUTO_CREATE_USER mode on (F-1):

mysql> set sql_mode = default;
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+

NO_AUTO_CREATE_USER is the newly added value among others (F-1).

S-2 The server run with the --no-defaults option must produce same values as presented in S-1.

MySQL boot

S-3 Overridden sql_mode variable value in the configuration file (*.cnf), which is missing NO_AUTO_CREATE_USER mode, should produce warning during server startup (F-2):

[Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.

Turning 'NO_AUTO_CREATE_USER' mode on and off

S-4 Explicit switching on and off the NO_AUTO_CREATE_USER sql mode causes the warning (F-3):

mysql> set sql_mode = default;
mysql> set sql_mode = '';
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. 
|         |      | It will be removed in a future release.
+---------+------+-----------------------------------------------------------+
mysql> set sql_mode = 'NO_AUTO_CREATE_USER';
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. 
|         |      | It will be removed in a future release.
+---------+------+-----------------------------------------------------------+

S-5 When the NO_AUTO_CREATE_USER sql mode remains unaffected by the set sql_mode query, no warning is produced:

mysql> set sql_mode = default;
mysql> set sql_mode = 'NO_AUTO_CREATE_USER';
mysql> show warnings;
0 row in set (0.00 sec)

S-6 Setting sql_mode to its default value does not produce any warning, no matter whether the initial value of the sql_mode has not contained NO_AUTO_CREATE_USER mode.

User creation using GRANT statement

S-7 User creation using the GRANT statement is blocked with the default settings. It produces error.

mysql> GRANT ALL ON *.* TO user1@localhost;
ERROR 1133 (42000): Can't find any matching row in the user table

It's still possible create a user with the NO_AUTO_CREATE_USER mode removed:

mysql> set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO user1@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MTR test suite

Changed default behavior of the server resulted in the need of removal of the 'NO_AUTO_CREATE_USER' mode during creation of the user using the GRANT statement. Following strategies have been use to minimalize impact in the test:

• Removal of the 'NO_AUTO_CREATE_USER' mode (session only) just for GRANT statements, where the user is being created once

+set @orig_sql_mode= @@sql_mode;
+set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
 grant usage on *.* to mysqltest_1@localhost;
+set sql_mode= @orig_sql_mode;

• Removal of the 'NO_AUTO_CREATE_USER' mode (session only) for the entire test file, where GRANT statements are spread in the whole file

+set @orig_sql_mode= @@sql_mode;
+set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
 ...
 grant usage on *.* to mysqltest_1@localhost;
 ...
 grant usage on *.* to mysqltest_2@localhost;
 ...
+set sql_mode= @orig_sql_mode;
 EOF

• Removal of the 'NO_AUTO_CREATE_USER' mode (global and session) for the entire test file, where GRANT statements are used by the connecting clients

+set @orig_sql_mode_session= @@SESSION.sql_mode;
+set @orig_sql_mode_global= @@GLOBAL.sql_mode;
+set GLOBAL sql_mode= (select replace(@@GLOBAL.sql_mode,'NO_AUTO_CREATE_USER',''));
+set SESSION sql_mode= (select replace(@@SESSION.sql_mode,'NO_AUTO_CREATE_USER','')); 
 ...
 grant usage on *.* to mysqltest_1@localhost;
 ...
 grant usage on *.* to mysqltest_2@localhost;
 ...
+set GLOBAL sql_mode= @orig_sql_mode_global;
+set SESSION sql_mode= @orig_sql_mode_session;
 EOF

Numerous of *.result files have been modified, due to the fact that set sql_mode = default throws the warning:

+set sql_mode= default;
+Warnings:
+Warning        3090    Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.