[Real MySQL 8.0] 계정과 역할

MySQL 은 계정 뿐 아니라 접속 호스트도 계정의 일부가 됩니다. 그래서 MySQL 은 계정을 언급할 때 항상 호스트도 같이 명시합니다.

'test'@'127.0.0.1' # MySQL 가 올라가 있는 서버의 로컬호스트 에서만 test 계정에 접속 가능.

'test'@'%' # 모든 호스트에서 접속이 가능 % 는 모든 호스트를 뜻한다.

 

만약 같은 계정에 대해 두거지 호스트 설정이 있다면, 

항상 범위가 작은 것이 우선 선택됩니다. 만약 위 예시가 모두 설정이 되어있다면,

범위가 더 작은 로컬호스트만에서 접속이 가능한 정보가 적용이 됩니다.

 

MySQL 의 계정은 일반적으로 시스템 계정과 일반 계정으로 나뉩니다.

 

계정의 종류

시스템 계정과 일반 계정의 가장 큰 차이는 타 사용자에 대한 동작을 제어하거나 변경하는 권한의 차이입니다.

 

시스템 계정 만의 기능은 다음과 같습니다.

• 계정 관리 (권한 부여 및 계정의 생성 및 삭제)
• 다른 세션 또는 그 세션에서 실행중인 쿼리의 종료
• Stored 프로그램 생성 시 DEFINER 를 타 사용자로 지정

 

MySQL 에는 내장계정이 존재하는데, 다음의 3개 계정은 특별한 역할을 맡는 계정이므로 삭제되지 않도록 주의하여야합니다.

기본적으로 잠겨있기 때문에 고의로 account_locked 설정을 건드리지 않는 이상 사용할 수 없습니다.

 

mysql.sys@'localhost' 8.0부터 기본으로 내장된 sys 스키마 객체들의 DEFINER 입니다.

mysql.session@'localhost' MySQL 플러그인이 서버로 접속할때 사용되는 계정입니다.

mysql.infoschema@'localhost' information_schema 에 정의된 뷰의 DEFINER 로 사용되는 계정입니다.

 

sys : performance_schema(DB의 성능정보) 를 활용하기 편하도록 만든 View 의 모음입니다.

information_schema: DB의 메타정보 (인덱스, 테이블 등의 스키마 정보) 를 모아둔 읽기전용 데이터베이스 입니다.

 

계정의 생성

8.0 버전 이전에는 GRANT 명령어로 유저의 생성과 권한부여가 동시에 가능했지만,

8.0 이후 버전에서는 CREATE USER 로 생성만을, GRANT 로 권한관리만을 담당하게 변경되었습니다.

 

책에서는 일반적으로 많이 사용되는 CREATE USER 옵션들을 명시합니다.

 

mysql> CREATE USER 'user'@'%'
        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

 

사용자의 인증방식과 비밀번호를 설정합니다.

대표적인 2가지 인증방식을 소개하겠습니다.

 

• Native Pluggable Authentication: 5.7 버전까지 기본값이었던 인증방식입니다. 비밀번호에 대한 SHA-1 해시를 저장해두고, 입력된 값과 해시를 단순비교하여 인증합니다.
• Caching SHA-2 Pluggable Authentication: 5.6 부터 도입되고 8.0 부터 기본값이 된 방식입니다. SHA-2 알고리즘을 사용하고 SALT 키를 이용하여, 수천번의 연산 후 결과값을 비교하기 떄문에 동일 입력에서도 다른 해시값을 반환합니다. 많은 연산으로 인한 성능 저하로 인해 연산 결과를 메모리에 캐시하여 사용하며, SSL/TLS 혹은 RSA 키페어를 통해 접속을 강제합니다.

Caching SHA-2 Pluggable Authenticaiton 은 보안상으로 우수하지만, SSL/TLS 혹은 RSA 키페어 접속을 강제하기 떄문에, 기존 버전과의 호환성을 위하여 기본 옵션을 변경하고 싶을 때가 있습니다. 

그럴때에는 default_authentication_plugin 시스템 변수를 변경해줍니다.

 

 

REQUIRE

 

MySQL 접속시 SSL/TLS 채널을 사용할 지 여부를 설정합니다. 설정하지 않을 시 비암호화 채널로 연결하게 됩니다.

설정하지 않았더라도 앞서 말한대로 Caching SHA-2 Pluggable Authenticaiton 은 암호화 채널 접속을 강제하기 때문에,

암호화된 채널만으로만 접속할 수 있습니다.

 

PASSWORD EXPIRE

 

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

별도로 명시하지 않을 경우, default_password_lifetime 시스템 변수에 저장된 기간으로 설정됩니다.

 

PASSWORD EXPIRE # 계정 생성과 동시에 비밀번호 만료
PASSWORD EXPIRE NEVER # 만료기간 없음
PASSWORD EXPIRE DEFAULT # 시스템 변수를 따라감
PASSWORD EXPIRE INTERVAl n DAY # 오늘부터 n 일이 유효기간

 

PASSWORD HISTORY

 

한번 사용했던 비밀번호를 재사용 불가능하게하는 옵션입니다.

 

PASSWORD HISTORY DEFAULT # password_history 시스템 변수를 따른 갯수만큼 이력 저장, 이력에 있는 비밀번호 사용불가
PASSWORD HISTORY n # n개 까지 저장, 이력에 있는 비밀번호 사용불가

 

PASSWORD REUSE INTERVAL

 

한번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션입니다

별도 명시하지 않을 경우 password_reuse_interval 시스템 변수 값을 따라갑니다.

 

PASSWORD REUSE INTERVAL DEFAULT # 시스템 변수를 따라감
PASSWORD REUSE INTERVAL n DAY # 사용 n 일후 비밀번호 재사용이 가능

 

