사용자 및 권한

김민우·2022년 12월 29일

MySQL

목록 보기

2/7

MySQL에서 사용자 계정을 생성하는 방법이나 각 계정의 권한을 설정하는 방법은 다른 DBMS와는 조금 차이가 있다. 대표적으로 MySQL의 사용자 계정은 단순히 사용자의 아이디뿐 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인한다.
또한, MySQL 8.0 버전부터는 권한을 묶어서 관리하는 역할(Role, 롤)의 개념이 도입됐기 때문에 각 사용자의 권한으로 미리 준비된 권한 세트(Role)를 부여하는 것도 가능하다.

DB 서버의 보안은 갈수록 중요해지고 있으므로 반드시 계정의 식별 방식과 권한, 역할에 대한 기본적인 내용은 꼭 숙지하자.

1. 사용자 식별

MySQL의 사용자는 다른 DBMS와는 조금 다르게 사용자의 계정뿐 아니라 사용자의 접속 지점(클라이언트가 실행된 호스트명이나 도메인 또는 IP 주소)도 계정의 일부가 된다. 따라서 MySQL에서는 계정을 언급할 때는 다음과 같이 항상 아이디와 호스트를 함께 명시해야 한다.

MySQL에서 식별자를 감싸는 역할은 따옴표(')가 한다는 것을 알아두자. MySQL에서 사용자 계정은 항상 MySQL 서버가 기동 중인 로컬 호스트에서 svc_id 라는 아이디로 접속할 때만 사용될 수 있는 계정이다. 만약 사용자 계정에 다음과 같은 계정만 등록돼 있다면 다른 컴퓨터에서는 svc_id 라는 아이디로 접속할 수 없음을 의미한다. 이는 IP를 통해 설정할 수 있다.

'svc_id'@'127.0.0.1'

만약 모든 외부 컴퓨터에서 접속 가능한 사용자 계정을 만들고 싶다면 호스트 부분(IP 주소)를 %로 대체하면 된다.

'svc_id'@'%'

그러면 다음과 같이 2개의 사용자 계정이 있는 MySQL 서버가 있다고 가정해보자.

'svc_id'@'192.168.0.10' (이 계정의 비밀번호는 123)
'svc_id'@'%' (이 계정의 비밀번호는 abc)

IP주소가 192.168.0.10인 PC에서 이 MySQL 서버에 접속할 때 MySQL 서버가 첫 번째 계정 정보를 이용해 인증을 실행할지, 아니면 두 번째 계정 정보를 이용할지에 따라 접속은 성공할 수 있고 실패할 수 있다. MySQL은 둘 중 어떤 것을 선택할까? 권한이나 계정 정보에 대해 MySQL은 범위가 가장 작은 것을 선택한다는 것을 알아두자. 이를 알면 둘 중 첫 번째를 선택한다는 것을 바로 알 수 있다.

즉, 이러한 상황에서 비밀번호를 abc로 하면 비밀번호가 일치하지 않는다는 이유로 접속이 거절될 것이다. 이러한 상황을 조심하자.

2. 사용자 계정 관리

2.1. 시스템 계정과 일반 계정

MySQL 8.0 부터 계정은 SYSTEM_USER 권한을 가지고 있는지 여부에 따라 시스템 계정(System Account)과 일반 계정(Regular Account)로 구분된다. 여기서 소개하는 시스템 계정은 MySQL 서버 내부적으로 실행되는 백그라운드 스레드와는 무관하며, 시스템 계정도 일반 계정과 같이 사용자를 위한 계정임을 알아두자. 시스템 계정은 데이터베이스 서버 관리자를 위한 계정이며, 일반 계정은 응용 프로그램이나 개발자를 위한 계정 정도로 생각하면 이해하기 쉬울 것이다.

시스템 계정은 시스템 계정과 일반 계정을 관리할 수 있지만 일반 계정은 시스템 계정을 관리할 수 없다. 또한 다음과 같이 데이터베이스 서버 관리와 관련된 중요 작업은 시스템 계정으로만 수행할 수 있다.

계정 관리 (계정 생성 및 삭제, 그리고 계정의 권한 부여 및 제거)
다른 세션(커넥션) 또는 그 세션에서 실행 중인 쿼리를 강제 종료
스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정

이렇게 시스템 계정과 일반 계정의 개념이 도입된 것은 DBA(데이터베이스 관리자) 계정에는 SYSTEM_USER 권한을 할당하고 일반 사용자를 위한 계정에는 SYSTEM_USER 권한을 부여하지 않게 하기 위해서이다.

MySQL 서버에는 다음과 같이 내장된 계정들이 있는데 'root'@'localhost'를 제외한 3개의 계정은 각기 다른 목적으로 사용되므로 삭제하지 않도록 주의하자.

'mysql.sys'@localhost'
- sys 스키마의 객체 (View나 함수 그리고 프로시저)들의 DEFINER로 사용되는 계정
'mysql.session'@localhost'
- MySQL 플러그인이 서버로 접근할 때 사용되는 계정
'mysql.sys'@localhost'
- information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

이 3개의 계정은 처음부터 잠겨(account_locked 컬럼) 있는 상태이므로 의도적으로 이를 풀지 않는 한 악의적인 용도로 사용할 수 없으므로 보안을 걱정하지는 않아도 된다.

2.2 계정 생성

MySQL 8.0 버전부터는 계정 생성을 CREATE USER 명령어로, 권한 부여는 GRANT 명령어로 수행한다. 계정 생성부터 알아보자.

계정을 생성할할 때는 다음과 같은 다양한 옵션을 설정할 수 있다.

계정의 인증 방식과 비밀번호
비밀번호 관련 옵션(유효 기간, 이력 개수, 재사용 불가 시간)
기본 역할
SSL 옵션
계정 잠금 여부

일반적으로 많이 사용되는 옵션을 가진 CREATE USER 명령은 다음과 같다.

mysql> CREATE USER 'user'@'%'
		IDENTIFIED WITH 'mysql_native_password' by 'password'
        REQUIRE NONE
        PASSWORD EXPIRED INTERVAL 30 DAY
        ACCOUNT UNLOCK
        PASSWORD HISTORY DEFAULT
        PASSWORD REUSE INTERVAL DEFAULT
        PASSWORD REQUIRE CURRENT DEFAULT;

위 예제의 각 옵션들을 하나씩 살펴보자.

2.2.1 IDENTIFIED WITH

사용자의 인증 방식과 비밀번호를 설정한다. IDENTIFIED WITH 뒤에는 반드시 인증 방식(인증 플러그인의 이름)을 명시해야 하는데, MySQL 서버의 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시해야 한다. MySQL 서버에서는 다양한 인증방식을 플러그인 형태로 제공하며, 다음 4가지의 방식이 가장 대표적이다.

Native Pluggable Authentication
Caching SHA-2 Pluggable Authentication
PAM Pluggable Authentication
LDAP Pluggable Authentication

MySQL 8.0 부터는 Caching SHA-2 Pluggable Authentication이 기본 인증으로 바뀌었다. 하지만, Cacing SHA-2 Pluggable Authentication은 SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에 기본 MySQL 5.7까지의 연결 방식과는 다른 방식으로 접속해야 한다. 그래서 보안 수준은 좀 낮아질 수 있지만 기존 버전과 호환성을 고려한다면 Caching SHA-2 Pluggable Authentication 보다는 Native Pluggable Authentication 인증 방식으로 계정을 생성해야 할 수 있다.

만약, MySQL 8.0이상 버전에서도 Native Pluggable Authentication을 기본 인증 방식으로 설정하고자 한다면 다음과 같이 MySQL 설정을 변경하거나 my.cnf 설정 파일에 추가하면 된다.

mysql> SET GLOBAL default_authentication_plugin="mysql_native_password"

CREATE USER 또는 ALTER USER 명령어를 통해 MySQL 서버의 계정을 생성 또는 변경할 때 연결 방식과 비밀번호 옵션, 자원 사용과 관련된 여러 옵션을 설정할 수 있다.

2.2.2 REQUIRE

MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 결정한다 만약 별도로 설정하지 않으면 비암호화 채널로 연결하게 된다. 하지만 REQUIRE 옵션을 SSL로 설정하지 않아도 Caching SHA-2 Pluggable Authentication 인증 방식을 사용하면 암호화된 채널만으로 MySQL 서버에 접속할 수 있게 된다.

2.2.3 PASSWORD EXPIRE

비밀번호의 유효기간을 설정하는 옵션이며, 별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효기간이 설정된다. 개발자나 데이터베이스 관리자의 비밀번호는 유효 기간을 설정하는 것이 보안상 안전하나, 응용 프로그램 접속용 계정에 유효 기간을 설정하는 것은 위험할 수 있으니 주의하자.

다음은 PASSWORD EXPIRE 절에 설정 가능한 옵션이다.

PASSWORD EXPIRE
- 계정 생성과 동시에 비밀번호의 만료 처리
PASSWORD EXPIRE NEVER
- 계정 비밀번호의 만료 기간 없음
PASSWORD EXPIRE DEFAULT
- default_password_lifetime 시스템 변수에 저장된 기간으로 유효기간을 설정
PASSWORD EXPIRE INTERVAL n
- 비밀번호의 유효 기간을 오늘로부터 n일자로 설정

2.2.4 PASSWORD HISTORY

한 번 사용했던 비밀번호를 재사용하지 못하게 하는 옵션이다. PASSWORD HISTORY 절에 설정 가능한 옵션은 다음과 같다.

PASSWORD HISTORY DEFAULT
- password_history 시스템 변수에 저장된 개수만큼 비밀번호의 이력을 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없다.
PASSWORD HISTORY n
- 비밀번호의 이력을 최근 n개 까지만 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없다.

한 번 사용했던 비밀번호를 사용하지 못하게 하려면 이전에 사용했던 비밀번호를 MySQL 서버가 기억하고 있어야 하는데, 이를 위해 MySQL 서버는 mysql DB의 password_history 테이블을 사용한다.

2.2.5 PASSWORD REUSE INTERVAL

한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션이다. 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정된다. PASSWORD REUSE INTERVAL 절에 설정 가능한 옵션은 다음과 같다.

PASSWORD REUSE INTERVAL DEFAULT
- password_reuse_interval 시스템 변수에 저장된 기간으로 설정
PASSWORD REUSE INTERVA n
- n일자 이후에 비밀번호를 재사용할 수 있게 설정

2.2.6 PASSWORD REQUIRE

비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션이며, 별도로 명시되지 않으면 password_require_current 시스템 변수의 값으로 설정된다. PASSWORD REQUIRE 절에 사용가능한 옵션은 다음과 같다.

PASSWORD REQUIRE CURRENT
- 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
PASSWORD REQUIRE OPTIONAL
- 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
PASSWORD REQUIRE DEFAULT
- password_require_current 시슽템 변수의 값으로 설정

2.2.7 ACCOUNT LOCK / UNLOCK

계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부를 결정한다.

ACCOUNT LOCK
- 계정을 잠금하지 못하게 잠금
ACCOUNT UNLOCK
- 잠긴 계정을 다시 사용 가능 상태로 잠금 해제

3. 비밀번호 관리

3.1 고수준 비밀번호

MySQL 서버의 비밀번호는 유효기간이나 이력 관리를 통한 재사용 금지 기능뿐 아니라 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 것이 가능하다. MySQL 서버에서 비밀번호의 유효성 체크 규칙을 적용하기 위해선 validate_password 컴포넌트를 이용하면 된다.

비밀번호 정책은 크게 다음 3가지 중에서 선택할 수 있으며 디폴트 값은 MEDIUM 이다.

LOW
- 비밀번호의 길이만 검증
MEDIUM
- 비밀번호의 길이와 숫자와 대소문자 그리고 특수문자의 조합을 검증
STRONG
- MEDIUM 단계의 검증을 수행하고 금칙어가 포함되있는지 여부까지 검증

비밀번호의 길이는 validate_password.length 시스템 변수에 설정된 길이 이상인지로 검증을 한다.
숫자와 대소문자, 특수문자는 각각 validate_password.mixed_case_count 와 validate_password.number_count, validate_password.special_char_count 시스템 변수에 설정된 글자 수 이상을 포함하고 있는지를 검증한다.
마지막으로 금칙어는 validate_password.dictonary_file 시스템 변수에 설정된 사전 파일에 명시된 단어를 포함하고 있는지를 검증한다.

높은 수준의 보안을 원한다면 validate_password.dictonary_file에 일반적으로 많이 사용하는 비밀번호를 저장해놓으면 된다. 이 시스템 변수에 금칙어를 등록하는 방법은 다음과 같이 텍스트 파일에 한 줄에 하나씩 금칙어를 적은 후 해당 파일을 등록하면 된다. 물론 이는 하나씩 직접 등록하기 보단 깃허브를 검색하여 파일을 내려받아 내가 필요한 것을 더 추가하는 것이 효율적이다.

<validate_password.dictonary_file 예시>
password
123456
12345678
qwerty
...

이렇게 금칙어 파일이 준비되면 MySQL 서버에 금칙어 파일을 등록하면 된다. 당연하겠지만 비밀번호 정책을 담당하는 validate_password.policy 값이 STRONG으로 설정이 되있어야 하므로 이 값도 함께 변경해야 한다.

mysql> SET GLOBAL validate_password.dictonary_file = 'prohibitve_word.data`;
mysql> SET GLOBAL validate_password.policy='STRONG';

참고
MySQL 8.0부터는 validate_password가 플러그인이 아닌 컴포넌트 형태로 제공된다. 사용자 측면에선 플러그인이나 컴포넌트 모두 거의 동일한 기능을 제공하며, 단지 시스템 변수 이름만 차이가 있다. 하지만 플러그인의 단점을 보완하기 위해 컴포넌트가 나온 것 이므로 컴포넌트를 사용하자.

3.2 이중 비밀번호

일반적으로 많은 응용 프로그램 서버들이 공용으로 데이터베이스 서버를 사용하기 때문에 데이터베이스 서버의 계정 정보는 응용 프로그램 서버로부터 공용으로 사용되는 경우가 많다. 이러한 구현 특성 때문에 데이터베이스 서버의 계정 정보는 쉽게 변경하기가 어려운데, 그중에서도 데이터베이스 계정의 비밀번호는 서비스가 실행 중인 상태에서 변경이 불가능하다. 그래서 서비스에서 데이터베이스 계정의 비밀번호는 처음 설정한 상태로 몇 년동안 사용되는 경우가 많다.

보안을 생각했을 때, 데이터베이스 계정의 비밀번호는 주기적으로 바꿔주는것이 권장되나 이를 위해선 서비스를 모두 멈춰야 했기에 불가능한 일이였다.

이 같은 문제점을 해결하기 위해 MySQL 8.0 부터는 계정의 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능을 제공한다. 이 기능을 이중 비밀번호(Dual Password)라 한다.

중요
이중 비밀번호라는 단어의 뜻을 생각하면 두 비밀번호가 모두 일치해야 로그인이 가능하다는 의미로 보이나, 여기서 이중 비밀번호는 둘 중 하나의 비밀번호만 일치해도 로그인이 가능하다는 의미이다. 이를 명심하자.

하나의 계정에 대해 두 개의 비밀번호를 동시에 설정할 때, 두 개의 비밀번호는 각각 프라이머리(Primary)와 세컨더리(Secondary)로 구분된다. 최근에 설정한 비밀번호는 프라이머리 비밀번호이며, 이전 비밀번호가 세컨더리가 된다.

이중 비밀번호를 사용하려면 다음과 같이 기존 비밀번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션만 추가하면 된다.

-- 비밀번호를 "old_password"로 설정
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';

-- 비밀번호를 "new_password"로 변경하면서 기존 비밀번호를 세컨더리 비밀번호로 설정
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

이렇게 되면 root 계정은 두 비밀번호중 아무거나 입력해도 로그인이 된다. 이렇게 설정된 상태에서 데이터베이스에 연결하는 응용 프로그램의 소스코드나 설정 파일의 비밀번호를 새로운 비밀 번호인 new_password로 변경하고 배포 및 재시작을 순차적으로 실행한다.

MySQL 서버에 접속하는 모든 응용 프로그램의 재시작이 완료되면 이제 다음 명령으로 세컨더리 비밀번호는 삭제한다. 세컨더리 비밀번호를 꼭 삭제해야 하는 것은 아니지만 계정의 보안을 위해 다음과 같이 세컨더리 비밀번호는 삭제하는 것이 좋다.

-- 세컨더리 비밀번호 삭제
mysql> ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

이렇게 세컨더리 비밀번호를 삭제하면 이후부턴 프라이머리 비밀번호(새롭게 바꾼 비밀번호)로만 로그인이 가능하다.

4. 권한(Privilege)

MySQL 5.7 버전까지는 권한은 글로벌(Global) 권한과 객체 단위의 권한으로 구분됬고, MySQL 8.0 부터는 동적 권한이 추가됬고 글로벌 권한과 객체 단위의 권한을 묶어 정적 권한이라 한다.

정적 권한 : MySQL 서버의 소스코드에 고정적으로 명시돼있는 권한
- 글로벌 권한
  - 데이터베이스나 테이블 이외의 객체에 적용되는 권한
  - GRANT 명령에서 특정 객체를 명시하면 안된다.
- 객체 권한
  - 데이터베이스나 테이블 객체에 적용되는 권한
  - GRANT 명령으로 권한을 부여할때는 반드시 특정 객체를 명시헤야 한다.
동적 권한 : MySQL 서버가 시작되면서 동적으로 생성하는 권한
- 예) MySQL 서버의 컴포넌트나 플러그인이 설치되면 그 때 등록되는 권한

