[Real MySQL 8.0] 3. 사용자 및 권한 (1부)

차_현·2025년 1월 27일

사용자 식별

MySQL의 사용자는 다른 DBMS랑은 조금 다르다. 그 이유는 사용자의 계정 뿐만 아니라 사용자의 접속 지점(클라이언트가 실행된 호스트 명이나 도메인 또는 IP주소)도 계정이 일부가 되기 때문이다.

'user'@'127.0.0.1'
'user'@'%'

(여기서 %는 모든 IP 또는 호스트를 의미한다)

그리고 물론 의도적으로 아래와 같이 중첩된 계정을 생성해서 접속하지는 않겠지만, 혹시 실수로 그럴 수도 있으니.

'user'@'192.168.0.10' (이 계정의 비밀번호를 123 이라고 하자)
'user'@'%' (이 계정의 비밀번호를 999 라고 하자)

IP 주소가 192.168.0.10인 PC에서 이 MySQL 서버에 접속할 때, MySQL 서버가 위의 두개의 계정 중 첫 번째 계정 정보로 인증을 할지, 아니면 두 번째 계정 정보로 인증을 진행할까?

MySQL에서는 두 계정 정보 중, 범위가 좁은걸 먼저 선택하기 때문에 IP가 명시된 계정 정보인 'user'@'192.168.0.10' 이 계정 정보를 이용해서 인증을 하게 된다.

사용자 계정 관리

시스템 계정과 일반 계정

SYSTEM_USER 권한을 가지고 있느냐에 따라서 시스템 계정이냐, 일반 계정이냐 를 나눈다.

시스템 계정은 왜 필요할까? 데이터베이스 서버 관리와 같은 중요 작업은 시스템 계정으로만 수행할 수 있고,

계정 관리(계정 생성, 삭제, 계정의 권한 부여, 계정의 권한 제거), 다른 세션 또는 그 세션에서 실행 중인 쿼리를 강제 종료 그리고 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정할 수 있다.

보통 DBA를 위한 계정으로 시스템 계정을 사용하고 이를 통해 일반 사용자에게는 SYSTEM_USER 권한을 부여하지 않게 한다.

