Real MySQL 읽고 정리하기

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

사용자 계정은 단순히 사용자의 아이디뿐 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인한다.
또한 8.0 버전부터는 권한을 묶어서 관리하는 역할의 개념이 도입되어 사용자의 권한으로 미리 준비된 권한 세트를 부여하는것도 가능하다.

데이터베이스 서버의 보안은 갈수록 중요해지고 있으므로 계정의 식별 방식과 권한, 역할에 대한 기본적인 내용을 숙지해보도록 하자!

사용자 식별

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

• 예시 1번

'svc_id'@'127.0.0.1'

(사용자의 계정에 예시 1번과 같이 계정만 등록되어 있으면 다른 컴퓨터에서 svc_id라는 아이디로 접속하지 못한다.)

• 예시 2번

'svc_id'@'192.168.0.10' (이 계정의 비밀번호가 1234라고 가정)
'svc_id'@'%' (이 계정의 비밀번호가 abcd라고 가정)

예시 2번과 같이 두개의 사용자 계정이 있는 MySQL 서버가 있다고 가정했을 때 둘 중 어느 계정 정보를 이용해 인증을 실행할지에 대한 기준은 계정 정보 범위가 작은것을 항상 먼저 선택한다고 한다.

위 예시 2번은 계정 정보 범위가 큰 '%' ('%' 문자는 모든 IP 또는 호스트명을 의미)는 우선 순위가 되지 못해 192.168.0.10의 사용자 계정을 기준으로 로그인 인증이 실행된다.(abcd 비밀번호 입력시 실패하게 됨)

이처럼 중첩된 계정을 생성하지는 않겠지만 실수로 이런일이 자주 발생하기도 한다고 하니! 계정 생성할 때 주의해야한다.

사용자 계정 관리

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

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

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

• 시스템 계정
  • 데이터베이스 서버 관리자를 위한 계정
  • 일반 계정을 관리(생성 삭제 및 변경)할 수 있다.
  • 데이터베이스 서버 관리와 관련된 중요 작업 수행
    ex) 계정관리, 다른세션 또는 그 세션에서 실행중인 쿼리 강제 종료
• 일반 계정
  • 응용프로그램이나 개발자를 위한 계정
  • 당연하지만 시스템 계정을 관리 할 수 없다.

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

일반적으로 데이터베이스에는 계정과 사용자라는 말을 혼용하는데, 설명의 편의를 위해 '사용자'와 '계정' 이라는 단어를 다음과 같이 구분해서 사용하겠다고 한다.

• 사용자 : MySQL 서버를 사용하는 주체(사람 또는 응용 프로그램)
• 계정 : MySQL 서버에 로그인하기 위한 식별자(로그인 아이디)

MySQL 서버에는 다음과 같이 내장된 계정들이 있는데, 'root'@'locaghost'를 제외한 3개의 계정은 내부적으로 각기다른 목적으로 사용되고 있어서 삭제되지 않도록 주의해야한다고 한다!

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

위에 언급된 3개의 계정은 잠겨있어서 의도적으로 잠긴 계정을풀지 않는 한 악의적인 용도로 사용될 수 없으므로 보안 걱정은 하지않아도 된다.

2. 계정 생성

MySQL 5.7 버전까지는 GRANT 명령으로 권한의 부여와 동시에 계정생성이 가능했지만 MySQL 8.0 버전 부터는 계정의 생성은 CREATE USER 명령으로, 권한부여는 GRANT 명령으로 구분해서 실행하도록 바뀌였다.

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

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

일반적으로 많이 사용되는 옵션을 가진 CREATE USER 명령을 보도록 하자.

CREATE USER 'user'@'%'
	IDENTIFIED WITH 'mysql_native_password' BY 'password'

1. IDENTIFIED WITH

사용자의 인증 방식과 비밀번호를 설정한다.
IDENTIFIED WITH 뒤에는 반드시 인증 방식(인증 플러그인의 이름)을 명시해야 하는데, 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시 하면 된다.
다양한 인증 방식을 플로그인으로 제공하며, 대표적인 4가지 방식이 있다.

• Native Pluggable Authentication
  • 5.7 버전까지 기본으로 사용되던 방식이며 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 비교하는 인증 방식
• Caching SHA-2 Pluggable Authentication
  • 암호화 해시값 생성을 위해 SHA-2(256비트) 알고리즘을 사용해서 보안에 더 중점적으로 두어 NAtive - Pluggable Authentication 와의 보안 차이를 보여준다.
  • 내부적으로 Salt 키를 활용하여 해시를 하며, 성능 문제를 해결하기 위해 MySQL 서버에 해시 결괏값을 메모리에 캐시해서 사용한다.
  • 인증을 사용하기 위해 SSL/TSL 또는 RSA 키페어를 반드시 사용해야 하고 이를 위해 클라이언트에서 접속할 때 SSL 옵션을 활성화 해야한다.
• PAM Pluggable Authentication
  • 유닉스나 리눅스 패스워드 또는 LDAP(Lightweight Directory Access Protocol)같은 외부인증을 사용할 수 있게 해주는 인증 방식으로, MySQL 엔터프라이즈 에디션에서만 사용 가능하다.
• LDAP Pluggable Authentication
  • LDAP를 이용한 외부 인증을 사용할 수 있게 해주는 인증 방식으로, MySQL 엔터프라이즈 에디션에서만 사용 가능하다

