[Real MySQL 8.0] 사용자 및 권한

mysql은 사용자의 접속 지점 (호스트 명 or ip 주소)도 계정의 일부가 됩니다.

%는 모든 IP 또는 모든 호스트명을 의미합니다.

 

아래 두 계정은 서로 다른 비밀번호를 가지고 있는 다른 계정입니다.

'svc_id'@'%'

'svc_id'@'127.0.0.1'

 

만약 127.0.0.1 로 접속을 한다면 mysql은 아래 계정을 선택합니다.

권한이나 계정 정보에 대해 mysql은 범위가 가장 작은 것을 항상 먼저 선택하기 때문입니다.

그래서 이 경우에 '%'에 대한 계정의 비밀번호를 입력하면 비밀번호가 일치하지 않습니다.

 

계정 종류

시스템 계정: SYSTEM_USER 권한을 가짐, db 서버 관리자

- 계정 관리

- 다른 세션 또는 그 세션에서 실행 중인 쿼리 강제 종료

- 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 지정

일반 계정: SYSTEM_USER 권한을 갖지 않음, 개발자

 

mysql 내장 계정 (default 잠겨있음)

'mysql.sys'@'localhost': mysql 8.0부터 기본적으로 내장된 sys 스키마의 객체(뷰, 함수, 프로시저)들의 DEFINER로 사용되는 계정

'mysql.session'@'localhost': mysql 플러그인이 서버로 접근할 때 사용되는 계정

'mysql.infoschema'@'localhost': information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

 

CREATE USER

IDENTIFIED WITH (인증방식)

- Native Pluggable Authentication: SHA-1 알고리즘으로 비밀번호 암호화 8.0 이전까지 기본

- Caching SHA-2 Pluggable Authentication: SHA-2 암호화 및 해시 결과값을 메모리에 캐시해서 사용, SSL/TSL 혹은 RSA 사용 필수, 8.0부터 기본, 성능 안좋음

- PAM Pluggable Authentication: 유닉스나 리눅스 패스워드, LDAP (Lightweight Directory Access Protocol) 사용 가능, mysql 엔터프라이즈에서만 사용 가능

- LDAP Pluggable Authentication: LDAP 사용 가능, mysql 엔터프라이즈에서만 사용 가능

 

PASSWORD EXPIRE (비밀번호 유효기간)

별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 설정됩니다.

- PASSWORD EXPIRE: 계정 생성과 동시에 비밀번호의 만료 처리

- PASSWORD EXPIRE NEVER: 계정 비밀번호의 만료기간 없음

- PASSWORD EXPIRE DEFAUT: default_password_lifetime 시스템 변수에 저장된 기간으로 비밀번호의 유효기간 설정

- PASSWORD EXPIRE INTERVAL n DAY: 비밀번호의 유효 기간을 오늘부터 n일자로 설정

 

PASSWORD HISTORY (비밀번호 사용 가능 횟수)

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

- PASSWORD HISTORY DEFAULT: password_history 시스템 변쉥 저장된 개수만큼 비밀번호 이력 저장, 이력에 남아있는 비밀번호는 재사용할 수 없음

- PASSWORD HISTORY n: 비밀번호를 최근 n개까지 저장하며 이력에 남아있는 비밀번호는 재사용할 수 없음

 

password_history 테이블

SELECT * FROM mysql.password_history;

 

PASSWORD REUSE INTERVAL (사용한 비밀번호 재사용 금지 기간)

별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정

- PASSWORD REUSE INTERVAL DEFAULT: password_reuse_interval 변수에 저장된 기간으로 설정

- PASSWORD REUSE 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: 잠긴 계정을 다시 사용 가능 상태로 잠금 해제

 

비밀번호 유효성 검사 정책

mysql 서버 프로그램에 내장되어있는 validate_password 컴포넌트를 설치합니다.

 

INSTALL COMPONENT 'file://component_validate_password';

SELECT * FROM mysql.component;
SHOW GLOBAL VARIABLES LIKE 'validate_password%';

SHOW GLOBAL VARIABLES LIKE 'validate_password%';

validate_password.policy 3가지

- LOW: 비밀번호 길이 검증

validate_password.length

- MEDIUM: LOW + 숫자, 대소문자, 특수문자 배합 검증

validate_password.mixed_case_count, validate_password.number_count, validate_password.special_char_count

- STRONG: MEDIUM + 금칙어 포함 여부 검증

validate_password.dictionary_file

 

Dual Password (이중 비밀번호)

mysql 8.0 부터 가능, 두가지 비밀번호중 하나만 일치하면 로그인 가능

