[Real MySQL] 3. 사용자 및 권한

사용자 식별

MySQL의 사용자 식별 방법

사용자 계정 + 접속 IP를 확인

MySQL 8.0 부터는 역할 ROLE 개념 도입

mysql에서는 사용자 계정과 접속 지점(호스트명, 도메인 또는 IP주소)도 계정의 일부가 된다.

계정을 언급할 때는 항상 아이디와 호스트를 함께 명시해야 한다.

아이디와 IP 주소를 감싸는 역따옴표(`)또는 홀 따옴표(')로 식별자를 감싸서 사용

CREATE USER 'test'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'127.0.0.1';

CREATE USER `test`@`127.0.0.1` IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO `test`@`127.0.0.1`;

test 계정은 로컬 호스트(127.0.0.1)에서만 접속 가능하다. 다른 호스트에서는 접속할 수 없다.

'test'@'127.0.0.1'

외부에서 제한 없이 접속 가능하게 하고자 한다면 % 로 명시 % 는 모든 IP 또는 모든 호스트명

'test'@'192.168.0.10'
'test'@'%'

위와 같이 2개의 계정이 있다고 가정할 때 test 계정으로 로그인을 한다면 MySQL은 구체적인 호스트 정의가 된 계정을 먼저 선택한다.

만약 192.168.0.10 이라는 IP를 가진 클라이언트가 있다고 가정했을 때

CREATE USER 'test'@'%' IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

이렇게 test 라는 계정을 생성했다면 1234 비밀번호로 로그인이 가능하다.

이후 192.168.0.10 에게 qwer 비밀번호의 test 계정을 생성했다면 어떻게 되었을까?

CREATE USER 'test'@'192.168.0.10' IDENTIFIED BY 'qwer';

1234 : 로그인 불가능
qwer : 로그인 가능

여기에 192.168.0.11 에게는 다른 비밀번호의 계정을 생성했다고 하면

CREATE USER 'test'@'192.168.0.11' IDENTIFIED BY 'asdf';

192.168.0.10 -> qwer로만 로그인 가능
192.168.0.11 -> asdf로만 로그인 가능
192.168.0.12 -> 1234로만 로그인 가능

책에서는 범위가 가장 작은 것이라고 되어 있으나 정확히는 호스트 이름 또는 IP주소가 일치하는 계정을 우선적으로 시도한다.

특정 IP 대역 (서브넷 대역)에 대해 설정도 가능하다.

CREATE USER 'test'@'192.168.0.11' IDENTIFIED BY 'password1';
CREATE USER 'test'@'192.168.0.%' IDENTIFIED BY 'password2';
CREATE USER 'test'@'192.168.1.%' IDENTIFIED BY 'password2';
CREATE USER 'test'@'192.168.%' IDENTIFIED BY 'password3';
CREATE USER 'test'@'%' IDENTIFIED BY 'password4';

사용자 계정 관리

시스템 계정과 일반 계정

MySQL 8.0의 계정은 SYSTEM_USER 권한의 할당 여부에 따라 시스템 계정과 일반 계정으로 구분된다.

시스템 계정(System Account) : DBA(데이터 베이스 관리자)를 위한 계정
관리자 계정(Regular Account) : 응용 프로그램 또는 개발자를 위한 계정, 시스템 계정 관리 불가

시스템 계정만이 데이터베이스 서버 관리와 관련된 중요 작업이 가능하다.

계정 관리(계정 생성, 삭제 및 권한 부여, 제거)
다른 세션(Connection)또는 본 세션에서 실행중인 쿼리 강제 종료
스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정

따라서 일반적으로 일반 사용자, 응용 프로그램을 위한 계정에는 SYSTEM_USER 권한이 부여되지 않게하자

내장 계정

MySQL에는 'root'@'localhost'를 제외한 내장 계정들이 존재한다.

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

위 계정들은 삭제 되면 안되기에 설치 처음부터 잠긴 상태로 생성된다.

mysql> SELECT user, host, account_locked FROM mysql.user WHERE user LIKE 'mysql.%';
+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
+------------------+-----------+----------------+

계정 생성

MySQL 5.7 까지는 GRANT 명령만으로 권한의 부여와 동시에 계정 생성이 가능하다.

그러나 MySQL 8.0 부터는 CREATE USER 명령어로 먼저 계정을 생성한 후에 GRANT 명령어를 통해 권한을 부여하도록 구분되어 실행하도록 바뀌었다.

5.7 이전에는 root 계정을 외부에서 접속 가능하도록 할 때 아래와 같이 GRANT만 이용할 수 있었으나

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

8.0부터는 먼저 외부에서 접속 가능한 root 계정을 생성한 후에 권한을 부여할 수 있다.

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

계정 생성 옵션

계정을 생성할 때는 아래의 옵션을 설정할 수 있다.

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

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

사용자 인증 방식과 비밀번호를 설정

Native Pluggable Authentication
- 5.7 까지 기본으로 사용되던 방식
- 단순 비밀번호에 대한 해시 값(SHA-1 알고리즘)을 저장하여 해시값을 비교하는 방식 (레인보우 테이블 공격에 취약)
- 8.4 부터 비활성화
Caching SHA-2 Pluggable Authentication
- SHA-2 알고리즘을 이용한 방식
- 내부적으로 Salt 키를 사용하여 동일한 키 값에도 결과가 달라짐
- SSL/TLS 또는 RSA 키페이를 사용해야 하기에 클라이언트에서 SSL 옵션을 활성화 해야 함
PAM Pluggable Authentication
- 유닉스(리눅스) 계정 또는 다른 외부 인증을 사용할 수 있게 해주는 방식
- Enterprise Edition에서만 사용 가능
LDAP Pluggable Authentication
- LDAP(Lightweight Directory Access Protocol)를 이용한 외부 인증 방식
- Enterprise Edition에서만 사용 가능

5.7 이후에는 Native Authentication이 기본 인증 방식이 아니기 때문에 이전 응용 프로그램과 충돌이 발생할 수 있다. 이를 해결하기 위해 Native 방식으로 계정을 생성하거나 비밀번호를 변경해야 할 수도 있다.

SET GLOBAL default_authentication_plugin='mysql_native_password"

CREATE USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';
CREATE USER 'test'@'localhost' IDENTIFIED WITH sha256_password BY '1234';
CREATE USER 'test'@'localhost' IDENTIFIED WITH caching_sha2_password BY '1234';

MySQL 8.4 부터는 기본적으로 mysql_native_password 플러그인이 활성화 되어있지 않다. ~.cnf 파일에 아래와 같이 추가해 주어야 한다.

[mysqld]
mysql_native_password=ON

MySQL 9.0부터는 플러그인이 제거된다고 하니 되도록이면 사용하지 않는 쪽으로 하자..

REQUIRE

암호화된 SSL/TLS 채널 사용 여부 설정

기본은 비암호화 채널

그러나 Caching SHA-2 Authentication 인증 방식이라면 암호화된 채널만으로 접속해야 함

PASSWORD EXPIRE

비밀번호 유효기간 설정

기본은 default_password_lifetime

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 개 만큼 저장하여 이력이 남아있는 비밀번호는 사용할 수 없다.

PASSWORD REUSE INTERVAL

한 번 사용한 비밀번호의 재사용 금지 기간을 설정

기본은 password_interval 시스템 변수에 저장된 기간으로 설정

PASSWORD INTERVAL DEFAULT
PASSWORD INTERVAL n DAY : n 일 이후에 비밀번호를 재사용할 수 있도록 함

PASSWORD REQUIRE

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

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

ACCOUNT LOCK/UNLOCK

계정을 사용 가능 여부를 설정

ACCOUNT LOCK : 계정을 잠금
ACCOUNT UNLOCK : 계정 잠금 해제

참고 내용

Real MySQL 8.0 1권 (백은빈, 이성욱)