[MySQL 8.0] 사용자(계정) 및 권한 정복하기

시스템 계정과 일반 계정

SYSTEM_USER 권한을 가지고 있느냐 없느냐에 따라 시스템 계정과 일반 계정으로 구분된다.

시스템 계정은 DB 서버 관리자(DBA)를 위한 계정이고 일반 계정은 개발자를 위한 계정이다.

시스템 계정은 다음과 같이 DBMS 관리와 관련된 중요 작업을 수행할 수 있는 권한이 있다.

• 데이터베이스 관리 작업
• 일반 계정 관리
• 다른 세션 또는 그 세션에서 실행 중인 쿼리 강제 종료

참고로 MySQL 서버에는 다음과 같이 내장된 계정들이 있다.

SELECT user, host, account_locked FROM mysql.user WHERE user LIKE 'mysql.%';

root@localhost를 제외한 위 3개의 계정은 내부적으로 다음과 같은 목적으로 사용된다.

 

mysql.infoschema@localhost

infromation_schema에 정의된 뷰들을 관리하는 시스템 계정

MySQL의 메타데이터(테이블, 칼럼, 인덱스 등의 정보)를 조회하는 데 사용할 수 있다.

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'titles'

위 시스템 계정들은 account_locked가 ‘Y’로 되어있어 잠금을 풀지 않는 한 악의적으로 사용할 수 없다.

 

mysql.session@localhost

MySQL 플러그인이 서버와 통신할 때 사용하는 내부 시스템 계정

 

mysql.sys@localhost

sys 스키마의 객체(뷰, 함수, 프로시저 등)를 관리하는 계정

성능 모니터링과 문제 해결을 위한 도구 제공한다.

SELECT * FROM sys.memory_global_total;              	-- 전체 메모리 사용량
SELECT * FROM sys.memory_global_by_current_bytes;   	-- 메모리 사용량 상세
SELECT * FROM sys.memory_by_host_by_current_bytes;  	-- 호스트별 메모리 사용량
SELECT * FROM sys.memory_by_thread_by_current_bytes;	-- 스레드별 메모리 사용량

더 다양한 스키마 뷰, 함수, 프로시저 종류들은 아래 문서에서 찾을 수 있다.

https://dev.mysql.com/doc/refman/8.0/en/sys-schema-views.html

https://dev.mysql.com/doc/refman/8.0/en/sys-schema-functions.html

https://dev.mysql.com/doc/refman/8.0/en/sys-schema-procedures.html

---

계정 생성하기

다음과 같이 계정을 생성할 수 있다.

CREATE USER '계정명'@'호스트IP'

이 때 계정명과 호스트IP를 홑따옴표 (’)로 감싸주어 MySQL이 식별할 수 있도록 해야한다.

만약 호스트IP를 생략한다면 ‘%’가 추가된다.

(%는 와일드카드를 의미하며 모든 IP 또는 모든 호스트명을 뜻한다.)

 

계정 생성 옵션

다음과 같이 추가적인 옵션으로 계정을 생성할 수도 있다.

CREATE USER '계정명'@'호스트IP' 
IDENTIFIED WITH 'mysql_native_password' BY '비밀번호'
REQUIRE NONE
PASSWORD EXPIRE INTERVAL 30 DAY
PASSWORD_HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE
ACCOUNT UNLOCK;

이외에도 많은데 사용할수 있을만한? 옵션들만 가져왔으며 하나씩 알아보자.

 

IDENTIFIED WITH

사용자의 인증 방식과 비밀번호를 결정한다.

WITH 다음에는 인증 방식(인증 플러그인)을 명시해야하는데

기본 인증 방식을 사용한다면 IDENTIFIED BY ‘비밀번호’ 형식으로 명시할 수 있다.

MySQL 서버의 대표적인 4가지 인증 방식은 다음과 같다. (더 궁금하다면 추가로 알아보자)

• Native Pluggable Authentication (5.7의 기본 인증 방식)
• Caching SHA-2 Pluggable Authentication (8.0의 기본 인증 방식)
• PAM Pluggable Authentication
• LDAP Pluggable Authentication

 

