사용자 및 권한

MySQL의 사용자 계정은 단순히 사용자의 아이디 뿐 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인합니다.
MySQL 8.0부터는 권한을 묶어서 관리하는 역할의 개념이 도입됐기 때문에 각 사용자의 권한으로 미리 준비된 권한 세트를 부여하는 것도 가능합니다. 데이터베이스의 보안은 중요하기에, 반드시 계정의 식별 방식과 권한, 역할에 대한 기본적인 내용을 숙지해야 합니다.

 

사용자 식별

MySQL은 다른 DBMS와 달리 사용자의 접속 지점(클라이언트가 실행된 호스트명이나 도메인 또는 IP주소)도 계정의 일부가 됩니다.
→ 계정을 언급할 때는 항상 아이디와 호스트를 함께 명시해야 한다.
(예) svc_id@127.0.0.1
호스트가 함께 명시되기 때문에, 위 아이디로 MySQL서버에 접속을 하면, 같은 아이디로 다른 컴퓨터에서는 접속을 할 수 없습니다. 만약 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하고 싶다면 사용자 계정의 호스트 부분을 %문자로 대체하면 됩니다.
(예) svc_id@%

만약 IP주소가 127.0.0.1인 컴퓨터에서 svc_id로 로그인을 시도하면, 위 두 계정 중에서 범위가 작은 svc_id@127.0.0.1이 선택됩니다. 따라서 비밀번호는 svc_id@127.0.0.1에 연결된 비밀번호로 입력을 시도해야 합니다. ( 하지만 애시당초 중첩된 계정을 MySQL에 등록하는 걸 추천하지 않음 )

사용자 계정 관리

 

시스템 계정과 일반 계정

MySQL 8.0부터는 SYSTEM_USER 권한을 가지고 있는지 여부에 따라 시스템 계정과 일반 계정으로 구분됩니다. 시스템 계정은 데이터베이스 관리자를 위한 계정이며, 일반 계정은 응용 프로그램이나 개발자를 위한 계정입니다.

 

MySQL Workbench 내에서도 계정 관리를 진행할 수 있다.

 

시스템 계정은 다음과 같이 데이터베이스 서버 관리가 가능합니다.

a) 시스템 계정은 일반 계정을 관리 ( 계정 생성 및 삭제, 권한 부여 및 제거 )
b) 다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제로 종료
c) Stored 프로그램 생성 시 DEFINER를 타 사용자로 설정

이외에 MySQL서버에 초기부터 내장된 계정도 있습니다.

a) mysql.sys@localhost: 내장된 스키마의 객체(뷰나 함수, 프로시저)들의 DEFINER로 사용되는 계정
b) mysql.session@localhost: MySQL 플러그인이 서버로 접근할 때 사용되는 계정
c) mysql.infoschema@localhost: information_schema에 정의된 뷰의 DEFINER로 사용되는 계정
→ 처음부터 잠겨 있기에 악의적 용도로 사용할 수 없다 ( 보안 걱정 X )

 

계정 생성

MySQL 5.7: GRANT 명령으로 권한의 부여와 동시에 계정 생성 가능
MySQL 8.0: CREATE USER 명령으로 계정 생성 후 → GRANT 명령으로 권한 부여

* 계정 생성 시 부여할 수 있는 옵션: 계정의 인증 방식과 비밀번호, 비밀번호 관련 옵션(비밀번호 유효기간, 비밀번호 이력 개수, 비밀번호 재사용 불가 기간), 기본 역할, 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;

a) IDENTIFIED WITH: 인증 방식. 8.0에서는 디폴트로 Caching SHA-2 Pluggable Authentication 사용 중 ( 보안성 높아짐 ). 5.7에서는 디폴트로 Native Pluggable Authenticcation 사용 (해시 SHA-1 알고리즘 사용).
b) REQUIRE: SSL/TLS 채널을 사용할지 여부 결정. Caching SHA-2 인증을 사용하면, SSL 설정을 하지 않아도 암호화된 채널만으로 접속 가능.
c) PASSWORD EXPIRE: 비밀번호 유효기간 설정 (디폴트는 시스템 변수에 저장된 값)
d) PASSWORD HISTORY: 한 번 사용했던 비밀 번호를 재사용하지 못하게 설정
e) PASSWORD REUSE: 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정
f) PASSWORD REQUIRE: 비밀번호가 만료되어 변경할 때, 기존 비밀번호를 필요로 할지 말지를 결정하는 옵션

 

비밀번호 관리

MySQL 서버의 비밀번호는 유효 기간이나 이력을 통한 재사용 금지 이외에도, 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 존재합니다.

비밀번호 정책은 3가지 중 선택이 가능합니다.
a) LOW: 비밀번호의 길이만 검증
b) MEDIUM: 비밀번호의 길이 검증 + 숫자와 대소문자, 특수문자의 배합을 검증
c) STRONG: 비밀번호의 길이 검증 + 숫자와 대소문자, 특수문자의 배합을 검증 + 금칙어 포함 여부 검증

서비스가 실행 중인 상태에서 계정 비밀번호 변경은 어렵기에, MySQL 8.0부터는 이중 비밀번호 등장했습니다.
최근에 설정한 비밀번호가 프라이머리 비밀번호, 이전에 설정한 비밀번호가 세컨더리 비밀번호가 됩니다. 

SET PASSWORD [FOR user] auth_option
    [REPLACE 'current_auth_string']
    [RETAIN CURRENT PASSWORD];

 

권한

글로벌 권한: 데이터베이스나 테이블 이외의 객체에 적용되는 권한 (GRANT 명령 뒤에 객체를 명시하지 않음)

GRANT SUPER ON *.* TO user@localhost;

객체 권한: 데이터베이스나 테이블을 제어하는데 필요한 권한 (GRANT + 객체)

- 모든 DB에 대한 권한 부여

GRANT EVENT ON *.* TO user@localhost;

- 특정 스키마에 대한 권한 부여

GRANT EVENT ON %schema%.* TO user@localhost;

- 특정 테이블에 특정 권한만 부여

GRANT SELECT, INSERT, UPDATE, DELETE ON %schema%.%table% TO user@localhost;

 

역할 ( Role )

MySQL 8.0부터는 권한들을 묶어 '역할'로 사용할 수 있습니다.

- 역할 정의

CREATE ROLE role_emp_read, role_emp_write;

- 역할에 권한 부여

GRANT SELECT ON employees.* TO role_emp_read;