CODEWITHSUNDEEP
mysqlpasswords
Nguyen
Nguyen

Reputation: 3181

Your password does not satisfy the current policy requirements

I want to create a new user in MySQL with the syntax:

create user 'demo'@'localhost' identified by 'password';

But it returns an error:

Your password does not satisfy the current policy requirements.

I have tried many passwords but they don't work. How can I fix this?

Upvotes: 317

Views: 697540

Answers (27)

Yogesh More
Yogesh More

Reputation: 93

Seems like the password does not match all the conditions. Setting up all combinations sometimes gets harder, So I searched for a strong Password Generator. Then I found Passwordhero.com, After that, I chose a strong option that worked like a CHARM!!enter image description here See if this image might help.

Upvotes: 0

Hung Nguyen
Hung Nguyen

Reputation: 5788

Because of your password. You can see password validate configuration metrics using the following query in MySQL client:

SHOW VARIABLES LIKE 'validate_password%';

The output should be something like that :

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+

Now that the rules for a valid password are clear, you could chose a valid password.

To check the strength of the password you chose, use the VALIDATE_PASSWORD_STRENGTH() function, for example:

SELECT VALIDATE_PASSWORD_STRENGTH('weak');
+------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('weak') |
+------------------------------------+
|                                 25 |
+------------------------------------+
SELECT VALIDATE_PASSWORD_STRENGTH('lessweak$_@123');
+----------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('lessweak$_@123') |
+----------------------------------------------+
|                                           50 |
+----------------------------------------------+
SELECT VALIDATE_PASSWORD_STRENGTH('N0Tweak$_@123!');
+----------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('N0Tweak$_@123!') |
+----------------------------------------------+
|                                          100 |
+----------------------------------------------+

Alternatively, you can lower the password policy level or change the validation rules, for example:

SET GLOBAL validate_password.length = 6;
SET GLOBAL validate_password.number_count = 0;

Check the MySQL Documentation.

Upvotes: 564

De Zin Htang
De Zin Htang

Reputation: 71

#password must include small-letters,at least one Capital letter,one Character and Numbers

e.g create user 'username'@'localhost' identified by 'user_Name@123';

Upvotes: 1

Ruchira Nawarathna
Ruchira Nawarathna

Reputation: 1487

You have to change MySQL password policy to LOW.

login as root

mysql -u root -p

change policy

SET GLOBAL validate_password.policy=LOW;

or

SET GLOBAL validate_password.policy=0;

exit

exit

restart MySQL service

sudo service mysql restart

You have to change the MySQL password policy to Low = 0 / Medium = 1 / High = 2.

SET GLOBAL validate_password.policy=0;
SET GLOBAL validate_password.policy=1;    
SET GLOBAL validate_password.policy=2;

Upvotes: 17

Pavan Kumar V
Pavan Kumar V

Reputation: 787

mysql -u root -p

type your password and hit enter And in Myqsl terminal type

SHOW VARIABLES LIKE 'validate_password%';

you'll see a table containing password validation data like this

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| 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      |
+--------------------------------------+--------+

Then copy paste this query

SET GLOBAL validate_password.policy=LOW;SET GLOBAL validate_password.length=0;SET GLOBAL validate_password.mixed_case_count=0;SET GLOBAL validate_password.number_count=0;SET GLOBAL validate_password.special_char_count=0;

Now your table should look like this

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 0     |
| validate_password.mixed_case_count   | 0     |
| validate_password.number_count       | 0     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 0     |
+--------------------------------------+-------+

If it does not look like that write query individually syntax:

SET GLOBAL validate_password.<validator_name>=<value>;

Then exit mysql and type this command to change the password of your root

$(brew --prefix mysql)/bin/mysqladmin -u root -p password ""

(please include $)

It will ask you to type the current password enter it.

And what yo password is removed 🎉

Upvotes: 2

seb_dom
seb_dom

Reputation: 113

In my case, the error comes up when there is a wrong quote:

GRANT ALL ON database_name.* TO ‘user1’@'8.10.12.2'; (wrong single quote around user name)

vs

GRANT ALL ON database_name.* TO 'user1'@'8.10.12.2'; (correct)

Upvotes: 1

hizlan erpak
hizlan erpak

Reputation: 319

SHOW VARIABLES LIKE 'validate_password%';


mysql> SET GLOBAL validate_password.length = 6;
mysql> SET GLOBAL validate_password.number_count = 0;
mysql> SET GLOBAL validate_password.policy=LOW;
SHOW VARIABLES LIKE 'validate_password%';

Upvotes: 13

sohaieb azaiez
sohaieb azaiez

Reputation: 780


this information is important to all answers above : When you restart mysql server all policy config will be RESET and will be LOST, so to make your policy configuration PERSISTENT, you should follow these instructions: (tested on ubuntu server version 20.10)

  1. After Applying these changes with mysql Hung Nguyen answer
  2. sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
  3. add this last line:
