[Real MySQL 8.0 1] 03 사용자 및 권한

DBMS 선택 순서

1. 안정성 / 2. 성능과 기능 / 3. 커뮤니티나 인지도

점수 부여할 때 사용하는 대표적인 기준
웹 사이트 언급 횟수 / 검색 빈도 / 기술 토론 빈도 / DBMS별 구인 / 전문가 인맥

 

DB-Engines Ranking

---

1. 사용자 식별

사용자의 계정 + 사용자의 접속 지점

클라이언트가 실행된 호스트명 or 도메인/IP 주소

=> 계정 언급할 때는 항상 아이디와 호스트 함께 명시해야 함

 

항상 MySQL 서버가 기동 중인 로컬 호스트에서 svc_id라는 아이디로 접속할 때만 사용될 수 있는 계정

'svc_id'@'127.0.0.1'

 

IF 모든 외부 컴퓨터에서 접속이 가능한 사용자 계정 생성하고 싶음

-> 사용자 계정의 호스트 부분을 %문자로 대체

%문자: 모든 IP/모든 호스트명

 

'svc_id'@'192.168.0.10' -- 비밀번호: 123
'svc_id'@'%' -- 비밀번호: abc

-> 계정 정보 중 범위가 좁은 계정 정보 이용해 사용자 인증(`'svc_id'@'192.168.0.10'` # 비밀번호: 123)

=> (`'svc_id'@'%'` # 비밀번호: abc) 인증 -> 접속 거절

---

2. 사용자 계정 관리

1) 시스템 계정과 일반 계정

`SYSTEM_USER` 권한 가지고 있는지 -> 시스템 계정(System Account)/일반 계정(Regular Account)

• 시스템 계정
  • MySQL 서버 내부적으로 실행되는 백그라운드 스레드와 무관
  • 사용자를 위한 계정, 서버 관리자를 위한 계정
  • 데이터베이스 서버 관리와 관련된 중요한 작업
    • 계정 관리(계정 생성 및 삭제, 권한 부여 및 제거)
      • 시스템 계정, 일반 계정 관리(생성 삭제 및 변경) O
    • 다른 세션(Connection) or 세션에서 실행 중인 쿼리 강제 종료
    • 스토어드 프로그램 생성 -> DERINER를 타 사용자로 설정
• 일반 계정
  • 응용 프로그램 or 개발자를 위한 계정

=> DBA(데이터베이스 관리자) 계정: `SYSTEM_USER` 권한 할당/ 일반 사용자 계정: `SYSTEM_USER` 권한 부여 X

 

서버에 내장된 계정

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

=> `account_locked` 칼럼에 잠겨 있음

SELECT user, host, account_locked FROM mysql.user WHERE user LIKE 'mysql.%';

---

2) 계정 생성

8.0 버전 이후

(5.7 버전까지는 계정 생성 + 권한 부여: `GRANT`)

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

 

계정 생성할 때 옵션 설정

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

 

일반적으로 많이 사용되는 옵션을 가진 CREATE USER 명령

CREATE USER 'user'@'%'
IDENTIFIED WITH 'myslq_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 서버의 기본 인증방식 사용: `IDENTIFIED BY 'password'` 형식으로 명시해야 함

• Native Pluggable Authentication: 5.7 버전까지 기본으로 사용되던 방식
  • 비밀번호에 대한 해시(SHA-1 알고리즘) 값 저장, 클라이언트가 보낸 값과 해시값이 일치하는지 비교
• Caching SHA-2 Pluggable Authentication: 5.6 버전에 도입, 8.0 버전에서 더 보완된 인증 방식
  • 암호화 해시값 생성 -> SHA-2(256비트) 알고리즘 사용
  • Native Authentication과 차이: 암호화 해시 알고리즘 차이(보안에 더 중점을 둔 알고리즘)
    • Native Authentication 플러그인: 입력이 동일 해시값 출력
    • Caching SHA-2 Authentication: 내부적으로 Salt 키 활용, 수천 번의 해시 계선 수행해서 결과 만들어 냄
      -> 동일한 키 값에서도 결과가 달라짐
      => 시간 소모↑ -> 성능 ↓ 
      => 보완: 해시 결괏값을 메모리에 캐시해서 사용 
  • SSL/TLS or RSA 키페어 반드시 사용해야 함 -> 클라이언트에서 접속할 때 SSL 옵션을 활성화해야 함
• PAM Pluggable Authentication: 유닉스/리눅스 패스워드/LDAP(Lightweight Directory Access Protocol) 같은 외부 인증을 사용할 수 있게 해주는 인증 방식
  • 엔터프라이즈 에디션에서만 사용 가능
