[Real MySQL 8.0 1권] 03. 사용자 및 권한

유혁·2026년 3월 28일

CS Database Real MySQL 8.0

Real MySQL 8.0

목록 보기

4/22

본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.

3.1 사용자 식별

MySQL의 경우 타 RDBMS와 다르게 사용자의 계정 뿐만 아니라 접속 지점 또한 계정의 일부가 되므로 아이디와 호스트를 함께 명시해야 한다.

MySQL 서버의 계정 선택 기준은 항상 범위가 가장 작은 것을 먼저 선택한다.
예를 들어 아래와 같이 ID가 동일하지만 IP가 지정된 계정과, 모든 IP를 허용하는 계정이 각각 존재하는 경우에 IP가 192.168.0.10인 PC에서 svc_id에 로그인 한다면 범위가 더 작은 첫 번째 계정을 선택하게 되므로 비밀번호는 123을 입력해야 한다.

'svc_id'@'192.168.0.10' (비밀번호 123)
'svc_id'@'%' (비밀번호 abc)

% 문자는 모든 IP 또는 모든 호스트명을 의미하며, 모든 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하고 싶은 경우 호스트 부분에 % 문자를 사용할 수 있다.

이와 같이 중복되는 계정을 생성하게 되는 경우 원하는 계정으로 로그인 할 수 없는 문제가 발생할 수 있기 때문에 주의해야 한다.

3.2 사용자 계정 관리

3.2.1 시스템 계정과 일반 계정

MySQL 8.0부터 SYSTEM_USER 권한 유무에 따라 시스템 계정(System Account)과 일반 계정(Regular Account)로 구분된다.

시스템 계정

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

MySQL 서버에 내장된 계정들 중 'root'@'localhost'를 제외한 3개의 계정은 각기 다른 목적으로 사용되므로 삭제하지 않도록 주의가 필요하며, 기본적으로 잠겨있는 상태이다.

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

3.2.2 계정 생성

MySQL 8.0 버전부터는 계정의 생성은 CREATE USER, 권한 부여는 GRANT 명령으로 구분해서 실행된다.

계정 생성 시 설정할 수 있는 옵션

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

3.3 비밀번호 관리

3.3.1 고수준 비밀번호

MySQL 서버의 비밀번호는 이를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능이 있다.

## validate_password 컴포넌트 설치
mysql> INSTALL COMPNENT 'file://component_validate_password'

## 설치된 컴포넌트 확인
mysql> SELECT * FROM mysql.component;

## 컴포넌트에서 제공하는 시스템 변수 확인
mysql> SHOW GLOBAL VARIABLES LIKE 'validate_password%';

비밀번호 정책은 크게 3가지 중 선택 가능하며, 기본값은 MEDIUM이다.

LOW: 비밀번호 길이만 검증
MEDIUM: 비밀번호 길이 검증, 숫자와 대소문자, 특수문자의 배합 검증
STRONG: MEDIUM 레벨 검증 + 금칙어 포함 여부 검증

금칙어를 처리하고 싶은 경우, validate_password.dictionary_file 시스템 변수에 금칙어들이 저장되니 사전 파일을 등록하면 된다.
또한 금칙어를 적용하는 경우 validate_password.policy 시스템 변수를 STRONG으로 함께 변경해야 한다.

3.2 이중 비밀번호

MySQL 서버의 이중 비밀번호 기능은 하나의 계정에 대해 2개의 비밀번호를 동시에 설정할 수 있는데, 2개의 비밀번호는 프라이머리(Primary)와 세컨더리(Secondary)로 구분된다.

최근에 설정된 비밀번호가 프라이머리 비밀번호, 이전 비밀번호가 세컨더리 비밀번호가 되며, 이중 비밀 번호를 사용하려면 기존 비밀번호 변경 문구에 RETAIN CURRENT PASSWORD 옵션을 추가한다.

## 비밀번호를 "abcdef"로 설정
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';

## 비밀번호를 "123456"로 변경하며, 기존 비밀번호 세컨더리 비밀번호 설정
mysql> ALTER USER 'root'@'localhost IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

이후 MySQL 서버에 접속하는 모든 응용 프로그램의 재시작이 완료된 경우 계정의 보안을 위해 다음 명령으로 세컨더리 비밀번호를 삭제하여, 기존 비밀번호로는 로그인이 불가능해진다.

mysql> ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

3.4 권한(Privilege)

MySQL 8.0 버전부터는 MySQL 5.7 버전의 정적 권한에 동적 권한이 더 추가되었다.