스토어드 프로그램이란?
- 스토어드 프로그램(Stored Program)은 SQL 문들의 집합으로 구성되어, 데이터베이스 서버에 저장된 프로그램이다. 사용자는 이를 호출함으로써 복잡한 연산을 수행하고, 데이터 처리 로직을 데이터베이스 서버에서 직접 실행할 수 있다. 스토어드 프로그램은 주로 아래와 같은 형태를 가진다.
  - 스토어드 프로시저(Stored Procedure): 사용자가 명시적으로 호출하여 실행하는 일련의 SQL 문들의 집합이다. 데이터 조회, 변경, 비지니스 로직 처리 등을 수행할 수 있다.
  - 트리거(Triggers): 데이터베이스 테이블에 대한 특정 이벤트(예: INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 SQL 문들의 집합이다.
  - 함수(Functions): 호출 시 특정 값을 반환하는 SQL 문들의 집합이다. 일반적으로 데이터 변환, 계산 등의 작업을 수행한다.
DEFINER란?
- DEFINER는 스토어드 프로그램이나 뷰를 생성할 때, 해당 객체를 실행할 때 기본 SQL 보안 컨텍스트를 지정하는 속성이다.
- 보안 접근 및 제어 : DEFINER는 스토어드 프로그램이나 뷰가 실행될 때의 권한을 명시적으로 설정할 수 있게 해준다. 이를 통해 데이터베이스 관리자는 민감한 데이터에 대한 접근을 엄격하게 제어할 수 있다.
- 실행 컨텍스트 : 스토어드 프로그램이나 뷰가 호출되었을 때, DEFINER로 지정된 사용자 권한으로 실행된다. 이는 호출자의 권한과는 독립적으로, 스토어드 프로그램 내의 SQL 문이 실행될 권한을 제어한다.
- 만약, 특정 데이터베이스에 어떤 민감한 정보를 다루고 있는 테이블이 있을 때, 어떤 사용자가 해당 테이블에 대한 직접적인 조회나 혹은 변경할 수 있는 권한이 없다. 그런데 이 사용자는 업무상 필요로 하는 정보가 이 테이블에 포함이 되어있기 때문에, 데이터베이스 관리자(DBA)는 DEFINER가 높은 권한을 가진 사용자로 설정된 스토어드 프로시저(Stored Procedure)를 생성할 수 있고, 이 스토어드 프로시저(사용자가 명시적으로 호출하여 실행하는 일련의 SQL 문들의 집합) 는 민감한 정보를 조회하고, 사용자가 필요로 하는 데이터만 가공해서 반환하는 로직을 포함할 수 있게 된다.

아래는 ‘root’@’localhost’ 계정을 제외한 내장된 계정들이다.

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

아래는 내장된 시스템 계정을 확인하는 방법이다.

계정 생성

MySQL 5.7 버전까지는 GRANT 명령으로 권한의 부여와 동시에 계정 생성까지 가능했었다.

GRANT ALL PRIVILEGES ON *.* TO 'temp_user'@'%' IDENTIFIED BY '12345';

SELECT * FROM mysql.user;

위의 첫번째 명령어는 계정 생성과 동시에 권한을 부여하는 명령어, 그리고 아래는 생성된 계정을 확인하는 명령어였다.

하지만 MySQL 8.0부터는 GRANT 명령으로 권한 부여와 동시에 계정 생성이 못하게 바뀌었다.

계정 생성은 CREATE USER 명령어로 해야하며, 권한 부여는 GRANT 명령으로 구분해서 실행하도록 바뀌었다.

그리고 계정을 생성할 때는 아래와 같은 옵션을 설정할 수 있다.

계정의 인증 방식과 비밀번호
비밀번호 관련 옵션(비밀번호 유효 기간, 비밀번호 이력 개수, 비밀번호 재사용 불가 기간)
기본 역할(Role)
SSL 옵션
계정 잠금 여부

mysql> CREATE USER 'user'@'%'
					IDENTIFIED WITH 'mysql_native_password' BY 'password'
			    REQUIRED NONE
			    PASSWORD EXPIRED INTERVAL 30 DAY
			    ACCOUNT UNLOCK
			    PASSWORD HISTORY DEFAULT
			    PASSWORD REUSE INTERVAL DEFAULT
			    PASSWORD REQUIRE CURRENT DEFAULT;

위의 명령어는 일반적으로 많이 사용되는 유저 생성(CREATE USER) 명령어다. 이제 위 명령어의 다양한 옵션들은 어떤 것을 의미하는지 한번 알아보겠다.

IDENTIFIED WITH

사용자의 인증 방식과 비밀번호를 설정한다. IDENTIFIED WITH 뒤에는 항상 인증 방식(인증 플러그인의 이름)을 명시해야 한다. MySQL 서버의 기본 인증 방식을 사용하고 싶다면, IDENTIFIED BY ‘password’ 형식으로 명시하면 된다. MySQL 서버에서는 다양한 인증 방식을 플러그인 형태로 제공한다.

Native Pluggable Authentication : MySQL 5.7 버전까지는 기본으로 사용되었고, 단순히 비밀번호에 대한 해시(SHA-1 알고리즘) 값을 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 비교하는 인증 방식이다.
Caching SHA-2 Pluggable Authentication : MySQL 5.6 버전에 도입되고, MySQL 8.0 버전에서는 조금 더 보완된 인증 방식으로, 암호화 해시값 생성을 위해 SHA-2(256비트) 알고리즘을 사용한다.
Native Authenticaiton과의 가장 큰 차이점은 암호화 해시 알고리즘 차이이고, SHA-2 Authentication은 저장된 해시값의 보안에 더 중점을 둔 알고리즘이다. 내부적으로 Salt 키를 활용하며, 수천 번의 해시 계산을 수행해서 결과를 만들어 내기에 동일한 키 값에 대해서도 결과가 달라질 수 있다.
해시값을 계산하는 방식은 상당히 시간이 많이 걸려서 성능이 떨어질 수 있는데, 이를 보완하기 위하여 MySQL 서버는 해시 결과 값을 메모리에 캐시해서 사용하게 된다. 그래서 맨 앞에 Caching이라는 용어가 붙은 것이다.
마지막으로 SSL/TLS 또는 RSA 키페어를 반드시 사용해야 하는데, 이를 위해 클라이언트에서 접속할 때, SSL 옵션을 활성화 해야 한다.
PAM Pluggable Authentiaction : 유닉스나 리눅스 패스워드 또는 LDAP(Lightweight Directory Access Protocol) 같은 외부 인증을 사용할 수 있게 해주는 인증 방식이다. 엔터프라이즈 에디션 버전에서만 사용이 가능하다.
LDAP Pluggable Authentication : LDAP을 이용한 외부 인증을 사용할 수 있게 해주는 방식으로, 엔터프라이즈 에디션 버전에서만 사용이 가능하다. LDAP란 디렉터리 서비스(조직 내에서 자원이나 개체(사용자, 그룹, 장치 등)에 대한 정보를 저장하고 관리하며, 이를 효율적으로 검색하거나 접근할 수 있도록 도와주는 서비스)에 접근하고 관리하기 위한 표준 프로토콜이다.
주로 조직의 사용자 계정, 그룹, 네트워크 장치, 자원 정보 등을 저장하고 관리하는 디렉터리 서비스와 상호작용하는 데 사용된다.

MySQL 5.7 버전까지는 Native Authentication이 기존 인증 방식이었지만, 8.0 버전부터는 Caching SHA-2 Authentication은 SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에, 기존의 방식과 다른 방법으로 접속해야 한다. 그래서 만약 기존 버전과의 호환성이 문제라면 Native Authentication 인증 방식으로 계정을 생성해야 할 수 있다. 그리고 이를 위해 my.cnf 설정 파일에 추가해야 한다.

mysql> SET GLOBAL default_authentication_plugin="mysql_native_password"

CREATE USER 또는 ALTER USER 명령을 통해, MySQL 서버의 계정을 생성하거나 변경할 때 연결 방식과 비밀번호 옵션, 자원 사용과 관련된 여러 옵션을 설정할 수 있다.

REQUIRE

MySQL 서버에 접속할 때 암호화 된 SSL/TLS 채널을 사용할지 여부를 설정한다. 별도로 해당 옵션을 설정하지 않는다면, 비암호화 채널로 연결된다. 그러나 REQUIRE 옵션을 SSL로 설정하지 않았다고 하더라도 Caching SHA-2 Authentication 인증 방식을 사용하면 암호화된 채널만으로 MySQL 서버에 접속할 수 있게 된다.

PASSWORD EXPIRE

비밀번호의 유효 기간을 설정하는 옵션이고, 별도로 명시하지 않는다면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정된다. 개발자나 DBA의 비밀번호는 유효 기간을 설정하는 것이 보안상 안전하지만, 응용 프로그램 접속용 계정에 유효기간을 설정하는 것은 위험할 수 있다. 아래는 설정할 수 있는 다양한 옵션들이다.

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개 까지만 저장하고, 저장된 이력에 남아있는 비밀번호는 다시 사용할 수 가 없다.

위의 명령어는 MySQL 서버가 mysql DB의 비밀번호 변경 이력을 password_history 테이블에 확인할 수 있는 명령어이다. 나는 비밀번호 변경 이력 옵션을 활성화하지 않고, 이력이 없기 때문에 Empty set으로 뜨는 것이다.

PASSWORD REUSE INTERVAL

한번 사용하였던 비밀번호의 재사용 금지 기간을 설정하는 옵션이다. 따로 이 옵션을 사용하지 않는다면, pasword_reuse_interval 시스템 변수에 저장된 기간으로 설정된다.

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

위의 명령어를 통해 나의 재사용 금지 기간을 확인해보았다. 난 값이 0이므로 재사용 제한이 비활성화되어 이전에 사용한 비밀번호를 언제든 다시 사용할 수 있는 것이다. 그 외에 위에 2개 변수도 한번 살펴보자.

password_history: 0
- 저장된 비밀번호 기록 개수를 나타냅니다. 값이 0이므로 비밀번호 기록을 저장하지 않으며, 비밀번호 변경 시 이전 비밀번호와의 비교가 이루어지지 않는다.
password_require_current: OFF
- 비밀번호 변경 시 현재 비밀번호 입력을 요구할지 여부를 나타냅니다. OFF로 설정되어 있으므로 현재 비밀번호를 입력하지 않아도 비밀번호를 변경할 수 있습니다.

PASSWORD REQUIRE

비밀번호가 만료되어 새로운 비밀번호를 변경할 때, 현재 비밀번호(변경하기 전 이미 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션이다. 별도로 명시하지 않으면, password_require_current 시스템 변수의 값으로 설정된다. 이전 스크린 샷에서 봤던 시스템 변수이다.

PASSWORD REQUIRE CURRENT : 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
PASSWORD REQUIRE OPTIONAL : 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
PASSWORD REQUIRE DEFAULT : password_require_current 시스템 변수의 값으로 설정

ACCOUNT LOCK / UNLOCK

계정을 생성할 때 혹은 ALTER USER 명령을 통해 사용자 계정 정보를 변경할 때, 계정을 사용하지 못하게 잠글지 여부를 설정하는 옵션이다.

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

차_현

이전 포스트

[Real MySQL 8.0] 2. 설치와 설정 (2부)

다음 포스트