• LDAP Pluggable Authentication: LDAP을 이용한 외부 인증을 사용할 수 있게 해주는 인증 방식
  • 엔터프라이즈 에디션에서만 사용 가능

=> (기본 인증 방식) 5.7 버전까지: Native Authentication -> 8.0 버전부터: Caching SHA-2 Authentication 

But, Caching SHA-2 Authentication은 SSL/TLS or RSA 키페어 필요

=> 기존 버전과의 호환성 고려하면 Native Authentication 인증 방식으로 계정 생성해야 할 수 있음

-- Native Authentication을 기본 인증 방식으로 설정
SET GLOBAL default_authentication_plugin="mysql_native_password"

 

REQUIRE

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

별도 설정 X -> 비암호화 채널로 연결 

But, `REQUIRE` 옵션을 SSL로 설정하지 않았어도 Caching SHA-2 Authentication 인증 방식 사용

-> 암호화된 채널만으로 서버 접속할 수 있게 됨

 

PASSWORD EXPIRE

비밀번호의 유효 기간을 설정하는 옵션

별도 명시 X -> `default_password_lifetime` 시스템 변수에 저장된 기간으로 유효 기간이 설정

개발자/데이터베이스 관리자의 비밀번호 설정 -> 보안상 안전 But, 응용 프로그램 접속용 계정에 유효 기간 설정하는 것은 위험

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

 

PASSWORD HISTORY

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

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

-> 한 번 사용했던 비밀번호 사용 X -> 이전에 사용했던 비밀번호를 서버가 기억해야 함

=> mysql DB의 `password_history` 테이블 사용

SELECT * FROM mysql.password_history;

 

PASSWORD REUSE INTERVAL

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

별도 명시 X -> `password_reuse_interval` 시스템 변수에 저장된 기간으로 설정

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

 

PASSWORD REQUIRE

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

별도 명시 X -> `password_require_current` 시스템 변수의 값으로 설정

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

 

ACCOUNT LOCK / UNLOCK

계정 생성 시 or `ALTER USER` 명령 -> 계정 정보 변경할 때 계정 사용하지 못하게 잠글지 여부 결정

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

---

3. 비밀번호 관리

1) 고수준 비밀번호

유효기간이나 이력 관리 ~> 재사용 금지 기능

/ 비밀번호 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합 강제하거나 금칙어 설정하는 기능

=> 비밀번호의 유효성 체크 규칙 적용 -> `validation_password` 컴포넌트 이용

-- validation_password 컴포넌트 설치
INSTALL COMPONENT 'file://component_validation_password';

-- 설치된 컴포넌트 확인
SELECT * FROM mysql.component;

-- 컴포넌트에서 제공하는 시스템 변수 확인
SHOW GLOBAL VARIABLES LIKE 'validation_password%';

 

비밀번호 정책 3가지 중에서 선택, 기본값: MEDIUM

• LOW: 비밀번호의 길이만 검증
• MEDIUM: 비밀번호의 길이 검정, 숫자와 대소문자, 특수문자의 배합 검증
  • `validate_password.mixed_case_count`, `validate_password.number_count`, `validate_password.special_char_count` 시스템 변수에 설정된 글자 수 이상을 포함하고 있는지
  • `validate_password.dictionary_file` 시스템 변수에 설정된 사전 파일에 명시된 단어를 포함하고 있는지 검증
• STRONG: MEDIUM 레벨의 검증 모두 수행 + 금칙어가  포함됐는지 여부까지 검증

 

서버에 금칙어 파일 등록(STRONG만 작동)

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

---

2) 이중 비밀번호

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

-> (해결) 8.0 버전부터는 계정의 비밀번호로 2개의 값을 동시에 사용

= 이중 비밀번호(Dual Password): 2개의 비밀번호 중 하나만 일치하면 로그인이 통과되는 것

프라이머리(Primary): 최근 설정된 비밀번호 / 세컨더리(Secondary): 이전 비밀번호

 

이중 비밀번호 사용 -> 기존 비밀번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션 추가

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

-- 비밀번호 변경 + 기존 비밀번호를 세컨더리 비밀번호로 설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

1. 프라이머리 비밀번호: 'old_password'로 변경, 세컨더리 비밀번호: 빈 상태
2. 이전 비밀번호였던 'old_password' -> 세컨더리 비밀번호로 설정
, 새롭게 설정한 'new_password'는 프라이머리 비밀번호로 설정

=> 새로운 비밀번호 'new_password'로 변경하고 배포 및 재시작 순차적으로 실행
-> 서버에 접속하는 모든 응용 프로그램이 재시작 완료 -> 세컨더리 비밀번호 삭제

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

---

4. 권한(Privilege)

5.7 버전까지 권한: 글로벌(Global) 권한 / 객체 단위의 권한

