Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
Description
As per the MariaDB doc,
NO_AUTO_CREATE_USER : Don't automatically create users with GRANT. Produce a 1133 error: "Can't find any matching row in the user table". Default since MariaDB 10.1.7.
https://mariadb.com/kb/en/library/sql-mode/
https://mariadb.com/kb/en/library/grant/
But we need to specify that "unless authentication information is specified. The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH"
NO_AUTO_CREATE_USER
Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless authentication information is specified. The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.
https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
[nil@centos68 master]$ mysql -uroot -p --socket=/tmp/mysql_sandbox27753.sock
|
Enter password:
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 3
|
Server version: 10.1.33-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [(none)]>
|
MariaDB [(none)]> show variables like '%sql_mode%' ;
|
+---------------+--------------------------------------------+
|
| Variable_name | Value |
|
+---------------+--------------------------------------------+
|
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
+---------------+--------------------------------------------+
|
1 row in set (0.01 sec)
|
|
|
MariaDB [(none)]> GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED BY '';
|
ERROR 1133 (28000): Can't find any matching row in the user table
|
MariaDB [(none)]>
|
MariaDB [(none)]> GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED VIA PAM using 'mariadb' require ssl ;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> select host, user from mysql.user where user='user123' ;
|
+------+----------+
|
| host | user |
|
+------+----------+
|
| % | user123 |
|
+------+----------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]>
|