USER 생성 및 권한 부여 옵션 정리

USER 생성

예시)

mysql> CREATE USER 'test'@'%'
IDENTIFIED WITH 'mysql_native_password' BY 'Password'
REQUIRE NONE
PASSWORD EXPIRE INTERVAL 30 DAY
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT
ACCOUNT UNLOCK;

참고)
MySQL 8.0 부터는 GRANT 전에 USER가 먼저 생성되어 있어야 합니다!
GRANT 문을 먼저 수행하면 오류가 발생하게 됩니다.

1. IDENTIFIED WITH (인증 방식 및 비밀번호 지정)

사용자 인증 방식과 비밀번호를 지정 하는 구문
IDENTIFIED WITH 뒤에는 인증 방식(인증 플러그인의 이름)을 명시
MySQL 서버에서 설정된 기본(DEFAULT) 인증 방식을 사용하고자 한다면 WITH 없이 IDENTIFIED BY '패스워드' 로 바로 생성

• Native Pluggable Authentication

  • MySQL 5.7 버전까지 기본으로 사용하던 방식
  • 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는 지를 비교 하는 인증 방식
  • 입력시 동일한 해시값을 출력

• Caching SHA-2 Pluggable Authentication

  • MySQL 8.0 버전부터 기본으로 사용하는 방식
  • 암호화 해시값을 생성을 위해서 SHA-2(256비트) 알고리즘을 사용
  • 내부적으로 Salt 키를 활용하며, 수천 번의 해시 계산을 수행해서 결과를 만들어 내기 때문에 동일한 키 값에 대해서 결과가 다름
  • 해시값을 계산하는 방식의 성능을 보완하기 위해 해시 결과값을 메모리에 캐시하여 사용
  • SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에 이전과는 다른 방식으로 접속 필요

보안 수준이 좀 낮아지더라도 기존 버전의 호환성을 고려 하여 Native Authentication 로 계정을 생성 하여 사용할 수 있거나 기본 설정을 Native Authentication 으로 설정하여 사용 가능

mysql> SET GLOBAL default_authentication_plugin="mysql_native_password";

2. REQUIRE (암호화 채널 사용 여부)

MySQL 서버에 접속 할 때 암호화된 SSL/TSL 채널을 사용할지 여부를 설정 하는 항목으로 별도로 설정하지 않으면 비암호화 채널로 연결 됨
하지만 REQUIRE 옵션을 SSL 로 설정 하지 않아도 Caching SHA-2 Authentication 인증 방식으로 계정을 사용중이라면 암호화된 채널만 MySQL 서버에 접속 할 수 있게 됨

REQUIRE NONE 설정 시 비암호화 채널로 연결

3. PASSWORD EXPIRE (만료일 지정)

비밀번호의 유효기간을 설정
계정 생성 시 명시 하지 않으면 default_password_lifetime 시스템 변수에 설정된 기간으로 설정됨
기본값은 0 ➡️ 자동 만료 기능 비활성화(일수 제한 없음)

• PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호의 만료 처리
• PASSWORD EXPIRE NEVER : 계정 비밀번호의 만료 기간 없음
• PASSWORD EXPIRE DEFAULT : default_password_lifetime 시스템 변수를 따름
• PASSWORD EXPIRE INTERVAL n DAY : 비밀번호 유효 기간을 설정

패스워드 만료 시 로그인은 가능하나 쿼리 수행 시 에러 메세지 출력
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

4. PASSWORD HISTORY (비밀번호 재사용 설정) (8.0 기능)

한번 사용했던 비밀번호를 재사용하지 못하게 설정

• PASSOWRD HISTORY DEFAULT
  • password_history 시스템 변수에 저장된 개수 만큼 비밀번호의 이력을 저장하고 저장된 이력의 비밀번호는 재사용할 수 없음
  • password_history 의 기본값은 0 으로 패스워드 재사용 제한 없음(히스토리 저장 X)
• PASSWORD HISTORY n
  • 비밀번호 저장 이력을 최근 n 개 까지로 설정

저장된 패스워드는 mysql.password_history 딕셔너리에서 확인 가능

5. PASSWORD REUSE INTERVAL (비밀번호 재사용 기간 설정) (8.0 기능)

한 번 사용했던 비밀번호의 재사용 금지 기간을 설정
별도로 명시 하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정 (기본값 0 ➡️ 제한 없음)

