[MDEV-11180] CREATE USER .. IDENTIFIED WITH .. BY .. is not supported Created: 2016-10-30  Updated: 2018-05-23  Resolved: 2018-05-22

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Authentication and Privilege System
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Elena Stepanova Assignee: Vicențiu Ciorbaru
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7978 CREATE/ALTER USER as in 5.7 Closed
relates to MDEV-12321 authentication plugin: SET PASSWORD s... Closed
Sprint: 10.2.6-2, 10.2.6-3, 10.3.1-1

 Description   

According to MDEV-7978, 5.7 account management syntax related to IDENTIFIED clause should be supported. The following variation is not:

CREATE USER ... IDENTIFIED WITH ... BY ...

10.2

 
MariaDB [test]> create user foo@localhost identified with 'auth_socket' by 'abc';
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'by 'abc'' at line 1
 
MariaDB [test]> select @@version;
 
+----------------------+
 
| @@version            |
 
+----------------------+
 
| 10.2.3-MariaDB-debug |
 
+----------------------+
 
1 row in set (0.00 sec)

MySQL 5.7

 
MySQL [test]> create user foo@localhost identified with 'auth_socket' by 'abc';Query OK, 0 rows affected (0.00 sec)
 
 
 
MySQL [test]> select @@version;
 
+--------------+
 
| @@version    |
 
+--------------+
 
| 5.7.16-debug |
 
+--------------+
 
1 row in set (0.00 sec)

I'm not sure if it is supposed to work, but it's worth checking.



 Comments   
Comment by Vicențiu Ciorbaru [ 2017-03-25 ]

Investigated how MySQL does this. First, their documentation mentions:

IDENTIFIED WITH auth_plugin BY 'auth_string'
 
 
 
Sets the account authentication plugin to auth_plugin, hashes the cleartext 'auth_string' value, and stores the result in the mysql.user account row.

This doesn't explain how the hash is calculated. Trying out tests in the MySQL server reveals the following:

When doing:

create user foo@localhost identified with 'sha256_password' by 'abc';

We get a different hash value then:

create user foo@localhost identified with 'mysql_native_password' by 'abc';

This hints that the logic is different between authentication plugins. Looking for the code that detects this:

  if (Str->uses_identified_by_clause ||
 
      (Str->auth.length == 0 && !user_exists))
 
  {
 
    st_mysql_auth *auth= (st_mysql_auth *) plugin_decl(plugin)->info;
 
    inbuf= Str->auth.str;
 
    inbuflen= Str->auth.length;
 
    if (auth->generate_authentication_string(outbuf,
 
                                             &buflen,
 
                                             inbuf,
 
                                             inbuflen))
 
....

We find this function call that takes the authentication string passed by the parser and passes it forward to the plugin itself. This is newly introduced API for authentication plugins.

struct st_mysql_auth
 
{
 
...
 
  /**
 
    New plugin API to generate password digest out of authentication string.
 
    This function will first invoke a service to check for validity of the
 
    password based on the policies defined and then generate encrypted hash
 
 
 
    @param[OUT]   outbuf      A buffer provided by server which will hold the
 
                              authentication string generated by plugin.
 
    @param[INOUT] outbuflen   Length of server provided buffer as IN param and
 
                              length of plugin generated string as OUT param.
 
    @param[IN]    inbuf       auth string provided by user.
 
    @param[IN]    inbuflen    auth string length.
 
 
 
    @retval  0 OK
 
             1 ERROR
 
 
 
  */
 
  int (*generate_authentication_string)(char *outbuf,
 
      unsigned int *outbuflen, const char *inbuf, unsigned int inbuflen);
 
...
 
}

Our current implementation does not allow for easy introduction of this behavior. I would not do it in 10.2. I would adjust the documentation to not allow BY syntax for IDENTIFIED WITH <plugin> BY <not_hashed_auth_string>, as it currently is in the server code. We could implement this in 10.3.
serg Thoughts?

Comment by Sergei Golubchik [ 2017-03-26 ]

Agree. In fact, I've created MDEV-12321 few days ago which is exactly about extending the Auth API to have a method for hashing passwords. It's marked for 10.3.

Although I didn't think about IDENTIFIED WITH ... BY ... — I find it ridiculous to have all the that variants:

CREATE USER foo@bar IDENTIFIED { VIA  | WITH } plugin { USING | AS | BY } "data";

while keeping in mind that with BY "data" is a plain-text password, while with other variants it's a hash. BY does not have this meaning, it's something one has to remember for this specific use case.

I thought about something like

CREATE USER foo@bar IDENTIFIED { VIA  | WITH } plugin { USING | AS } PASSWORD("data");

which would match the existing syntax of

SET PASSWORD = "data";
 
SET PASSWORD = PASSWORD("data");

So, "data" by default is a hash of the password. That is, "data" is exactly what's stored in mysql.user.auth_string column. And by using PASSWORD(...) syntax one can call the plugin method to convert the "data" into the format that goes into mysql.user.auth_string column.

Generated at Thu Feb 08 07:47:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.