REQUIRE

MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 설정한다.

(별도로 설정하지 않으면 비암호화 채널로 연결)

REQUIRE를 설정하지 않았다고 하더라도 Caching SHA-2 인증 방식을 사용한다면 암호화된 채널만으로 MySQL 서버에 접속할 수 있다.

보통 내부망을 사용할 할 텐데 이렇게 까지 암호화하는 건 과하지 않나? 생각이 들긴한다.

 

PASSWORD EXPIRE

비밀번호의 유효 기간을 설정한다.

별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정된다.

다음과 같은 옵션을 사용할 수 있다.

• PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호의 만료 처리
• PASSWORD EXPIRE NEBER : 계쩡 비밀번호의 만료 기간 없음
• PASSWORD EXPIRE DEFAULT : 명시하지 않은 것과 같다.
• PASSWORD EXPIRE INTERVAL n DAY : 비밀번호의 유효 기간을 오늘부터 n일자로 설정

 

PASSWORD_HISTORY

한 번 사용했던 비밀번호를 재사용하지 못하게 설정한다.

다음과 같은 옵션을 사용할 수 있다.

• PASSWORD_HISTORY DEFAULT : password_history 시스템 변수에 저장된 개수만큼 비밀번호 이력을 관리하고, 이력에 남이있는 비밀번호는 재사용 불가.
• PASSWORD_HISTORY n : 비밀번호 이력을 최근 n개 까지만 저장, 이력에 남이있는 비밀번호는 재사용 불가.

 

PASSWORD REUSE INTERVAL

한 번 사용했던 비밀번호의 재사용 금지 기간을 설정한다.

별도로 명시하지 않으면 password_resuse_interval 시스템 변수에 저장된 기간으로 설정된다.

다음과 같은 옵션을 사용할 수 있다.

• PASSWORD REUSE INTERVAL DEFAULT : 명시하지 않은 것과 같다.
• PASSWORD REUSE INTERVAL n DAY : n 일자 이후에 비밀번호를 재사용할 수 있게 설정

 

PASSWORD REQUIRE

비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호를 필요로 할지 말지를 결정하는 옵션.

별도로 명시하지 않으면 password_require_current 시스템 변수의 값으로 설정된다.

다음과 같은 옵션을 사용할 수 있다.

• PASSWORD REQUIRE CURRENT : 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
• PASSWORD REQUIRE OPTIONAL : 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
• PASSWORD REQUIRE DFEAULT : 명시하지 않은 것과 같다.

 

ACCOUNT

사용자 계정의 접근을 제어하기 위한 보안 기능이다.

LOCK은 계정을 사용하지 못하게 잠그고, UNLOCK은 사용 가능한 상태이다.

 

주의할 점

계정 생성에 주의할 점이 있다.

만약 아래와 같은 동일한 2개의 계정이 있다고 가정해보자.

'hyun'@'127.0.0.1' 	-- 비밀번호 : 1234
'hyun'@'%' 		-- 비밀번호 : abcd

IP가 127.0.0.1인 계정에서 MySQL 서버에 접속할 때 MySQL 서버는 어떤 계정 정보를 가지고 인증을 할까?

바로, 더 범위가 적은 127.0.0.1 IP를 가진 계정 정보로 인증한다.

따라서 1234로 접속이 가능하다.

중복된 계정을 생성할 일이 많진 않을 수 있지만 실수로 위와 같은 상황이 발생할 수 있다는 것을 인지해야한다.

(필자는 한번 이런 상황을 겪었다..)

---

Dual Password (이중 비밀번호)

MySQL 8.0 버전 이전까지는 서비스가 실행 중인 상태에서 보안을 위해 비밀번호를 변경한다면 서비스를 종료하고 변경했어야 했다.

하지만 MySQL 8.0 이후부터는 서비스를 종료하지 않고도 변경이 가능한 ‘Dual Password’ 기능이 나왔다.

이중 비밀번호를 사용하기 위해서는 다음과 같이 RETAIN CURRENT PASSWORD 옵션을 사용하면 된다.