• PASSWORD REUSE INTERVAL DEFAULT : password_reuse_interval 변수의 값을 따름
• PASSWORD REUSE INTERVAL n DAY : n 일자 이후 비밀번호를 재사용 할 수 있게 설정

6. PASSWORD REQUIRE (8.0.13 기능)

비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지말지를 결정
별도로 명시 하지 않으면 password_require_current 시스템 변수의 값으로 설정 (기본값 OFF)

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

7. ACCOUNT LOCK / UNLOCK (계정 잠금)

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

권한 부여

예시)

> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, EXECUTE on *.* to 'repl'@'ip_address';
> GRANT PROCESS, SUPER, EXECUTE, REPLICATION CLIENT ON *.* TO 'mmm'@'ip_address';

권한 목록

* MySQL Document 참고) https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

Privilege	Grant Table Column	Context
ALL [ PRIVILEGES ]	all privileges	모든 권한 (GRANT OPTION 제외)
ALTER	Alter_priv	테이블 스키마 변경
ALTER ROUTINE	Alter_routine_priv	스토어드 프로시저 변경
CREATE	Create_priv	데이터베이스, 테이블, 인덱스 생성
CREATE ROLE	Create_role_priv	ROLE 생성
CREATE ROUTINE	Create_routine_priv	스토어드 프로시저 생성
CREATE TABLESPACE	Create_tablespace_priv	테이블 스페이스 생성
CREATE TEMPORARY TABLES	Create_tmp_table_priv	임시 테이블 생성
CREATE USER	Create_user_priv	유저 생성
CREATE VIEW	Create_view_priv	뷰 생성
DELETE	Delete_priv	레코드 삭제
DROP	Drop_priv	테이블 삭제
DROP ROLE	Drop_role_priv	ROLE 삭제
EVENT	Event_priv	이벤트 생성 및 변경
EXECUTE	Execute_priv	프로시저 실행
FILE	File_priv	서버 호스트에서 파일 접근
GRANT OPTION	Grant_priv	권한 부여 옵션
INDEX	Index_priv	인덱스 생성/삭제
INSERT	Insert_priv	레코드 INSERT
LOCK TABLES	Lock_tables_priv	테이블 잠금
PROCESS	Process_priv	프로세스 목록, 프로세스 실행 쿼리 조회
PROXY	proxies_priv	프록시 사용자 설정
REFERENCES	References_priv	FK 설정
RELOAD	Reload_priv	로그, 권한, 테이블 정보에 대한 FLUSH
REPLICATION CLIENT	Repl_client_priv	SHOW [ MASTER | SLAVE ] STATUS
REPLICATION SLAVE	Repl_slave_priv	Slave 서버에서 Master 서버 접속
SELECT	Select_priv	레코드 SELECT
SHOW DATABASES	Show_db_priv	DATABASES 목록 조회
SHOW VIEW	Show_view_priv	VIEW의 생성 스크립트 조회
SHUTDOWN	Shutdown_priv	MySQL 서버 종료
SUPER	Super_priv	특정 상황에서 제한을 넘어 작업 가능
TRIGGER	Trigger_priv	트리거 생성/삭제
UPDATE	Update_priv	레코드 UPDATE
USAGE	no privileges	아무 권한 없는 사용자 생성

SUPER

리눅스 Root 사용자와 같은 것은 아님
특정 상황에서 제한을 넘어서 어떠한 작업을 할 수 있는 권한

• read_only 설정된 MySQL 서버에서 데이터 변경
• max_connections 제한까지 모두 커넥션이 생성된 상황에서 SUPER 권한 사용자에 대해 단 1개의 커넥션 추가 생성 가능
• MySQL 5.0(TRIGGER 권한이 없는 버전)에서 트리거 생성 및 삭제 시 필요
• 특이산 상황에서 필요한 권한은 모두 할당되어 있는 느낌
• 일반 사용자나 서비스 계정에 부여하지 않는 것을 권장

PROCESS

• 서버 내에서 실행중인 스레드에 대한 정보 (세션에서 실행 중인 명령문에 대한 정보) 액세스
• SHOW PROCESSLIST 명령어, mysqladmin processlist 명령어, information_schema.PROCESSLIST 테이블, performance_schema.processlist 테이블 사용 가능
• PROCESS 권한이 있는 경우 모든 스레드의 정보에 액세스 가능
• PROCESS 권한이 없는 비익명 사용자의 경우 자신의 스레드 정보에만 액세스 가능
• PROCESS 권한이 없는 익명 사용자의 경우 자신의 스레드 정보에도 액세스 불가능