How to Allow User Creation with Simple password in MySQL

Question: How to allow user creation with simple password without any character limit or enforcement of special character etc.?

With validate_password plugin enabled in the server, the user has to configure a password according to the password policy rules. By default, it requires 8 characters long, one number character, one mixed case character and one special character.

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

If you want to create a less complex password, you can change these variables. For example:

set global validate_password_policy=0;
set global validate_password_mixed_case_count=0;
set global validate_password_number_count=0;
set global validate_password_length=0;
set global validate_password_special_char_count=0;
set global validate_password_length=0;

It will allow you to create a user with empty password. And this only affect the new user. You can revert these changes after creating new simple password user.

set global validate_password_length=8;
set global validate_password_policy=1;
set global validate_password_mixed_case_count=1;
set global validate_password_number_count=1;
set global validate_password_special_char_count=1;

However having a user with no password or simple password on a production system would be a HUGE security breach. There are other methods available to not include the plaintext password as part of the command:

1. Use the mysql_config_editor configuration utility to hold the authentication information.
2. Provide the authentication information in the [client] or [mysql] section of a MySQL option file (Ex: my.cnf file in your home directory). You can find out more information about this at option_files.
3. Use an authentication mechanism (e.g. PAM or Windows Auth) that does not rely on passwords.