백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.
1. 사용자 식별
'svc'@'127.0.0.1'
'svc'@'%'
고유 사용자 계정
- 아이디와 호스트의 결합
- 호스트
- 사용자의 접속 원점을 의미합니다.
- 사용자가 특정 서버에 접근할 수 있도록 허용된 위치
- 로그인 시에 계정의 접속지점이 호스트 정보와 불일치하다면 접속할 수 없습니다.
2. 사용자 계정 관리
시스템 계정과 일반 계정
- SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정과 일반 계정으로 구분됩니다.
시스템 계정
- mysql.admin (데이터베이스 서버 관리자를 위한 계정)
- 권한
- 계정 관리 (CREATE USER, GRANT 명령어)
- 다른 세션 강제 종료 (KILL 명령어)
- 스토어드 프로그램 생성 시, 명시적으로 타 사용자를 DEFINER로 설정 (CREATE DEFINER 명령어)
일반 계정
내장 계정
SELECT user, host, account_locked FROM mysql.user WHERE USER LIKE 'mysql.%';
mysql.sys
- sys 스키마 객체를 실행하는데 사용됨 (DEFINER)
- sys 스키마는 서버의 성능 및 관리 상태를 모니터링하는데 필요한 객체를 보유함
- 뷰: sys.processlist, sys.host_summary
- 프로시저: sys.ps_truncate_all_tables
mysql.session
- 플러그인이 MySQL 서버와 통신할 때 사용되는 계정
- 플러그인들은 MySQL 서버 내부에서 동작하는데, 그 과정에서 서버 내부로 접근해야 할 경우가 생김
- 이 때, mysql.session 계정이 내부적으로 사용됨
- 플러그인들은 데이터베이스의 기능을 확장하는 추가 모듈 (복제, 인증, 로그 수집 등)
mysql.infoschema
- information_schema의 뷰 실행을 위한 계정 (DEFINER)
- information_schema는 서버의 메타데이터 정보를 제공하는 데이터베이스 (테이블, 컬럼, 인덱스 등)
계정 생성
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: 비밀번호에 대해 해시값을 저장하고 인풋값이 일치하는지 비교하는 방식
- Caching SHA-2 Pluggable Authentication: 암호화 해시값 생성을 위해 SHA-2 알고리즘을 사용합니다.(ssl or RSA 필수)
- PAM Pluggable Authentication: 외부 인증을 사용할 수 있게 해주는 인증방식
- LDAP Pluggable Authentication: LDAP 인증을 사용할 수 있게 해주는 인증방식
REQUIRE
- MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할 지 여부를 설정한다
PASSWORD EXPIRE
- 비밀번호의 유효기간을 설정하는 옵션
- 별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 설정됩니다.
- INTERVAL n DAY (유효기간)
PASSWORD HISTORY
- 한번 사용했던 비밀번호를 재사용하지 못하게 하는 옵션
- password_history 테이블에 password_history 시스템 변수 갯수만큼 패스워드 변경 이력을 저장합니다.
PASSWORD REUSE INTERVAL
- 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션
- password_reuse_interval 시스템 변수 기간 만큼 패스워드 재사용이 금지됩니다.
PASSWORD REQUIRE
- 비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호를 입력해야 할지 말지를 결정하는 옵션
- CURRENT: 현재 비밀번호 입력 필요 O
- OPTIONAL: 현재 비밀번호 입력 필요 X
- DEFAULT: password_require_current 시스템 변수의 값으로 설정
ACCOUNT LOCK / UNLOCK
3. 비밀번호 관리
고수준 비밀번호
- 비밀번호의 유효기간이나 재사용 금지 뿐만 아니라 글자의 조합이나 금칙어를 설정하는 기능도 있습니다.
- 이를 위해 validate_password 컴포넌트를 사용합니다.
정책
- LOW: 길이
- MEDIUM: 길이, 숫자, 대소문자, 특수문자
- STRONG: 길이, 숫자, 대소문자, 특수문자, 금칙어
validate_password 시스템 변수
- validate_password.check_user_name
- validate_password.length
- validate_password.mixed_case_count: 포함되어야 할 최소한의 구분된 대소문자 수
- validate_password.number_count
- 'validate_password.special_char_count'
- validate_password.policy
- validate_password.dictionary_file: 금칙어가 작성된 파일 경로
이중 비밀번호
- 두 개의 비밀번호를 설정할 수 있으며, 하나만 맞더라도 로그인이 성공합니다.
Primary, Secondary 설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_pwd';
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_pwd' RETAIN CURRENT PASSWORD;
Secondary 삭제
ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;
4. 권한
글로벌 권한과 객체 권한
글로벌 권한
- MySQL 서버의 전반적인 관리와 관련된 권한 (데이터베이스나 테이블은 제외됨)
- ex) SUPER, SHUTDOWN, PROCESS, RELOAD 권한
객체 권한
- 데이터베이스나 테이블을 제어하는데 필요한 권한 입니다.
- 반드시 특정 객체를 명시해야 합니다.
- ex) PRIVILEGES, SELECT, INSERT, PROCEDURE
동적 권한
- 서버가 기동될 때, 동적으로 생성되는 권한
- 백업 관리자와 복제 관리자가 꼭 필요한 권한만 부여할 수 있습니다.
명령어
- 권한 부여: GRANT
- 권한 확인: SHOW GRANTS FOR
테이블
- 권한 저장: mysql.global_priv 테이블
GRANT 문
GRANT privilege_list ON db.table TO 'user'@'host';
GRANT SUPER ON *.* TO 'user'@'localhost';
GRANT EVENT ON *.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';
- 권한을 부여하는 명령어 입니다.
- 각 권한의 특성에 따라 ON 절에 명시되는 오브젝트의 내용이 바뀌어야 합니다.
5. 역할
특징
생성 과정
정의
CREATE ROLE role_emp_read, role_emp_write;
권한 부여
GRANT SELECT ON employees.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;
계정에 역할 부여
GRANT role_emp_read TO 'reader'@'localhost';
GRANT role_emp_read, role_emp_write TO 'writer'@'localhost';
역할 활성화
SET ROLE 'role_emp_read';
- activate_all_roles_on_login 시스템 변수: 로그인과 동시에 부여된 역할이 자동으로 활성화