Bug #76044 set password success for plugin users without warnings
Description:
create normal with user certain privileges;
create proxy user after installing auth_test_plugin for above created user
try to change password of proxy user with set password command and observe no errors
How to repeat:
./mysqld --no-defaults --initialize-insecure -uroot --basedir=../ --datadir=./data --gdb --port=3307 &
./mysqld --no-defaults -uroot --basedir=../ --datadir=./data --gdb --port=3307 &
[lroot@vitro77 bin]# ./mysql -uroot --port=3307 --socket=/tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.6-m16-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> install plugin test_plugin_server soname 'auth_test_plugin.so';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER 'empl_external'@'localhost' IDENTIFIED WITH test_plugin_server AS 'employee';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO 'employee'@'localhost' IDENTIFIED BY 'passkey';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for employee@localhost;
+-------------------------------------------------------+
| Grants for employee@localhost |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'employee'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for empl_external@localhost;
+----------------------------------------------------------------------+
| Grants for empl_external@localhost |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'empl_external'@'localhost' |
| GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user,host,plugin,authentication_string from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+
| user | host | plugin | authentication_string |
+---------------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | |
| employee | localhost | mysql_native_password | *4B2410F7B212BA2D7FCAC48B57574E82EF0F5281 |
| empl_external | localhost | test_plugin_server | employee |
+---------------+-----------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> set password for 'empl_external'@localhost='test';<--Here Warning is expected since no effect is there even after changing password
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin,authentication_string from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+
| user | host | plugin | authentication_string |
+---------------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | |
| employee | localhost | mysql_native_password | *4B2410F7B212BA2D7FCAC48B57574E82EF0F5281 |
| empl_external | localhost | test_plugin_server | employee |<--no change to this row after setting password as above
+---------------+-----------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
And one more issue in the similar aspect:
Are we allowed to change password with alter user command..? it is not throwing error now but login is failed to this user since authentication_string is changed by alter command.
mysql> select user,host,plugin,authentication_string from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+
| user | host | plugin | authentication_string |
+---------------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | |
| employee | localhost | mysql_native_password | *4B2410F7B212BA2D7FCAC48B57574E82EF0F5281 |
| empl_external | localhost | test_plugin_server | employee |
+---------------+-----------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> alter user empl_external@localhost identified by 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin,authentication_string from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+
| user | host | plugin | authentication_string |
+---------------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | |
| employee | localhost | mysql_native_password | *4B2410F7B212BA2D7FCAC48B57574E82EF0F5281 |
| empl_external | localhost | test_plugin_server | test |<--employee got replaced by test with alter command due to which this user becomes invalid and login fails
+---------------+-----------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
[lroot@vitro77 bin]# ./mysql -uempl_external -pemployee --port=3307 --socket=/tmp/mysql.sock --plugin-dir=..//lib/plugin --default-auth=auth_test_plugin
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'empl_external'@'localhost' (using password: YES)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.