Description:
create normal user and proxy user with certain permissions
now login with proxy user and expire password of normal user(proxied)
and execute any command in the same session of proxy user and observe that it throws error ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
but after session is disconnected and connected back,this error is not shown.
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 &
with root user:
mysql> install plugin test_plugin_server soname 'auth_test_plugin.so';
Query OK, 0 rows affected (0.00 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,password_expired from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+------------------+
| user | host | plugin | authentication_string | password_expired |
+---------------+-----------+-----------------------+-------------------------------------------+------------------+
| root | localhost | mysql_native_password | | N |
| empl_external | localhost | test_plugin_server | employee | N |
| employee | localhost | mysql_native_password | *4B2410F7B212BA2D7FCAC48B57574E82EF0F5281 | N |
+---------------+-----------+-----------------------+-------------------------------------------+------------------+
3 rows in set (0.00 sec)
########with empl_external user which is proxy user:###########
[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.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
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> select user(),current_user(),@@proxy_user;
+-------------------------+--------------------+-----------------------------+
| user() | current_user() | @@proxy_user |
+-------------------------+--------------------+-----------------------------+
| empl_external@localhost | employee@localhost | 'empl_external'@'localhost' |
+-------------------------+--------------------+-----------------------------+
1 row in set (0.00 sec)
mysql> select user,host,plugin,authentication_string,password_expired from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+------------------+
| user | host | plugin | authentication_string | password_expired |
+---------------+-----------+-----------------------+-------------------------------------------+------------------+
| root | localhost | mysql_native_password | | N |
| empl_external | localhost | test_plugin_server | employee | N |
| employee | localhost | mysql_native_password | *4B2410F7B212BA2D7FCAC48B57574E82EF0F5281 | N |
+---------------+-----------+-----------------------+-------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> alter user employee@localhost password expire;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin,authentication_string,password_expired from mysql.user;<--now disconnect this session and connect it back and no error seen for any statement
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> exit
Bye
[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.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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> select user(),current_user(),@@proxy_user;
+-------------------------+--------------------+-----------------------------+
| user() | current_user() | @@proxy_user |
+-------------------------+--------------------+-----------------------------+
| empl_external@localhost | employee@localhost | 'empl_external'@'localhost' |
+-------------------------+--------------------+-----------------------------+
1 row in set (0.00 sec)
