MySQL Bugs: #70451: Password Validation Plugin doesn't play nice with the GRANT and REVOKE commads

Bug #70451	Password Validation Plugin doesn't play nice with the GRANT and REVOKE commads
Submitted:	27 Sep 2013 14:39	Modified:	19 Nov 2013 13:50
Reporter:	Joe Grasse (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.6.12, 5.6.13, 5.6.14, 5.7.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression, validate_password

Description:
The Password Validation Plugin doesn't play nice with the GRANT and REVOKE commands.

When the validate_password plugin is loaded, all grant commands now require the password to be provided even if the user already has a password that meets the password requirements.

Also, the revoking of privileges no longer seems to work.

How to repeat:
Install the password validation plugin as described here http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html#validate-password-plu....

Then follow steps below.

mysql root@localhost:(none) > SHOW GLOBAL VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

mysql root@localhost:(none) > CREATE SCHEMA IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql root@localhost:(none) > CREATE USER 'test'@'%' IDENTIFIED BY 'Test123$';
Query OK, 0 rows affected (0.01 sec)

mysql root@localhost:(none) > GRANT SELECT ON test.* TO 'test'@'%';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql root@localhost:(none) > GRANT SELECT ON test.* TO 'test'@'%' IDENTIFIED BY 'Test123$';
Query OK, 0 rows affected (0.00 sec)

mysql root@localhost:(none) > REVOKE SELECT ON test.* FROM 'test'@'%';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql root@localhost:(none) > REVOKE SELECT ON test.* FROM 'test'@'%' IDENTIFIED BY 'Test123$';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Test123$'' at line 1

To revoke permissions I had to remove the password validation plugin.

mysql root@localhost:(none) > SHOW GLOBAL VARIABLES LIKE 'validate_password%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

Empty set (0.00 sec)

mysql root@localhost:(none) > REVOKE SELECT ON test.* FROM 'test'@'%';
Query OK, 0 rows affected (0.01 sec)

Suggested fix:
Only check the the password on a grant statement if the user doesn't already have a password. Don't check passwords when revoking privileges.

Hello Joseph,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

I reported this internally already.
Bug 16938568 - ERROR 1819 (HY000): YOUR PASSWORD DOES NOT SATISFY THE CURRENT POLICY REQUIREMEN

This is a regression since 5.6.12, the fix for
Bug 16346443 - EMPTY PASSWORDS ARE ALLOWED WITH VALIDATE-PASSWORD-POLICY SET.

This has now been fixed. Noted in 5.6.15, 5.7.3 changelogs.

GRANT without an IDENTIFIED BY clause resulted in an error even for
existing users.

5.6$ bzr log -r 5550
------------------------------------------------------------
revno: 5550
committer: Ashish Agarwal<ashish.y.agarwal@oracle.com>
branch nick: bug_16938568
timestamp: Wed 2013-10-23 15:47:54 +0530
message:
  Bug#16938568: ERROR 1819 (HY000): YOUR PASSWORD DOES NOT SATISFY
                THE CURRENT POLICY REQUIREMEN
  
  PROBLEM: Grant without indentified clause was not allowed
           even if the user was previously created.
           This bug is a regression caused by bug#16346443.
           We missed as case: allow to execute grant statement
           without identified clause when user exsist.
  
  SOLUTION: Allowing grant statement (without identified clause)
            to execute when validate_password plugin is on.

it looks like it is 2021 and this bug still exists. In  MySQL 8.0 version