정적 권한 - MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한
동적 권한 - MySQL 서버가 시작되면서 동적으로 생성하는 권한

사용자에게 권한을 부여할 때는 GRANT 명령을 사용하며, 각 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트(DB, 테이블)의 내용이 바뀌어야 한다.

mysql> GRANT privilege_list ON db.table TO 'user'@'host';

글로벌 권한

mysql> GRANT SUPER ON *.* TO 'user'@'localhost';

글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에 항상 ON 절에 모든 DB의 오브젝트를 포함한 MySQL 서버 전체를 의미하는 *.*를 사용하게 된다.

DB 권한

mysql> GRANT EVENT ON *.* TO 'user'@'localhost';
mysql> GRANT EVENT ON employees.* TO 'user'@'localhost';

DB 권한은 특정 DB 혹은 서버에 존재하는 모든 DB에 권한을 부여할 수 있기 때문에 위처럼 *.* 혹은 employees.* 모두 사용할 수 있지만, 테이블까지 명시할 수는 없다.

테이블 권한

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.* TO 'user'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.department TO 'user'@'localhost';

테이블 권한은 모든 DB, 특정 DB, 그리고 특정 DB의 특정 테이블에 대해서만 권한을 부여하는 것이 가능하다.

테이블의 특정 컬럼에 부여할 수 있는 권한은 DELETE를 제외한 INSERT, UPDATE, SELECT 3가지로, 각 권한 뒤에 컬럼을 명시하는 형태로 부여한다.

mysql> GRANT SELECT,INSERT,UPDATE(dept_name) ON employees.department TO 'user'@'localhost';

여러 레벨이나 범위로 권한을 설정하는 것이 가능하지만, 테이블이나 컬럼 단위 권한을 설정하게 된다면 나머지 모든 테이블, 모든 컬럼에 대한 권한 체크를 하기 때문에 전체 성능에 영향이 갈 수 있어 지양하는 편이다.
컬럼 단위의 접근 권한이 정말 필요한 경우 테이블에서 권한을 허용한 뒤 별도의 뷰를 만드는 방법도 있다.

3.5 역할(Role)

MySQL 8.0 버전부터 권한을 묶어서 역할(Role)을 사용할 수 있게 됐다.
역할을 정의한 뒤 실질적인 권한을 부여하고, 이후 해당 역할을 계정에 부여하는 방식으로 사용한다.

## 역할 정의
mysql> CREATE ROLE
		role_emp_read,
        role_emp_write;
        
## 역할에 권한 부여
mysql> GRANT SELECT ON employees.* TO role_emp_read;
mysql> GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

## 계정에 역할 부여
mysql> GRANT role_emp_read TO reader@'127.0.0.1';
mysql> GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';

위 과정으로 역할을 생성하여 계정에 역할을 부여하는 작업을 완료하였지만, 실제로 DB 데이터 조회 혹은 변경 시 권한이 없다는 에러를 만나게 된다. 이는 SET ROLE 명령을 실행해 해당 역할을 활성화해야만 한다.

## 역할 활성화
mysql> SET ROLE 'role_emp_read';

기본적으로 MySQL 서버는 역할이 자동으로 활성화되지 않게 설정되어있다.
자동으로 활성화하기 위해서는 activate_all_roles_on_login 시스템 변수를 아래와 같이 변경하면 된다.

mysql> SET GLOBAL activate_all_roles_on_login=ON;

MySQL 서버의 역할은 사용자 계정과 거의 같은 모습이며, 서버 내부적으로 역할과 계정을 동일한 객체로 취급된다. 단지 하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해 권한 제어가 가능하도록 한 것이다.

역할을 생성하는 경우 호스트 부분을 별도로 명시하지 않는 경우, 자동으로 모든 호스트(%)가 추가된다. 아래 두 CREATE ROLE 명령은 동일한 역할을 만들게 된다.

mysql> CREATE ROLE
		role_emp_read,
        role_emp_write;
        
mysql> CREATE ROLE
		role_emp_read@'%',
        role_emp_write@'%';

역할과 계정은 내외부적으로 동일한 객체이지만 MySQL 서버에서 CREATE ROLE 명령과 CREATE USER 명령을 구분해서 지원하는 이유는 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용될 수 있게 하기 위해서다.

CREATE USER 명령에 대해서는 권한이 없지만 CREATE ROLE 명령만 실행 가능한 사용자는 역할을 생성할 수 있다. 이렇게 생성된 역할은 계정과 동일한 객체를 생성하지만, 실제 이 역할은 account_locked 컬럼의 값이 'Y'로 설정되어 로그인 용도로 사용할 수 없게 된다.