1

I run following commands:

CREATE USER 'dbuser'@'%' IDENTIFIED BY PASSWORD('mypass');
CREATE USER 'dbuserx'@'%' IDENTIFIED BY PASSWORD('mypass');
SET PASSWORD FOR 'dbuser'@'%' = PASSWORD('mypass');

And that results in:

MariaDB [(none)]> select host, user, password,authentication_string from mysql.user;
+------------------+---------+-------------------------------------------+-------------------------------------------+
| host             | user    | password                                  | authentication_string                     |
+------------------+---------+-------------------------------------------+-------------------------------------------+
| %                | dbuser  |                                           | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| %                | dbuserx | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |                                           |
+------------------+---------+-------------------------------------------+-------------------------------------------+

Is this proper? I read there is PAM in MariaDB by default, but why user creation does not store password in authentication_string right from start?

Could someone explain the difference and possible problems with this?

Mariadb 10.3

  • As stated by manuals CREATE USER syntax is the next: `IDENTIFIED BY PASSWORD 'hash_string' (no patentheses around pass string). Here PASSWORD isn't a function call but lexical part of the query. – Kondybas Jul 7 '18 at 15:02
2

All of your CREATE USER syntax is 'backwards'. In the "native password" scheme:

mysql> CREATE USER 'se211604p'@'localhost'
               IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'se211604m'@'localhost'
               IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, password from user where user like 'se2%';
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| se211604p | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| se211604m | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-----------+-----------+-------------------------------------------+

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
  • But does it pose any kind of problems in future? – Gacek Jul 8 '18 at 8:49
  • @Gacek - MySQL is moving away from "native password" into various PAM alternatives. Is that the kind of 'future problem' you are asking about? – Rick James Jul 8 '18 at 15:32
  • More like, if I use CREATE USER should I follow it with SET PASSWORD to ensure that pass is stored in auth field instead of password field. Or should I not care about it and keeping password password field will not pose any problems? – Gacek Jul 9 '18 at 10:42
0

According to the page

mysql.user Table

at the very bottom:

When the plugin column is empty, MariaDB defaults to authenticating accounts with either the mysql_native_password or the mysql_old_password plugins. It decides which based on the hash used in the value for the Password column. When there's no password set or when the 4.1 password hash is used, (which is 41 characters long), MariaDB uses the mysql_native_password plugin. The mysql_old_password plugin is used with pre-4.1 password hashes, (which are 16 characters long).

MariaDB also supports the use of alternative authentication plugins. When the plugin column is not empty for the given account, MariaDB uses it to authenticate connection attempts. The specific plugin then uses the value of either the Password column or the authentication_string column to authenticate the user.

There may or there may not be PAM (pluggable authentication module) authentication. A number of implementations exist, see:

Password, Authentication and Encryption Plugins

So for example:

SELECT user, host,
  CONCAT(SUBSTR(password,1,5),':',LENGTH(password)) 
   AS pass,
  CONCAT(SUBSTR(authentication_string,1,5),':',LENGTH(authentication_string)) 
   AS auth,
  plugin
  FROM user;

gives

+---------------+------------+----------+----------+-----------------------+
| user          | host       | pass     | auth     | plugin                |
+---------------+------------+----------+----------+-----------------------+
| root          | %          | *687E:41 | *667F:41 | mysql_native_password |
| joe.bloggs    | %          | *E123:41 | *E1E9:41 | mysql_native_password |
| chloe.price   | %          | *FFA0:41 | :0       |                       |
| max.caulfield | %          | *981D:41 | :0       |                       |
| doge          | %          | *A6B1:41 | :0       |                       |
+---------------+------------+----------+----------+-----------------------+

The last three users have no plugin configured, so the value of column password counts. As the values therein are strings of length 41 (which is actually the maximum, the type of that column being char(41)), the mysql_native_password plugin is used.

The first two users are forcibly authenticated using the mysql_native_password plugin. It is left unspecified whether that plugin gets its input from the password column or the authentication_string column in case it needs more flexibility; it depends on the implementation! As authentication_string is of type TEXT, there can be a lot of plugin-specific stuff in there, from SHA-2 hashes longer than 41 characters to possibly whole certificate chains.

In the present case, there does not seem to be a need to have the password hash additionally in column authentication_string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.