03) 사용자 및 권한

사용자 식별
1. MySQL사용자는 다른 DBMS와 달리 사용자 계정과 사용자의 접속 지점(IP)도 계정의 일부로 인식
2. 아이디와 IP주로를 감싸는 부분은 백틱(``)을 이용하며 MySQL에서 식별자를 감싸는 역할, 홑따옴표(’’)로 바뀌어서 사용되기도 함
3. 예시) 'svc_id'@'127.0.0.1'
4. 위 예시는 MySQL서버가 가동중인 호스트에서 svc_id라는 아이디로만 접속할 수 있는 계정
5. 만약 모든 외부에서 접근 가능한 계정을 만들고자 한다면 IP주소 대신 ‘%’를 사용하면 된다.
6. 예시) 'svc_id'@'%'
7. MySQL서버에서 접속할 때는 여러개의 계정 중 범위가 가장 좁은 계정을 선택하여 인증을 실행함(예시에서는 'svc_id'@'127.0.0.1' 계정이 해당)
사용자 계정 관리
1. MySQL 8.0부터 계정은 SYSTEM_USER권한을 가지고 있느냐 없느냐에 따라 시스템 계정과 일반 계정을 구분
2. 시스템 계정: DB서버 관리자를 위한 계정(시스템 계정과 일반 계정 관리도 가능), 쿼리 강제 종료, 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정
3. 일반 계정: 응용 프로그램이나 개발자를 위한 계정
4. MySQL이 기본적으로 제공하는 계정
  1. ‘mysql.sys’@’localhost’: MySQL 8.0부터 기본으로 내장된 sys스키마의 객체(뷰나 함수, 프로시저)들의 DEFINER로 사용되는 계정
  2. ‘mysql.session’@’localhost’: MySQL 플러그인이 서버로 접근할 때 사용되는 계정
  3. ‘mysql.infoschema’@’localhost’: information_schema에 정의된 뷰의 DEFINER로 사용되는 계정
  4. 위 계정은 처음부터 잠겨있는 상태이므로 악의적인 용도로 사용 불가능
계정 생성
1. MySQL 8.0부터는 계정의 생성은 CREATE USER명령으로, 권한 부여는 GRANT 명령으로 구분해서 사용
2. 계정 생성시 가능한 옵션
  1. 계정의 인증 방식과 비밀번호
  2. 비밀번호 관련 옵션(비밀번호 유효 기간, 비밀번호 이력 개수, 비밀번호 재사용 불가 기간)
  3. 기본 역할
  4. SSL옵션
  5. 계정 잠금 여부
3. IDENTIFIED WITH
  1. 사용자의 인증방식과 비밀번호를 설정
  2. MySQL의 기본 인증방식을 사용한다면 INDENTIFIED BY ‘password’ 형식으로 WITH절 생략
  3. MySQL이 제공하는 대표적인 인증방식
    1. Native Pluggable Authentication
      1. MySQL 5.7까지 사용되던 방식
      2. 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 확인
      3. 입력이 동일할 때 동일한 해시값을 출력
    2. Caching SHA-2 Pluggable Authentication
      1. MySQL 5.6버전에 도입, MySQL 8.0 버전에서는 보완된 인증 제공
      2. Native Pluggable Authentication와 가장 큰 차이는 사용되는 암호화 알고리즘 차이(SHA1 → SHA2)
      3. 내부적으로 salt키를 사용하여, 수천번의 해시 계산을 수행해서 결과를 만들어서 입력이 동일하더라도 매번 값이 달라짐
      4. 앞선 이야기처럼 수천번 계산을 하기 때문에 성능면에서는 좋지 못함
      5. 이를 보완하기 위해 MySQL서버는 해시 결괏값을 메모리에 캐시해서 사용(이러한 이유로 Cashing이라는 명명 사용)
      6. 이 옵션을 사용하기 위해서는 SSL/TLS 또는 RSA키페어를 반드시 사용해야 하므로, 클라이언트 접속 시 SSL옵션을 반드시 활성화해야함
      7. SCRAM(Salted Challenge Response Authentication Mechanism) 사용
    3. PAM Pluggable Authentication
      1. 유닉스나 리눅스 패스워드 또는 LDAP(Lightweight Directory Access Protocol)같은 외부 인증을 사용할 수 있도록 해줌
      2. MySQL 엔터프라이즈 에디션만 사용 가능
    4. LDAP Pluggable Authentication
      1. LDAP을 이용한 외부 인증을 사용할 수 있도록 해주는 인증 방식
      2. MySQL 엔터프라이즈 에디션만 사용 가능
  4. MySQL 5.7버전까지는 Native Authentication 인증 방식이 사용되었지만 MySQL 8.0버전 이후부터는 Caching SHA-2 Authentication 인증 방식이 기본적용
  5. 위 이유로 업그레이드를 하는 경우 인증방식을 기존방식으로 사용해야 하는 경우 생길 수 있음
