MySQL root 비밀번호 변경하기 (MySQL 5.7버전 이상)

by lizard2019 posted Jun 10, 2019
?

Shortcut

PrevPrev Article

NextNext Article

ESCClose

Larger Font Smaller Font Up Down Go comment Print

1. 비밀번호 변경에대한 문서

 

예전의 MySQL에서는 간단히 Update 문으로 비밀번호가 변경되었으나 강화되 보안 규정 때문에 이제는 이방법으로는 비밀번호가 변경되지 않는다.

 

ALTER USER Authentication Options

An account name may be followed by an auth_option authentication option that specifies the account authentication plugin, credentials, or both. It may also include a REPLACE clause that specifies the current account password to be replaced.

  • auth_plugin names an authentication plugin. The plugin name can be a quoted string literal or an unquoted name. Plugin names are stored in the plugin column of the mysql.user system table.

    For auth_option syntaxes that do not specify an authentication plugin, the default plugin is indicated by the value of the default_authentication_plugin system variable. For descriptions of each plugin, see Section 6.5.1, “Authentication Plugins”.

  • Credentials are stored in the authentication_string column of the mysql.user system table. An 'auth_string' or 'hash_string' value specifies account credentials, either as a cleartext (unencrypted) string or hashed in the format expected by the authentication plugin associated with the account, respectively:

    • For syntaxes that use 'auth_string', the string is cleartext and is passed to the authentication plugin for possible hashing. The result returned by the plugin is stored in the authentication_string column. A plugin may use the value as specified, in which case no hashing occurs.

    • For syntaxes that use 'hash_string', the string is assumed to be already hashed in the format required by the authentication plugin. If the hash format is inappropriate for the plugin, it will not be usable and correct authentication of client connections will not occur.

  • REPLACE 'current_auth_string' clause (available as of MySQL 8.0.13) specifies the current account password to be replaced, as a cleartext (unencrypted) string:

    • The clause must be given if password changes for the account are required to specify the current password (to verify that the user attempting to make the change actually knows the current password).

    • The clause is optional if password changes for the account may but need not specify the current password.

    • The statement fails if the clause is given but does not match the current password, even if the clause is optional.

    • REPLACE can be specified only when changing the account password for the current user.

    For more information about password verification by specifying the current password, see Section 6.3.8, “Password Management”.

ALTER USER permits these auth_option syntaxes:

  • IDENTIFIED BY 'auth_string' [REPLACE 'current_auth_string']

    Sets the account authentication plugin to the default plugin, passes the cleartext 'auth_string' value to the plugin for hashing, and stores the result in the mysql.user account row.

    The REPLACE clause, if given, specifies the current account password, as described previously in this section.

  • IDENTIFIED WITH auth_plugin

    Sets the account authentication plugin to auth_plugin, clears the credentials to the empty string (the credentials are associated with the old authentication plugin, not the new one), and stores the result in the mysql.user account row.

    In addition, the password is marked expired. The user must choose a new one when next connecting.

  • IDENTIFIED WITH auth_plugin BY 'auth_string' [REPLACE 'current_auth_string']

    Sets the account authentication plugin to auth_plugin, passes the cleartext 'auth_string' value to the plugin for hashing, and stores the result in the mysql.useraccount row.

    The REPLACE clause, if given, specifies the current account password, as described previously in this section.

  • IDENTIFIED WITH auth_plugin AS 'hash_string'

    Sets the account authentication plugin to auth_plugin and stores the hashed 'hash_string' value as is in the mysql.user account row. The string is assumed to be already hashed in the format required by the plugin.

(출처:https://dev.mysql.com/doc/refman/8.0/en/alter-user.html)

 

문서를 보면 ALTER로 user 계정 암호를 변경한다.

위의 4개의 예시 중에서 IDENTIFIED WITH auth_plugin BY 'auth_string' 을 이용할 것이다. 

auth_plugin은 인증 플러인으로써, plugin 필드열에 저장된다. mysql의 기본 인증플러그인은 mysql_native_password이다.(변경 가능함)

auth_string은 (암호화되지않은)일반텍스트를 인증플러그인으로 반환하여 authentication_string 필드열에 저장된다.

 

먼저 user 테이블의 host, user, plugin, authentication_string을 확인해보자.

 

 

mysql> select host, user, plugin, authentication_string, password_last_changed from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | plugin                | authentication_string                                                  | password_last_changed |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | root             | caching_sha2_password |                                                                        | 2018-09-11 18:20:19   |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
 
 

위와 같이 비밀번호 설정 전에는 authentication_string이 빈칸일 것이다. 저기에 암호화된 비밀번호를 넣을 것이다!

(password_last_changed는 바뀐거 확인하려구 넣어본것)

 

 

1. 비밀번호 변경

ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '변경할비밀번호';

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'pw123';
Query OK, 0 rows affected (0.07 sec)
 
 

이렇게 적어주면 비밀번호가 변경된다. 아래처럼 암호화된 비밀번호로 변경된것을 볼 수 있다.

 

mysql> select host, user, plugin, authentication_string, password_last_changed from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | plugin                | authentication_string                                                  | password_last_changed |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2018-09-11 18:20:19   |
| localhost | root             | mysql_native_password | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA                              | 2018-09-12 10:28:23   |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
 
아래와 같은 에러가 발생하면
ERROR 1819 (HY000): Your password does not satisfy the current policy requirement 
 
패스워드 정책 문제로 아래 명령어로 변경해야 합니다.
SET GLOBAL validate_password_policy=LOW;
 
 

마지막으로 가장 중요한 게 남았다. 변경하면 끝이 아니라, 저장을 해줘야 한다!

 

 

2. 변경 사항 저장

flush privileges;

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
 

비밀번호가 성공적으로 바뀌었을 것이다.

quit로 나갔다가 다시 로그인하여 변경한 비밀번호로 로그인하자.

 

mysql> quit;
Bye
 
C:\Windows\system32>mysql -uroot -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.12 MySQL Community Server - GPL
 
Copyright (c) 2000, 2018, 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>
TAG •