Last Updated on 8월 14, 2023 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 기본적인 계정 생성 과 8.0 에서 추가된 계정 생성시 추가된 옵션, 권한 과 추가된 ROLE 에 대해서 확인해 보도록 하겠습니다.
해당 포스팅은 Real MysQL 8.0 책을 정리한 내용이며, MySQL Document 를 같이 참조하였습니다.
계정
MySQL 8.0 부터 계정은 SYSTEM_USER 권한을 가지고 있느냐에 따라서 시스템 계정(System Account) 와 일반 계정(Regular Account) 으로 구분됩니다.
시스템 계정은 시스템 계정과 일반 계정을 관리(생성 삭제 및 변경) 할 수 있지만 일반 계정은 시스템 계정을 관리할 수 없습니다.
또한 다음과 같이 데이터베이스 서버 관리 와 관련된 중요 작업은 시스템 계정으로만 수행할 수 있습니다.
• 계정 관리(계정 생성 및 삭제, 그리고 계정의 권한 부여 및 제거)
• 다른 세션(Connection) 또는 그 세션에서 실행 중인 쿼리를 강제 종료
• 스토어드 프로그램 생성시 DEFINER 를 타 사용자로 지정
이렇게 시스템 계정과 일반 계정의 개념이 도입된 것은 DBA(데이터베이스 관리자) 계정에는 SYSTEM_USER 권한을 할당하고 일반 사용자를 위한 계정에는 부여하게 하기 위해서입니다.
MySQL 서버에는 내장된 계정들이 있으며 'root'@'localhost' 를 제외한 아래의 3개의 계정은 각각의 목적이 있으므로 삭제되지 않도록 주의해야 합니다.
• 'mysql.sys'@'localhsot'
MySQL 8.0 부터 기본으로 내장된 sys 스키마의 객체(뷰나 함수, 프로시저)들의 DEFINER로 사용되는 계정
• 'mysql.session'@'localhost'
MySQL 플러그인이 서버로 접근할 때 사용되는 계정
• 'mysql.infoschema'@'localhost'
information_schema 에 정의된 뷰의 DEFINER 로 사용되는 계정
위의 3개 계정은 처음부터 계정 잠금이 되어 있는 상태이므로 의도적으로 잠금을 풀지 않는 경우를 제외하고는 악의적인 용도로 사용할 수 없으므로 보안상 걱정하지 않아도 될 것 같습니다.
Note: 대역대가 다른 동일 계정
동일한 계정명에서 접속 Host 가 다를 경우 접속은 적은 범위의 대역대로 접속이 됩니다.
'%' 와 '192.168.%' 이 있을 경우 범위가 더 작은 '192.168.%' 대역대 계정으로 접속하게 됩니다.
계정 생성
MySQL 5.7 까지는 GRANT 명령으로 권한의 부여와 동시에 계정 생성이 가능하였습니다.
하지만 MySQL 8.0 버전 부터는 유저 생성시에는 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분하여 실행하도록 변경되었습니다.
계정 생성시 다음과 같은 옵션을 설정할 수 있습니다.
• 계정 인증 방식과 비밀번호
• 비밀번호 관련 옵션(비밀번호 유효기간, 비밀번호 이력 개수, 비밀번호 재사용 불가 기간) 정책 설정
• 기본 역할(ROLE)
• SSL 옵션
• 계정 잠금 여부
계정 생성 예시)
mysql> CREATE USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'Password' REQUIRE NONE PASSWORD EXPIRE INTERVAL 30 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
계정 생성 시 각 항목에 의미는 다음과 같습니다
IDENTIFIED WITH
사용자 인증 방식과 비밀번호를 지정하는 구문입니다.
IDENTIFIED WITH 뒤에는 인증 방식(인증 플러그인의 이름)을 명시하면 됩니다
MySQL 서버에서 설정된 기본(DEFAULT) 인증 방식을 사용하고자 한다면 WITH 없이 IDENTIFIED BY '패스워드' 로 바로 생성하면 됩니다.
MySQL 서버에서는 다양한 인증방식을 플러그인 형태로 제공하며 아래 4가지가 대표적인 인증 방식입니다.
• Native Pluggable Authentication
MySQL 5.7 버전까지 기본으로 사용하던 방식으로 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는 지를 비교하는 인증 방식입니다.
• Caching SHA-2 Pluggable Authentication
MySQL 5.6 버전 부터 도입되고 MySQL 8.0 버전에서는 조금 더 보완된 인증 방식으로, 암호화 해시 값을 생성을 위해서 SHA-2(256비트) 알고리즘을 사용합니다.
Native Authentication 의 가장 큰 차이는 사용되는 암호화 해시 알고리즘의 차이이며, SHA-2 Authentication 은 저장된 해시 값의 보안에 더 중점을 둔 알고리즘으로 이해하면 됩니다.
Native Authentication 플러그인은 입력 시 동일한 해시 값을 출력하지만 Caching SHA-2 Authentication 는 내부적으로 Salt 키를 활용하며, 수천 번의 해시 계산을 수행해서 결과를 만들어 내기 때문에 동일한 키 값에 대해서 결과가 달라지게 됩니다.
이처럼 해시 값을 계산하는 방식은 상당히 시간이 소모되며 성능이 매우 떨어지기 때문에 이를 보완하기 위해서는 MySQL 서버에서는 해시 결과값을 메모리에 캐시해서 사용을 하게 됩니다.
그래서 인증 방식의 이름에 'Caching' 이 포함된 것입니다.
이 인증 방식을 사용하려면 SSL/TLS 또는 RSA 키페어를 반드시 사용해야 합니다.
PAM Pluggable Authentication 와 LDAP Pluggable Authentication 이 있으나 MySQL 엔터프라이즈에서 사용할 수 있는 옵션입니다.
MySQL 5.7 버전까지는 Native Authentication 이 기본 인증 방식으로 사용되었지만, MySQL 8.0 부터는 Caching SHA-2 Authentication 이 기본 인증으로 변경되었습니다.
Caching SHA-2 Authentication 은 SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에 기존과는 다르게 접속에 대한 환경 준비가 필요할 수 있습니다.
Caching SHA-2 Authentication 에 대한 보다 자세한 내용은 다음 포스팅을 참조하시면 됩니다.
그래서 보안 수준이 좀 낮아지더라도 기존 버전의 호환성을 고려하여 Native Authentication 로 계정을 생성하여 사용할 수 있거나 기본 설정을 Native Authentication 으로 설정하여 사용할 수도 있습니다.
mysql> SET GLOBAL default_authentication_plugin="mysql_native_password";
REQUIRE
MySQL 서버에 접속할 때 암호화된 SSL/TLS 사용할지 여부를 설정하는 항목으로 별도로 설정하지 않으면 비암호화 채널로 연결됩니다.
하지만 REQUIRE 옵션을 SSL 로 설정하지 않았다고 해도 Caching SHA-2 Authentication 인증 방식으로 계정이 사용 중이라면 SS/TLS 로 접속을 기본으로 합니다.
PASSWORD EXPIRE
비밀번호의 유효기간을 설정합니다.
계정 생성 시 명시하지 않으면 default_password_lifetime 시스템 변수에 설정된 기간으로 설정됩니다.
default_password_lifetime 기본값은 0 으로 자동 만료 기능의 비활성화 입니다(기본 설정은 일수 제한 없음)
• PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호의 만료 처리
• PASSWORD EXPIRE NEVER : 계정 비밀번호의 만료 기간 없음
• PASSWORD EXPIRE DEFAULT : default_password_lifetime 시스템 변수를 따름
• PASSWORD EXPIRE INTERVAL n DAY : 비밀번호 유효 기간을 설정
패스워드 만료가 되면 로그인은 성공하나 쿼리를 수행하면 아래와 같은 에러 메세지를 받게 됩니다.
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
PASSWORD HISTORY (8.0 기능)
한번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션입니다.
•PASSOWRD HISTORY DEFAULT : password_history 시스템 변수에 저장된 개수만큼 비밀번호의 이력을 저장하고 저장된 이력의 비밀번호는 재사용할 수 없습니다.
password_history 시스템 변수의 기본값은 0 으로 패스워드 재사용의 제한이 없습니다(히스토리를 저장하지 않음)
•PASSWORD HISTORY n : 비밀번호 저장 이력을 최근 n 개 까지로 설정
저장된 패스워드는 mysql.password_history 딕셔너리에서 확인 가능 합니다
PASSWORD REUSE INTERVAL(8.0 기능)
한번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션입니다.
별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정되게 됩니다.
password_reuse_interval 시스템 변수의 기본값으로 0 으로 제한이 없는 것입니다.
• PASSWORD REUSE INTERVAL DEFAULT : password_reuse_interval 변수의 값을 따름
• PASSWORD REUSE INTERVAL n DAY : n 일자 이후 비밀번호를 재사용 할 수 있게 설정
PASSWORD REQUIRE(8.0 기능)
비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션입니다.
별도로 명시 하지 않으면 password_require_current 시스템 변수의 값으로 설정됩니다.
password_require_current 기본 값은 OFF 이며 8.0.13 에서 소개된 시스템 변수입니다.
• PASSWORD REQUIRE CURRENT : 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
• PASSWORD REQUIRE OPTION : 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
• PASSWORD REQUIRE DEFAULT : password_require_current 시스템 변수의 값으로 설정
ACCOUNT LOCK / UNLOCK
• ACCOUNT LOCK : 계정을 사용하지 못하도록 잠금
• ACCOUNT UNLOCK : 계정을 다시 사용 가능한 상태로 잠금 해제
비밀번호 관리
고수준의 비밀 번호
MySQL 서버의 비밀번호는 유효기간이나 이력 관리를 통해 재사용 금지 기능뿐만 아니라 비밀번호를 쉽게 유출할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 제공하고 있습니다.
(복잡성, 복잡도)
MySQL 서버 8.0에서 비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트 를 이용해야 하며 아래와 같이 컴포넌트를 설치를 하면 됩니다.
validate_password 컴포넌트는 MySQL 서버 프로그램에 내장되어 있기 때문에 바로 설치를 진행할 수 있습니다.
(file:// 부분에 별도로 파일 경로를 지정하지 않아도 됩니다.)
-- 설치 mysql> INSTALL COMPONENT 'file://component_validate_password'; -- 설치된 컴포넌트 확인 mysql> select * from mysql.component; +--------------+--------------------+------------------------------------+ | component_id | component_group_id | component_urn | +--------------+--------------------+------------------------------------+ | 1 | 1 | file://component_validate_password | +--------------+--------------------+------------------------------------+
validate_password 컴포넌트가 설치되었다면 아래와 같이 시스템 변수에서 확인할 수 있습니다.
mysql> show global variables like 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +--------------------------------------+--------+
Note
MySQL 8.0 에서는 기능을 확장하는 방법이 기존 플러그인 외 컴포넌트 방식이 추가되었습니다.
플러그인과 컴포넌트 간의 차이점에 관련한 내용은 아래 포스팅을 참조하시면 됩니다.
비밀번호 정책(validate_password.policy )은 크게 다음 3가지 중에서 선택할 수 있으며, 기본값은 MEDIUM 입니다.
• LOW : 비밀번호의 길이만 검증
• MEDIUM : 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
• STRONG : MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함되었는지 여부 까지를 확인
validate_password.length 는 비밀번호의 길에 대한 정책에 대한 시스템 변수입니다.
대소문자, 숫자 , 특수문자는 mixed_case_count 와 number_count ,special_char_count 시스템 변수에 설정된 수 이상을 포함하고 있는지를 검증하게 됩니다.
dictionary_file 시스템 변수는 설정된 파일에 명시된 금칙어 단어가 포함하고 있는지 검증하게 됩니다.
높은 수준의 보안을 요구 하는 서비스에서는 많은 사전의 금칙어를 텍스트 파일로 저장을 하면 되며 아래와 같이 한줄에 하나씩 작성을 하면 됩니다.
password
apple
1234
qwer
...
...
하지만 위와 같이 금칙어를 하나씩 입력해서 작성하기에는 상당히 많은 시간이 소모될 것입니다.
그렇기 때문에 이미 만들어져 있는 금칙어를 사용하는 것이 쉽게 설정을 할 수 있는 방법일 것입니다.
이런 금칙어는 여러 곳에서 검색해서 찾을 수 있을 것이며 그 중에 하나의 깃허브 저장소 주소는 아래와 같습니다.
링크 URL
서버에서 직접 다운로드 받기 위해서는 아래와 같이 wget 명령어를 통해서 다운로드 받으시면 됩니다.
wget https://raw.githubusercontent.com/danielmiessler/SecLists/master/Passwords/Common-Credentials/10k-most-common.txt
금칙어 중에서 letmein1 를 사용해보도록 하겠습니다.
grep letmei 10k-most-common.txt
letmein
letmein1
letmein2
1letmein
letmein22
letmeinn
먼저 금칙어를 등록하기 위해서는 validate_password.policy 시스템 변수가 'STRONG' 으로 설정이 필요 합니다.
validate_password.dictionary_file 시스템 변수에는 파일은 상대경로(FullPath) 로 입력하거나 data directory 에 파일을 위치해 놓으면 됩니다.
utf8 문자집합으로 되어있는 텍스트 파일로 최대 허용 파일의 크기는 1MB 입니다.
금칙어 적용
-- 시스템 변수 변경 mysql> SET GLOBAL validate_password.policy='STRONG'; -- 유저 생성(금칙어 등록 전), 생성 완료 mysql> create user test1@'%' identified by 'letmein1LETMEIN11234!@#$'; Query OK, 0 rows affected (0.00 sec) <-- 계정 생성 완료 -- 금칙어 등록 mysql> SET GLOBAL validate_password.dictionary_file='/var/lib/mysql/10k-most-common.txt'; -- 유저 생성 시도 mysql> create user test2@'%' identified by 'letmein1LETMEIN11234!@#$'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements <-- 금칙어에 의해서 생성 불가
VALIDATE_PASSWORD_STRENGTH
계정을 생성하거나 패스워드를 변경하기 전에 설정된 정책(policy) 기준으로 설정하려는(생성하려는) 암호의 강도를 함수를 통해서 평가해볼 수 있습니다.
VALIDATE_PASSWORD_STRENGTH 함수를 사용하면 되며 아래와 같이 암호에 대한 강도를 평가해 볼 수 있으며 0(약함) 에서 100(강함) 사이의 정수를 반환하게 됩니다.
mysql> SELECT VALIDATE_PASSWORD_STRENGTH('test123');
+---------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('test123') |
+---------------------------------------+
| 25 |
+---------------------------------------+
mysql> SELECT VALIDATE_PASSWORD_STRENGTH('TesT123456!@');
+--------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('TesT123456!@') |
+--------------------------------------------+
| 100 |
+--------------------------------------------+
Note
5.7 버전에서는 validate_password 가 플러그인 형태로 제공되었으며, MySQL 8.0 버전 부터 컴포넌트 형태로 제공됩니다.
플러그인일 경우 mysql.plugin 에서 조회(select) 해보면 되며, 컴포넌트는 mysql.component 를 통해서 확인(조회) 해볼 수 있습니다.
Dual Password
개발자나 업무팀에서 사용하는 사용자 계정이 아닌 서비스나 응용 프로그램에서 사용되는 계정의 경우 공용으로 사용되는 경우가 많으며 또한 계속 사용중임에 따라서 비밀번호를 변경하기는 사실 쉽지 않습니다.
그래서 계정이 생성이 되고 본격적으로 서비스가 되어서 해당 계정이 계속 사용 중이며 그 기간이 상당수 길며 또한 여러 서비스 또는 여러 부서에서 사용하다 보면 사실 비밀번호는 그대로 사용되는 경우는 많이 있게 됩니다.
보안상으로 패스워드를 주기적으로 또는 한번은 변경을 해야 할 경우 이전 버전까지는 서비스를 한번은 멈춰야 할수도 있었지만 8.0 버전 부터는 계정의 비밀번호를 2개의 값을 동시에 사용하는 기능이 추가되었습니다.
Dual Password 라고 합니다.
MySQL 서버의 Dual Password 기능은 하나의 계정에 대해서 2개의 비밀번호를 동시에 설정할 수 있으며, 2개의 비밀번호는 Primary 와 Secondary 로 구분되게 됩니다.
최근에 설정된 비밀번호는 Primary 비밀번호이며, 이전 비밀번호는 Secondary 비밀번호가 되게 됩니다.
Dual Password 를 사용하기 위해서는 아래와 같이 ALTER USER 구문에서 RETAIN CURRENT PASSWORD 옵션을 사용하면 됩니다
-- 비밀번호를 변경 mysql> ALTER USER test@'%' IDENTIFIED BY 'TestPass1234!@'; -- 비밀번호를 변경하면서 기존의 비밀번호(TestPass1234!@) 를 Secondary 비밀번호 설정 mysql> ALTER USER test@'%' IDENTIFIED BY 'Password1234!@' RETAIN CURRENT PASSWORD;
이와 같이 설정을 하면 최근에 설정한 비밀번호 "Password1234!@" 가 Primary 로, 이전에 설정한 패스워드 "TestPass1234!@" 는 Secondary 로 설정되게 되며 2개의 비밀번호 모두 사용 가능한 상태가 됩니다.
mysql.user 조회 내용
mysql> select user,host,authentication_string, User_attributes
from mysql.user
where user='test';
+------+------+------------------------------------------------------------------------+
| user | host | authentication_string | User_attributes |
+------+------+------------------------------------------------------------------------+
| test | % | $A$005$#[`{qk | {"additional_password": "$A$005${G_}a+OWdeB"} |
+------+------+------------------------------------------------------------------------+
* 가로 길이가 길어서 일부 내용은 편집하였습니다.
authentication_string 가 Primary 이며, User_attributes 는 이전 비밀번호인 Secondary 입니다.
이렇게 2개의 패스워드를 사용할 수 있는 환경을 설정 후에 서비스에서 접속 정보를 순차적으로 점증적으로 변경하기가 더 쉬울 것입니다.
계정을 사용하는 어플리케이션의 정보가 모두 변경되었다면 이제 이전 비밀번호를 삭제하면 되며 DISCARD OLD PASSWORD 옵션을 사용하면 됩니다.
mysql> ALTER USER test@'%' DISCARD OLD PASSWORD;
mysql> select user,host,authentication_string, User_attributes
from mysql.user
where user='test';
+------+------+--------------------------------------------+
| user | host | authentication_string | User_attributes |
+------+------+--------------------------------------------+
| test | % | $A$005$#[`{qk | NULL |
+------+------+--------------------------------------------+
* 가로 길이가 길어서 일부 내용은 편집하였습니다.
권한
MySQL 5.7 버전까지는 글로벌(Global)권한과 객체 단위의 권한으로 구분되어져 있었습니다.
데이터베이스나 테이블이외의 객체에 적용되는 권한을 글로벌 권한이라고 하며, 데이터베이스나 테이블을 제어하는데 필요한 권한을 객체 권한이라고 합니다.
글로벌 권한에는 대표적으로 FILE 이나 PROCESS, SHOW DATABASES 등이 있습니다
객체 권한은 GRANT 명령으로 권한을 부여할 때 특정 객체를 명시해야 하며, 반대로 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 않아야 합니다
예외적으로 ALL(또는 ALL PRIVILEGES)는 글로벌과 객체 권한 두가지 용도로 사용될 수 있으며 특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여하게 되며,
글로벌 ALL 이 사용되면 글로벌 수준에서 가능한 모든 권한을 부여받게 됩니다.
MySQL 8.0 버전 부터는 동적 권한이 더 추가되었습니다. 동적 권한은 MySQL 서버가 시작되면서 동적으로 생성하는 권한을 의미하며 예를 들어 MySQL 서버의 컴포넌트나 플러그인 설치에 의해 그때 등록되는 권한을 동적 권한이라고 하며 아래와 같습니다.
• APPLICATION_PASSWORD_ADMIN (added in MySQL 8.0.14)
• AUDIT_ADMIN
• BACKUP_ADMIN
• AUTHENTICATION_POLICY_ADMIN (added in MySQL 8.0.27)
• BINLOG_ADMIN
• BINLOG_ENCRYPTION_ADMIN
• CLONE_ADMIN
• CONNECTION_ADMIN
• ENCRYPTION_KEY_ADMIN
• FIREWALL_ADMIN
• FIREWALL_EXEMPT (added in MySQL 8.0.27)
• FIREWALL_USER
• FLUSH_OPTIMIZER_COSTS (added in MySQL 8.0.23)
• FLUSH_STATUS (added in MySQL 8.0.23)
• FLUSH_TABLES (added in MySQL 8.0.23)
• FLUSH_USER_RESOURCES (added in MySQL 8.0.23)
• GROUP_REPLICATION_ADMIN
• GROUP_REPLICATION_STREAM
• INNODB_REDO_LOG_ARCHIVE
• INNODB_REDO_LOG_ENABLE
• NDB_STORED_USER
• PASSWORDLESS_USER_ADMIN (added in MySQL 8.0.27)
• PERSIST_RO_VARIABLES_ADMIN
• REPLICATION_APPLIER
• REPLICATION_SLAVE_ADMIN
• RESOURCE_GROUP_ADMIN
• RESOURCE_GROUP_USER
• ROLE_ADMIN
• SERVICE_CONNECTION_ADMIN
• SESSION_VARIABLES_ADMIN (added in MySQL 8.0.14)
• SET_USER_ID
• SHOW_ROUTINE (added in MySQL 8.0.20)
• SYSTEM_USER (added in MySQL 8.0.16)
• SYSTEM_VARIABLES_ADMIN
• TABLE_ENCRYPTION_ADMIN (added in MySQL 8.0.16)
• VERSION_TOKEN_ADMIN
• XA_RECOVER_ADMIN
동적 권한의 상세 내용은 아래 문서에서 Dynamic Privilege Descriptions 항목을 확인해보시면 됩니다.
SHOW_ROUTINE 에 대한 내용은 아래 이전 포스팅을 참조하시면 됩니다.
MySQL 8.0 에서는 권한 별로 세분화하여 많은 동적 권한이 추가되었으며 그에 따라서 5.7 에서는 SUPER 라는 관리자 권한이 필요하였던 작업이 세분화된 동적 권한을 부여함으로써 가능해지게 권한을 조정할 수 있습니다.
즉, 이전에 비해 필요한 권한만 부여가 가능하고 불필요한 많은 권한을 부여하지 않아도 되는, 5.7 버전에 비해서 세세한 권한 관리가 가능 해졌습니다.
ROLE(역할)
MySQL 8.0 버전 부터 권한을 묶어서 계정에게 권한을 줄수 있는 ROLE(역할) 기능이 추가되었으며, 쉽게 권한이 모인 주머니 또는 여러 권한이 담긴 바구니 라고 표현할 수도 있을것 같습니다.
MySQL 에서 ROLE 은 서버 내부에서도 계정과 동일한 모습을 하고 있습니다.
사실 ROLE 은 Oracle Database 에서 먼저 있던 기능으로 Oracle Database 에서는 이전 부터 통상적으로 사용되는 권한 관리 기능이었습니다.
Oracle 권한 부여가 GRANT SELECT ON <schema name> to <<user>> 이와 같은 유저 단위(MySQL에서는 논리 스키마(데이터베이스))의 권한 부여가 불가능 하였기 때문입니다.
그래서 오라클을 사용하는 환경에서는 보통 물리 디자인과 업무 디자인시 ROLE 의 이름과 ROLE 에 부여할 권한의 의미 등을 사전에 정의(Define) 하게 됩니다.
가령 RL_CUSTOME_SEL 과 같은 ROLE 이 있다면 테이블 중에서 고객과 관련된 테이블 생성시 테이블 생성 후 ROLE 에 테이블에 대한 select 권한 같이 부여하게 됩니다.(테이블 생성과 ROLE에 권한 부여가 한 세트)
또는 RL_CRM_SEL 이라는 ROLE 이 있을 경우 CRM 이라는 유저의 모든 테이블에 대해서는 테이블 생성시 RL_CRM_SEL ROLE 에 조회 권한을 부여하는 형태로 사용할 수 있습니다.
이렇게 만들어진 ROLE 은 서비스 나 응용 어플리케이션 또는 개발자나 업무팀의 개인 계정 등에 롤을 부여하여 사용하는 형태로 이전 부터 Oracle 사용 환경에서는 자주 사용하는 권한 관리 객체이었습니다.
MySQL 에서의 ROLE 사용법은 예제는 아래와 같이 확인해보도록 하겠습니다.
ROLE 생성
mysql> CREATE ROLE role_read, role_write;
위와 같이 CREATE ROLE 명령어를 통해 생성할 수 있으며, ROLE을 만들고 나면 별다른 권한이 없는 빈 껍데기만 있는 상태입니다.
이름으로 알 수 있는 것처럼 조회용과 데이터 변경이 가능한 목적을 가진 2개의 ROLE을 생성하였습니다.
ROLE 에 권한 부여
포스팅에서는 아래와 같이 확인 되는 4개의 논리 스키마에 대해서 ROLE 의 이름에 맞춰서 권한을 부여하도록 하겠습니다.
mysql> show databases; +--------------------+ | Database | +--------------------+ | customer | | deal | | price | | product | +--------------------+ mysql> grant select on customer.* to role_read; mysql> grant select on deal.* to role_read; mysql> grant select on price.* to role_read; mysql> grant select on product.* to role_read; mysql> grant select,insert,delete,update on customer.* to role_read; mysql> grant select,insert,delete,update on deal.* to role_read; mysql> grant select,insert,delete,update on price.* to role_read; mysql> grant select,insert,delete,update on product.* to role_read;
이와 같이 각각의 ROLE에 권한을 부여 하였습니다. 이제 계정 에 ROLE을 부여하도록 하겠습니다.
ROLE 을 계정에 부여
-- 계정 생성 mysql> create user 'app_read'@'%' identified by 'Password1234!@'; mysql> create user 'app_write'@'%' identified by 'Password1234!@'; -- ROLE 을 계정에 부여함 mysql> grant role_read to 'app_read'@'%'; mysql> grant role_write to 'app_write'@'%';
하지만 새로 생성한 계정으로 접속하여 쿼리를 수행하면 아래와 같이 권한 에러가 발생되는 것을 확인 할 수 있습니다.
mysql> select * from customer.tb_test; ERROR 1142 (42000): SELECT command denied to user 'app_read'@'localhost' for table 'tb_test'
ROLE 사용하러면 접속 후 명시적으로 SET ROLE 선언 후에 사용할 수 있습니다. 선언 후 다시 쿼리를 수행하면 정상적으로 수행될 것입니다.
mysql> SET ROLE 'role_read'; mysql> select * from customer.tb_test; +------+ | id | +------+ | 1 | +------+
이렇게 매번 접속할 때마다 SET ROLE 를 하는 것은 매우 번거로운 일 것 입니다. 그래서 자동적으로 ROLE을 활성화 하여 사용할 수 있는 방법으로는 2가지가 있습니다.
1) 시스템 변수 설정
두가지 중에서 첫번째로는 activate_all_roles_on_login 시스템 변수와 관련이 있으며 기본값은 OFF입니다. ON 으로 설정하면 매번 SET ROLE 을 하지 않아도 ROLE 을 사용할 수 있도록 로그인 시 활성화할 수 있습니다.
mysql> SET GLOBAL activate_all_roles_on_login=ON;
2) SET DEFAULT ROLE 사용
두번째로는 부여한 ROLE 이 자동으로 활성화 될수 있도록 SET DEFAULT ROLE 을 할 수도 있습니다.
-- 먼저 권한 부여 mysql> grant role_read to 'app_read'@'%'; -- SET DEFAULT ROLE 수행 mysql> SET DEFAULT ROLE role_read to 'app_read'@'%';
이와 같이 SET DEFAULT ROLE 로 지정하면 로그인시 'SET ROLE' 을 하지 않아도 자동으로 ROLE 이 활성화가 됩니다.
MySQL 8.0 에서 추가된 ROLE 은 사용자 계정과 거의 같은 모습을 하고 있으며 MySQL 내부적으로도 ROLE과 계정은 동일한 객체로 취급을 하고 있습니다.
mysql> select user,host,password_expired,account_locked
from mysql.user
where user IN ('role_read','app_read');
+-----------+------+------------------+----------------+
| user | host | password_expired | account_locked |
+-----------+------+------------------+----------------+
| app_read | % | N | N |
| role_read | % | Y | Y |
+-----------+------+------------------+----------------+
mysql.user 딕셔너리를 살펴보면 이와 같이 권한과 ROLE 이 같이 내용이 확인되는 걸 알 수 있습니다.
role 의 특징으로 host 대역대가 기본적으로 % 으로 되며 password_expired 와 account_locked 이 Y 로 생성되게 됩니다.
하지만 mysql.user 딕셔너리에는 이러한 특징 이외 명시적으로 ROLE 임을 나타내는 Flag 값인 컬럼이 별도로는 없습니다.
위에서 설명한 것 처럼 내부적으로 처리가 사실상 동일 하기 때문에 구분짓기가 어렵고 그렇기 때문에 ROLE 을 사용할 때는 이와 같이 ROLE 임을 식별할 수 있도록 'role_' 과 같은 prefiix 를 쉽게 식별할 수 있도록 하는 것이 권장됩니다.
그리고 이와 같이 ROLE 생성시 호스트를 지정하여 생성하였을 경우도 사실 큰 의미는 없습니다. ROLE 의 호스트 부분은 아무런 영향이 없습니다.
mysql> CREATE ROLE role_test@'localhost';
계정에 SET DEFAULT ROLE 또는 생성한 ROLE 이 부여된 계정 간의 정보는 아래 딕셔너리에서 추가로 확인 할 수 있습니다.
mysql.default_roles
mysql.role_edges
mysql> select * from mysql.default_roles; +------+----------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+----------+-------------------+-------------------+ | % | app_read | % | role_read | +------+----------+-------------------+-------------------+ mysql> select * from mysql.role_edges; +-----------+------------+---------+-----------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+------------+---------+-----------+-------------------+ | % | role_read | % | app_read | N | | % | role_write | % | app_write | N | +-----------+------------+---------+-----------+-------------------+
Reference
Reference Book
• Real MySQL 8.0
Reference Link
• mysql.com/server-system-variables
• mysql.com/privileges-provided
• mysql.com/create-role
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
