Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16716

syntax error with "create user .. identified via"

    XMLWordPrintable

Details

    Description

      Hi,

      I'd like to create a new user using the syntax stated in the knowledge base, but all I get is a syntax error:

      show plugins;
      -- +-------------------------------+----------+--------------------+---------+---------+
      -- | Name                          | Status   | Type               | Library | License |
      -- +-------------------------------+----------+--------------------+---------+---------+
      -- | binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
      -- | mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
      -- | mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
      -- | ...
      create user 'someone'@'localhost' identified via 'mysql_native_password' by 'foo';
      -- 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 'foo'' at line 1
      create user 'someone'@'localhost' identified via 'mysql_native_password' as 'foo';
      -- ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
      select password("foo");
      -- +-------------------------------------------+
      -- | password("foo")                           |
      -- +-------------------------------------------+
      -- | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
      -- +-------------------------------------------+
      create user 'someone'@'localhost' identified via 'mysql_native_password' as password('foo');
      -- 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 'password('foo')' at line 1
      

      Same results for VIA/WITH, BY/AS/USING, having the authentication plugin either with or without quotation marks.
      A possible workaround is to set the password after creating the user:

      create user 'someone'@'localhost';
      set password for 'someone'@'localhost' = password("foo");
      select user,host,password,authentication_string,plugin from mysql.user where user = "someone";
      -- +---------+-----------+----------+-------------------------------------------+-----------------------+
      -- | user    | host      | password | authentication_string                     | plugin                |
      -- +---------+-----------+----------+-------------------------------------------+-----------------------+
      -- | someone | localhost |          | *F3A2A51A9B0F2BE2468926B4132313728C250DBF | mysql_native_password |
      -- +---------+-----------+----------+-------------------------------------------+-----------------------+
      

      What am I missing?

      Best,
      Philippe

      Attachments

        Activity

          People

            serg Sergei Golubchik
            phil Philippe Kueck
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.