[Real MySQL 8.0]2, 3장

📝 2장 MySQL 시스템 변수의 특징

 

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

CMD에서 SHOW VARIABLES && SHOW GLOBAL VARIABLES로 확인 가능하다.

SHOW VARIABLES && SHOW GLOBAL VARIABLES

 

📌 적용 범위 측면의 시스템 변수

시스템 변수 값이 어떻게 MySQL 서버와 클라이언트에 영향을 미치는지 판단하려면 각 변수가 무엇인지 알아야 한다.

시스템 변수는 적용 범위에 따라 글로벌과 세션으로 나뉘며 일반적으로 세션별로 적용되는 시스템 변수의 경우 글로벌 변수 뿐만아니라 세션 변수에도 동시에 존재한다.

글로벌 변수

• 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수이다.
• 주로 MySQL 자체 설정과 관련 있다.

세션 변수

• 클라이언트(사용자)가 MySQL 서버에 접속할 때, 해당 클라이언트의 세션에만 영향을 미치는 변수
• 클라이언트(사용자)가 처음에 접속하면 기본적으로 부여하는 기본값을 가진다.
• 클라리언트(사용자)의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경 가능하다.

 

글로벌 변수와 세션 변수는 각각 시스템 성능 조정, 쿼리 실행 방식 등을 제어하는 중요한 역할을 하며, 필요에 따라 조정할 수 있다.

 

📌 변경 가능 여부에 따른 시스템 변수

MySQL 서버의 시스템 변수는 MySQL 서버가 가동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분된다.

정적 변수

• 정적 변수는 프로그램 실행 중에 한 번만 초기화되고, 프로그램이 종료 될 때까지 값을 유지한다.
• 주로 프로그램 상태 유지, 성능 최적화에 경우 사용된다.

동적 변수

• 프로그램 실행 중에 동적으로 메모리가 할당되고, 프로그램 실행 중에 필요에 따라 생성되고 소멸된다.

📌SET PERSIST

그렇다면 시스템 변수는 어떻게 변경할 수 있을까요?

기본적으로 SET GLOBAL "";로 할 수 있습니다.

SET GLOBAL max_connections=500;

해당 명령어를 이용하면 시스템 변수의 값을 일시적으로 변경할 수 있습니다.

즉 해당 런타임 중에 반영되고 MySQL의 설정 파일인 my.cnf에는 반영되지 않습니다.

 

영구 반영을 원한다면 SET PERSIST를 이용해야 합니다.

SET PERSIST max_connections=5000;

해당 명령은 시스템 변수를 변경하면 MySQL 서버는 변경된 값을 즉시 적용함과 동시에 별도의 설정 파일에 변경 내용을 저장한다. 그리고 MySQL 서버가 다시 시작될 때 기본 설정 파일과 별도의 설정 파일을 같이 적용한다.

 

SET PERSIST 명령은 세션 변수에는 적용이 되지 않고, 시스템 변수를 변경하면 자동으로 GLOBAL 시스템 변수의 변경으로 인식하고 변경한다

 

만약 SET PERSIST로 추가된 시스템 변수의 내용을 삭제해야 하면 

RESET PERSIST로 삭제해야 안전하다.

//SET PERSIST로 생성된 별도의 파일에서 해당 시스템 변수를 삭제시킨다.
RESET PERSIST max_connections;

 

📝 3장 사용자 및 권한

 

MySQL에서 사용자 계정을 생성하는 방법이나 각 계정의 권한을 설정하는 방법은
다른 DBMS 와 조금 차이가 있다.

📌3-1 사용자 식별

MySQL은 사용자의 계정뿐 아니라 사용자의 접속 지점(호스트명, 도메인, IP)도 계정 일부가 됩니다. 따라서 MySQL에서 계정을 언급할 때는 다음과 같이 항상 아이디와 호스트를 함께 명시해야 합니다. 

// 로컬에서만 접근 가능
'svv_id'@'127.0.0.1'

// 모든 외부에서 접근하는 계정
'svc_id'@'%'

만약 동일한 계정에 접근하면 접근 범위가 작은 것부터 항상 먼저 선택하여 접근합니다.

 

📌3-2 사용자 계정 관리

MySQL8.0부터 계정은 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정과 일반 계정으로 구분됩니다.

시스템 계정은 윈도우의 Admin 역할과 비슷하며 다음과 같은 기능을 사용할 수 있습니다.

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

DEFINER이란?
스토어드 프로그램(프로시저, 함수, 트리거 등)의 권한을 결정하는 속성이며 실행될 때 어떤 사용자 권한으로 실행될지를 설정합니다.
기본적으로 시스템 계정이 아니라면 자기자신 권한으로 실행 시킵니다.

 

다음은 MySQL 서버에 내장된 기본 계정들 입니다.

1. ‘mysql.sys’@’localhost’: MySQL 8.0부터 기본으로 내장된 sys 스키마 객체의 DEFINER로 사용되는 계정
2. ‘mysql.session’@’localhost’: MySQL 플러그인이 서버로 접근할 때 사용되는 계정
3. ‘mysql.infoschema’@’localhost’: information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

📌 3-3 계정 생성

MySQL 8.0 버전부터 계정 생성을 할때 CREATE USER 명령을 사용합니다. 권한 부연는 GRANT로 구분하여 실행합니다.

 

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 HISTORY	한번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션
PASSWORD REUSE INTERVAL	한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정
PASSWORD REQUIRE	비밀번호가 만료되어 새로운 비밀번호로 변경할 떄 현재 비밀번호를 필요로 할지 말지를 결정하는 옵션 별도로 명시되지 않으면 password_require_current 시스템 변수의 값으로 설정
ACCOUNT LOCK / UNLOCK	계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부 결정 ACCOUNT LOCK: 계정을 사용하지 못하게 잠금 ACCOUNT UNLOCK: 잠긴 계정을 다시 사용 가능 상태로 잠금 해제

 