[mysqld]
#
# * Basic Settings
#
..
..
..
validate_password.policy = LOW   # <== this line
..
..
# IMPORTANT notes: 
# - you can add more policy commands each by your needs.
# - notice the "." before "policy" , in some mysql versions is "_" , so be aware of that.

  1. Save the file and restart mysql server: sudo service mysql restart

  2. Connect and check the persistence of your configuration:
    sudo mysql -u your_username -p
    show variables like "validate_pass%";
    result should be like this one: 

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 0     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+

I hope this can help people.

Edit: thanks to @ahmednawazbutt: don't forget to FLUSH PRIVILEGES;

Upvotes: 0

Black
Black

Reputation: 20242

This is a bug in phpmyadmin. Start mysql with

sudo mysql -p

Then execute this command:

SET GLOBAL validate_password.policy = 0;

Now you can change your password. If you like you can reset it after it:

SET GLOBAL validate_password.policy = MEDIUM;

(You can use LOW, MEDIUM or HIGH)

Then execute exit to close mysql.

Solution found here.

Upvotes: 4

Mr. Durga prasad patra
Mr. Durga prasad patra

Reputation: 361

If you trying to set a blank password. Then running the following query in MySQL client:

SET GLOBAL validate_password.check_user_name = No;
SET GLOBAL validate_password.dictionary_file = '';
SET GLOBAL validate_password.length = 0;
SET GLOBAL validate_password.mixed_case_count = 0;
SET GLOBAL validate_password.number_count = 0;
SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.special_char_count = 0;

The output should be something like that :

    SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 0     |
| validate_password.mixed_case_count   | 0     |
| validate_password.number_count       | 0     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 0     |
+--------------------------------------+-------+

Then you can update your blank password using this query:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

Upvotes: 7

singh
singh

Reputation: 1070

If you don't care what the password policy is. You can set it to LOW by issuing below mysql command:

mysql> SET GLOBAL validate_password.length = 4;
mysql> SET GLOBAL validate_password.policy=LOW;

Upvotes: 103

PKSingh
PKSingh

Reputation: 561

The best option is to just go through your password policy and set a password accordingly. You can check the value of your existing Password Validation Plugin System Variables by running the below command

mysql> SHOW VARIABLES LIKE 'validate_password%';

It's never a good practice to reduce your security policies by manipulating the system variables.

You can read about the details of each variable and its use case from the official documentation, links for reference MySQL 5.6 MySQL 5.7 MySQL 8.0

Upvotes: 0

Javasick
Javasick

Reputation: 2993

For MySQL 8 you can use the following script:

SET GLOBAL validate_password.LENGTH = 4;
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.special_char_count = 0;
SET GLOBAL validate_password.check_user_name = 0;
ALTER USER 'user'@'localhost' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;

Upvotes: 46

Vaibs
Vaibs

Reputation: 2096

If the mysql is hosted on aws , just uninstall the plugin. From the root user fire below

UNINSTALL PLUGIN validate_password;

Upvotes: 1

Harish Kumar Saini
Harish Kumar Saini

Reputation: 452