사용자에게 권한을 부여할 때는 GRANT 명령을 사용한다. GRANT 명령은 다음과 같은 문법으로 작성하는데, 각 권한의 특성에 따라 GRANT 명령의 ON절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀌어야 한다.

mysql> GRANT privilege_list ON db.table TO 'user'@'host';

privilege_list 는 구분자(,)를 사용하여 여러 권한 키워드를 동시에 사용할 수 있도록 해준다. TO 키워드는 권한을 부여받을 사용자를 의미하고 ON 키워드는 어떤 오브젝트 권한을 부여할 지 결정하는데 이 ON 절에 주목할 필요가 있다. 권한의 범위에 따라 ON절을 사용하는 방법이 달라지기 때문이다. 이는 예제로 살펴보자.

참고
MySQL 8.0 부터는 반드시 존재하는 사용자에 대해서만 GRANT 명령어를 실행할 수 있으므로 이에 주의하자.

글로벌 권한

mysql> GRANT SUPER ON *.* TO 'user'@'localhost';

글로벌 권한은 특정 DB나 테이블에 부여될 수 없으므로 글로벌 권한을 부여할 때 GRANT 명령의 ON 절에는 항상 *.*를 사용해야 한다.

*.* : 모든 DB의 모든 오브젝트(테이블과 스토어드 프로시저나 함수 등)을 포함해서 MySQL 서버 전체를 의미

