We need to change the auth api to support plugins that have the concept of "auth info" which isn't a password and should not be changed by the user in SET PASSWORD.

Options:

1) Note that "hash_password=NULL" approach is consistent with password validation — validation is only used when hash_password is not NULL, otherwise the string is not considered a password.

2) usbsn auth plugin is a plugin (from the plugin book) where auth_str is not a password, but should be settable by a user.

or may be it shouldn't be a plugin property, but a server setting? or per-account setting?

another option would be to introduce a new concept of "auth info, not user password" in addition to (not instead of) the password. So that hypothetically a plugin could have both. Could be specified like

create user identified via "plugin:auth" using "password"

the first "auth" will not be considered a password and a user won't be able to change it.

It needs no sql parser changes and no auth api changes unless a plugin wants to support both (none does at the moment). It's SQL syntax for users though, need to migrate scripts, etc.

"plugin:auth" isn't very SQL-ish, so could be config "auth" or may be even arbitrary key-value pairs like in CREATE TABLE.

simple solution: sysvar @@pam_service. If set, it overrides auth string

Multi-step approach:

1. sysvar @@pam_service. This is done in 10.2

2. Later, as needed, key=value pairs, CREATE TABLE style. In the parser

--- a/sql/sql_yacc.yy

+++ b/sql/sql_yacc.yy

@@ -17253,6 +17253,10 @@ opt_auth_str:

             MYSQL_YYABORT;

           $$->pwtext= $4;

         }

+      | using_or_as IDENT_sys '=' TEXT_STRING_sys { }

+      | using_or_as IDENT_sys '=' ident { }

+      | using_or_as IDENT_sys '=' real_ulonglong_num { }

+      | using_or_as IDENT_sys '=' DEFAULT { } /* may be? */

       ;

  opt_require_clause:

They're defined similar to https://mariadb.com/kb/en/engine-defined-new-tablefieldindex-attributes/ including the "SYSVAR" type. Array of declarations is stored in the info structure. Every option declares whether it's user or DBA settable. PASSWORD option uses hash_password() and preprocess_hash() and its values are validated.

It would allow something like

create user foo@bar identified by pam using service='mysql' use_cleartext_plugin=0 winbind_workaround=true

                               or unix_socket

                               or gssapi using group='Administrator' mech_name=negotiate

                               or ed25519 using auth='Y5fV74JAVRMOK2cdnUsYS+WW9sXaaL/o+6WGKOgqnzc'

Old auth_string from USING ... and AS ... are treated as a password. In particular pam plugin will use service= value from the CREATE USER if specified, otherwise the sysvar if specified, otherwise the old auth_string.

Implemented solution: disallow

SET PASSWORD = 'string'

for plugins that don't support

SET PASSWORD = PASSWORD('string')

That is, if the plugin doesn't provide a password hashing function PASSWORD(), then SET PASSWORD won't work.