• 글로벌 권한: 데이터베이스/테이블 이외의 객체에 적용되는 권한
  • `GRANT` 명령에서 특정 객체를 명시하지 말아야 함
• 객체 권한: 데이터베이스/테이블 제어하는 데 필요한 권한
  • `GRANT` 명령으로 권한 부여할 때 반드시 특정 객체를 명시해야 함

예외: ALL(or ALL PRIVILEGES): 글로벌과 객체 권한 2가지 용도로 사용될 수 있음

• 특정 객체에 ALL 권한 부여 -> 해당 객체에 적용될 수 있는 모든 객체 권한 부여
• 글로벌로 ALL 사용 -> 글로벌 수준에서 가능한 모든 권한 부여

 

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

5.7 버전까지는 SUPER라는 권한이 데이터베이스 관리를 위해 꼭 필요한 권한 But, 8.0 부터는 쪼개져 동적 권한으로 분산

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

---

사용자에게 권한 부여

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

`GRANT OPTION` 권한은 다른 권한과 달리 `GRANT` 명령 마지막에 `WITH GRANT OPTION` 명시해서 부여
`privilege_list`에는 구분자(,)를 써서 권한 여러 개 동시에 명시할 수 있음
`TO 키워드` + 권한을 부여할 대상 사용자 명시
`ON 키워드` + 어떤 DB의 어떤 오브젝트에 권한을 부여할지 결정(권한의 범위 -> 사용하는 방법 달라짐)

 

글로벌 권한

특정 DB나 테이블에 부여 X -> 항상 *.* 사용

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

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

 

DB 권한

서버에 존재하는 모든 DB에 대해 권한 부여 or 특정 DB에 대해서만 권한 부여  

DB 권한만 부여하는 경우(테이블에 대해 부여 X) -> `employees.department` 같이 테이블까지 명시 X

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

 

테이블 권한

서버의 모든 DB에 권한 부여 or 특정 DB의 오브젝트에 대해서만 권한 부여 or 특정 DB의 특정 테이블에 대해서만 권한 부여

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

 

특정 칼럼에 대해서만 권한을 부여하는 경우 -> `DELETE` (X) 

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

=> 잘 사용하지 X -> 칼럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 칼럼에 대해서도 권한 체크

=> 전체적인 성능에 영향 미칠 수 있음

-> 권한이 필요시 테이블에서 권한을 허용하고자 하는 칼럼만으로 별도의 뷰(`VIEW`)를 만들어 사용하는 방법

뷰도 하나의 테이블로 인식 -> 뷰의 칼럼에 대해 권한 체크 X 뷰 자체에 대한 권한만 체크 O

---

5. 역할(Role)

이름의 역할 정의

CREATE ROLE
role_emp_read,
role_emp_write;

 

빈 껍데기만 있는 역할 정의 -> `GRANT` 명령으로 실질적인 권한 부여

• `role_emp_read`: employees DB의 모든 객체에 대해 읽기(`SELECT`) 권한만 부여
• `role_emp_write`: employees DB의 모든 객체에 대해 데이터 변경(`INSERT`, `UPDATE`, `DELETE`) 권한 부여

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

 

역할은 그 자체로 사용 X 계정에 부여해야 함 -> reader, writer 생성

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

 

계정 생성 But, 권한 부여 X -> 역할 부여

• reader 계정: `role_emp_read` 역할만 부여
• writer 계정: `role_emp_read`, `role_emp_write` 역할 부여

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

=> 실제 역할은 부여 O But, `role_emp_read` 역할 X

 

해당 역할 활성화

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

SET ROLE 'role_emp_read';

SELECT current_role();

SELECT COUNT(*) FROM employees.employees;

=> 서버의 역할이 자동으로 활성화되지 X 설정 

-> `activate_all_roles_on_login` 시스템 변수로 설정

-> 로그인과 동시에 부여된 역할이 자동으로 활성화

SET GLOBAL activate_all_roles_on_login=ON;

---

Q. MySQL 서버는 계정과 권한을 어떻게 구분?

A. 하나의 계정에 다른 계정의 권한을 병합하면 되므로 구분할 필요 X

계정과 역할이 내부적으로 똑같은 객체

 

Q. 서버에서 굳이 왜 `CREATE ROLE`, `CREATE USER` 명령 구분해서 지원?

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

`CREATE USER` 명령에 대해서는 권한 X

`CREATE ROLE` 명령만 실행 가능한 사용자는 역할을 생성할 수 있음

-> 이렇게 생성된 역할은 계정과 동일한 객체 생성 But, 실제 이 역할은 `account_locked` 칼럼 값 'Y' => 로그인 용도로 사용 X

---

출처

GitHub - wikibook/realmysql80: 《Real MySQL 8.0》 예제 파일