MySql은 사용자 아이디뿐 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인한다
MySql 8.0 버전부터는 권한을 묶어서 관리하는 역할 개념이 도입
사용자 식별
사용자의 개정뿐 아니라 사용자의 접속 지점도 계정의 일부가 되기에 계정을 언급할 때는 항상 아이디와 호스트를 함께 명시해야 한다
// 로컬에서만 접근 가능
'svv_id'@'127.0.0.1'
// 모든 외부에서 접근하는 계정
'svc_id'@'%'동일한 계정에 접근 범위가 적은것이 있다면 접근범위 중 가장 작은 것을 항상 먼저 선택하여 접근
사용자 계정 관리
어떤 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정과 일반 계정으로 구분
시스템 계정에서만 가능한 것
- 계정 관리
- 다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제 종료
- 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정
MySql 서버에 내정된 계정들
- ‘mysql.sys’@’localhost’: MySql 8.0부터 기본으로 내장된 sys 스키마 객체의 DEFINER로 사용되는 계정
- ‘mysql.session’@’localhost’: MySql 플러그인이 서버로 접근할 때 사용되는 계정
- ‘mysql.infoschema’@’localhost’: information_schema에 정의된 뷰의 DEFINER로 사용되는 계정
계정 생성
MySql 8.0 버전부터는 계정의 생성은 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행하도록 변경
GRANT로 설정할 수 있는 옵션
- 계정의 인증 방식과 비밀번호
- 비밀번호 관련 옵션(유효 기간, 재사용 불가 기간, 비밀번호 이력 개수)
- 기본 역할
- SSL 옵션
- 계정 잠금 여부
가장 많이 사용되는 옵션을 가진 CREATE USER 명령어
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 채널을 사용할지 여부 설정
- 디폴트 값은 비암호화 채널로 연결
PASSWORD EXPIRE
- 비밀번호의 유효 기간을 설정하는 옵션, 별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정
- 보안 상 기간을 설정해주는 것이 안전
- PASSWORD EXPIRE 옵션들
- PASSWORD EXPIRE: 계정 생성과 동시에 비밀번호의 만료 처리
- PASSWORD EXPIRE NEVER: 계정 비밀번호의 만료 기간 없음
- PASSWORD EXPIRE DEFAULT: default_password_lifetime 시스템 변수에 저장된 기간으로 비밀번호의 유효 기간을 설정
- PASSWORD EXPIRE INTERVAL n DAY: 비밀번호의 유효 기간을 오늘부터 n일자로 설정
PASSWORD HISTORY
- 한번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션
- PASSWORD HISTORY 옵션들
- PASSWORD HISTORY DEFAULT: password_history 시스템 변수에 저장된 개수만큼 비밀번호의 이력을 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없다
- PASSWORD HISTORY n: 비밀번호의 이력을 최근 n개까지만 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없다
PASSWORD REUSE INTERVAL
- 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션
- 별도로 명시하지 않으면 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 옵션들
- PASSWORD REQUIRE CURRENT: 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
- PASSWORD REQUIRE OPTIONAL: 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
- PASSWORD REQUIRE DEFAULT: password_require_current 시스템 변수의 값으로 설정
ACCOUNT LOCK / UNLOCK
- 계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부 결정
- ACCOUNT LOCK: 계정을 사용하지 못하게 잠금
- ACCOUNT UNLOCK: 잠긴 계정을 다시 사용 가능 상태로 잠금 해제
비밀번호 관리
비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어 사용하는 기능도 존재
비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하는데 이를 설치해야 사용 가능
INSTALL COMPONENT 'file://component_validate_password';비밀번호 정책(default: MEDIUM)
- LOW: 비밀번호의 길이만 검증
- MEDIUM: 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
- STRING: MEDIUM + 금칙어가 포함됐는지 여부까지 검증
금칙어 설정 방법
SET GLOBAL validate_password.dictionary_file='prohibitive_word.data';
SET GLOBAL validate_password.policy='STRONG';이중 비밀번호
서비스 중인 DB에서 비밀번호가 변경이 되면 장애가 발생하기에 서비스 중인 경우 서비스를 멈추지 않고서는 비밀번호를 변경하는 것은 불가능했다
이를 개선하기 위해 계정의 비밀번호로 2개의 값을 동시에 사용할 수 있게 했다
Primary: 최근 설정한 비밀 번호
Secondary: 이전 비밀번호
이중 비밀번호를 사용하려면 기존 비밀번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션을 추가
Secondary 비밀번호를 꼭 삭제할 필요는 없지만 보안을 위해서 삭제하는 것이 좋다
권한
사용자에게 권한을 부여할 때는 GRANT 명령을 사용
역할
권한을 묶어서 역할을 사용할 수 있게 됐다
// 역할 생성
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;
// 유저 생성
CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'qwerty';
// 역할 부여
GRANT role_emp_read TO reader@'127.0.0.1'
GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1'로그 아웃 후 다시 로그인하면 역할이 초기화 되는데 이를 위해 아래 글로벌 변수를 설정하면 로그인과 동시에 역할이 활성화 됨
SET GLOBAL activate_all_roles_on_login=ON;