How to create a six character password in MySQL 5.7

Question

I need to create a user with a six character password in new MySQL on my mac. I know that the lowest setting in 5.7 will allows only eight characters. Is there any way to go around that?

I type in CREATE USER 'newsier'@'localhost' IDENTIFIED BY 'special'

It outputs the error

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Logomachy

This seems a strange question. If you dont want password validation to run, dont load the plugin – Hamitosemitic

How do i not load it. Because it showed up default in 5.7 – Logomachy

Allow users to use the passwords / phrases they desire. Don't limit passwords – Terranceterrane

@Hamitosemitic - How do we unload the plugin? – Signalment

SHOW PLUGINS; will show you the plugins installed and if you want to uninstall one the command is UNINSTALL PLUGIN plugin_name – Intend

Answer 1

You are using the password validation plugin. By default it only allows 8 characters and longer passwords. Because it can't check the value of a hash, @RiggsFolly is correct that pre-hashing the password will work.

However, if you want to change the options, you'll need to set the value of the validate_password_length system variable. You can do this in the configuration file or:

SET GLOBAL validate_password_length=6;

Serviceable

This does NOT seem to work in mysql 5.7.11 for mac installed via brew. I get the same error even after changing the options. – Signalment

Answer 2

First you login with mysql -u root -p and check the current policy rules by:

# SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 5      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

Then you can change any of the above variables at your will:

# SET GLOBAL validate_password_length = 5;
# SET GLOBAL validate_password_number_count = 0;
# SET GLOBAL validate_password_mixed_case_count = 0;
# SET GLOBAL validate_password_special_char_count = 0;

Finally you can create a database and a user accessing it with a simpler password:

# CREATE DATABASE test1;
# GRANT ALL PRIVILEGES ON test1.* TO user1@localhost IDENTIFIED BY "pass1";
# FLUSH PRIVILEGES;

After that you can login with mysql -u user1 -p test1 using password pass1

Pesticide

This is the right answer. The error 'ERROR 1819 (HY000): Your password does not satisfy the current policy requirements' could be because of mixed_cese_count or special_char_count. Wherever I read it said, change the pass length, but it was about mixed_case. Thanks! – Publicize

For MySQL ver 8.0.26-0, the code is like SET GLOBAL validate_password.number_count = 0; instead of an underscore within it. – Endlong

Answer 3

MySQL 5.7+ by default haves a Password validation system. In case if you don't want to go strictly with the policy and need to assign your own then just disable the password validation and restart mysqld process.

Edit my.cnf file :

vi /etc/my.cnf

in [mysqld]

validate-password=off

Save the file and then restart the process

sudo service mysqld restart
or
systemctl restart mysqld

and then change the Root Password using the following and follow the steps it won't throw exception for the Root Password.

mysql_secure_installation
or
/usr/bin/mysql_secure_installation

If you are doing installation for the first time and want to know the temporary password then use the following to find the first time password:

 grep 'temporary password' /var/log/mysqld.log

Let me know your comments on the same, in the below comment box.

Potence

Answer 4

It's possible to entirely disable the password "plugin" that seems overly strict by running the following: uninstall plugin validate_password

Should you need it again you can turn it back on via: INSTALL PLUGIN validate_password SONAME 'validate_password.so'

Optionally, you could disable it, set your password and re-enable it:

uninstall plugin validate_password;
CREATE USER 'newsier'@'localhost' IDENTIFIED BY 'special';
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

Further reading:

• https://dev.mysql.com/doc/refman/5.7/en/server-plugin-loading.html
• https://dev.mysql.com/doc/refman/5.7/en/validate-password-plugin-installation.html

Work

Answer 5

Check current password policy rules by

SHOW VARIABLES LIKE 'validate_password%';

Now set new value of those variable:

SET GLOBAL validate_password_length = 6 ; // that you like
SET GLOBAL validate_password_policy = LOW ;

Spout

Answer 6

I believe you can get round it by using a pre hashed password like this :-

CREATE USER 'newsier'@'localhost' IDENTIFIED WITH mysql_native_password
       AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';

But that does mean you need to hash special correctly before this will actually set a password that you can then use the plain text version of.

Hamitosemitic

Answer 7

This might help:

GRANT ALL ON wordpress.* TO 'user'@'localhost' IDENTIFIED WITH mysql_native_password AS'yourpassword';

Bushhammer