기존 비밀번호 변경 쿼리에 RETAIN CURRENT PASSWORD 옵션만 추가하면 됩니다.

// 기존 비밀번호 변경 쿼리문
ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';

// 비밀번호를 변경하면서 기존 비밀번호는 세컨더리 비밀번호로 설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

// 세컨더리 비밀번호 삭제
ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

프라이머리 비밀번호: 최신 비밀번호

세컨더리 비밀번호: 이전 비밀번호

 

Privilege (권한)

mysql에는 글로벌 권한과 객체 단위의 권한이 있습니다.

8.0 이전까지는 정적 권한만 있었지만, 8.0부터는 동적 권한이 추가됐습니다.

동적 권한은 mysql 서버가 시작되면서 동적으로 생성하는 권한을 의미합니다.

예) mysql 서버의 컴포넌트나 플러그인이 설치되면 그때 등록되는 권한

 

사용자에게 권한을 부여할때는 아래 쿼리문을 사용합니다.

GRANT privilege_list ON db.table TO 'user'@'host';

privilege_list 에는 , 를 사용해서 권한 여러개를 동시에 명시할 수 있습니다.

 

글로벌 권한은 특정 DB나 테이블에 부여될 수 없기때문에 아래와 같이 사용합니다.

*.*: 모든 DB의 모든 오브젝트를 포함하는 mysql 서버 전체를 의미

GRANT SUPER ON *.* TO 'user'@'localhost';

 

CREATE USER, CREATE ROLE과 같은 글로벌 권한은 DB 혹은 오브젝트 단위로 부여할 수 있는 권한이 아니여서 항상 *.*로만 대상을 사용할 수 있습니다.

 

DB권한은 특정 DB 에만 권한을 부여할 수도 있기때문에 아래와 같이 사용이 가능합니다.

GRANT EVENT ON *.* TO 'user'@'localhost';
GRANT EVENT ON employees.* TO 'user'@'localhost';

 

하지만 DB 권한은 DB 자체에 적용이 되기 때문에 DB 내의 특정 테이블을 지정할 수는 없습니다.

*.*: 가능

employees.*: 가능

employees.department: 불가능

 

테이블 권한은 다음과 같이 부여할 수 있습니다.

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.department TO 'user'@'localhost';

컬럼 권한에는 DELETE 제외한 SELECT, INSERT, UPDATE가 있습니다.

GRANT SELECT, INSERT, UPDATE(dept_name) ON employees.department TO 'user'@'localhost';

 

컬럼 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크를 하기 때문에 별도의 뷰를 만들어 뷰 자체애 대한 권한을 체크하는것이 더 좋은 방법입니다.

 

역할 (Role)

 

8.0부터 권한을 묶어서 역할을 사용할 수 있게 됐습니다.

-- ROLE 생성
CREATE ROLE role_emp_read, role_emp_write;
-- ROLE에 권한 부여
GRANT SELECT ON test2.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON test2.* TO role_emp_write;
-- 비밀번호 정책 수준 낮춤
set global validate_password.policy=LOW;
SET GLOBAL validate_password.LENGTH = 4;
-- 계정 생성
CREATE USER reader@'localhost' IDENTIFIED BY 'reader';
CREATE USER writer@'localhost' IDENTIFIED BY 'writer';
-- 계정에 역할 부여
GRANT role_emp_read TO reader@'localhost';
GRANT role_emp_write TO writer@'localhost';
-- 권한 확인
SHOW grants;

 

특정 계정에 역할을 부여하더라도, 실제로는 권한이 부여되어있지도 않고 역할이 비활성화되어있습니다.

아래 이미지처럼 역할을 활성화하면 정상적으로 역할이 부여되어 권한을 획득합니다.

 

-- mysql 서버에로그인할 때 역할을 자동으로 활성화하는 쿼리
show variables like 'activate_all_roles_on_login';
SET GLOBAL activate_all_roles_on_login=ON;

 

mysql 서버 내부적으로 역할과 계정은 동일한 객체로 취급됩니다.

(하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해서 권한 제어하기때문에 구분할 필요가 없음)

다만, 역할은 account_locked가 Y입니다.

역할을 생성할 때는 호스트를 명시하지 않는데, 기본값으로 %가 지정됩니다.

역할과 계정이 동일하게 취급되는데 CREATE ROLE, CREATE USER 로 나뉘는 이유는 권한 분배를 위해서입니다.

CREATE USER에 대한 권한이 없지만 CREATE ROLE 권한을 가진 사용자는 역할을 생성할 수 있습니다.

이렇게 생성한 권한은 로그인도 안되서 계정만 사용하는것보다 보안이 좋습니다.