DB 권한

mysql> GRANT EVENT ON *.* TO 'user'@'localhost';
mysql> GRANT EVENT ON employees.* TO 'user'@'localhost';

DB 권한은 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대해 권한을 부여할 수 있기 때문에 ON 절에 *.*이나 employees.* 모두 사용할 수 있다.

하지만 DB 권한만 부여하는 경우에는(DB 권한은 테이블에 대해 부여할 수 없기 때문에) employees.department 와 같이 테이블까지 명시할 수는 없다. DB 권한은 서버의 모든 DB에 적용할 수 있으므로 대상에 *.*을 사용할 수 있다. 또한 특정 DB에 대해서만 권한을 부여하는 것도 가능하기 때문에 db.*로 대상을 설정하는 것도 가능하다. 하지만 오브젝트 권한처럼 db.table로 오브젝트(테이블)까지 명시할 수는 없다.

테이블 권한

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'user'@'localhost';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON employees.department TO 'user'@'localhost';

테이블 권한은 모든 DB에 권한을 부여하는 것도 가능하며, 특정 DB의 오브젝트에 대해서만 권한을 부여하는 것도 가능하다. 또한, 특정 DB의 테이블에 대해서만 권한을 부여하는 것도 가능하다.

테이블의 특정 칼럼에 대해서만 권한을 부여하는 경우에는 GRANT 명령의 문법이 조금 달라져야 한다. 컬럼에 부여할 수 있는 권한은 DELETE를 제외한 INSERT, UPDATE, SELECT 3가지 이며, 다음과 같이 각 권한 뒤에 컬럼을 명시하는 형태로 부여한다.

