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

Enhance ALTER USER for multiple authentication methods

    XMLWordPrintable

    Details

      Description

      Ideally, it would probably make sense to provide ALTER USER syntax that supports multiple types of operations related to the multiple authentication methods functionality introduced by MDEV-11340.

      For example, let's say that we have the default root@localhost account:

       
      MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost';
      +----------------------------------------------------------------------------------------------------+
      | CREATE USER for root@localhost                                                                     |
      +----------------------------------------------------------------------------------------------------+
      | CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket |
      +----------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      Users might want ALTER USER statements like:

      • An ALTER USER statement to set the authentication methods to exactly what the statement says, and discard any existing ones. I think the syntax for that would be the currently supported syntax:

      -- change authentication method, discard existing ones
      ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password');
      

      • An ALTER USER statement to modify an existing authentication method, and leave the user account's other existing authentication methods as-is. This syntax might make sense:

      -- change existing authentication method, keep other existing ones too
      ALTER USER 'root'@'localhost' MODIFY IDENTIFIED VIA mysql_native_password USING PASSWORD('password');
      

      • An ALTER USER statement to add a new authentication method to the beginning of the list, and leave other existing authentication methods as-is. This syntax might make sense:

      -- add new authentication method to beginning, keep existing ones too
      ALTER USER 'root'@'localhost' ADD IDENTIFIED VIA pam USING 'mariadb' FIRST;
      

      • An ALTER USER statement to add a new authentication method to the end of the list, and leave other existing authentication methods as-is. This syntax might make sense:

      -- add new authentication method to end, keep existing ones too
      ALTER USER 'root'@'localhost' ADD IDENTIFIED VIA pam USING 'mariadb' LAST;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              ralf.gebhardt@mariadb.com Ralf Gebhardt
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: