책너두 (Real MySQL 8.0 1권) 4일차 (~64p)

요약

• 사용자 식별에 아이디 뿐만 아니라 호스트도 함께 명시해야 함.
• 여러 시스템 내장 계정의 존재를 알게 됨.
• 유저 생성시 여러 옵션으로 권한, 정책들을 사용할 수 있음을 알게 됨.
• 이중 비밀번호를 통해 응용 서비스의 다운 타임 없이 비밀번호를 새롭게 변경할 수 있는 방식을 알게됨.

발췌

패스

메모

사용자 식별

• MySQL 사용자 계정을 생성 하는 방법 & 각 계정 권한 설정 방법은 다른 DBMS 와 조금 차이가 있음.
  • MySQL 사용자 계정은 사용자의 아이디 뿐만 아니라 해당 사용자의 접속 지점 (클라이언트가 실행된 호트스명 or 도메인 or IP 주소)도 계정의 일부가 된다.
  • 항상 아이디와 호스트를 함께 명시해야 함.

'svc_id'@'127.0.0.1'

• MySQL 서버가 기동중인 로컬 호스트에서 svc_id 로 접속할때 사용할 수 있는 계정임.
• 위 계정만 존재한다면 svc_id 계정으로 다른 컴퓨터에서 접근할 수 없음.
• 만약 외부 컴퓨터에서 접속 가능한 사용자 계정을 생성하고 싶다면 호스트 부분을 %문자로 대체한다.

'svc_id'@'192.168.0.10', 비밀번호 123

'svc_id'@'%' , 비밀번호 abc

• 만약 아이디는 같은데 위와 같이 호스트 부분이 다르다면 MySQL 에서는 범위가 가장 작은것을 항상 먼저 선택한다.
• 즉, 'svc_id'@'192.168.0.10' 를 이용한 권한, 인증을 먼저 수행한다.
• 만약 192.168.0.10 인 PC 에서 svc_id 로 로그인을 시도한다면 'svc_id'@'192.168.0.10' 에서 인증을 실행하게 된다.
• 만약 192.168.0.10 이 아닌 PC 에서 svc_id 로 로그인을 시도한다면 'svc_id'@'%' 에서 인증을 실행하게 된다.

시스템 계정과 일반 계정

• MySQL 8.0 부터는 SYSTEM_USER 권한을 가지는지 여부에 따라 계정을 다음과 같이 구분한다.
  • 시스템 계정 (System Account)
    • MySQL 서버 내부적으로 실행되는 백그라운드 스레드와 무관함.
    • 일반 계정과 같이 사용자를 위한 계정임.
    • 시스템 계정은 데이터베이스 서버 관리자를 위한 계정임.
    • 시스템 계정과 일반 계정을 관리(생성 삭제 & 변경) 할 수 있다.
    • 아래와 같은 데이터베이스 서버 관리와 중요 작업은 시스템 계정으로만 수행할 수 있음.
      • 계정 관리
      • 다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제 종료
      • 스토어드 프로그램 생성 시 DEFINER 를 타 사용자로 설정
  • 일반 계정 (Regular Account)
    • 일반 계정은 응용 프로그램이나 개발자를 위한 계정임.
    • 시스템 계정을 관리할 수 없음.
• 사용자와 계정이라는 단어를 다음과 같이 구분하여 생각하자.
  • 사용자 : MySQL 서버를 사용하는 주체 (사람 또는 응용 프로그램)
  • 계정 : MySQL 서버에 로그인하기 위한 식별자 (로그인 아이디)
• MySQL 서버에는 다음과 같이 내장된 계정들이 있음.
• ‘root’@’localhost’ 를 제외한 다음 3개의 계정은 내부적으로 각기 다른 목적으로 사용됨. → 삭제 되지 않도록 주의 필요
  • ‘mysql.sys’@’localhost’ : 8.0 부터 기본으로 내장된 sys 스키마의 객체 (뷰, 함수, 프로시저)들의 DEFINER 로 사용되는 계정
  • ‘mysql.session’@’localhost’ : MySQL 플러그인이 서버로 접근할 때 사용되는 계정
  • ‘mysql.infoschema’@’localhost’ : information_scbhema 에 정의된 뷰의 DEFINER 로 사용되는 계정