mysql> GRANT SELECT(컬럼명) ON ... To ...

이렇게 명시하면 명시한 컬럼 외에 다른 컬럼은 SELECT 할 수 없다.

employees DB의 department 테이블에서 dept_name 컬럼을 업데이트 할 수 없게 권한을 부여하려면 다음과 같이 GRANT 명령어를 사용하면 된다.

이 경우는 SELECT나 INSERT는 모든 컬럼에 대해 수행할 수 있지만 UPDATE는 dept_name 컬럼에 대해서만 수행할 수 있다.

mysql> GRANT SELECT, INSERT, UPDATE(dept_name) ON employees.department TO 'user'@'localhost';

여러 가지 레벨이나 범위로 권한을 설정하는 것이 가능은 하지만 테이블이나 컬럼 단위의 권한은 잘 사용하지 않는다. 컬럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크를 일일히 하기 때문에 전체적인 성능에 영향을 미칠 수 있다.

컬럼 단위의 접근 권한이 반드시 필요하다면 GRANT 명령어 보다는 특정 컬럼만을 하는 뷰(View)를 만들어 사용하는 방법을 고려해보자. 뷰도 하나의 테이블로 인식되기 때문에 GRANT 명령어와는 다르게 별도로 모든 컬럼에 대한 조사를 하지 않기 때문이다.

