[Real MySQL 8.0 (1권)] 3장 - 사용자 및 권한

이 글은 'Real MySQL - 백은빈,이성욱'을 공부하며 작성한 글로 생략된 내용은 책을 구매해서 확인하는 것을 권장합니다.

https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=278488709

Real MySQL 8.0 1권

 

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

3.1 사용자 식별

MySQL의 사용자는 사용자의 계정 뿐만 아니라 사용자의 접속 지점(클라이언트가 실행된 호스트명, 도메인, IP 주소)도 계정의 일부

-> MySQL에서 계정 언급 시 항상 아이디와 호스트를 함께 명시

# 로컬호스트에서만 접속 가능한 계정
'svv_id'@'127.0.0.1'

# 모든 외부에서 접속 가능한 계정
'svc_id'@'%'

다음과 같은 2개의 사용자가 있을 때, IP 주소가 192.168.0.10인 PC에서 이 MySQL 서버 접속 시 어떤 계정으로 접속할 까?

'svc_id'@'192.168.0.10' (이 계정의 비밀번호는 123)
'svc_id'@'%' (이 계정의 비밀번호는 456)

권한이나 계정 정보에 대해 MySQL은 범위가 가장 작은 것을 항상 먼저 선택한다 -> 'svc_id'라는 아이디와 456이라는 비밀번호를 로그인 시도 시 실패

 

 

3.2 사용자 계정 관리

3.2.1 시스템 계정과 일반 계정

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

• 시스템 계정 : 데이터베이스 서버 관리자를 위한 계정
• 일반 계정 : 응용 프로그램이나 개발자를 위한 계정

시스템 계정은 시스템 및 일반 계정 관리, 다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제 종료, 스토어드 프로그램 생성(프로시저, 함수, 트리거 등) 시 DEFINER를 타 사용자로 설정이 가능하다.

 

'root'@'localhost'를 제외하고 MySQL 서버에 내장된 기본 계정

- 해당 계정들은 처음부터 잠겨(account_locked 칼럼)있는 상태이며, 내부에서 각기 다른 목적으로 사용되므로 삭제하지 않게 주의

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

3.2.2 계정 생성

MySQL 5.7 버전까지는 GRANT 명령으로 권한의 부여와 동시에 계정 생성 가능, MySQL 8.0 버전부터는 계정의 생성은 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행

# 일반적으로 많이 사용되는 옵션들을 같이 작성한 예시
mysql> 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	- 사용자 인증 방식과 비밀번호를 설정 - MySql 8.0 부터는 Caching SHA-2 Authentication이 기본 인증
REQUIRE	- MySql 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부 설정 - 별도로 명시하지 않았으면 비암호화 채널로 연결 - SSL 옵션으로 선택하지 않았더라도 Caching SHA-2 Authentication 인증 방식 사용 시 암호화된 채널로만 MySQL 서버에 접속 가능
PASSWORD EXPIRE	- 비밀번호 유효 기간을 설정하는 옵션 - 별도로 명시하지 않았으면 default_password_lifetime 시스템 변수에 저장된 기간으로 설정
PASSWORD HISTORY	- 한번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션
PASSWORD REUSE INTERVAL	- 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션 - 별도로 명시하지 않았으면 password_reuse_interval  시스템 변수의 값으로 설정
PASSWORD REQUIRE	- 비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호를 입력 받을지 말지 결정하는 옵션 - 별도로 명시하지 않았으면 password_require_current 시스템 변수의 값으로 설정
ACCOUNT LOCK / UNLOCK	- 계정 생성 시 또는 ALTER USER 명령으로 계정 정보를 변경시 계정을 잠글지 말지 결정 - ACCOUNT LOCK : 계정을 사용하지 못하게 잠금 - ACCOUNT UNLOCK : 잠금 계정을 다시 사용 가능 상태로 변경

 

 

3.3 비밀번호 관리

MySQL에서는 유효기간이나 이력 관리를 통한 재사용 금지 기능 뿐만 아니라 비밀번호의 조합, 금지 글자, 금칙어 설정도 가능하다.