PASSWORD REQUIRE

 

비밀번호가 만료되어 새 비밀번호를 설정할 때, 현재 비밀번호를 요구할지 말지를 설정하는 옵션입니다.

별도 명시하지 않을경우 password_require_current 시스템 변수 값으로 설정됩니다,

 

PASSWORD REQUIRE CURRENT # 현재 비밀번호 요구
PASSWORD REQUIRE OPTIONAL # 현재 비밀번호 필요 없음
PASSWORD REQUIRE DEFAULT # 시스템 변수를 따라감

 

ACCOUNT LOCK / UNLOCK

 

계정을 사용하지 못하도록 잠글지 여부를 결정합니다. 명령어가 명확해서 따로 기술하지 않겠습니다.

 

고수준 비밀번호

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             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+

 

validate_password.policy 에 설정되어 있는 비밀번호 정책에 따라 각 옵션들을 체크합니다.

 

• LOW: 비밀번호 길이만 검증 (validate_password.length)
• MEDIUM: 비밀번호길이 검증, 숫자와 대소문자 그리고 특수문자의 배합을 검증합니다
  (mixed_case_count, number_count, special_char_count)
• STRONG: 아래 레벨 모두의 검증을 수행하며, 금칙어 포함 여부 (dictionary_file) 또한 판단합니다.

 

이중 비밀번호

운영 서비스에서 접속하는 계정의 경우, 서비스를 다운시킬수 없기 때문에 비밀번호 변경이 쉽지 않았습니다.

MySQL 8.0 부터는 비밀번호를 2개 등록하여 하나만 통과가 되면 되도록하는 이중 비밀번호 기능을 통하여, 쉽게 변경할 수 있습니다.

 

# 비밀번호를 old 로 지정
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'old'
# new 라는 비밀번호를 추가하고 현재 비밀번호를 세컨더리로 지정
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new' RETAIN CURRENT PASSWORD;
# 세컨더리 패스워드를 삭제
mysql> ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

 

 

 

권한

 

5.7 버전까지의 MySQL 권한은 글로벌권한과 객체권한 이라는 정적권한 들로만 나누어져 있었고, 8.0 버전에서 동적권한이 추가되었습니다.

 

• 글로벌 권한 : 글로벌 권한은 데이터베이스나 테이블 같은 특정 객체에 해당하는 권한이 아닌 전역적인 영역의 권한을 말합니다. 대부분 Server administration 용도의 권한입니다. 
• 객체 권한: 객체 권한은 스토어드 프로그램이나, 특정 데이터베이스 혹은 테이블에 적용되는 권한입니다. DML, DDL 문이 여기 속합니다.
• 동적 권한: MySQL 실행시 동적으로 생성하는 권한을 뜻합니다. MySQL 컴포넌트나 플러그인이 추가되었을때 등록되는 권한등이 있습니다. 5.7 버전까지 데이터베이스 관리를 위한 필수 권한이었던 SUPER 권한이 동적권한으로 분산되어 들어갔습니다.

-- 글로벌 권한
GRANT [글로벌 권한] ON *.* TO 'user'@'localhost'
# 글로벌 권한은 ON 절이 항상 *.* 이어야한다.

-- DB 권한
GRANT [DB 권한] ON [데이터베이스].* TO 'user'@'localhost'

-- 테이블 권한
GRANT [테이블 권한] ON [데이터베이스].[테이블] TO 'user'@'localhost'

# 상위 Scope 의 선언 방식은 모두 사용할 수 있다.

-- 컬럼 권한
GRANT [컬럼 권한](컬럼 명) ON [데이터베이스].[테이블] TO 'user'@'localhost'

 

컬럼 권한의 경우, 하나라도 설정되어 있으면 모든테이블의 모든 컬럼에 대해 권한체크를 하여 성능에 전체적으로 영향을 줄 수 있습니다.

컬럼 권한이 필요한 경우, 별도의 해당 컬럼이 포함된 별도의 뷰를 만들어서 뷰에 권한 부여를 하는 방법으로 사용할 수 있습니다.

 

역할

MySQL 8.0 부터는 역할(ROLE) 이 사용 가능해졌습니다. 역할은 권한의 집합입니다.

 

# 역할 생성

mysql> CREATE ROLE [역할 명], [역할 명2]...

# 역할에 권한 부여

mysql> GRANT [권한] ON [스코프] TO [역할 명]

# 역할을 유저에게 부여

mysql> GRANT [역할 명] TO [계정]@[호스트]

사용법은 위와 같습니다.

하지만 이대로는 바로 권한이 적용되지 않고, SET ROLE 명령어로 활성화 해주어야하고 재로그인 하면 다시 역할이 비활성화 됩니다.

이때는 active_all_roles_on_login 시스템 변수를 활성화 시켜주면 로그인시 자동으로 역할이 활성화 됩니다.

 

SELECT user, host, account_locked FROM mysql.user;

위 쿼리를 수행해보면, 역할이 account_locked 만 비활성화 된 호스트가 '%' 인 계정과 같다라는걸 알 수 있습니다.

즉 역할은 내부적으로 유저와 같은 객체라는 것입니다. 하지만, account_locked 가 비활성화 되 있기 때문에 로그인 용도로는 사용할 수 없습니다. 

 

마지막으로 부여한 권한과 역할의 관계를 확인하는 법을 알아봅시다.

 

# 모든 권한 보기
SHOW GRANTS

-- 깔끔하게 보고 싶다면

# 계정별 기본 역할 테이블
SELECT * FROM mysql.default_roles
# 역할 관계 그래프 테이블
SELECT * FROM mysql.role_edges