MySQL Bugs: #75383: Allow per session overrides of validate_password

Bug #75383	Allow per session overrides of validate_password_* variables
Submitted:	1 Jan 2015 11:46	Modified:	7 Jan 2015 14:23
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.6.22	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	password, validate_password, Validation

Description:
I want to know if any users are using their username as password. So I want to generate password hashes for each username. This works for mysql_old_password and mysql_native_password and won't work for sha256_password as that uses a salt.

This works only if the password validation plugin is not enabled and/or configured to allow short/weak passwords.

How to repeat:
mysql> SELECT user,host,password FROM mysql.user WHERE password=PASSWORD(user) OR password=OLD_PASSWORD(user);
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Suggested fix:
Allow per session overrides for the password validation settings if the user has SUPER (or some new SECADMIN priv)

Another possible solution is to return NULL (instead of an error) if the argument for PASSWORD() fails the validation, but that might impact other processes and is not a complete fix.

Hi Daniel,

Thanks for the feature request!  For the use case you described, I'd much rather see people compute the password hash using an alternate method:

mysql> SELECT CONCAT('*', UPPER(SHA1(UNHEX(SHA1('test')))));
+-----------------------------------------------+
| CONCAT('*', UPPER(SHA1(UNHEX(SHA1('test'))))) |
+-----------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29     |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select password('test');
+-------------------------------------------+
| password('test')                          |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec)

Providing a means for users (even those with the already-overloaded SUPER privilege) to bypass password validation isn't something I'd like to see.  That obviously ignores the fact that SUPER users can do that today, by changing the global variable.  I'd also like to see that locked down.

I didn't bother providing an OLD_PASSWORD() equivalent; if there are MySQL Server 5.6 users who are using pre-4.1 password hashing, I'd suggest that's the most immediate security vulnerability, not just whether they use their user name as their password.  :)

That's a very nice method for mysql_native_password. Maybe it should be added to the documentation (refman/internals) ?

For mysql_old_passwords: That's indeed a security issue. But *many* people who started with a RHEL5 MySQL 5.0.77 config keep using it. I think it's good to have it removed in 5.7.

But pre-4.1 and not the biggest risk. I often encounter situations where a number of accounts (and often also root or super accounts) don't have a password set at all. This is due to the situation where the NO_AUTO_CREATE_USER sqlmode protects against and the fact that not everyone understands the difference between 'root'@'localhost' and 'root'@'::1' and that they might have different passwords. Bug #70744 and Bug #70745 also don't really help here.

A security auditor might first want to know the complete situation and should not change any setting (that should be done by someone with an admin role).

Besides mysql_native_password and mysql_old_password there also is the sha256_password (old_passwords=2). I tried to use a similar method for that but password() doesn't help here as doesn't use the salt stored in authentication_string. I tried to use SHA2(), but when I had a look at my_crypt_genhash from mysys_ssl/crypt_genhash_impl.cc this seemed to complex to do in SQL (an UDF might work, but is not really an option)

The SUPER privilege is indeed already used for too many things... a roles system and more fine grained security would be nice.

Someone with the SUPER privilege might already change the global settings, but that could (in theory) allow someone to set a password in the brief timewindow needed to do this validation.

I do thing the proposed solution can be used. So overriding the variables is not needed for me anymore.

This requires in depth analysis before being accepted as a feature request.