• 위 3개의 계정은 처음부터 잠겨 있는 상태임 (account_locked 컬럼). 의도적으로 잠긴 계정을 풀지 않는 이상, 악의적인 용도로 사용할 수 없음 → 보안 걱정 X

계정 생성

• 5.7 부터는 GRANT 명령으로 권한 부여와 동시에 계정 생성이 가능했음.
• 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;

IDENTIFIED WITH

• 사용자의 인증 방식과 비밀번호를 설정한다.
• IDENTIFIED WITH 뒤에 반드시 인증 방식 (인증 플러그인의 이름) 을 명시 해야함.
• MySQL 서버 기본 인증 방식을 사용한다면 IDENTIFIED BY 'password' 형식으로 명시해야함.
• MySQL 서버에서는 다양한 인증 방식을 플러그인 형태로 제공. 아래 4가지 방식이 가장 대표적임
  • Native Pluggable Authentication
    • 단순히 비밀번호에 대한 해시 (SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 비교하는 인증 방식
  • Caching SHA-2 Pluggable Authentication
    • 암호화 해시값 생성에 SHA-2(256비트) 알고리즘을 사용함.
    • 보안에 더 중점을 둔 알고리즘임.
    • 해당 해시값 계산 방식은 시간소모가 있어 성능이 매우 떨어짐
    • 이를 보완하기 위해 MySQL 서버는 해시 결과를 메모리에 캐시함.
    • 이 인증 방식은 SSL/TLS 또는 RSA 키페어를 반드시 사용해야함.
      • 클라이언트에서 접속할 때 SSL 옵션을 활성화 해야 함.
  • PAM Pluggable Authentication
    • 유닉스나 리눅스 패스워드, 또는 LDAP (Lightweight Directory Access Protocol) 같은 외부 인증을 사용할 수 있게 하는 인증 방식임.
    • 엔터프라이즈 에디션에서만 사용 가능
  • LDAP Pluggable Authentication
    • LDAP 를 이용한 외부 인증을 사용할 수 있게 하는 인증 방식임.
    • 엔터프라이즈 에디션에서만 사용 가능
• 5.7 버전까지는 Native Authentication 이 기본 인증 방식임
• 8.0 부터는 Caching SHA-2 Authentication 이 기본 인증 방식으로 바뀜
  • 8.0 에서 Native Authentication 을 기본 인증 방식으로 설정하고자 한다면 다음과 같이 MySQL 설정을 변경하거나, my.cnf 설정 파일에 추가하면 됨

  mysql> SET GLOBAL default_authentication_plugin="mysql_native_password"

• Caching SHA-2 Authentication 는 SCRAM (Salted Challenge Response Authentication Mechanism) 인증방식을 사용함.
  • 평문 비밀번호를 이용해 5000번 이상 암호화 해시 함수를 실행해야 MySQL 서버로 로그인 요청을 보냄
    • 5000 은 최소 설정 값임. 더 늘릴 수 있음.
  • 무작위로 비밀번호 입력하는 무차별 대입 공격 (brute-force attack) 을 어렵게 만듦.
  • 하지만 유저 혹은 응용프로그램 연결을 느리게 만듦.
  • 응용 프로그램에서 많은 커넥션을 연결할 경우 응용 프로그램 서버의 CPU 자원을 많이 소모하게 될 수 있음.

REQUIRE

• MySQL 서버에 접속할 때, 암호화된 SSL/TLS 채널을 사용할지 여부를 설정함.
• 별도 설정이 없으면 비암호화 채널로 연결
• SSL 로 설정하지 않아도 Caching SHA-2 Authentication 인증 방식을 사용하면 암호화된 채널만으로 MYSQL 서버에 접속 가능

PASSWORD EXPIRE

• 비밀번호 유효 기간 설정 옵션
• 별도 명시 없으면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정됨.
• 개발자 or 데이터베이스 관리자 비밀번호는 유효기간 설정하는게 보안상 안전
• 응용 프로그램 접속용 계정에 유효기간을 설정하는것은 위험할 수 있으니 주의
• 해당 명령 설정 옵션은 다음과 같다.
  • PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호 만료 처리
  • PASSWORD EXPIRE NEVER : 계정 비밀번호의 만료 기간 없음
  • PASSWORD EXPIRE DEFAULT : default_password_lifetime 시스템 변수에 저장된 기간으로 비밀번호 유효기간 설정
  • PASSWORD EXPIRE INTERVAL n DAY : 비밀번호의 유효 기간을 오늘부터 n 일자로 설정

PASSWORD HISTORY

• 한 번 사용했던 비밀번호를 재사용 하지 못하게 설정하는 옵션임.
• 옵션은 다음과 같다.
  • PASSWORD HISTORY DEFAULT : password_history 시스템 변수에 저장된 개수만큼 비밀번호 이력을 저장함. 저장된 이력에 남아있는 비밀번호는 재사용 불가
  • PASSWORD HISTORY n : 비밀번호의 이력을 최근 n 개까지만 저장함. 저장된 이력에 남아있는 비밀번호는 재사용 불가
• MySQL 서버는 password_history 테이블을 통해 이전에 사용했던 비밀번호를 기억하여 한 번 사용했던 비밀번호를 사용하지 못하게 함.

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

• 계정 생성 or ALTER USER 명령을 사용해 계쩡 정보 변경 시 계정을 사용하지 못하게 잠글지 여부를 결정
• 옵션은 다음과 같다.
  • ACCOUNT LOCK : 계정을 사용하지 못하게 잠금
  • ACCOUNT UNLOCK : 잠긴 계정을 다시 사용 가능 상태로 잠금 해제

---

고수준 비밀번호

• 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능
• 비밀번호 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용해야함 → 설치해야 함.
  • validate_password 컴포넌트는 MySQL 서버 프로그램에 내장됨. → INSTALL COMPONENT 명령의 file:// 부분에 별도 파일 지정하지 않아도 됨.

  ## validate_password 컴포넌트 설치
  mysql> INSTALL COMPONENT 'file://component_validate_password';
  
  ## 설치된 컴포넌트 확인
  mysql> SELECT * FROM mysql.component;
  
  ## 컴포넌트에서 제공하는 시스템 변수 확인
  mysql> SHOW GLOBAL VARIABELS 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 → 시스템 변수에 등록된 단어로 금칙어 판단 검증
• 금칙어 파일은 금칙어들을 한 줄에 하나씩 기록해서 저장한 텍스트 파일로 작성하고 등록해주면 됨.

이중 비밀번호

• 많은 응용 프로그램이 공용으로 데이터베이스 서버를 사용하기 때문에 계정 정보는 응용 프로그램 서버로부터 공용으로 사용되는 경우가 많음.
  • 이 때문에 계정 정보를 쉽게 변경하기 어려움. 특히, 계정 비밀번호는 서비스가 실행 중인 상태에서 변경이 불가능 했음. → 처음 설정한 비밀번호로 몇 년동안 사용되는 경우가 많았음.
  • 보안을 위해 비밀번호를 주기적으로 바꿔야 했는데, 지금까지는 서비스를 모두 멈추지 않고서는 비밀번호를 변경하는 것은 불가능 했음.
• 8.0 버전 부터는 계정의 비밀번호를 2개의 값을 동시에 사용할 수 있는 기능을 추가함.
• 이 기능을 이중 비밀번호(Dual Passowrd 라고 소개함
  • 2개의 비밀번호 둥 하나만 일치하면 로그인이 통과됨.
• Primary 와 Secondary 로 구분됨.
  • Primary : 최근에 설정된 비밀번호
  • Secondary : 이전 비밀번호

## 비밀번호를 'old_password' 로 설정
## 프라이머리 -> 'old_password', 세컨더리 -> 빈값
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';

## 비밀번호를 'new_password' 로 변경하면서 기존 비밀번호('old_password') 를 세컨더리 비밀번호로 설정
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

• 위 방식으로 이중 비밀번호를 사용가능
• 이중 비밀번호 셋팅 후 응용 프로그램의 설정 파일의 비밀번호를 새로운 비밀번호인 ‘new_password’ 로 바꾸고 배포 한다.
• 비밀번호 변경 작업이 마무리 되면 세컨더리 비밀번호를 삭제한다.
  • 계정을 보안을 위해 비밀번호를 삭제하는게 좋음.

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