만약 MySQL 8.0 버전에서도 Native Pluggable Authentication을 기본 인증 방식으로 설정하고 싶다면
'SET GLOBAL defalut_authentication_plugin="mysql_native_password"로 설정을 변경하거나 my.conf 설정 파일에 추가하면 된다고 한다!

2. REQUIRE NONE

MySQL 서버에 접속할 떄 암호화된 SSL/TSL 채널을 사용할지 여부를 설정한다.(별도 설정 없을 시 비암호화 채널 연결)
하지만 REQUIRE 옵션을 SSL로 설정하지 않더라도 Caching SHA-2 Authentication 인증 방식을 사용하면 암호화된 채널만으로 MySQL에 접속할 수 있게 된다.

3. PASSWORD EXPIRE

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

개발자나 데이터베이스관리자의 비밀번호는 유효기간을 설정하는 것이 보안상 안전하지만 응용 프래그램 접속용 계정에 유효 기간을 설정하는 것은 위험할 수 있으니 주의해야한다.

PASSWORD EXPIRE 절에 설정 가능한 옵션

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

4. PASSWORD HISTORY

한번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션인데,PASSWORD HISTORY 절에 설정 가능한 옵션은 다음과 같다.

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

한번 사용했던 비밀번호를 사용하지 못하게하려면 이전에 사용했던 비밀번호를 MySQL 서버가 기억해야 하고 있어야 하는데, 이를 위해 MySQL 서버는 mysql DB의 password_history 테이블을 사용한다.

아무래도 히스토리 옵션을 사용하지 않아서 기록에 없나보다!

5. PASSWORD REUSE INTERVAL

한번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정된다.

PASSWORD REUSE INTERVAL 절에 설정 가능한 옵션

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

6. PASSWORD REQUIRE

비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션이며, 별도로 명시되지 않으면 password_requre_current 시스템 변수의 값으로 설정된다.

PASSWORD REQUIRE 절에 설정 가능한 옵션

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

7. ACCOUNT LOCK / UNLOCK

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

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

비밀번호 관리

1. 고수준 비밀번호

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

유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하면 되고 다음과 같이 컴포넌트를 설치해야한다.

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

## 설치된 컴포넌트 확인

MySQL 서버 프로그램에 내장돼 있기에 별도의 파일 경로를 지정하지 않아도 된다.

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

SHOW GLOBAL VARIABLES LIKE 'validate_password%';

비밀번호 정책은 크게 3가지 중에서 선택 할 수 있다.(기본값은 MEDIUM)

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

비밀번호 길이는 validate_password.length 시스템변수에 설정된 길이 이상의 비밀번호가 사용됐는지를 검증하고, 숫자와 대소문자, 특수문자는 mixed_case_count와 number_count, special_char_count 시스템 변수에 설정된 글자 수 이상을 포함하는지 검증한다.
금칙어는 dictionary_file 시스템 변수에 설정된 사전파일에 명시된 단어를 포함하고 있는지 검증한다.

연속된 비밀번호(1234와 같은) 비밀번호를 사용해도 아무런 에러 없이 설정되는데 validate_password.dictionary_file 시스템 변수에 금칙어들이 저장된 사전 파일을 등록하면 된다.
validate_password.policy 시스템변수가 'STRONG'으로 설정된 경우에만 작동하므로 변경을 같이 해주어야한다.

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

2. 이중 비밀번호

MySQL 서버의 Dual Password 기능은 하나의 계정에 대해서 2개의 비밀번호를 동시에 설정할 수 있으며, 2개의 비밀번호는 Primary 와 Secondary 로 구분 되게 됩니다.

최근에 설정된 비밀번호는 Primary 비밀번호이며, 이전 비밀번호는 Secondary 비밀번호가 되게 됩니다.
Dual Password 를 사용하기 위해서는 아래와 같이 ALTER USER 구문에서 RETAIN CURRENT PASSWORD 옵션을 사용하면 됩니다

-- 비밀번호를 변경
mysql> ALTER USER test@'%' IDENTIFIED BY 'TestPass1234!@';
-- 비밀번호를 변경하면서 기존의 비밀번호(TestPass1234!@) 를 Secondary 비밀번호 설정
mysql> ALTER USER test@'%' IDENTIFIED BY 'Password1234!@' RETAIN CURRENT PASSWORD;

보안상으로 패스워드를 주기적으로 또는 한번은 변경을 해야할 경우 이전 버전까지는 서비스를 한번은 멈춰야 할수도 있었지만 8.0 버전 부터는 계정의 비밀번호를 2개의 값을 동시에 사용하는 기능이 추가 되었습니다.

계정을 사용하는 어플리케이션의 정보가 모두 변경 되었다면 이제 이전 비밀번호를 삭제하면 되며 DISCARD OLD PASSWORD 옵션을 사용하면 됩니다.

mysql> ALTER USER test@'%' DISCARD OLD PASSWORD;

권한(Privilege)

역할(Role)

use mysql
왜 mysql 데이터베이스를 사용하도록 해야하는지 궁금했음.
-> 사용자 계정에 관한 정보는 mysql database에 들어있기 때문에 mysql 데이터베이스에 들어가 아래 과정들을 진행합니다.
사용자 계정에 관한 정보는 mysql database에 들어가 있다고 합니다.