MySQL 스터디 - 사용자 및 권한

새롭게 이직한 곳에서 MySQL 스터디를 하기로 하였습니다.

Real MySQL 8.0 1권 <= 해당 책 내용을 바탕으로 스터디를 진행하였습니다.

이번 스터디는 MySQL의 **사용자 및 권한**에 대해 알아보겠습니다.

---

1. 사용자 식별

MySQL의 사용자는 다른 DBMS와 다르게 사용자의 계정뿐 아니라 사용자의 접속 지점(클라이언트가 실행된 호스트명이나 도메인 또는 IP주소) 도 계정의 일부가 됩니다.

• MySQL에서 계정을 언급할 때는 항상 아이디와 호스트를 함께 명시

그리고 역 따옴표(`)는 MySQL에서 ID와 IP 주소를 감싸는 식별자를 감싸는 따옴표 역할을 합니다.
이는 종종 홑 따옴표(')로 바뀌어서 사용되기도 합니다.

`svc_id`@`127.0.0.1`

그리고 위와 같은 내용의 의미는 "svc_id" 계정을 "127.0.0.1 (즉, localhost)" 에서 동작 중인 MySQL 서버에 접근할 수는 있도록 권한 설정을 해주는 것 입니다.

`svc_id`@`%`

특정 IP를 지정해주는 것이 아닌 모든 외부 IP에서 접속이 가능한 사용자 계정을 생성하고 싶다면, 아래와 같이 권한 설정을 해주면 됩니다.

---

2. 사용자 계정 관리

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

이것에 대해 자세히 알아보겠습니다.

2-1. 시스템 계정과 일반 계정

• 시스템 계정과 일반 계정의 차이
  시스템 계정은 DB 서버 관리자를 위한 계정이며, 일반 계정은 응용 프로그램이나 개발자를 위한 계정 정도로 생각하면 됩니다.

표로 정리를 하자면,

기능	시스템 계정	일반 계정
계정 관리	O	X
다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제 종료	O	X
스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정	O	X

그리고 MySQL 서버에는 기본적으로 내장된 계정들이 있습니다.

• mysql.sys@localhost: MySQL 8.0 부터 기본으로 내장된 sys 스키마의 객체들의 DEFINER로 사용되는 계정
• mysql.session@localhost: MySQL 플러그인이 서버로 접근할 때 사용되는 계정
• mysql.infoschema@localohost: information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

위 계정들은 처음부터 잠겨 있는 계정들 입니다.

이제 계정 생성에 대해 알아보겠습니다.

2-2. 계정 생성

MySQL 5.7 버전까지는 GRANT 명령으로 계정 생성 및 권한 부여까지 한꺼번에 가능 했습니다.

그러나 8.0 부터는 아래 명령으로 구분이 되었습니다.

• 계정 생성은 CREATE USER
• 권한 부여는 GRANT

그리고 계정 생성할 때 아래와 같이 다양한 옵션을 설정할 수 있습니다.

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

실제로 사용되는 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

해당 옵션은 사용자의 인증 방식과 비밀번호를 설정 해줍니다.

IDENTIFIED WITH 뒤에는 반드시 인증 방식을 먼저 명시를 해주어야 합니다.

MySQL 5.7 버전까지는 Native Authentication이 기본 인증 방식으로 사용되었습니다.
그러나 8.0 버전부터는 Caching SHA-2 Authentication이 기본 인증으로 바뀌었습니다.

Caching SHA-2 Authentication은 SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에, 기존 패스워드 입력하는 방식 보다 복잡해졌습니다.

테스트 단계에서는 Native Authentication 방식으로 사용해주는 게 더 간단 합니다.
그래서 Native Authentication 방식을 기본 인증 방식으로 설정하기 위해선
MySQL 설정을 아래와 같이 명령어를 사용해서 변경 해주면 됩니다.

mysql> set GLOBAL default_authentication_plugin="mysql_native_password"

REQUIRE

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

PASSWORD EXPIRE

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

그리고 PASSWORD EXPIRE 절에 설정 가능한 옵션은 아래와 같습니다.

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

PASSWORD HISTORY

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

PASSWORD HISTORY 절에 설정 가능한 옵션은 아래와 같습니다.

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

PASSWORD REUSE INTERVAL

사용자가 이전 비밀번호를 다시 사용할 수 있는 시점을 제한하는 옵션 입니다.

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

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

PASSWORD REQUIRE

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

별도로 명시하지 않으면 password_require_current 시스템 변수의 값으로 설정된다.

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

ACCOUNT LOCK / UNLOCK

계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글 수 있는 옵션

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

즉, 정리를 하자면 아래와 같습니다.

옵션명	설명
IDENTIFIED WITH	사용자의 인증 방식과 비밀번호 설정
REQUIRE	MySQL 서버에 접속할 때, 암호화된 SSL/TLS 채널 사용 여부 설정
PASSWORD EXPIRE	비밀번호의 유효 기간 설정하는 옵션
PASSWORD HISTORY	한번 사용했던 비밀번호를 재사용하지 못하게 하는 옵션
PASSWORD REUSE INTERVAL	사용자가 이전 비밀번호를 다시 사용할 수 있는 시점을 제한하는 옵션
PASSWORD REQUIRE	비밀번호가 만료되었을 시, 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지 결정하는 옵션
ACCOUNT LOCK / UNLOCK	계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글 수 있는 옵션

---

3. 비밀번호 관리

이번엔 비밀번호 관리에 대해 알아보겠습니다.

3-1. 고수준 비밀번호

DB의 사용자 패스워드를 고수준으로 관리해야하는 이유는 다음과 같습니다.

구분	이유
🔐 보안 강화	해킹·유출 시 사용자 및 시스템 피해 방지
📜 법적 규제 준수	GDPR, 개인정보보호법 등 보안 요구사항 충족
🧱 해킹 피해 최소화	해시 사용 시 평문 비밀번호 노출 방지
👀 내부자 위협 예방	개발자·DBA 등 내부 접근자에 의한 노출 방지
⚙️ 비밀번호 정책 구현	유효기간, 복잡성, 재사용 제한 등 적용 가능
🏢 기업 신뢰도 보호	유출 사고 시 기업 이미지·신뢰도 타격 예방

이제 실제로 MySQL에서 비밀번호를 단순하게 지정못하게 막고 복잡한 규칙을 넣도록 설정 해보겠습니다.

우선 MySQL 서버에서 비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용해야 합니다.

따라서 설치해보록 하겠습니다.

## validate_password 컴포넌트 설치
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.changed_characters_percentage|0     |
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     |

비밀번호 정책(validate_password.policy)은 크게 3가지 중에 선택합니다. (기본값은 MEDIUM)

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

그리고 나머지 시스템 변수들에 대해 설명을 정리하면 아래와 같습니다.

변수명	설명
validate_password.changed_characters_percentage	비밀번호 변경 시 이전 비밀번호와 얼마나 **다른 문자 비율(%)**이 필요한지를 설정합니다. 예: 0이면 제한 없음, 50이면 최소 절반은 달라야 함
validate_password.check_user_name	ON이면 사용자 이름이 비밀번호에 포함되어 있으면 비밀번호 거부 즉, 사용자 ID와 유사한 비밀번호 사용을 막음
validate_password.dictionary_file	사전에 정의된 단어 목록 파일 경로. 이 파일에 있는 단어가 포함된 비밀번호는 거부됨 ※ 현재는 비어 있어 미사용 상태
validate_password.length	비밀번호의 최소 길이 예: 8이면 최소 8자 이상 입력해야 함
validate_password.mixed_case_count	대소문자 혼합이 필요한 경우의 최소 개수 예: 1이면 최소 1개의 대문자와 1개의 소문자가 포함되어야 함
validate_password.number_count	비밀번호에 포함되어야 하는 숫자의 최소 개수
validate_password.policy	비밀번호 검증의 보안 수준을 설정 옵션: LOW, MEDIUM, STRONG MEDIUM이면 길이 + 대소문자 + 숫자 + 특수문자 요구
validate_password.special_char_count	포함되어야 할 특수문자의 최소 개수

3-2. 이중 비밀번호

DB 계정의 비밀번호는 서비스가 실행 중인 상태에서 변경이 불가능 합니다.

그래서 처음 설정한 상태로 몇 년동안 사용되는 경우가 많고, 보안을 위해서 주기적으로 변경을 해야하지만, 서비스를 멈추지 않고서는 비밀번호를 변경하는 것은 불가능 했었습니다.

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

이중 비밀번호는 하나의 계정에 대해 2개의 비밀번호를 동시에 설정할 수 있습니다.

2개의 비밀번호는 Primary와 Secondary로 구분이 됩니다.

여기서 최근에 설정된 비밀번호는 Primary 비밀번호, 그 이전에 설정한 비밀번호는 Secondary 로 구분이 됩니다.

이제 이중 비밀번호를 설정하는 방법에 대해 알아보겠습니다.

## 먼저 비밀번호 설정
## 이 때는 'old_password' 비밀번호가 Primary 비밀번호 입니다.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';

## RETAIN CURRENT PASSWORD 옵션을 하여 Primary 비밀번호 변경
## 아래 명령어를 실행하면 'old_password'는 Secondary가 되고 'new_password'가 Primary로 됩니다.
mysql> ALTER USER 'root'@'localhost IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

이렇게 하면 첫 번째로 설정한 패스워드인 'old_password' 는 Secondary
그 이후에 설정한 패스워드인 'new_password'는 Primary 가 됩니다.

이 상태에서 root 계정은 두 비밀번호 중 아무거나 입력해도 로그인이 됩니다.

그리고 이렇게 설정된 상태에서 DB에 연결하는 응용 프로그램의 소스코드나 설정 파일의 비밀번호를 새로운 비밀번호인 'new_password'로 변경하고 배포 및 재시작을 하고

MySQL 서버에 접속하는 모든 응용 프로그램의 재시작이 완료되면 Secondary 패스워드를 삭제해주게 되면

## Secondary 패스워드 삭제 방법
mysql> ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

앞서 설명했던 상황인, 서비스 도중에 DB 패스워드를 변경할 수 있습니다.

3-4. 권한 (Previlege)

권한(Privilege)은 사용자가 어떤 작업을 데이터베이스, 테이블, 컬럼 혹은 기타 리소스에 대해 수행할 수 있는지를 정의하는 매우 중요한 보안 요소 입니다.

권한을 통해 사용자의 접근을 제어하고 시스템의 무결성과 보안을 유지할 수 있습니다.

권한은 두 가지로 나뉩니다.

1. 글로벌 권한
2. 객체 권한

• 글로벌 권한

MySQL 서버 전체에 영향을 미치는 권한 입니다. 모든 데이터베이스, 테이블, 사용자 등에 적용이 됩니다.

## 글로벌 권한 부여 예시
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

• 객체 권한

특정한 데이터베이스, 테이블, 컬럼, 프로시저 등 개별 객체에만 적용되는 권한입니다.

## 객체 권한 부여 예시
## localhost에서만 접속이 가능한 user 라는 사용자는 employees 데이터베이스의 salaries 테이블을 조회(SELECT), 수정(UPDATE) 작업만 할 수 있도록 권한 부여
mysql> GRANT SELECT, UPDATE ON employees.salaries TO 'user'@'localhost';

동적 권한

MySQL 8.0 버전에서는 5.7 버전에서 없던 동적 권한 이라는 권한 시스템이 추가되었습니다.

🔥 동적 권한(Dynamic Privileges)이란?

MySQL 8.0에서 도입된 새로운 형태의 권한으로,
기존의 SELECT, INSERT, UPDATE와 같은 정적(Static) 권한에 포함되지 않는,
특정 기능이나 컴포넌트에 대해 동적으로 관리 가능한 권한입니다.

즉, 필요할 때마다 새로 추가될 수 있는 권한 시스템 입니다.

동적 권한이 추가되면서 예전과 다르게 데이터베이스 관리를 위해 필요했던 SUPER 권한 하나로 다 제어하지 않고,

SUPER 권한을 잘게 쪼개서 관리자 별로 꼭 필요한 권한만 부여할 수 있게 하였습니다.

이제 동적 권한 부여하는 방식에 대해 알아보겠습니다.

몇 가지 주의사항이 있습니다.

1. 항상 on *.* 형식으로 부여해야 함
2. SHOW GRANTS FOR 'user'@'host'; 로도 확인 가능하지만, 더 세부적으로 mysql.global_grants 테이블에서 직접 확인 할 수 있습니다.
3. FLUSH PRIVILEGES 필요 없음 (GRANT/REVOKE 시 자동적용)

## 동적 권한 부여 방법
mysql> GRANT BACKUP_ADMIN ON *.* TO 'backupuser'@'%';

정적, 동적 권한의 저장소 테이블 정보

구분	저장소 테이블	설명
정적 권한	mysql.user	계정 정보 & 계정이나 역할에 부여된 글로벌 권한
	mysql.db	계정이나 역할에 DB 단위로 부여된 권한
	mysql.tables_priv	계정이나 역할에 테이블 단위로 부여된 권한
	mysql.colums_priv	계정이나 역할에 칼럼 단위로 부여된 권한
	mysql.procs_priv	계정이나 역할에 스토어드 프로그램 단위로 부여된 권한
동적 권한	mysql.global_grants	계정이나 역할에 부여되는 동적 글로벌 권한

3-5. 역할(Role)

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

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


간단하게 MySQL에서 역할을 사용하는 예시를 살펴보겠습니다.

## role_emp_read, write 라는 역할 2개를 생성하는 명령어
mysql> CREATE ROLE
        role_emp_read,
        role_emp_write;

앞서 생성한 역할은 빈 껍데기만 있는 역할을 생성한 것 입니다.

그리고 다음과 같이 GRANT 명령으로 각 역할에 대해 실질적인 권한을 부여하고 계정에 역할을 부여해서 사용 합니다.

## 각 역할에 권한을 부여해주는 명령어
mysql> GRANT SELECT ON employees.* TO role_emp_read;
mysql> GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

## reader, writer 라는 유저 생성
mysql> CREATE USER reader@'127.0.0.1' IDENTIFIED BY '1234';
mysql> CREATE USER writer@'127.0.0.1' IDENTIFIED BY '1234';

## 생성한 reader, writer 유저에 역할 부여
mysql> GRANT role_emp_read TO reader@'127.0.0.1';
mysql> GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';

## 각 user 마다 부여된 권한 확인
mysql> show grants for 'reader'@'127.0.0.1';
mysql> show grants for 'writer'@'127.0.0.1';

## 그리고 꼭 역할이 부여된 user로 접속을 해서 역할을 활성화를 시켜주어야지 역할 안에 있는 권한을 사용할 수 있습니다.
linux> mysql -h 127.0.0.1 -u reader -p
mysql> SET ROLE 'role_emp_read';

---

이상으로 MySQL 스터디 - 사용자 및 권한 부분을 알아보았습니다.

감사합니다.