[스터디] 03. 사용자 및 권한

목차

• 03.사용자 및 권한
  • 3.1 사용자 식별
  • 3.2 사용자 계정 관리
    • 3.2.1 시스템 계정과 일반 계정
    • 3.2.2 계정 생성
  • 3.3 비밀번호 관리
    • 3.3.1 고수준 비밀번호
    • 3.3.2 이중 비밀번호
  • 3.4 권한(Privilege)
  • 3.5 역할(Role)
• 참고자료

 

MySQL의 사용자 계정은 사용자의 아이디와 해당 사용자가 접속하고 있는 IP를 확인합니다. MySQL 8.0 버전부터는 권한을 묶어서 관리하는 역할의 개념이 되었으므로 해당 내용에 대해 학습해 봅니다.

03.사용자 및 권한

3.1 사용자 식별

---

• MySQL에서 계정을 언급할 때는 아이디와 호스트를 함께 명시 → '사용자명'@'호스트명'
• 식별자를 감싸는 따옴표는 역따옴표(`)나 홑따옴표(')로 사용
• 로컬 호스트 예) 'svc_id'@'127.0.0.1'
• 외부 호스트 예) 'svc_id'@'%' → 호스트 부분을 % 문자로 대체
• 서로 동일한 아이디가 있는 경우, 권한이나 계정 정보에 대해 범위가 가장 작은 것(로컬 < 외부)을 항상 먼저 선택

3.2 사용자 계정 관리

---

3.2.1 시스템 계정과 일반 계정

• MySQL 8.0 버전부터 SYSTEM_USER 권한 유무에 따라 시스템 계정과 일반 계정으로 구분
• 시스템 계정(System Account): 데이터베이스 서버 관리자(DBA)를 위한 계정
  • 계정 관리(생성 및 삭제, 권한 부여 및 제거)
  • 다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제 종료
  • 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정
• 일반 계정(Regular Account): 응용 프로그램이나 개발자를 위한 계정

• MySQL 서버에 내장된 계정(잠겨있는 상태로 존재)
  • 'mysql.sys'@'localhost': MySQL 8.0부터 내장된 sys 스키마 객체들의 DEFINER로 사용되는 계정
  • 'mysql.session'@'localhost': MySQL 플러그인이 서버로 접근할 때 사용되는 계정
  • 'mysql.infoschema'@'localhost': information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

• DEFINER란?
  • 정의: MySQL 계정을 명명하여 저장된 객체(프로시저, 함수, 트리거, 이벤트, 뷰)의 실행 시 사용될 권한 결정.
  • 기본 설정: 객체를 생성한 사용자가 기본적으로 DEFINER가 됨.
  • 설정 규칙:
    • SET_ANY_DEFINER 권한이 있는 경우, DEFINER로 지정할 수 있음. 계정이 존재하지 않는 경우 경고가 발생하며, 객체가 고아 객체가 될 위험이 있음.
      • SET_ANY_DEFINER: 뷰 또는 저장된 프로그램을 실행할 때 유효 권한 부여 ID를 설정
      • 고아 객체: DEFINER 속성으로 지정된 계정이 존재하지 않은 객체. 계정이 존재하지 않기 때문에 예상대로 작동하지 않을 수 있음.

• • • 자신의 계정만 DEFINER로 설정할 수 있으며, CURRENT_USER 또는 CURRENT_USER()로 지정할 수 있음.
      • CURRENT_USER, CURRENT_USER(): 서버가 현재 클라이언트를 인증하는 데 사용한 MySQL 계정의 사용자 이름과 호스트 이름 조합 반환함.
  • 스토어드 프로시저란?
    • 데이터베이스에서 반복적으로 사용할 SQL 쿼리들을 모아 하나의 함수처럼 실행할 수 있게 하는 쿼리의 집합
    • 데이터 처리 로직을 데이터베이스 서버에 저장하고, 필요할 때마다 호출하여 실행할 수 있음.

(예시)

--- 'admin'@'localhost' 계정을 DEFINER로 사용하여 프로시저 p1을 생성함.
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
--- 프로시저가 DEFINER의 보안 컨텍스트에서 실행됨.
SQL SECURITY DEFINER
BEGIN
  --- 테이블 t1의 counter 컬럼 값을 1 증가시킴.
  UPDATE t1 SET counter = counter + 1;
END;

프로시저 p1은 admin 사용자의 권한을 가지고 실행되어 데이터베이스 작업을 수행합니다. DEFINER 또는 명령을 실행하는 사용자가 필요한 권한이 없다면 SQL 명령은 실패하며, 데이터베이스 시스템은 권한이 없다는 오류 메시지를 반환합니다.

3.2.2 계정 생성

• MySQL 5.7 버전까지는 GRANT 명령으로 권한 부여와 계정 생성 동시에 가능
• MySQL 8.0 버전부터는 계정 생성은 CREATE USER 명령, 권한 부여는 GRANT 명령으로 구분
• 계정 생성 옵션
  • 계정의 인증 방식과 비밀번호
  • 비밀번호 관련 옵션(비밀번호 유효 기간, 비밀번호 이력 개수, 비밀번호 재사용 불가 시간)
  • 기본 역할
  • SSL 옵션
  • 계정 잠금 여부

책에 있는 CRERATE USER 명령 중 일부만 사용해서 직접 실습해 보았습니다.

(예시)

--- 사용자 생성, 비밀번호 설정, 비밀번호 만료 기간 90일로 설정하는 예제
CREATE USER 'newuser'@'localhost'
IDENTIFIED BY 'password'
PASSWORD EXPIRE INTERVAL 90 DAY;

--- 사용자 목록 조회
SELECT User, Host FROM mysql.user;

--- 비밀번호 만료 설정 확인
SELECT password_expired, password_lifetime FROM mysql.user WHERE user = 'newuser' AND host = 'localhost';

사용자 생성 후 사용자 목록을 조회하면, 생성한 'newuser'@'localhost'와 위에서 언급한 MySQL 서버에 내장된 계정도 확인할 수 있습니다. 또한,  mysql.user 테이블의 password_expired와 password_lifetime 컬럼을 통해 비밀번호 만료 관련 설정을 확인할 수 있습니다.

▲ 사용자 생성 예제1

▲ 사용자 생성 예제2

▲ 사용자 생성 예제3

 

3.2.2.1 IDENTIFIED WITH

 

• 사용자의 인증 방식과 비밀번호 설정
• IDENTIFIED BY 'password' 형식
• 대표적인 방식
  1. Native Pluggable Authentication: 비밀번호에 대한 해시 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 비교하는 인증 방식. MySQL 5.7까지 기본으로 사용
  2. Caching SHA-2 Pluggable Authentication: 암호화 해시값 생성을 위해 SHA-2 알고리즘 사용. 사용하려면 SSL/TLS 또는 RSA 키페어가 반드시 필요
  3. PAM Pluggable Authentication: 유닉스나 리눅스 패스워드 또는 LDAP 같은 외부 인증을 사용할 수 있게 해주는 인증 방식. MySQL 엔터프라이즈 에디션에서만 사용 가능
  4. LDAP Pluggable Authentication: LDAP을 이용한 외부 인증을 사용할 수 있게 해주는 인증 방식. MySQL 엔터프라이즈 에디션에서만 사용 가능
• 기본 인증 방식
  • MySQL 5.7 버전까지는 Native Authentication 사용
    • 간단하고 효과적인 인증 방법이지만, 보안 알고리즘의 발전과 함께 더 강력한 보안 메커니즘이 요구됨.
  • MySQL 8.0 버전부터는 Caching SHA-2 Pluggable Authentication 사용
    • SHA-256, 즉 SHA-2 알고리즘을 사용하여 비밀번호를 해싱하고, 이 해시를 사용하여 인증을 수행함.
    • 비밀번호 해시를 캐싱하여 인증 성능을 향상시킬 수 있음.
    • 보안 수준은 낮아지겠지만 기존 버전과의 호환성을 위해 Native Authentication 인증 방식으로 계정을 생성해야 할 수도 있음.

 

3.2.2.2  REQUIRE

 

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

 

3.2.2.3 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_history 시스템 변수에 저장된 개수만큼 비밀번호의 이력을 저장, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없음.
  • PASSWORD HISTORY n: 비밀번호의 이력을 최근 n개까지만 저장, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없음.

 

3.2.2.5 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 OPTIONAL: 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
  • PASSWORD REQUIRE DEFAULT: password_require_current 시스템 변수의 값으로 설정

 

3.2.2.7 ACCOUNT LOCK / UNLOCK

 

• 계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부 결정
  • ACCOUNT LOCK: 계정을 사용하지 못하게 잠금
  • ACCOUNT UNLOCK: 잠긴 계정을 다시 사용 가능 상태로 잠금 해제

 

위 옵션들을 사용하면 아래와 같이 newuser 사용자를 생성하는 SQL문을 작성할 수 있을 것 같습니다.

(예시)

CREATE USER 'newuser'@'localhost'
IDENTIFIED BY 'password'
--- 사용자가 서버에 접속할 때 SSL을 통한 암호화된 연결만 사용하도록 요구함.
REQUIRE SSL
--- 비밀번호가 90일 후에 만료되도록 설정함.
PASSWORD EXPIRE INTERVAL 90 DAY
--- 최근 사용된 3개의 비밀번호를 저장하고, 이 비밀번호들은 재사용할 수 없음.
PASSWORD HISTORY 3
--- 사용자가 비밀번호 변경 후 180일이 지나야 이전에 사용했던 비밀번호를 재사용할 수 있음.
PASSWORD REUSE INTERVAL 180 DAY
--- 계정을 생성하면서 바로 잠금.
ACCOUNT LOCK;

3.3 비밀번호 관리

---

3.3.1 고수준 비밀번호

• MySQL 서버의 비밀번호는 비밀번호를 유추할 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능 존재
• 비밀번호 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용
• 비밀번호 정책(기본값은 MEDIUM)
  • LOW: 비밀번호의 길이만 검증
  • MEDIUM: 비밀번호의 길이 및 숫자와 대소문자, 그리고 특수문자의 배합을 검증
  • STRONG: MEDIUM 레벨의 검증을 모두 수행, 금칙어가 포함됐는지 여부 검증

3.3.2 이중 비밀번호

• 데이터베이스 계정의 비밀번호는 서비스가 실행 중인 상태에서 변경 불가능
• MySQL 8.0 버전부터 계정의 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능(이중 비밀번호, Dual Password) 추가
• 최근에 설정된 비밀번호(프라이머리, Primary)와 이전 비밀번호(세컨더리, Secondary)로 구분
• 사용하려면 기본 비밀번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션 추가

3.4 권한(Privilege)

---

• MySQL 5.7 버전까지
  • 글로벌 권한: 데이터베이스나 테이블 이외의 객체에 적용되는 권한
    • GRANT 명령에 객체 명시하지 말아야 함.
    • 특정 DB나 테이블에 부여될 수 없기 때문에 GRANT 명령의 ON절에는 항상 *.*를 사용함.
  • 객체 권한: 데이터베이스나 테이블을 제어하는 데 필요한 권한
    • GRANT 명령 객체 명시해야 함.
    • 특정 칼럼에만 권한을 부여하는 경우에 부여할 수 있는 권한은 DELETE를 제외한 INSERT, UPDATE, SELECT이며, 각 권한 뒤에 칼럼을 명시하는 형식임.
  • ALL(ALL PRIVILEGES): 글로벌과 객체 권한 두 가지 용도로 사용될 수 있음.
    • 특정 객체에 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여함.
    • 글로벌로 사용되면 글로벌 수준에서 가능한 모든 권한을 부여함.
• MySQL 8.0 버전부터
  • 정적 권한: MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한
  • 동적 권한: MySQL 서버가 시작되면서 동적으로 생성하는 권한
    • 예) 컴포넌트나 플러그인이 설치되면 등록되는 권한

3.5 역할(Role)

---

• MySQL 8.0 버전부터 권한을 묶어서 역할을 사용할 수 있음.
• CREATE ROLE 명령과 GRANT ROLE 명령을 사용하여 역할을 생성하고, 사용자에게 할당할 수 있음.
• SET ROLE 명령으로 계정이 역할을 사용할 수 있게 하려면 해당 역할을 활성화함.
• 사용자가 MySQL 서버에 로그인할 때 역할을 자동으로 활성화할지 여부를 activate_all_roles_on_login 시스템 변수로 설정할 수 있음.

SET GLOBAL activate_all_roles_on_login = ON; --- 로그인과 동시에 부여된 역할이 자동으로 활성화

• 하나의 계정에 다른 계정의 권한을 병합하기 때문에 MySQL 서버는 역할과 계정을 구분할 필요가 없음.
• MySQL 서버에서 CREATE ROLE 명령과 CREATE USER 명령을 구분하는 이유는 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용하게 하기 위함.

참고자료

1) 백은빈, 이성욱. Real MySQL 8.0 (1권). 위키북스, 2021.

2) MySQL DOCUMENTATION, https://dev.mysql.com/doc/refman/8.4/en/stored-objects-security.html#stored-objects-security-definer

3) 위키백과, https://ko.wikipedia.org/wiki/%EC%A0%80%EC%9E%A5_%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.