In my opinion setting the "validate_password_policy" to "low" or uninstalling the "validate password plugin" is not the right thing to do. You must never compromise with security of your database (unless you don't really care). I am wondering why people are even suggesting these options when you simply can pick a good password.

To overcome this problem, I executed following command in mysql: SHOW VARIABLES LIKE 'validate_password%' as suggested by @JNevill. My "validate_password_policy" was set to Medium, along with other things. Here is the screenshot of its execution: Validate Password Policy

The result is self explanatory. For a valid password (when Password policy is set to medium):

  • Password must be at least 8 characters long
  • Mixed case count is 1 (At least 1 letter in small and 1 letter in caps)
  • Number count is 1
  • Minimum special Character count is 1

So a valid password must obey the policy. Examples of valid password for above rules maybe:

  • Student123@
  • NINZAcoder$100
  • demoPass#00

You can pick any combination as long as it satisfies the policy.

For other "validate_password_policy" you can simply look the values for different options and pick your password accordingly (I haven't tried for "STRONG").

https://dev.mysql.com/doc/refman/5.6/en/validate-password-options-variables.html

Upvotes: 13

Christian
Christian

Reputation: 11

There are some moments where finding a quick solution is great. But if you can avoid lowing passwords restrictions, it can keep you from having big headache in future.

Next link is from the official documentation https://dev.mysql.com/doc/refman/5.6/en/validate-password.html, where they expose an example exactly like your one, and they solve it.

The only one problem you have is that your password is not strong enough to satisfy the actual minimum policy (not recommended to remove). So you can try different passwords as you can see in the example.

Upvotes: 0

CollinsKe
CollinsKe

Reputation: 87

mysql> SET GLOBAL validate_password.policy = 0;

Upvotes: 1

mujuonly
mujuonly

Reputation: 11861

Set password that satisfies 7 MySql validation rules

eg:- d_VX>N("xn_BrD2y

Making validation criteria bit more simple will solve the issue

SET GLOBAL validate_password_length = 6;
SET GLOBAL validate_password_number_count = 0;

But recommended a Strong password is a correct solution

Upvotes: 1

Klaus
Klaus

Reputation: 198

didn't work for me on ubuntu fresh install of mysqld, had to add this: to /etc/mysql/mysql.conf.d/mysqld.cnf or the server wouldn't start (guess the plugin didn't load at the right time when I simply added it without the plugin-load-add directive)

plugin-load-add=validate_password.so
validate_password_policy=LOW
validate_password_length=8
validate_password_mixed_case_count=0
validate_password_number_count=0
validate_password_special_char_count=0

Upvotes: 0

Dushan
Dushan

Reputation: 1565

The problem is that your password wont match the password validation rules. You can simple follow below steps to solve your problem.

You can simply see password validation configuration matrix by typing below code.

mysql-> SHOW VARIABLES LIKE 'validate_password%';

Then in your matrix you can find below variables with corresponding values and in there you have to check validate_password_length , validate_password_number_count and validate_password_policy.

Check the values used for those variables. Make sure your validate_password_length should not be greater than 6. You can set that to 6 by using below code.

SET GLOBAL validate_password_length = 6;

And after that you need to set validate_password_number_count to 0. Do it by using below code.

SET GLOBAL validate_password_number_count = 0;

Finally you have to set you validate_password_policy to low. Having that as Medium or High wont allow your less secure passwords. Set that to low by below code. 

SET GLOBAL validate_password_policy=LOW;

Upvotes: 2

Jekayode
Jekayode

Reputation: 1622

For Laravel users having this issue with MySQL 8.0.x, add

'modes'=> [
             'ONLY_FULL_GROUP_BY',
             'STRICT_TRANS_TABLES',
             'NO_ZERO_IN_DATE',
             'NO_ZERO_DATE',
             'ERROR_FOR_DIVISION_BY_ZERO',
             'NO_ENGINE_SUBSTITUTION',
            ],

to your database.php file as below:

// database.php

    'connections' => [

        'mysql' => [
            'driver'      => 'mysql',
            'host'        => env( 'DB_HOST', '127.0.0.1' ),
            'port'        => env( 'DB_PORT', '3306' ),
            'database'    => env( 'DB_DATABASE', 'forge' ),
            'username'    => env( 'DB_USERNAME', 'forge' ),
            'password'    => env( 'DB_PASSWORD', '' ),
            'unix_socket' => env( 'DB_SOCKET', '' ),
            'charset'     => 'utf8mb4',
            'collation'   => 'utf8mb4_unicode_ci',
            'prefix'      => '',
            'strict'      => true,
            'engine'      => null,
            'modes'       => [
                'ONLY_FULL_GROUP_BY',
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_ENGINE_SUBSTITUTION',
            ],
        ],
    ],

It fixed it for me.

Upvotes: 3

Anurag Sahu
Anurag Sahu

Reputation: 41

I had the same Problem and the Answer is just Right in front of you, remember when you ran the script while installing mysql like

sudo mysql_secure_installation

there somewhere you chose which password type would you like to chose

There are three levels of password validation policy:

LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

you have to give a password to same policy

here are some sample examples for your passwords:
for type 0: abcdabcd
for type 1: abcd1234
for type 2: ancd@1234

Upvotes: 3

Tino Bellmann
Tino Bellmann

Reputation: 1

This error message has nothing to do with the stored password in your table. It also occures if you type (on SQL console)

"select password('123456789')"

or if

"select password('A123456789')"

or if

"select password('A!123456789')"

If you type

"select password('A!a123456789')"

then it will work. Just use big + small letters, special chars and numbers to create your password.

You can disable these checks in my.cnf, but then you will have a security risk!

in [mysqld] add:

validate_password_policy=LOW
validate_password_special_char_count=0
validate_password_length=0
validate_password_mixed_case_count=0
validate_password_number_count=0

Upvotes: 0

Philip
Philip

Reputation: 7166

For MySQL 8*

SET GLOBAL validate_password.policy=LOW

Reference Link to explain about policy - Click Here

Upvotes: 41

Oroki
Oroki

Reputation: 61

Step 1: check your default authentication plugin

SHOW VARIABLES LIKE 'default_authentication_plugin';

Step 2: veryfing your password validation requirements

SHOW VARIABLES LIKE 'validate_password%';

Step 3: setting up your user with correct password requirements

CREATE USER '<your_user>'@'localhost' IDENTIFIED WITH '<your_default_auth_plugin>' BY 'password';

Upvotes: 6

Leo Sammy
Leo Sammy

Reputation: 402

After running the command sudo mysql_secure_installation.

  1. Run sudo mysql to enter into the mysql prompt.
  2. Run this SELECT user,authentication_string,plugin,host FROM mysql.user; to check for user root to have as plugin auth_socket.
  3. Then do run uninstall plugin validate_password; to drop priviledges before running this ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';Be sure to change password to a strong password.

NOTE: check out this link https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04 for more help

Upvotes: 25

kennyut
kennyut

Reputation: 3831

NOTE: This might not be a secure solution. But if you are working on a test environment, just need a quick fix and doesn't even care about the security settings. This is a quick solution.

The same issue happened to me when I ran "mysql_secure_installation" and modified password security level to 'medium'.

I bypassed the error by running the followings:

mysql -h localhost -u root -p
mysql>uninstall plugin validate_password;

make sure you reinstall the plugin "validate_password" if necessary.

Upvotes: 95

Related Questions