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

MySQL password 복잡도 설정 ( 5.6 이상 )

http://ndba.egloos.com/3434461

5.6 부터 패스워드 복잡도를 설정할 수 있는 플러그인이 있다. 

http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html

#### mysql plugin이 있는 경로 

[root@testvm1 init.d]# cd /usr1/mysql/5612/lib/plugin/

[root@testvm1 plugin]# ls -lrt

total 2144

-rw-r--r-- 1 mysql mysql    227 Jun 20 02:20 daemon_example.ini

-rwxr-xr-x 1 mysql mysql  15229 Jun 20 03:27 adt_null.so

-rwxr-xr-x 1 mysql mysql 586884 Jun 20 03:27 libmemcached.so

-rwxr-xr-x 1 mysql mysql  18103 Jun 20 03:27 qa_auth_client.so

-rwxr-xr-x 1 mysql mysql  25088 Jun 20 03:27 auth_test_plugin.so

-rwxr-xr-x 1 mysql mysql  12364 Jun 20 03:27 auth_socket.so

-rwxr-xr-x 1 mysql mysql  25611 Jun 20 03:27 auth.so

-rwxr-xr-x 1 mysql mysql  12926 Jun 20 03:27 qa_auth_server.so

-rwxr-xr-x 1 mysql mysql  23806 Jun 20 03:27 qa_auth_interface.so

-rwxr-xr-x 1 mysql mysql  17539 Jun 20 03:27 mypluglib.so

-rwxr-xr-x 1 mysql mysql 547780 Jun 20 03:27 innodb_engine.so

-rwxr-xr-x 1 mysql mysql 139156 Jun 20 03:27 validate_password.so

-rwxr-xr-x 1 mysql mysql 250750 Jun 20 03:27 semisync_slave.so

-rwxr-xr-x 1 mysql mysql 415322 Jun 20 03:27 semisync_master.so

-rwxr-xr-x 1 mysql mysql  42625 Jun 20 03:27 libdaemon_example.so

drwxr-xr-x 2 mysql mysql   4096 Aug 26 16:29 debug

#### my.cnf 세팅 

[mysqld]

server-id                       = 12

user                            = mysql

port                            = 3306

socket                          = /tmp/mysql.5612.sock

basedir                         = /usr1/mysql/5612

datadir                         = /data/mysql/data_5612

tmpdir                          = /tmp

pid-file                        = /tmp/mysqld_5612.pid

init_connect                    = "SET NAMES utf8;"

character-set-server            = utf8

collation-server                = utf8_general_ci

skip-character-set-client-handshake

skip-host-cache

skip-name-resolve

event_scheduler                 = ON

plugin-load                     = validate_password.so

validate-password               = FORCE_PLUS_PERMANENT

validate-password-policy        = MEDIUM

validate-password-mixed-case-count = 0

### plugin 경로가 제대로 설정되어 있나 확인 

[root@localhost] ((none)) 18:00> show global variables like '%plu%';

+---------------+------------------------------+

| Variable_name | Value                        |

+---------------+------------------------------+

| plugin_dir    | /usr1/mysql/5612/lib/plugin/ |

+---------------+------------------------------+

1 row in set (0.00 sec)

#### my.cnf 에서 설정한 패스워드 검증 관련 파라미터가 제대로 설정되어 있는지 확인 

[root@localhost] ((none)) 17:56> show global variables like '%vali%';

+--------------------------------------+--------+

| Variable_name                        | Value  |

+--------------------------------------+--------+

| query_cache_wlock_invalidate         | OFF    |

| validate_password_dictionary_file    |        |

| validate_password_length             | 8      |

| validate_password_mixed_case_count   | 0      |

| validate_password_number_count       | 1      |

| validate_password_policy             | MEDIUM |

| validate_password_special_char_count | 1      |

+--------------------------------------+--------+

7 rows in set (0.02 sec)

#### 패스워드 검증 정책에 따라 패스워드 설정이 되는지 확인  

[root@localhost] (mysql) 17:57> create user test_password identified by 'abcd';

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

[root@localhost] (mysql) 17:58> create user test_password identified by 'abcd123';

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

[root@localhost] (mysql) 17:59> create user test_password identified by 'abcd123!';

Query OK, 0 rows affected (0.01 sec)

### 패스워드 검증 정책 변경 

[mysqld]

…

…

plugin-load                     = validate_password.so

validate-password               = FORCE_PLUS_PERMANENT

validate-password-policy        = MEDIUM

validate-password-mixed-case-count = 0

validate-password-length        = 10

validate-password-special-char-count = 2

[root@localhost] ((none)) 18:07> show global variables like '%vali%';

+--------------------------------------+--------+

| Variable_name                                    | Value   |

+--------------------------------------+--------+

| query_cache_wlock_invalidate              | OFF     |

| validate_password_dictionary_file          |            |

| validate_password_length                     | 10       |

| validate_password_mixed_case_count    | 0        |

| validate_password_number_count          | 1        |

| validate_password_policy                      | MEDIUM |

| validate_password_special_char_count   | 2        |

+--------------------------------------+--------+

7 rows in set (0.02 sec)

[root@localhost] (mysql) 18:08> update user set password=password('abc') where user = 'test_password';

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

[root@localhost] (mysql) 18:08> update user set password=password('abc3456!') where user = 'test_password';

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

[root@localhost] (mysql) 18:09> update user set password=password('abc3456!@') where user = 'test_password';

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

[root@localhost] (mysql) 18:09> update user set password=password('abc34567!@') where user = 'test_password';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (mysql) 18:09> show warnings;

Empty set (0.00 sec)

[root@localhost] (mysql) 18:09>

출처 :

http://egloos.zum.com/ndba/v/3434461

Ndba

egloos.zum.com