각 계정이나 권한에 부여된 권한이나 역할을 확인하기 위해서는 SHOW GRANT 명령어를 사용할 수 있지만 표 형태로 깔끔하게 보고자 한다면 mysql DB의 권한 테이블을 참고하자.

5. 역할(Role)

MySQL 8.0 버전부터 권한을 묶어서 역할(Role)을 사용할 수 있게 되었다. 실제 MySQL 서버 내부적으로 역할(Role)은 계정과 똑같은 모습을 하고 있다. 따라서, 역할에 권한을 부여할 때는 TO 키워드 뒤에 계정 대신 역할을 명시하면 된다. 간단히 MySQL 8.0에서 역할을 사용하는 예시를 살펴보자.

우선 CREATE ROLE 명령어를 이용해 role_emp_read와 role_emp_write라는 이름의 역할을 정의한다.

-- 역할 생성
mysql> CREATE ROLE
		role_emp_read,
        role_emp_write;

위의 CREATE ROLE 명령어는 빈 껍데기만 있는 역할을 정의한 것이며, 다음과 같이 GRANT 명령어로 각 역할에 대한 실질적인 권한을 부여하면 된다. 역할의 이름대로 role_emp_read 역할에는 employees DB의 모든 객체에 대해 읽기(SELECT) 권한만 부여했고, role_emp_write 역할에는 employees DB의 모든 객체에 대해 데이터 변경(INSERT, UPDATE, DELETE) 권한을 부여했다.