해당 기능 사용을 위해서는 validate_password 컴포넌트가 필요하다.

# MySQL 서버 프로그램에 내장되어 있어 file:// 부분에 별도의 파일 경로 지정 필요x
mysql> INSTALL COMPONENT 'file://component_validate_password';

 

해당 컴포넌트에서 제공하는 비밀번호 정책 3가지 (기본 값은 MEDIUM)

1. LOW : 비밀번호의 길이만 검증
2. MEDIUM : 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
3. STRING : MEDIUM + 금칙어가 포함됐는지 여부까지 검증

금칙어 설정

이미 만들어져 있는 금칙어 파일 사용을 원한다면 따로 검색해서 내려받아 사용하는 것도 추천

파일이 준비되면 아래와 같이 서버에 파일을 등록, 비밀번호 금칙어는 시스템 변수가 STRONG으로 설정된 경우에만 작동된다.

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

 

 

주기적인 비밀번호의 변경이 보안에는 좋지만 서비스를 매번 멈추고 변경할 수는 없다. MySQL 8.0부터는 이에 대한 대안으로 계정의 비밀번호를 2개의 값을 동시에 사용할 수 있는 이중 비밀번호를 추가했다.

• 2개의 비밀번호 중 하나만 일치해도 통과
• 최근에 설정한 비밀번호는 'Prinary', 이전에 설정한 비밀번호는 Secondary
• 이중 비밀번호를 사용하려면 기존 비밀번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션을 추가하면 된다

 

 

3.4 권한 (Privilege)

MySQL 5.7은 글로벌 권한과 객체 단위의 권한으로 구분된다.

• 글로벌 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한, GRANT 명령에서 특정 객체 명시하면 안됨
• 객체 권한 : 데이터베이스나 테이블을 제어하는데 필요한 권한, GRANT 명령어로 권한 부여시 특정 객체를 반드시 명시해야함
• 예외적으로 ALL, ALL PRIVILEGES은 글로벌과 객체 권한 두 가지 용도로 모두 사용 가능

MySQL 8.0 부터는 동작 권한이 추가되었다.

• 동적 권한 : MySQL 서버가 시작되면서 동적으로 생성하는 권한, 기존 권한은 정적 권한으로 MySQL 서버의 소스코드에 명시되어 있음

권한 부여 GRANT

GRANT 권한종류 ON 권한범위 TO 유저

 

권한 범위별 부여

# 글로벌 권한, 특정 DB나 테이블에 부여 불가능-> ON 절에 항상 *.* 
mysql> GRANT SUPER ON *.* TO 'user'@'localhost'; 

# DB 권한, 특정 DB 혹은 서버에 존재하는 모든 DB에 권한 부여 가능
# DB 권한만 부여하는 경우 테이블 명시 X
mysql> GRANT EVENT ON *.* TO 'user'@'localhost'; 
mysql> GRANT EVENT ON employess.* TO 'user'@'localhost'; 

# 테이블 권한
# 서버의 모든 DB에 권한 부여 가능
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user'@'localhost';
# 특정 DB에 권한 부여 가능
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.* TO 'user'@'localhost';
# 특정 테이블에 대해 권한 부여 가능
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.department TO 'user'@'localhost';

 

 

3.5 역할 (Role)

MySQL 8.0 버전부터 권한을 묶어서 역할(Role) 사용 가능해졌다.

역할 사용 방법: CREATE ROLE 명령어로 역할 생성 후 해당 역할을 유저(사용자 계정)에게 GRANT 명령어로 부여

MySQL 서버의 역할은 계정과 거의 같은 모습(내외부적으로 동일한 객체)인데, 굳이 분리한 이유?

• 데이터베이스 관리의 직무를 분리할 수 있게 하여 보안을 강화하기 위함
• ex) CREATE USER 명령에 대해서는 권한x, CREATE ROLE 명령만 실행 가능한 사용자는 역할 생성만 가능 -> 해당 객체는 로그인 용도로 사용 불가

 

 

 

참고

• https://winter1396love.tistory.com/65
• https://quddnd.tistory.com/258