How to Restrict MySQL User Creation with Blank Password

Question: How can we not allow user creation with a blank password in MySQL?

Using a blank password for a user is always a no go. In order to restrict user creation with blank password follow the steps given below:

Before MySQL 5.6

In case of MySQL version below 5.6, this can not be achieved completely. But there are settings that can be used to come closer to our objective.

Set the below SQL mode on the MySQL instance:

SET SQL_MODE = NO_AUTO_CREATE_USER

Using NO_AUTO_CREATE_USER mode it is expected that – the statement must specify a NONEMPTY password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.

Because, when the clause IDENTIFIED BY is specified, it is a clear indication that one purposely wants to create a new user. We cannot prevent admin accounts from creating users with a blank password. Hence if you want to setup a monitoring rule to identify such users periodically, enable the below MySQL Enterprise Monitor (MEM) version 2.3 Advisors that would alert you of such accounts and that tells you what to do next:

Advisor 1: Server Has Accounts Without A Password
Advisor 2: Server Has Anonymous Accounts

MySQL 5.6 and above

Starting from MySQL 5.6, this can be achieved using Password Validation Plugin’s ‘validate_password_policy‘ function. Use the below options to cover most cases:

plugin-load = validate_password.so
validate-password = FORCE_PLUS_PERMANENT
validate-password-policy = X
sql_mode = NO_AUTO_CREATE_USER