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 theplugin
column of themysql.user
system table.For
auth_option
syntaxes that do not specify an authentication plugin, the default plugin is indicated by the value of thedefault_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 themysql.user
system table. An'
orauth_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:hash_string
'-
For syntaxes that use
'
, the string is cleartext and is passed to the authentication plugin for possible hashing. The result returned by the plugin is stored in theauth_string
'authentication_string
column. A plugin may use the value as specified, in which case no hashing occurs. -
For syntaxes that use
'
, 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.hash_string
'
-
-
A
REPLACE '
clause (available as of MySQL 8.0.13) specifies the current account password to be replaced, as a cleartext (unencrypted) string:current_auth_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
'
value to the plugin for hashing, and stores the result in theauth_string
'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 themysql.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'
value to the plugin for hashing, and stores the result in theauth_string
'mysql.user
account 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'
value as is in thehash_string
'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)
마지막으로 가장 중요한 게 남았다. 변경하면 끝이 아니라, 저장을 해줘야 한다!
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>