[Real MySQL 8.0] 서버 설정 및 사용자 계정 관리

💡Real MySQL 8.0 도서 학습하기

---

2.4 서버 설정

 

2.4.2 MySQL 시스템 변수의 특징

MySQL 서버는 기동하며 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고 접속된 사용자를 제어하기 위해 이런 값을 별도로 저장하는데 이를 시스템 변수라고 함.

SHOW VARIABLES or SHOW GLOBAL VARIABLES 라는 명령으로 확인 가능.

명령어의 결과

이런 시스템 변수가 어떻게 MySQL 서버와 클라이언트에 영향을 끼치는 지를 파악하기 위해선 각 변수가 글로벌인지 세션인지를 구분할 수 있어야 한다. 그리고 이를 위해선 우선 글로벌 변수와 세션 변수가 뭐고 어떤 관계인지를 파악해야한다.

시스템 변수가 가지는 5가지 속성의 의미는 다음과 같은데

• Cmd-Line : MySQL 서버의 명령행 인자로 설정될 수 있는지
• Option file : MySQL 설정 파일인 my.cnf로 제어할 수 있는지
• System Var : 시스템 변수인지 아닌지
• Var Scope : 시스템 변수의 적용 범위 -> 서버 전체(Global) or 서버와 클라이언트 간의 커넥션(Session)
• Dynamic : 시스템 변수가 동적인지 정적인지

2.4.3 글로벌 변수와 세션 변수

• 글로벌 범위의 시스템 변수는 하나의 MySQL 인스턴스 전역으로 미치는 시스템 변수를 의미, 주로 MySQL 서버 자체에 관련된 설정일 때가 많음. (ex, InnoDB 버퍼 풀 크기, MyISAM의 키 캐시 크기 등)
• 클라이언트가 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는데 사용된다. 세션 단위로 달라질 수 있음. 한번 연결된 커넥션의 세션 변수는 서버에서 강제로 변경 X
• 대부분 범위가 Both라고 명시되어 있는데 그게 MySQL 서버가 기억만 하고 있다가 실제 클라이언트와 커넥션이 생성될 때 해당 커넥션의 기본값으로 사용되는 값.

2.4.4 정적 변수와 동적 변수

MySQL 서버가 기동 중 값이 변경될 수 있는지 없는지 여부에 따라 결정됨.

SHOW를 통해 조회가 가능하고 SET을 통해 값을 변경할 수 있다. 하지만 이를 통해 변경되는 부분은 해당 MySQL 서버 인스턴스에만 적용된다.

일반적인 글로벌 변수는 서버 기동 중 변경 X -> 실시간으로 변경 할 수 있는 것도 있음. 그리고 이를 이용해서 Both이면서 글로벌인 변수 값을 변경하면 세션 변수로써의 변수 값은 바뀌지 않음.

2.4.5 SET PERSIST 

이게 SET GLOBAL max_connections=5000; 이런 식으로 커맨드에서는 변경하고 설정파일에서 변경하지 않아서 재시작 시 예전과 똑같은 서버 설정으로 부팅되는 경우가 존재한다. 그래서 MySQL에서는 SET PERSIST 명령을 도입했다.

SET PERSIST max_connections=5000; 요렇게 하면 서버에서는 변경된 값을 즉시 적용함과  동시에 별도의 설정 파일에 변경 내용을 추가로 기록해둔다.

SET PERSIST_ONLY max_connections=5000; 요렇게 하면 현재 서버에는 적용되지 않고 설정 파일에만 적용해서 재부팅 시에 적용되도록 설정할 수 있다.

2.4.6 my.conf 파일

모든 시스템 변수를 공부해야 하는 것은 아니지만 MySQL 서버를 제대로 사용하기 위해선 시스템 변수에 대한 이해가 상당히 많이 필요하다. (MySQL 8.0 서버에는 약 570개의 시스템 변수 존재)

이런 시스템 변수는 각 장에서 자세히 설명하고 있기에 이를 참조하자.

3.1 사용자 식별

