MySQL Bugs: #117423: RETAIN PASSWORD not working with Non Built-in authentication plugins

Bug #117423	RETAIN PASSWORD not working with Non Built-in authentication plugins
Submitted:	10 Feb 10:20	Modified:	11 Feb 8:38
Reporter:	Pranav Pandey	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S2 (Serious)
Version:	8.0.28, 8.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When retain password is used for non built-in authentication plugin, MySQL is throwing error 

mysql> ALTER USER 'proxy_doc_poc'@'%' IDENTIFIED WITH 'san_auth'  BY 'mysql' RETAIN CURRENT PASSWORD;

ERROR 3894 (HY000): Current password can not be retained for user 'proxy_doc_poc'@'%' because authentication plugin is being changed.

Note
ALTER USER 'proxy_doc_poc'@'%' BY 'mysql' RETAIN CURRENT PASSWORD;
is working but it is breaking the replication as on replica the above statment is getting converted to 
ALTER USER 'proxy_doc_poc'@'%' IDENTIFIED WITH 'san_auth'  BY 'mysql' RETAIN CURRENT PASSWORD;

How to repeat:
1. INSTALL any custom authentication plugin 
INSTALL PLUGIN san_auth SONAME 'san_auth_server.so'

2. create a new user with that plugin

CREATE USER `proxy_doc_poc`@`%` IDENTIFIED WITH 'san_auth' AS 'mysql2';

3. Try to alter that user with RETAIN CURRENT PASSWORD
ALTER USER 'proxy_doc_poc'@'%' IDENTIFIED WITH 'san_auth'  BY 'mysql' RETAIN CURRENT PASSWORD;

Suggested fix:
Instead of direct pointer comparison it should be a string comparison 

- if (Str->first_factor_auth_info.plugin.str != acl_user->plugin.str) {
+ if (!strcmp(Str->first_factor_auth_info.plugin.str,acl_user->plugin.str)) {

https://github.com/mysql/mysql-server/blob/6b6d3ed3d5c6591b446276184642d7d0504ecc86/sql/au...

The issue seems to be with how new ACL user is getting created , If you see here 
https://github.com/mysql/mysql-server/blob/6b6d3ed3d5c6591b446276184642d7d0504ecc86/sql/au...

In case of build in plugins the plugin name is getting reused , but incase of custom plugins new plugin name is getting created

typo in title

Hello Pranav Pandey,

Thank you for the report and feedback.
I tried with different plugin than the one mentioned in the report i.e. used test_plugin_server which is a loadable plugin (not built in) and observed the reported issue. I'm not sure whether this is intended behaviour but seen with not built in plugin.

-- 8.0.41

BugNumber=117423
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/$BugNumber/log.err --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &

-
mysql> INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'testuser'@'localhost'
    -> IDENTIFIED WITH test_plugin_server
    -> BY 'testpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'test_plugin_server'  BY 'testpassword' RETAIN CURRENT PASSWORD;
ERROR 3894 (HY000): Current password can not be retained for user 'testuser'@'localhost' because authentication plugin is being changed.
mysql>

-- With build in 

mysql> CREATE USER 'sha2user'@'localhost'
    -> IDENTIFIED WITH caching_sha2_password BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'sha2user'@'localhost' IDENTIFIED WITH 'caching_sha2_password'  BY 'password' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.00 sec)

regards,
Umesh

This should not be intended with any custom auth plugin right?

We should be able run commands with `RETAIN PASSWORD` for all Authentication plugins. The reporter has suggested a fix as well.