-- 기존 비밀번호 : 1234
-- 새 비밀번호 : abcd
ALTER USER 'hyun'@'127.0.0.1' IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;

이렇게 설정하면 기존 1234 비밀번호는 Secondary 비밀번호가 되고

새 비밀번호인 abcd는 Primary 비밀번호로 구분된다.

이 상태에서 둘중 어느 비밀번호를 입력하던 로그인이 가능하다.

 

이후 MySQL 서버의 재시작이 된다면, 보안상 Secondary 비밀번호를 다음과 같이 제거할 수 있다.

ALTER USER 'hyun'@'127.0.0.1' DISCARD OLD PASSWORD;

---

Privilege (권한)

MySQL 8.0에는 권한이 Global, Objects, Dynamic 권한으로 구별된다.

• Global 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
• Objects 권한 : 데이터베이스나 테이블을 제어하는 데 필요한 권한
• Dynamic 권한 : MySQL 서버가 시작되면서 동적으로 생성하는 권한

위 권한 종류들을 알아보려면 아래 공식 문서를 참고하자.

https://dev.mysql.com/doc/refman/9.0/en/privileges-provided.html

 

사용자에게 권한을 부여할 때는 다음과 같이 GRANT 명령을 사용할 수 있다.

GRANT privilege_list ON db.table TO '계정명'@'호스트IP';

 

글로버 권한 부여

글로벌 권한의 경우에는 DB나 테이블에 부여될 수 없어 GRANT 명령의 ON 절에 항상 *.*를 사용한다.

(*.*은 MySQL 서버 전체를 의미)

GRANT SUPER ON *.* TO '계정명'@'호스트IP';

 

DB나 TABLE에 대한 권한 부여

특정 DB나 TABLE에 대해서만 권한을 부여할 수 있는데 다음과 같이 적용할 수 있다.

GRANT SELECT ON *.* TO '계정명'@'호스트IP';
GRANT SELECT ON employees.* TO '계정명'@'호스트IP';
GRANT SELECT ON employees.titles TO '계정명'@'호스트IP';

 

특정 COLUMN에 대한 권한 부여

특정 컬럼에 대해서 권한을 부여할 경우 아래와 같이 부여할 수 있다.

-- employees.titles 테이블에서 SELECT는 모두 가능하지만 emp_no 컬럼은 DELETE 할 수 없음
GRANT SELECT, DELETE(emp_no) ON employees.titles TO '계정명'@'호스트IP';

---

Role (역할)

메서드 처럼 여러 권한이 적용된 Role를 만들어 이 역할을 계정에 부여하며 재사용 가능하다.

 

역할은 다음과 같이 생성할 수 있다.

-- 2개의 역할 생성 (현재는 빈껍데기)
CREATE ROLE role_read, role_write;

-- 실제 권한 부여
GRANT SELECT ON *.* TO role_read;
GRANT INSERT, UPDATE, DELETE ON *.* TO role_write;

 

이제 이 생성한 역할을 계정에 부여해야한다.

GRANT role_read, role_write TO 'hyun'@'127.0.0.1';

하지만 이렇게까지만 설정하고 해당 계정으로 접속해 조회나 쓰기 작업을 실행하면 Access denied라는 에러가 발생한다.

실제 역할은 부여돼 있지만 계정에 활성화된 역할을 조회해보면 역할이 없다고 나올 것이다.

 

해당 역할을 사용하려면 다음 명령어를 통해 활성화 해주어야한다.

SET ROLE role_read, role_write;

 

또 문제가 있는데 지금 상태로는 재접속 시 역할이 다시 비활성화된다.

아래 명령어를 설정해주어야만 재접속하더라도 부여한 역할이 자동으로 활성화된다.

(참고로 SYSTEM_VARIABLES_ADMIN 또는 SUPER 권한이 있는 계정에서 다음 옵션을 설정할 수 있다.)

SET GLOBAL activate_all_roles_on_login=ON;

---

참고자료

• Real MySQL 8.0