MySQL에서 사용자 계정 생성이나 권한 설정 방법은 다른 DBMS와 차이가 있다. 대표적으로 MySQL의 사용자 계정은 단순히 아이디 뿐만 아니라 어느 IP로 접속하고 있는지도 확인한다. 또 8.0 버전부터는 권한을 묶어서 관리하는 역할(Role) 개념이 도입 되어서 미리 준비된 권한 세트를 사용자에게 부여하는 것이 가능하다.

MySQL에서 사용자를 식별할 때는 어느 IP로 접속하는지도 확인한다. 그래서 계정 언급 시 아이디와 호스트를 함께 명시해야한다. (ex, `svc_id`@`127.0.0.1` -> 다른 컴퓨터에서는 svc_id로 접속 불가) 만약 모든 호스트에서 접근할 수 있게 만들고 싶다면 `svc_id`@`%` 이렇게 명시한다. 

또 주의해야 할 점은 서로 동일한 아이디가 있는 경우 MySQL 서버가 해당 사용자의 인증을 위해 어떤 계정을 선택하느냐이다. 예를 들어, `svc_id`@`192.168.0.10`(계정 PW : 123), `svc_id`@`%`(계정 PW : abc) 요렇게 있다고 가정하면 MySQL 서버에서는 어떤 계정 정보를 선택할까? 범위가 가장 작은 것을 항상 먼저 선택한다. 그니까 전자를 선택하게 된다. 그렇기 때문에 abc라는 비번으로 접근하면 비번이 일치하지 않는다고 접속이 거부되기에 계정 생성 시에 주의해야한다.

3.2 사용자 계정 관리

3.2.1 시스템 계정과 일반 계정

MySQL 8.0부터 계정은 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정과 일반계정으로 구분된다. 시스템 계정은 DB 서버 관리자를 위한 계정이고 일반 계정은 응용 프로그램이나 개발자를 위한 계정 정도로 생각하자.

시스템 계정은 시스템 계정과 일반 계정을 생성, 삭제 및 변경이 가능하지만 일반 계정은 시스템 계정을 관리할 수 없다. 또한 다음 작업은 시스템 계정으로만 가능하다.

• 계정 관리
• 다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제 종료
• 스토어드 프로그램 생성 시 DEFINER를 다른 사용자로 설정.

MySQL 서버에는 내장된 계정들이 있는데 `root`@`locahost`를 제외한 3개의 계정은 각기 내부적으로 다른 목적으로 사용되기에 삭제되지 않도록 주의하자.

• `mysql.sys`@`localhost` : MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체(뷰나 함수, 그리고 프로시저)들의 DeFINER로 사용되는 계정
• `mysql.session`@`localhost` : MySQL 플러그인이 서버로 접근할 때 사용되는 계정
• `mysql.infoschema`@`localhost` : information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

이 계정은 처음부터 잠겨있는 상태이기에 의도적으로 잠긴 계정을 풀지 않으면 악의적으로 사용할 수 없으므로 보안을 걱정하지 않아도 된다.

3.2.2 계정 생성

8.0 버전부터는 CREATE USER 명령으로 계정을 생성하고 권한 부여는 GRANT 명령으로 구분해서 실행하도록 변경되었다. 계정 생성 시 다양한 옵션 설정이 가능하다.

• 계정의 인증 방식과 비밀번호
• 비밀번호 관련 옵션 (유효 기간, 이력 개수, 재사용 불가 기간)
• 기본 역할 (Role)
• 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
    	PASSWROD REQUIRE CURRENT DEFAULT;

옵션에 대해서 하나씩 살펴보자.

IDENTIFIED WITH

요건 인증 방식에 대한 옵션이고 반드시 인증 플러그인의 이름을 명시해야 한다. 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시해야한다. 많은 인증 방식들이 존재하겠지만 다음 4가지 방식이 가장 대표적이다.