-- 역할에 권한 부여
mysql> GRANT SELECT ON employees.* TO role_emp_read;
mysql> GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

기본적으로 역할은 그 자체로 사용될 수는 없고 계정에 부여해야 한다. CREATE USER 명령어를 통해 reader와 writer 라는 계정을 생성하고 해당 계정에 각각 역할을 부여해보자.

-- 계정 생성
mysql> CREATE USER `reader`@`127.0.0.1` IDENTIFIED BY 'qwerty``;
mysql> CREATE USER `writer`@`127.0.0.1` IDENTIFIED BY 'qwerty``;

-- 계정에 역할 부여
mysql> GRANT role_emp_read TO `reader`@`127.0.0.1`
mysql> GRANT role_emp_write TO `writer`@`127.0.0.1`

그런데 지금 상태에서 reader나 writer 계정으로 로그인 후 employee DB의 데이터를 조회 및 변경을 하면 권한이 없다는 에러 메시지가 나오게 된다. 계정이 역할을 사용하기 위해선 SET ROLE 명령어를 통해 역할을 활성화 시켜야하는데 이 과정을 하지 않았기 때문이다. 다음과 같이 역할을 활성화 하자.

-- 역할 활성화
mysql> SET ROLE 'role_emp_read';
mysql> SET ROLE 'role_emp_write';

