[Mysql] mysql参数NO_AUTO_CREATE_USER

1、sql_mode存在NO_AUTO_CREATE_USER情况下：

root@db 01:05:  [(none)]> show variables like '%sql_mode%';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

root@db 01:05:  [(none)]> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | zabbix        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
| localhost | zabbix        |
+-----------+---------------+
5 rows in set (0.01 sec)

在包含NO_AUTO_CREATE_USER的情况下，使用grant去授权一个不存在的用户，必须要使用identified by参数或者identified with插件指定密码，才能够创建用户并授权成功：

root@db 01:07:  [(none)]> grant all privileges on *.* to testuser;
ERROR 1133 (42000): Can't find any matching row in the user table
root@db 01:07:  [(none)]> grant all privileges on *.* to testuser identified by 'testuser';
Query OK, 0 rows affected, 1 warning (0.01 sec)

2、sql_mode不存在NO_AUTO_CREATE_USER情况下

root@db 01:14:  [(none)]> set @@sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@db 01:17:  [(none)]> show variables like '%sql_mode%';
+---------------+----------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                              |
+---------------+----------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
root@db 01:17:  [(none)]> grant all privileges on *.* to testuser1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

没有密码也能够创建该用户，并授权成功，使用该用户没有密码可以直接登录

root@db 01:17:  [(none)]> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | testuser      |
| %         | testuser1     |
| %         | zabbix        |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
| localhost | zabbix        |
+-----------+---------------+
7 rows in set (0.00 sec)

3、总结：

NO_AUTO_CREATE_USER参数在未来将会被弃用，被弃用的原因是基于对复制安全性的考虑。而基于复制安全性的考虑，对用户进行管理操作的更好方法是使用：CREATE USER IF NOT EXISTS, DROP USER IF EXISTS, 和ALTER USER IF EXISTS而不是grant命令。这些语句可以保证复制的安全性当从库存在和主库的不同权限。