```
SET GLOBAL default_authentication_plugin=”mysql_native_password”
```
REQUIRE
1. MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부 설정
2. 기본값은 비암호화 채널로 연결
3. 하지만, 기본값으로 접속하더라도, Caching SHA-2 Authentication 인증 방식을 사용하면 암호화된 채널만으로 MySQL서버 접속 가능
PASSWORD EXPIRE
1. 비밀번호 유효 기간 설정 옵션
2. 기본값은 시스템 변수의 default_password_lifetime 변수의 값에 따라 적용
3. 개발자나 DBA의 비밀번호는 유효기간을 설정해주는 것이 보안상 안전
4. 하지만, 응용 프로그램 접속용 계정에 유효기간을 설정하는 것은 위험
5. PASSWORD EXPIRE 옵션
  1. PASSWORD EXPIRE: 계정 생성과 동시에 비밀번호 만료 처리
  2. PASSWORD EXPIRE NEVER: 계정 비밀번호 만료 기간 없음
  3. PASSWORD EXPIRE DEFAULT: default_password_lifetime: 시스템 변수에 저장된 기간으로 비밀번호의 유효 기간을 설정
  4. PASSWORD EXPIRE INTERVAL n DAY: 유효기간을 오늘부터 n일자로 설정
PASSWORD HISTORY
1. 한 번 사용한 비밀번호를 사용하지 못하도록 하는 옵션
2. PASSWORD HISTORY 옵션
  1. PASSWORD HISTORY DEFAULT:password_history: 시스템 변수에 저장된 개수만큼 비밀번호 이력을 저장하며, 저장된 이력에 남아있는 비밀번호를 재사용할 수 없음
  2. PASSWORD HISTORY n: 비밀번호의 이력을 최근 n개까지만 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용 불가능
3. MySQL에서는 password_history테이블을 이용해서 이전에 사용한 비밀번호를 저장
PASSWORD REUSE INTERVAL
1. 한 번 사용한 비밀번호의 재사용 금지 기간을 설정하는 옵션
2. 별도로 명시하지 않으면, password_reuse_interval 시스템 변수의 값을 기간으로 설정
3. PASSWORD REUSE INTERVAL 옵션
  1. PASSWORD REUSE INTERVAL DEFAULT:password_reuse_interval: 변수에 저장된 기간으로 설정
  2. PASSWORD REUSE INTERVAL n DAY: n일자 이후에 비밀번호를 재사용할 수 있도록 설정
PASSWORD REQUIRE
1. 비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하지 전 만료된 비밀번호)를 필요로 할지 결정하는 옵션
2. 별도로 명시하지 않으면 password_require_current 시스템 변수의 값을 참조
3. PASSWORD REQUIRE 옵션
  1. PASSWORD REQUIRE CURRENT: 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
  2. PASSWORD REQUIRE OPTIONAL: 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않도록 설정
  3. PASSWORD REQUIRE DEFAULT:password_require_current: 시스템 변수의 값으로 설정