MySQL 서버의 역할이 불편하고 수동적으로 보이는데, 이는 MySQL 서버의 역할이 자동으로 활성화 되지 않게 설정되있기 때문이다. 사용자가 MySQL 서버에 로그인할 때 역할을 자동으로 활성화할지 여부를 activate_all_roles_on_login 시스템 변수를 통해 설정할 수 있다. 다음과 같이 이 값을 ON으로 설정하면 매번 로그인마다 SET ROLE 명령어를 호출할 필요 없이 로그인과 동시에 역할이 활성화된다.

mysql> SET GLOBAL activate_all_roles_on_login=ON;

이제 역할의 비밀에 대해 좀 더 살펴보자. 앞에서 잠깐 언급했듯이 역할은 사용자 계정과 거의 유사한 모습을 하고 있으며 MySQL 서버 내부에서도 역할과 계정은 동일한 객체로 취급된다. 단지 하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해서 권한 제어가 가능해졌을 뿐이다. mysql.user의 user 테이블을 확인해보면 역할도 조회되는 것을 확인할 수 있다.

실제로 조회를 해보면 account_locked 컬럼 값만 다르고 아무런 차이가 없는 것을 알 수 있다. 그러면 MySQL 서버는 계정과 역할을 어떻게 구분할 까? 굳이 그럴 필요가 없다. 하나의 계정에 다른 계정의 권한을 병합하기만 하면 되므로 MySQL 서버는 역할과 계정을 구분할 필요가 없는 것이다.

일반적으로 CREATE USER 명령어로 계정을 생성할 때는 'reader'@'127.0.0.1 과 같이 계정 이름과 호스트 부분을 함께 명시한다. 하지만 CREATE ROLE 명령으로 역할을 생성할 때는 이름만 명시하지 호스트 부분을 명시하지 않으므로 호스트 부분이 모든 호스트('%')으로 추가된다. 물론 다음과 같이 @을 사용하여 호스트 부분을 별도로 설정할 수 있다.

-- 호스트 부분을 별도로 설정한 역할 생성
mysql> CREATE ROLE
		role_emp_read@'127.0.0.1',
        role_emp_write@'127.0.0.1';

이제 계정과 역할이 내부적으로는 똑같은 객체라는 것을 이해했으니, 다음과 같이 호스트 부분을 가진 역할에 대해 고민해보자.

mysql> CREATE ROLE role_emp_local_read@localhost;

mysql> CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';

mysql> GRANT SELECT ON employees.* TO role_emp_local_read@'localhost';

mysql> GRANT role_emp_local_read@'localhost' TO reader@'127.0.0.1';

위 예제는 role_emp_read@'localhost' 역할을 read@'127.0.0.1 계정에 부여하는 예제이다. 역할과 계정의 호스트 부분이 서로 달라서 호환되지 않는 상태인데, 이는 역할의 호스트 부분이 어떤 영향을 미치는지 확인해 보기 위함이다. 결론을 얘기하지면 상관이 없다. 역할의 호스트 부분은 아무런 영향이 없다. 만약, 역할을 다른계정에 부여하지 않고 직접 로그인하는 용도로 사용한다면(실제 계정처럼 사용한다면) 그 때는 호스트 부분이 중요해진다. 물론 그러기 위해서는 account_locked 컬럼을 'N'으로 설정해줘야 한다.

역할과 계정은 내외부적으로 동일한 객체인데 왜 굳이 CREATE USER, CREATE ROLE 명령어로 구분해서 지원할까? 이는 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용할 수 있게 하기 위해서이다. CREATE USER 명령어에 대해서는 권한이 없지만 CREATE ROLE 명령어만 실행 가능한 사용자는 역할만 생성할 수 있다. 이렇게 생성된 역할은 계정과 동일한 객체를 생성하긴 하지만 이 역할은 account_locked 컬럼이 'Y' 로 설정 되있어 로그인 용도로 사용할 수 없게 된다.

게정의 기본 역할 또는 역할에 부여된 그래프 관계는 SHOW GRANTS 명령을 사용할 수 있지만 표 형태로 깔끔하게 보고자 한다면 mysql DB의 권한 관련 테이블을 참고하자.

mysql.defaults_roles : 계정별 기본 역할
mysql.role_edges : 역할에 부여된 역할 관계 그래프

김민우

이전 포스트

MySQL 서버 설정

다음 포스트