[DB] Real MySQL 3장: 사용자 및 권한

서정범·2024년 2월 19일
db
1

Database

목록 보기
3/4

MySQL을 설치하고 나면 사용하려면 사용자 계정이 필요합니다.

사용자 계정은 왜 필요할까요?

MySQL에서 사용자 계정은 데이터베이스 시스템내에서 보안과 접근 제어를 관리하는데 필수적인 역할을 합니다.

  1. 인증(Authentication): 사용자 계정은 데이터베이스에 접근하려는 개인이나 애플리케이션 신원을 확인하는데 사용됩니다. 무단 접근으로부터 시스템을 보호하는 데 중요한 역할을 합니다.
  2. 권한 부여(Authorization): 사용자 계정을 통해 특정 데이터베이스, 테이블에 대한 접근 권한을 세밀하게 제어할 수 있습니다. 이를 통해 정보의 무결성과 보안을 유지할 수 있습니다.
  3. 계정 관리(Account Management): MySQL에서는 사용자 계정을 생성, 수정, 삭제하는 등의 관리 작업을 할 수 있습니다. 이를 통해 유연한 관리가 가능해집니다.
  4. 감사 및 로깅(Auditing and Logging): 사용자 계정을 통해 특정 사용자가 수행한 데이터베이스 작업을 추적할 수 있습니다. 이는 보안 사고 발생 시 원인 분석과 책임 소재를 파악하는 데 도움을 줄 수 있으며, 규정 준수를 위한 로깅 요구 사항을 충족시키는 데도 사용될 수 있습니다.

이러한 역할 수행을 통해 보안과 유연한 관리를 가능하게 해주기 때문에 필요로 합니다.

1. 사용자 식별

MySQL의 사용자는 다른 DBMS와는 조금 다르게 사용자의 계정뿐 아니라 사용자의 접속 지점도 계정의 일부가 됩니다.

예를 들자면 다음과 같습니다.

'svc_id'@'127.0.0.1'

여기서 svc_id는 사용자 계정이고, 127.0.0.1는 사용자의 접속 지점 IP주소 입니다.

그렇다면, 왜 MySQL은 IP 주소를 사용하는 것일까요?

여기서 중요한 것은 MySQL은 로컬 환경에서만 접근을 수행할 수 있는 것이 아닌 원격으로도 접근할 수 있다는 점입니다. 그렇기 때문에 다음과 같은 이유로 사용됩니다.

  1. 접근 제어(Access Control): MySQL은 IP 주소를 기반으로 한 접근 목록(ACLs)을 사용하여, 특정 IP 주소 또는 IP 주소 범위에서 오는 연결 요청만을 허용하거나 거부할 수 있습니다. 이를 통해 신뢰할 수 있는 네트워크 또는 특정 사용자만이 접근할 수 있도록 제한합니다.
  2. 보안 강화(Security Enhancement): IP 주소를 사용하여 특정 사용자 계정이 특정 IP 주소나 IP 주소 범위에서만 데이터베이스에 접근할 수 있도록 설정함으로써, 보안을 강화할 수 있습니다.
  3. 네트워크 환경에 대한 유연성(Network Flexiblity): MySQL은 로컬 네트워크뿐만 아니라, 원격 네트워크를 통한 접근도 지원합니다.
  4. 감사 및 모니터링(Auditing and Monitoring): IP 주소를 기반으로 한 접근 기록을 활용하여, 데이터베이스 접근 시도 및 활동을 모니터링하고 기록할 수 있습니다.

만약 특정 계정은 모든 IP 주소로부터 접근할 수 있도록 설정하고 싶다면 다음과 같이 하면 됩니다.

'user_id'@'%'

% 문자는 모든 IP 주소 또는 모든 호스트명을 의미합니다.

만약, 다음과 같이 2개의 사용자 계정이 있다고 가정해 봅시다.

'svc_id'@'192.168.0.10'
'svc_id'@'%'

IP 주소가 192.168.0.10인 PC에서 접근을 시도할 경우 어떤 것이 선택될까요?

권한이나 계정 정보에 대해 MySQL은 범위가 가장 작은 것을 항상 먼저 선택합니다.

2. 사용자 계정 관리

2.1 시스템 계정과 일반 계정

