MySQL 8 계정 이슈

mysql이 8 버전이 됨에 따라 계정 관리 부분에 변경이 많이 되었다.

해당 이슈는 mysql 5.7 에서 plugin으로 사용이 가능했던 sha2 인증이

8에서 default 옵션이 되었기 때문이다.

 

sha2로 변경된 이유는 기존에 mysql_native_password 경우에는

user의 mysql.user.authentication_string (5.7 이전에는 mysql.user.password)이

다음과 같이 생성되었다.

 

SELECT CONCAT(``'*'``,UPPER(SHA1(UNHEX(SHA1(``"some_strings"``)))));

 

String을 SHA1으로 암호화 하고, 해당 내용을 UNHEX후 다시 SHA1으로 암호화하지만,

SHA1 알고리즘은 충돌기법과 rainbow테이블을 이용하여 비밀번호가 노출될 수 있다.

그래서 mysql 8 부터는 RSA Key를 이용한 방식으로 처리된 것이다.

 

또한 계정을 생성하는 방식 또한 변경이 있었다.

아래는 5.7과 8에서의 계정 이용 방법이다.

 

MySQL Version 5.7

 

-- 계정 생성 

insert mysq.user (host, user, authentication_string, 

ssl_cipher, x509_issuer, x509_subject)

values ('%', 'account', password('Your Password'), '', '', '')

 

-- 권한 (모든 권한)

grant all privileges on *.* to 'account'@'%' with grant option;

 

-- 권한 일부 권한

grant select, insert, update, delete, execute on *.* to 'account'@'%' 

with grant option;

 

-- 비밀 번호 변경 방법1

UPDATE mysql.user SET authentication_string=PASSWORD('Your Password') 

WHERE User='account' AND Host='%'; 

 

-- 비밀 번호 변경 방법2

SET PASSWORD FOR 'account'@'%' = PASSWORD('Your Password');

 

--- 비밀 번호 변경 방법3

ALTER USER 'account'@'%' identified by 'Your Password'; 

 

-- 권한 확인 # show grants for 'user'@'접속위치'; 

show grants for 'account'@'%';

 

-- 계정 삭제 # drop user '계정아이디'@'접속위치'; 

drop user 'account'@'%';

 

-- 권한 삭제

-- revoke all[|select|insert...] on DB이름.테이블 FROM '계정아이디'@'접속위치'; 

revoke all on *.* FROM 'account'@'%';

 

-- 권한 flush

flush privileges;

 

MySQL Version 8

 

-- 계정 생성

create user 'account'@'%' identified by 'Your Password';

 

-- 권한 (모든 권한)

grant all privileges on *.* to 'account'@'%' with grant option;

 

-- 권한 일부 권한

grant select, insert, update, delete, execute on *.* to 'account'@'%' 

    with grant option;

 

--- 비밀 번호 변경 방법1

ALTER USER 'account'@'%' identified by 'Your Password'; 

 

--- 비밀 번호 변경 방법2 plugin 변경

ALTER USER 'account'@'%' identified with 

cashing_sha2_password by 'Your Password'; 

ALTER USER 'account'@'%' identified with 

mysql_native_password by 'Your Password'; 

 

-- 권한 확인 # show grants for 'user'@'접속위치'; 

show grants for 'account'@'%';

 

-- 계정 삭제 # drop user '계정아이디'@'접속위치'; 

drop user 'account'@'%';

 

-- 권한 삭제 

-- revoke all[|select|insert...] on DB이름.테이블 FROM '계정아이디'@'접속위치'; 

revoke all on *.* FROM 'account'@'%';

 

-- 권한 flush

flush privileges;

 

위와 같이 계정 변경 쿼리로 Password 변경을 시도할 때 Plugin을 변경하여 시도할 수 있다.

하지만 mysql_native_password 옵션으로 변경한 계정을

sha2 관련 library가 업데이트된 Tool이 아닌 기존 Tool 에서 접근했을 시

다음과 같은 오류를 확인할 수 있다.(다음 예제는 Toad for MySQL 이다,.)

 

해당 오류를 해결하기 위해서는 서버에서 다음과 같은 옵션을 수정해 주어야 하는데

cnf 파일에서 다음과 같은 변수를 추가해야 한다.

(추가하지 않을 시 default가 cashing_sha2_password 이다.)

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

 

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

 

[mysqld]

#

# * Basic Settings

#

user            = mysql

# pid-file      = /var/run/mysqld/mysqld.pid

# socket        = /var/run/mysqld/mysqld.sock

port            = 3306

# datadir       = /var/lib/mysql

 

default_authentication_plugin=mysql_native_password

 

default_authentication_plugin=mysql_native_password 으로

새로 생성된 계정이 mysql_native_password 방식을 따르게 하는 것이다.

해당 변수는 global read only 변수이기 때문에 cnf파일에서만 변경이 가능하며

해당 변수 설정 후에는 반드시 mysql server가 재시작되어야 한다.

 

위와 같이 보안 이슈 때문에 계정 인증에 대한 변경이 일어났지만

2018년 이후부터 계속 release 되고 있는 8 버전이나 아직 Tool 등이 대응되지 못한 듯 한다.

 

보안과 사용성은 역시 Trade-off 이지 않나 싶다.

 

 참조

MySQL 계정 및 권한 관리 DB Library (tistory.com)

MySQL 8.0 - Authentication_plugin 의 변경 | MINSQL

MySQL :: MySQL 8.0 Reference Manual :: 13.7.1.1 ALTER USER Statement