• Native Pluggable Authentication : MySQL 5.7 버전까지 기본으로 사용되었던 방식이고 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해부독, 클라이언트가 보낸 값과 일치하는지 비교하여 인증하는 방식이다.
• Cashing SHA-2, Pluggable Authentication : 8.0 버전에서 조금 더 보완된 인증 방식이고 Native와의 가장 큰 차이는 사용되는 암호화 해시 알고리즘의 차이이며 이는 좀 더 보안에 중점을 둔 알고리즘이다. 내부적으로 Salt 키를 활용해서 수천 번의 해시 계산을 수행해 결과를 만들기에 동일한 키 값에 대해서도 결과가 달라진다. 그러다 보니 성능이 매우 떨어지고 이를 보완하기 위해 MySQL 서버는 해시 결괏값을 메모리에 캐시해 사용하게 된다. 그래서 이름에 캐싱이 포함된 것이고 이 방식을 사용하기 위해선 SSL/TLS 또는 RSA 키페어를 반드시 사용해야 하는데 클라이언트 접속 시 SSL 옵션을 활성화해야 한다.
• PAM Pluggable Authentication : 유닉스나 리눅스 패스워드 또는 LDAP 같은 외부 인증을 사용할 수 있게 해주는 인증 방식이다.
• LDAP Pluggable Authentication : LDAP를 이용한 외부 인증을 사용할 수 있게 해주는 인증 방식이다.

REQUIRE

서버 접속 시 암호회된 SSL/TLS 채널 사용 여부를 설정하는 옵션이다.이걸 SSL로 설정하지 않아도 Cashing SHA-2 인증 방식을 사용하면 암호화된 채널만으로 MySQL 서버에 접속할 수 있게 된다.

PASSWORD EXPIRE

비밀 번호 유효 기간을 설정하는 옵션이고 명시하지 않으면 default_password_lifetime 시스템 변수의 값이 설정된다. 이는 보안상 안전하지만 응용 프로그램 접속용 계정에 유효 기간을 설정하는 것은 위험할 수 있으니 주의 하자. 여기 설정 가능한 옵션은 다음과 같다.

• PASSWORD EXPIRE : 계정 생성과 동시에 만료 처리
• PASSWORD EXPIRE NEVER : 만료 기간 없음
• PASSWORD EXPIRE DEFAULT : default_password_lifetime에 설정된 값으로 설정.
• PASSWORD EXPIRE INTERVAL n DAY : 오늘부터 n일자로 설정.

PASSWORD HISTORY

한번 사용했던 비밀번호를 재사용하지 못하게 하는 설정 옵션이다.

• PASSWORD HISTORY DEFAULT : password_history 시스템 변수에 저장된 개수만큼 이력을 저장하고 저장된 이력에 남아있는 비밀번호 재사용 불가.
• PASSWORD HISTORY n : 최근 n개의 이력 저장. 여기에 해당하는 비밀번호 재설정 불가.

이를 위해서 password_history 라는 테이블을 사용한다.

PASSWORD REUSE INTERVAL

한 번 사용했던 비밀번호 재사용 금지 기간 설정 옵션이다.

• PASSWORD REUSE INTERVAL DEFAULT : password_reuse_interval 변수에 저장된 기간으로 설정.
• PASSWORD REUSE INTERVAL n DAY : n일자 이후에 비밀번호를 재사용할 수 있게 설정.

PASSWORD REQUIRE

비밀번호 만료되어 새로운 비밀번호로 변경 시 현재 비밀번호를 필요로 할지 말지를 결정하는 옵션이다.

• PASSWORD REQUIRE CURRENT : 비밀번호 변경 시 현재 비밀번호를 먼저 입력하도록 설정.
• PASSWORD REQUIRE OPTIONAL : 비밀번호 변경 시 현재 비밀번호 입력 X.
• PASSWORD REQUIRE DEFAULT : password_require_current 시스템 변수 값으로 설정.

ACCOUNT LOCK / UNLOCK

계정 생성 시 또는 ALTER USER 명령을 사용해서 계정 변경 시 계정을 사용하지 못하게 잠글지 여부 설정.

• ACCOUNT LOCK : 사용하지 못하게 잠금
• ACCOUNT UNLOCK : 잠긴 계정을 다시 사용 가능 상태로 해제

---

마무리

MySQL에서 사용자를 어떻게 식별하는지 이런 부분에 대해서 생각을 안해봤었는데 호스트명이 다른 DBMS에서는 필요없는 경우도 있구나를 알게 되었고 계정 생성 시에 고려해야 할점에 대해서 알게 되어서 좋았습니다!

 

Reference

http://www.yes24.com/Product/Goods/103415627