MySQL 8.0부터 계정은 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정(System Account)과 일반 계정(Regular Account)으로 구분됩니다.

  • 시스템 계정: 데이터베이스 서버 관리자를 위한 계정
  • 일반 계정: 응용 프로그램이나 개발자를 위한 계정

데이터베이스 서버 관리와 관련된 중요 작업은 시스템 계정으로만 수행할 수 있습니다.

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

⚡️ 개념 정리 ⚡️

스토어드 프로그램(Stored Program)

Stored Program은 SQL 문들의 집합으로 구성된, 데이터베이스 서버에 저장된 프로그램입니다. 사용자는 이를 호출함으로써 복잡한 연산을 수행하고, 데이터 처리 로직을 데이터베이스 서버에서 직접 실행할 수 있습니다. Stored Program은 주로 아래와 같은 형태를 가집니다.

  • 스토어드 프로시저(Stored Procedure): 사용자가 명시적으로 호출하여 실행하는 일련의 SQL 문들의 집합입니다. 데이터 조회, 변경, 비지니스 로직 처리 등을 수행할 수 있습니다.
  • 트리거(Triggers): 데이터베이스 테이블에 대한 특정 이벤트(예: INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 SQL 문들의 집합입니다.
  • 함수(Functions): 호출 시 특정 값을 반환하는 SQL 문들의 집합입니다. 일반적으로 데이터 변환, 계산 등의 작업을 수행합니다.

DEFINER

DEFINER는 스토어드 프로그램이나 뷰를 생성할 때, 해당 객체를 실행할 때 사용할 기본 SQL 보안 컨텍스트를 지정하는 속성입니다. 즉, DEFINER로 지정된 사용자의 권한으로 스토어드 프로그램이나 뷰가 실행됩니다.

  • 보안 및 접근 제어: DEFINER는 스토어드 프로그램이나 뷰가 실행될 때의 권한을 명시적으로 설정할 수 있게 해줍니다. 이를 통해 데이터베이스 관리자는 민감한 데이터에 대한 접근을 엄격하게 제어할 수 있습니다.
  • 실행 컨택스트: 스토어드 프로그램이나 뷰가 호출되었을 때, DEFINER로 지정된 사용자 권한으로 실행됩니다. 이는 호출자의 권한과는 독립적으로, 스토어드 프로그램 내의 SQL 문이 실행될 권한을 제어합니다.

DEFINER의 필요성이 조금 의아할 수 있는데, 다음과 같은 예시를 통해서 이해할 수 있을 것 같습니다.

예를 들어서, 데이터베이스에는 민감한 정보를 다루는 테이블이 있고, 특정 사용자는 이 테이블에 대한 직접적인 조회나 변경 권한이 없다고 가정해 봅시다.

그러나 이 사용자가 업무상 필요로 하는 정보는 이 테이블에 포함되어 있습니다. 이 문제를 해결하기 위해, 데이터베이스 관리자는 DEFINER가 높은 권한을 가진 사용자로 설정된 스토어드 프로시저를 생성할 수 있습니다. 이 스토어드 프로시저는 민감한 정보를 조회하고, 필요한 데이터만을 가공하여 반환하는 로직을 포함할 수 있습니다.

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

  • 'mysql.sys'@'localhost': MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체(뷰나 함수, 그리고 프로시저)들의 DEFINER로 사용되는 계정
  • 'mysql.session'@'localhost': MySQL 플러그인이 서버로 접근할 때 사용되는 계정
  • 'mysql.infoschema'@'localhost': infomation_schema에 정의된 뷰의 DEFINER로 사용되는 계정

위에 언급한 3개의 계정은 처음부터 잠겨(account_lock)있는 상태이므로 의도적으로 잠긴 계정을 풀지 않는 한 악의적인 용도로 사용할 수 없습니다.

2.2 계정 생성

MySQL 5.7 버전까지는 권한의 부여와 동시에 계정 생성이 되는 GRANT 명령을 사용했습니다.

MySQL 8.0 버전 부터는 권한 부여와 계정 생성을 따로 해야합니다.

  • CREATE USER: 계정의 생성
  • GRANT: 권한 부여

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

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

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

CREATE USER 'user'@'%'
	IDENTIFIED WITH 'mysql_native_password' BY 'password'
    REQUIRED 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 형식으로 명시해야 합니다.

대표적으로 4가지 방식이 있습니다.

  • Native Pluggable Authentication: 5.7 버전까지 기본으로 사용되던 방식입니다. SHA-1 알고리즘을 사용합니다.
  • Caching SHA-2 Pluggable Authentication: 암호화 해시값 생성을 위해 SHA-2(256비트) 알고리즘을 사용합니다.
    • 위의 방식과의 가장 큰 차이는 암호화 해시 알고리즘을 사용한다는 것이며 해시값의 보안에 더 중점을 둔 알고리즘입니다.
    • 내부적으로 Salt 키를 활용하여 보안을 더 높일 수 있지만, 많은 연산으로 인해 성능 저하가 발생할 수 있기 때문에, MySQL 해시 결괏값을 메모리에 캐시하여 사용합니다. 그래서 이름에 'Caching'이 포함된 것입니다.
    • 반드시 SSL/TLS 또는 RSA 키페어를 사용해야 하는데, 이를 위해 클라이언트에서 접솔할 때 SSL 옵션을 활성화해야 합니다.
  • PAM Pluggable Authentication: 유닉스나 리눅스 패스워드 또는 LDAP(Lightweight Dirctory Access Protocol) 같은 외부 인증을 사용할 수 있게 해주는 인증 방식으로, MySQL 엔터프라이즈 에디션에서만 사용 가능합니다.
  • LDAP Pluggable Authentication: LDAP을 이용한 외부 인증을 사용할 수 있게 해주는 인증 방식으로, MYSQL 엔터프라이즈 에디션에서만 사용 가능합니다.

2.2.2 REQUIRED

MYSQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 설정합니다. 또한 변도로 설정하지 않으면 비암호화 채널로 연결하게 됩니다.

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

2.2.3 PASSWORD EXPIRE

비밀번호의 유효 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 default_password-lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정됩니다.

개발자나 데이터베이스의 관리자의 비밀번호는 유효 기간을 설정하는 것이 보안상 안전하지만 응용 프로그램 접속용 계정에 유효 기간을 설정하는 것은 위험할 수 있으니 주의합시다.

설정 가능한 옵션은 다음과 같습니다.

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

2.2.4 PASSWORD HISTORY

한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션인데, 설정 가능한 옵션은 다음과 같습니다.

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

2.2.5 PASSWORD REUSE INTERVAL

한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정됩니다.

설정 가능한 옵션은 다음과 같습니다.

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

2.2.6 PASSWORD REQUIRE

비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션이며, 벼도로 명시되지 않으면 password_require_current 시스템 변수의 값을 설정됩니다.

사용 가능한 옵션은 다음과 같습니다.

  • 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 컴포넌트를 이용하면 됩니다.

INSTALL COMPONENT 'file://component_validate_password';

여기서 명령어 file://을 사용하는 것은 해당 컴포넌트가 이미 MySQL 서버 프로그램에 내장돼 있기 때문입니다.

비밀번호 정책은 크게 다음 3가지 중에서 선택할 수 있으며, 기본값은 MEDINUM으로 자동 설정됩니다.

  • LOW: 비밀번호의 길이만 검증
  • MEDIUM: 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
  • STRONG: MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함됐는지 여부까지 검증

STRONG 옵션을 사용하기 위해서 금칙어 파일을 사용하려고 할 경우, MySQL 서버에 금칙어 파일을 등록하면 됩니다.

SET GLOBAL validate_password_dictionary_file='prohivitive_word.data';
SET GLOBAL validate_password_policy='STRONG';

⚡️ 플러그인 vs 컴포넌트 ⚡️

여기서 플러그인과 컴포넌트에 대한 용어가 계속 등장하는데 가볍게 정리를 하고 가자.

MySQL에서 플러그인(Plugins)와 컴포넌트(Components)는 데이터베이스의 기능을 확장하는 데 사용되는 매커니즘입니다.
플러그인

플러그인은 MySQL 서버에 동적으로 로드되어 서버의 기능을 확장할 수 있는 모듈입니다. 예를 들어보자면 다음과 같은 동작을 수행할 수 있습니다.

  • 인증 방식 변경
  • 새로운 스토리지 엔진 추가
  • 풀텍스트 검색 엔진 구현
  • 등등

플러그인은 MySQL 서버의 확장성을 제공해주지만, 다음과 같은 단점을 가지고 있습니다.

  • 개별 관리: 플러그인은 개별적으로 관리되고, 각각 별도로 로드하거나 제거되어야 합니다.
  • 호환성 문제: 서버 버전간의 호환성 문제가 발생할 수 있으며, 플러그인이 서버 업그레이드에 영향을 받을 수 있습니다.
  • 제한된 상호작용: 플러그인 간의 상호 작용이 제한적일 수 있으며, 플러그인과 서버 코어간의 통합도 때때로 제한적일 수 있습니다.

컴포넌트(Components)

컴포넌트 시스템은 MySQL 8.0에서 도입된 새로운 확장 매커니즘으로, 플러그인의 단점을 극복하고자 설계되었습니다.

  • 통합 관리: 컴포넌트는 시스템 전체에서 통합적으로 관리될 수 있으며, 여러 컴포넌트가 상호작용하며 더 복잡한 기능을 구현할 수 있습니다.
  • 향상된 호환성: 컴포넌트는 MySQL 서버의 내부 API와 더 긴밀히 통합되므로, 호환성 문제가 적고, 서버 업데이트 시의 영향을 더 적게 받습니다.
  • 확장된 기능성: 컴포넌트 시스템은 개발자에게 서버의 더 깊은 부분에 접근하고, 더 다양한 기능을 개발할 수 있는 능력을 제공합니다.

정리만 해보자면 플러그인은 서로에 대한 상호작용이 떨어져서 복잡한 기능 구현이 어렵고, 관리가 힘들다는 단점을 가지고 있고 이것을 극복하기 위해 컴포넌트를 사용한다고 정리할 수 있을 것 같습니다.

3.2 이중 비밀번호

많은 응용 프로그램 서버들이 공용으로 데이터베이스 서버를 사용할 수 있습니다.

이러한 구현 특성으로 인해 데이터베이스 서버의 계정 정보는 쉽게 변경하기 어려운데, 그중에서도 데이터베이스의 계정의 비밀번호는 서비스가 실행 중인 상태에서 변경이 불가능했습니다.

이 같은 문제점을 해결하기 위해서 MySQL 8.0 버전부터는 계정의 비밀번호를 2개의 값을 동시에 사용할 수 있는 기능을 추가했습니다.

MySQL 서버의 이중 비밀번호 기능은 하나의 계정에 대해 2개의 비밀번호를 동시에 설정할 수 있는데, 2개의 비밀번호는 프라이머리(Primary)와 세컨더리(Secondary)로 구분됩니다.

최근에 설정된 비밀번호는 Primary 비밀번호이며, 이전 비밀번호는 Secondary 비밀번호가 됩니다.

다음 일련의 과정을 살펴봅시다.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

성공적으로 이전 비밀번호를 삭제할 수 있는 것을 확인할 수 있습니다.

4. 권한

권한(Privilege)은 객체의 단위로 구분되며 다음과 같이 나눌 수 있습니다.

  • 글로벌 권한: 데이터베이스나 테이블 이외의 객체에 적용되는 권한
  • 객체 권한: 데이터베이스나 테이블을 제어하는 데 필요한 권한

객체 권한은 GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시해야 하며, 반대로 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 말아야 합니다.

또한 권한은 정적 권한과 동적 권한으로 구분될 수 있습니다.

  • 정적 권한: MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한
  • 동적 권한: MySQL 서버가 시작되면서 동적으로 생성하는 권한(예: 컴포넌트나 플러그인이 설치될 때)

책에서 표를 제시하면서 굉장히 많은 권한들을 나열하면서 설명하고 있는데 그 부분은 생략하도록 하겠습니다.

여기서는 사용자에게 권한을 부여할 때 권한의 범위에 따라 어떻게 사용되는지 살펴봅시다.

먼저 글로벌 권한부터 보자.

GRANT SUPER ON *.* To 'user'@'localhost';

글로벌 권한은 특정 DB나 테이븡레 부여될 수 없기 때문에 부여할 때 GRANT 명령의 ON 절에는 항상 *.*를 사용하게 됩니다.

다음은 DB 권한입니다.

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

DB 권한은 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대해 권한을 부여할 수 있기 때문에 *.*이나 DB_NAME.*와 같이 사용할 수 있습니다.

주의할 점은 테이블까지 명시할 수 없다는 것입니다.

다음은 테이블 권한입니다.

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

테이블 권한은 모든 DB 혹은 특정 DB의 오브젝트, 특정 DB의 특정 테이블에 대해서 권한을 부여할 수 있습니다.

여기서 만약 특정 컬럼에 대해서만 권한을 부여하고 싶은 경우 다음과 같이도 할 수 있습니다.

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

이렇게 할 경우 UPDATE 명령어는 employees DB의 department 테이블에서 dept_name 컬럼에 대해서만 수행할 수 있습니다.

참고로, DELETE는 해당 방식대로 사용할 수 없습니다.

하지만 이렇게 하는 것은 잘 사용하지 않습니다. 그 이유는 컬럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크를 하기 때문에 전체적인 성능에 영향을 미칠 수 있습니다.

만약, 컬럼 단위의 접근 권한이 꼭 필요하다면 별도의 뷰(VIEW)를 만들어 사용하는 방법도 생각할 수 있습니다.

뷰도 하나늬 테이블로 인식되기 때문에 뷰를 만들어 두면 뷰의 칼럼에 대해 권한을 체크하지 않고 뷰 자체에 대한 권한만 체크하게 됩니다.

5. 역할

MySQL 8.0 버전부터는 권한을 묵어서 역할(Role)을 사용할 수 있게 됐습니다.

실제 MySQL 서버 내부적으로 역할(Role)은 계정과 똑같은 모습을 하고 있습니다.

먼저, 역할을 생성해보도록 합시다.

CREATE ROLE
	role_emp_read,
    role_emp_write;

위의 CRETATE ROLE 명령에서는 빈 껍데기만 있는 역할을 정의한 것입니다.

실질적인 권한은 GRANT 명령을 통해 부여됩니다.

GRANT SELECT ON employees.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

이렇게 까지 말들어 두면 사용할 준비가 된 것입니다.

계정에 역할을 부여하는 것은 다음과 같이 할 수 있습니다.

GRANT role_emp_read TO reader@'127.0.0.1';
GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';

이렇게 역할을 부여하고 나면, 활성화 작업이 필요합니다.

활성화는 다음과 같이 할 수 있습니다.

SET ROLE 'role_emp_read';

즉, 다음과 같은 순서로 역할 부여 작업을 수행하면 됩니다.

역할 생성 -> 권한 부여 -> 계정 생성 -> 계정에 역할 부여 -> 역할 활성화

여기서 주의할 점은 계정이 로그아웃 됐다가 다시 로그인하면 활성화되지 않은 상태로 초기화돼 버린다는 것입니다.

이러한 동작은 불편할 수 있는데 다음과 같이 시스템 변수를 설정하여 부여된 역할이 자동으로 활성화되도록 설정할 수 있습니다.

SET GLOBAL activate_all_roles_on_login=ON;

MySQL 서버 내부적으로 역활과 계정은 동일한 객체로 취급됩니다.

실제로 SELETEC user, host, account_locked FROM mysql.user; 명령문을 통해 계정을 확인해보면 역할이 들어가 있는 것을 확인할 수 있습니다.

host도 자동적으로 %로 부여되어 있는 것을 확인할 수 있는데, 사실 역할을 생성할 때 host도 붙여서 생성할 수 있지만 이것은 아무런 영향이 없긴 합니다.

그렇다면 역할은 왜 사용하는 것일까요?

이는 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용될 수 있게 하기 위해서입니다.

CREATE USER 명령어에 대해서는 권한이 없지만 CREATE ROLE 명령만 실행 가능한 사용자는 역할을 생성할 수 있습니다.

이렇게 생성된 역할은 계정과 동일한 객체를 생성하지만 이 역할을 account_locked 컬럼의 값이 'Y'로 설정되어 있어서 로그인 용도로 사용할 수 없습니다.

참고한 자료

  • Real MySQL 8.0[1권]
profile
서정범
개발정리블로그
이전 포스트

[DB] Redis

다음 포스트

[DB] Real MySQL 4장: 아키텍처

0개의 댓글

관련 채용 정보