📌 3-4 비밀번호 관리

MySQL의 비밀번호는 유효기간이나 이력 관리를 통한 재사용 금지 기능뿐만 아니라 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자 조합을 강제하거나 금칙어 설정이 가능하다. 

만약 해당 기능을 사용하고 싶으면 validate_password 컴포넌트를 다운받아야 한다.

INSTALL COMPONENT 'file://component_validate_password';

 

해당 컴포넌트를 다운받으면 비밀번호 정책 중 3가지를 고를 수 있다.

1. LOW: 비밀번호의 길이만 검증
2. MEDIUM: 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
3. STRING: MEDIUM + 금칙어가 포함됐는지 여부까지 검증

금칙어 설정 방법

SET GLOBAL validate_password.dictionary_file='prohibitive_word.data';
SET GLOBAL validate_password.policy='STRONG';

 

📌 3-5 권한(Privilege)

MySQL의 권한은 크게 글로벌 권한과 객체 권한으로 구분된다. 

• 글로벌 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
• 객체 권한 : 데이터베이스나 테이블을 제어하는데 필요한 권한

객체 권한은 GRANT 명령으로 권한 부여할 대 반드시 특정 객체를 명시하며 글로벌 권한은 명시하지 않는다.

 

권한의 종류는 공식 문서를 확인하자

MySQL :: MySQL 9.0 Reference Manual :: 8.2.2 Privileges Provided by MySQL

MySQL :: MySQL 9.0 Reference Manual :: 8.2.2 Privileges Provided by MySQL

 

MySQL 8.0 버전부터 기존 권한에 동적 권한이 더 추가되었는데 기존 권한은 정적 권한으로 MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한을 의미하며 동적 권한은 MySQL 서버가 시작되면서 동적으로 생성하는 권한을 의미한다.

 

권한 부여 명령어: GRANT

GRANT OPTION

• TO: 권한을 부여할 대상 사용자 명시한다 
• ON: 어떤 DB의 어떤 오브젝트에 권한을 부여할지 결정하며 권한의 범위에 따라 방법이 다르다.

//글로벌 권한 -> DB단위나 오브젝트 단위로 부여할 수 있는 권한 X = 대상은 항상 *.* 
GRANT SUPER ON *.* TO 'user'@'localhost'; 

//DB 권한 -> 테이블에 대해 권한 부여 X = 테이블 명시 X 
GRANT EVENT ON employess.* TO 'user'@'localhost'; 

//테이블 권한 -> 특정 테이블에 대해서만 권한 부여 가능 
GRANT SELECT,INSERT,UPDATE,DELETE ON employees.department TO 'user'@'localhost';

 

📌 3-6 역할(Role)

MySQL 8.0 버전부터는 권한을 묶어서 역할을 사용할 수 있게 되었다.

//빈 껍데기만 있는 역할 정의 
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'; 

//계정에 역할 부여 
GRANT role_emp_read TO reader@'127.0.0.1';

 

위 예시처럼 진행하면 역할을 부여할 수 있지만 그 기능을 바로 사용할 수는 없다.

왜냐하면 MySQL에서 역할 부여는 말 그대로 부여만할 뿐 활성화가 되어있는 것은 아니다.

 

그렇기에 접속 후 해당 명령어로 활성화를 해주어야 한다.

//역할을 사용할 수 있도록 설정 
SET ROLE 'role_emp_read';

 

그러나 이런 방식이면 MySQL 서버의 역할이 매우 불편하고 수동적으로 보이기에 역할을 자동으로 활성화할지를 결정하는 시스템 변수 activate_all_roles_on_login를 설정하면 로그인 시 자동으로 부여된 역할이 활성화된다.

SET GLOBAL activate_all_roles_on_login=ON;

 

위 활동을 보면 MySQL 서버의 역할은 사용자 계정과 거의 비슷한 양상을 가진다. MySQL 서버 내부적으로도 역할과 계정은 동일한 객체로 취급하며, 단지 하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해서 권한 제어가 가능해진 것이다.

 

즉 MySQL 서버 내부적으로 계정과 역할은 차이가 없다. (그래서 역할에 대해서는 이름 설정이 중요하다)

 

---

🧷정리

1. 시스템 변수의 특징

• 글로벌 변수: 전체 MySQL 서버에 영향을 미치는 변수.
• 세션 변수: 각 클라이언트 세션에만 영향을 미치는 변수.
• 정적 변수: 서버가 시작될 때 한 번만 초기화되며 실행 중에는 변경 불가능.
• 동적 변수: 서버 실행 중에 변경 가능한 변수.
• SET PERSIST: 시스템 변수의 값을 영구적으로 변경하여, MySQL 서버가 재시작되더라도 설정이 유지되도록 할 수 있습니다.

2. 사용자 및 권한

• 계정 식별: 사용자 계정은 아이디뿐만 아니라 접속 지점(호스트명, 도메인, IP)도 포함됩니다.
• 사용자 계정 관리: 시스템 계정과 일반 계정으로 구분되며, 시스템 계정은 윈도우의 Admin 역할과 비슷한 기능을 갖습니다.
• 비밀번호 관리: 비밀번호의 유효기간, 이력 관리, 금칙어 설정 등을 통해 보안을 강화할 수 있습니다.
• 권한: MySQL 권한은 글로벌 권한과 객체 권한으로 나뉘며, 이를 GRANT 명령으로 부여합니다.
• 역할(Role): MySQL 8.0부터 권한을 묶어 역할을 부여할 수 있으며, 역할 활성화 여부를 설정할 수 있습니다.