[mysql] 문제해결. 원격허용 설정 후 grant시 ERROR 1819 (HY000)

원격접속 허용 설정 중 에러발생시

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

현재 인증유저 확인

mysql> select host,user,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *56C312544743E5FAF5B4BAAB1A9B033AE16E2568 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) VALUES ('118.222.x.x','root',password('패스워드'),'','','');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'118.222.x.x';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> select host,user,authentication_string from mysql.user;
+----------------+---------------+-------------------------------------------+
| host           | user          | authentication_string                     |
+----------------+---------------+-------------------------------------------+
| localhost      | root          | *56C312544743E5FAF5B4BAAB1A9B033AE16E2568 |
| localhost      | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost      | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 118.222.x.x | root          | *56C312544743E5FAF5B4BAAB1A9B033AE16E2568 |
+----------------+---------------+-------------------------------------------+
4 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%'
    -> ;
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| 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      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

패스워드는 위 정책에 위배되는 부분이 없음에도 grant에서 계속 에러

패스워드 정책을 off

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql>  SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'118.222.x.x';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

동일해서 다시 원복

mysql> SET GLOBAL validate_password_policy=MEDIUM;
Query OK, 0 rows affected (0.00 sec)

cf. 영구설정은 /etc/my.cnf
[root@ncloud-server-centos1 etc]# cd /
[root@ncloud-server-centos1 /]# find / -name my.*
/etc/my.cnf
/etc/my.cnf.d

다른 방법으로 시도
[root@ncloud-server-centos1 etc]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT user, host FROM mysql.user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| root          | 118.222.76.130 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
4 rows in set (0.00 sec)

mysql>  grant all privileges on *.* to 'root'@'118.222.x.x';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

같은방법으로는 동일하게 에러

valicate_password 플러그인을 삭제하고 grant하고 다시 설정하는 방법을 시도

mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'root'@'118.222.x.x';
Query OK, 0 rows affected (0.01 sec)

오, 에러안남

그렇다면 플러그인 다시 설치

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0.01 sec)

패스워드 정책 그대로임을 확인

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| 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      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

상황종료

cf. 패스워드 내 계정명이 있는지여부 체크 활성화

mysql> SET GLOBAL validate_password_check_user_name=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| 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      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql>