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)