ACCOUNT LOCK/UNLOCK
1. 계정 생성 시 또는 ALTER USER명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하도록 여부 결정
2. ACCOUNT LOCK: 계정을 사용하지 못하도록 잠금
3. ACCOUNT UNLOCK: 계정을 사용 가능 상태로 잠금 해제
고수준 비밀번호 관리
1. MySQL서버는 유효기간이나 이력 관리를 통한 재사용 금지 기능, 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 관리
2. 비밀번호 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하면 됨
3. validate_password 컴포넌트는 내장되어 있기 때문에 별도 경로를 지정하지 않고 설치 가능
4. INSTALL COMPONENT ‘file://component_validate_password’;
5. 설치하면 컴포넌트가 제공하는 시스템 변수 확인 가능
6. SHOW GLOBAL VARIABLES LIKE ‘validate_password&’;
7. validate_password.policy(비밀번호 정책) 옵션
  1. LOW: 비밀번호 길이만 검증
  2. MEDIAM: 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
  3. STRONG: MEDIAM레벨의 검증과 금칙어가 포함되었는지 여부까지 검증
8. 비밀번호의 길이는 validate_password.length 변수 참조
9. 숫자, 대소문자, 특수문자는 validate_password.mixed_case_count, validate_password.number_count, validate_password.special_char_count를 참조
10. 금칙어는 validate_password.dictionary_file 시스템 변수에 설정된 사전 파일에 명시된 단어를 포함하는지 확인
11. MySQL 5.7버전까지는 validate_password가 플러그인타입으로 제공됨
이중 비밀번호
1. 보통 DB계정 정보는 공용으로 사용되느 경우가 많음
2. 위 상황에 의해 계정 정보를 변경하는 것이 쉽지 않음
3. 또한 서비스가 실행 중인 상황에서는 비밀번호를 변경하는 것은 거의 불가능
4. 이를 위해 MySQL 8.0부터 계정의 비밀번호를 2개를 설정 가능하도록 지원, 둘 중 하나의 비밀번호만으로 로그인 가능
5. 비밀번호는 Primary, Secondary로 구분
  1. Primary: 최근에 설정된 비밀번호
  2. Secondary: 이전 비밀번호
6. 이중 비밀번호를 사용하려면 비밀번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션만 추가하면 됨
7. ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘old_password’(기존 비밀번호 변경)
8. ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘new_password’ RETAIN CURRENT PASSWORD; (이중 비밀번호 사용 쿼리)
9. Secondary비밀번호는 보안을 위해 삭제하는 것이 좋음
10. ALTER USER ‘root’@’localhost’ DISCARD OLD PASSWORD; (Secondary 비밀번호 삭제 쿼리)
권한
1. 권한 구분(MySQL 5.7까지는 글로벌, 객체 권한으로만 구분)
  1. 글로벌 권한: DB, 테이블 외의 객체에 적용되는 권한(권한 부여시 특정 객체 명시X)
  2. 객체 권한: DB, 테이블을 제어하는 데 필요한 권한(권한 부여시 반드시 특정 객체 명시해야함)
  3. ALL: 예외적으로 글로벌, 객체 권한에 사용됨, 특정 객체에 ALL이 부여되면 해당 객체의 모든 권한을 부여, 글로벌로 사용되면 글로벌 수준에서 가능한 모든 권한 부여
  4. 동적 권한: 서버가 시작되면서 동적으로 생성되는 권한(예를 들어 컴포넌트, 플러그인이 설치되면 그때 등록되는 권한)
2. MySQL 5.7까지는 SUPER라는 권한이 DB관리를 위해 꼭 필요한 권한이었지만, 8.0부터는 잘게 쪼개져 동적 권한으로 분리됨
3. 권한 부여
역할(ROLE)
쿼리 실행 구조