해당 포스트는 MySQL를 학습하며 필요한 내용을 정리한 포스트입니다.

🌈 3. 사용자 및 권한

MySQL에서 사용자 계정을 생성하는 방법이나 각 계정의 권한을 설정하는 방법은 다른 DBMS와는 조금 차이가 있다.

단순히 사용자의 아이디뿐만 아니라 해당 사용자가 어느 IP에서 접속했는지도 확인
MySQL 8.0 버전부터는 권한을 묶어서 관리하는 역할(Role) 개념 도입

💻 3.1 사용자 식별

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

아이디와 IP 주소를 감싸는 역할 역따옴표(`)는 MySQL에서 식별자를 감싸는 따옴표 역할을 하는데, 홑따옴표로(')도 바꿔쓰기도 한다.

🔻 예시

‘svc_id’@’127.0.0.1’

항상 MySQL 서버가 가동 중인 로컬 호스트에서 svc_id라는 아이디로 접속할 때만 사용할 수 있는 계정
이 계정만 등록되어 있다면 다른 컴퓨터에서는 svc_id라는 아이디로 접속할 수 없음

🔻 예시

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

위의 예시처럼 모든 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하려면 %을 사용하면 된다.
- 즉 % 문자는 모든 IP 또는 호스트 명을 의미한다.
동일한 아이디가 있을 때 권한이나 계정 정보에 대해 MySQL은 범위가 가장 작은 것을 항상 먼저 선택

💻 3.2 사용자 계정 관리

🍳 3.2.1 시스템 계정과 일반 계정

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

여기서 소개하는 시스템 계정은 MySQL 서버 내부적으로 실행되는 백그라운드 스레드와는 무관하며, 시스템 계정도 일반 계정과 같이 사용자를 위한 계정이다.

시스템 계정은 데이터베이스 서버 관리자를 위한 계정

일반 계정은 응용프로그램이나 개발자를 위한 계정 정도로 생각하면 된다.

시스템 계정은 시스템 계정과 일반 계정을 관리(생성 삭제 및 변경)할 수 있지만 일반 계정은 시스템 계정을 관리 할 수 없다.

데이터베이스 서버 관리와 관련된 중요 작업은 시스템 계정으로만 수행 가능

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

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

삭제되지 않도록 주의해야할 내장 계정
'mysql.sys'@'localhost'
MySQL 8.0 부터 기본으로 내장된 sys 스키마의 객체(뷰나 함수, 프로시저)들이 DEFINER로 사용되는 계정
'mysql.session'@'localhost'
MySQL 플러그인이 서버로 접근할 때 사용되는 계정
'mysql.infoschema'@'localhost'
information_schema에 정의된 뷰의 DEFINER로 사용되는 계정
언급한 3개의 계정은 처음부터 잠겨(account_locked 컬럼)있는 상태이므로 의도적으로 잠긴 계정을 풀지 않는 한 악의적인 용도로 사용할 수 없으므로 보안을 걱정하지는 않아도 된다.

🍳 3.2.2 계정 생성

MySQL 8.0 부터는 계정의 생성은 CREATE_USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행해도록 바뀌었다.

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

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

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

  
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;

✍ 3.2.2.1 IDENTIFIED WITH

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

IDENTIFIED WITH 뒤에는 반드시 인증 방식(인증 플러그인의 이름)을 명시

MySQL 서버의 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시

다양한 인증 방식을 플러그인 형태로 제공하며, 다음 4가지 방식이 대표적

Native Pluggable Authentification
- MySQL 5.7 버전까지 기본으로 사용되던 방식
- 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 비교하는 인증 방식
Caching SHA-2 Pluggable Authentification
- MySQL 5.6 버전에 도입되고 MySQL 8.0 버전에서는 조금 더 보완된 인증 방식
- 암호화 해시값 생성을 위해 SHA-2(256비트) 알고리즘을 사용한다.
- Nativa Authentification과 가장 큰 차이는 사용되는 암호화 해시 알고리즘
- SHA-2 Authentification은 저장된 해시값의 보안에 더 중점을 둔 알고리즘으로 이해
- Nativa Authentification 플러그인은 입력이 동일 해시값을 출력하지만 Caching SHA-2 Authentification은 내부적으로 Salt 키를 활용하여, 수천 번의 해시 계싼을 수행해서 결과를 만들어 내기 때문에 동일한 키 값에 대해서도 결과가 달라진다.
- 해시값을 계산하는 방식은 상당히 시간 소모적이기 때문에 성능이 많이 떨어지는데, 이를 보완하기 위해 MySQL 서버는 해시 결괏값을 메모리에 캐시해서 사용하게 된다. 그래서 이름에 ‘Caching’이 포함되는 것이다.
- SSL/TLS 또는 RSA 키페어를 반드시 사용해야 하므로 클라이언트에 접속할 때 SSL 옵션을 활성화해야 한다.
PAM Pluggable Authentification
- 유닉스나 리눅스 패스워드 또는 LDAP(Lightweight Directory Access Protocol)과 같은 외부 인증을 사용할 수 있게 해주는 인증 방식
- MySQL 엔터프라이즈 에디션에서만 사용 가능
LDAP Pluggable Authentification
- LDAP(Lightweight Directory Access Protocol)를 이용한 외부 인증을 사용할 수 있게 해주는 인증 방식
- MySQL 엔터프라이즈 에디션에서만 사용 가능

Caching SHA-2 Authentification 방식이 기본 인증이지만, SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에 기존 방식과는 다른 방식으로 접속해야 한다.

호환성을 고려하면 Native Authentification 인증 방식으로 계정을 생성해야 할 수도 있다.

만약 MySQL 8.0에서도 Native Authentification을 기본 인증 방식으로 설정하고자 한다면 MySQL 설정을 변경하거나 my.cnf 설정 파일에 추가 하면 된다.

  
--// Native Authentification을 기본 인증 방식으로 설정 
SET GLOBAL default_authentification_plugin="mysql_native_password"

CREATE USER 또는 ALTER USER 명령을 이용해 연결 방식과 비밀번호 옵션, 자원 사용과 관ㄹㄴ된 여러 옵션을 설정할 수 있다.

✍ 3.2.2.2 REQUIRE

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

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

REQUIRE 옵션을 SSL로 설정하지 않았다고 해도 Caching SHA-2 Authentification 인증 방식을 사용하면 암호화된 채널만으로 접속할 수 있게 된다.

✍ 3.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 DAY
- 비밀번호의 유효 기간을 오늘로부터 n일자로 설정

✍ 3.2.2.4 PASSWORD HISTORY

한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션

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

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

MySQL 서버는 mysql DB의 password_history 테이블을 사용해서 이전 사용했던 비밀번호를 서버가 기억

✍ 3.2.2.5 PASSWORD REUSE INTERVAL

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

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

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

✍ 3.2.2.6 PASSWORD REQUIRE

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

별도로 명시되지 않으면 password_require_current 시스템 변수의 값으로 설정

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

✍ 3.2.2.7 ACCOUNT LOCK / UNLOCK

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

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

💻 3.2 비밀번호 관리

🍳 3.2.1 고수준 비밀번호

비밀번호는 유효기간이나 이력 관리를 통한 재사용 금지 기능뿐만 아니라 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 있다.

비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하면 되는데, 우선 컴포넌트를 설치해야 한다.

  
# validate_password 컴포넌트 설치 
INSTALL COMPONENT 'file://component_validate_password';

SELECT * FROM mysql.component;

validate_password 컴포넌트가 설치되면 컴포넌트에서 제공하는 시스템 변수를 확인 할 수 있다.

SHOW GLOBAL VARIABLES LIKE '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.dictionary_file에 설정된 사전 파일에 명시된 단어를 포함하고 있는지

MySQL 서버에 금칙어 파일을 등록하면 된다.

비밀번호 금칙어는 validate_password.policy 가 STRONG인 경우에만 작동하므로 금칙어를 적용하려면 함께 변경해야 한다.

  
SET GLOBAL validate_password.dictionary_file='prohibitive_word.data';
SET GLOBAL validate_password.policy='STRONG';

🍳 3.2.2 이중 비밀번호

데이터베이스 서버의 계정 정보는 쉽게 변경하기 어렵다.

그중에서도 데이터베이스 계정의 비밀번호는 서비스가 실행 중인 상태에서는 변경이 불가능했다.

이러한 문제를 해결하기 위해 MySQL 8.0 버전부터는 계쩡의 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능 추가했다.

이 기능을 이중 비밀번호(Dual Password)라고 한다.

비밀번호 2개를 모두 맞게 입력했을 때 로그인되는 것이 아니라 2개의 비밀번호 중 하나만 일치하면 로그인이 통과되는 것을 의미

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

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

MySQL 서버의 이중 비밀번호 기능은 하나의 계정에 대해 2개의 비밀번호를 동시에 설정하는 것

2개의 비밀번호는 프라이머리(Primary)와 세컨더리(Secondary)로 구분

최근에 설정한 비밀번호가 프라이머리, 이전 비밀 번호는 세컨더리

기존 비밀 번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션 추가

💻 권한(Privilege)

5.7 버전까지 권한은 아래의 표와 같이 글로벌(Global) 권한과 객체 단위의 권한으로 구분

글로벌 권한
- 데이터베이스나 테이블 이외의 객체에 적용되는 권한을
- GRANT 명령에서 특정객체를 명시하지 말아야한다.
객체 권한
- 데이터베이스나 테이블을 제어하는 데 필요한 권한
- GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시
예외
- ALL(또는 ALL PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용
- 특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한 부여
- 글로벌로 ALL이 사용되면 글로벌 수준에서 가능한 모든 권한 부여

정적권한

MySQL 8.0 부터는 동적 권한이 더 추가 됐다.

정적권한은 MySQL 서버의 소스코드에 고정적으로 명시되어 있는 권한

동적권한은 (일부는 명시되어 있기도 하지만) MySQL 서버가 시작되면서 동적으로 생성되는 권한

예를 들어, MySQL 서버의 컴포넌트나 플로그인이 설치되면 그 때 등록되는 권한

동적권한

이전 버전에서 꼭 필요한 권한인 SUPER 가 8.0 버전부터는 잘게 쪼개져 동적 권한으로 분산됐다.

백업 관리자와 복제 관리자 개별로 꼭 필요한 권한만 부여할 수 있게 됨

사용자에게 권한을 부여할 때는 GRANT 명령어를 사용한다.

각 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀌어야 한다.

  
GRANT privilege_list ON db.table TO 'user'@'host'

8.0버전 부터 존재하지 않는 사용자에 대해 GRANT 명령이 실행되면 에러가 발생

반드시 사용자를 먼저 생성하고 GRANT 명령으로 권한 부여

GRANT OPTION 권한은 다른 권한과 달리 GRANT 명령의 마지막에 WITH GRANT OPTION을 명시해서 부여

privilege_list 에는 구분자(,)를 써서 앞의 명시된 권한 여러 개를 동시에 명시할 수 있다.

TO 키위드 뒤에는 권한을 부여할 대상 사용자를 명시

ON 키워드 뒤에는 어떤 DB의 어떤 오브젝트에 권한을 부여할지 결정할 수 있는데, 권한의 범위에 따라 사용하는 방법이 달라진다.

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

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

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

CREATE USER나 CREATE ROLE과 같은 글로벌 권한은 DB 단위나 ㅇ초브젝트 단위로 부여할 수 있느 권한이 아니므로 항상 *.*로만 대상을 사용할 수 있다.

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

DB 권한은 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대해 권한을 부여할 수 있기 때문에 위의 예와 같이 ON절에 *.* 이나 employee.* 모두 사용할 수 있다. -여기서 DB라 함은 DB내부에 존재하는 테이블뿐만 아니라 스토어드 프로그램들도 모두 포함

DB 권한만 부여하는 경우에는(DB 권한은 테이블에 대해 부여할 수 없기 때문에) employees, department와 같이 테이블까지 명시할 수 없다.

DB 권한은 서버의 모든 DB에 적용할 수 있으므로 대상에 *.*을 사용할 수 있다.

특정 DB에 대해서만 권한을 부여하는 것을 db.*처럼 대상을 설청하는 것도 가능

하지만 오브젝트 권한처럼 db.table로 오브젝트(테이블)까지 명시할 수는 없다

테이블권한

  
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';

특정 컬럼에 대해서만 권한을 부여하는 경우

컬럼에 부여할 수 있는 권한은 SELECT, INSERT, UPDATE로 3가지며 DELETE는 제외

각 권한 뒤에 컬럼을 명시하는 형태로 부여

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

각 계정이나 권한에 부여된 권한이나 역할을 확인하기 위해서는 SHOW GRANTS

표 형태로 보려면 mysql DB의 권한 관련 테이블 참조

💻 역할(Role)

8.0 버전부터 권한을 묶어서 역할(Role)을 사용할 수 있게 됐다.

우선 CREATE ROLE 명령을 이용해 role_emp_read 와 role_emp_write 라는 역할을 정의해보자

  
CREATE ROLE 
  role_emp_read,
  role_emp_write;

CREATE ROLE 명령만으로는 빈 껍데기만 있는 역할을 정의하는 것

GRANT 명령으로 각 역할에 대해 실질적인 권한을 부여하면 된다.

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

기본적으로 역할 그 자체로는 사용될 수 없고 계정에 부여해야 함.

  
CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'qwerty';

계정을 생성한 후 권한을 부여

  
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 명령어를 실행해 해당 역할을 활성화

역할이 활성화되면 그역할이 가진 권한은 사용할 수 있는 상태가 되지만 계정이 로그아웃됐다가 다시 로그인하면 역할이 활성화되지 않은 상태로 초기화

자동으로 역할을 활성화하려면 activate_all_roles_on_login 시스템 변수를 설정

  
SET GLOBAL activate_all_roles_on_login=ON;

📚 레퍼런스

백은빈, and 이성욱. Real MySQL 8.0 : 개발자와 DBA를 위한 MySQL 실전 가이드 1 / 백은빈, 이성욱 지음. (2021). Web.

백은빈, and 이성욱. Real MySQL 8.0 : 개발자와 DBA를 위한 MySQL 실전 가이드. 2 / 백은빈, 이성욱 지음 (2021). Web.

Real MySQL 03. 사용자 및 권한