MySQL user password vs authentication_string

mariadbMySQLpermissions

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

Best Answer

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 |
+-------------------------------------------+
Related Question