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

Enhance ALTER USER for multiple authentication methods

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

          is caused by

          Task - A task that needs to be done. MDEV-11340 Allow multiple alternative authentication methods for the same user

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21928 ALTER USER doesn't remove excess authentication plugins from mysql.global_priv

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              Unassigned Unassigned
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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