[RealMySQL8.0] 사용자 및 권한

 

사용자 식별 (Authentication)

사용자 계정은 사용자명 + 접속 지점(IP 또는 호스트)으로 구성된다. MySQL에서는 사용자 계정을 '사용자명'@'호스트명' 형태로 지정한다. 이때 호스트명은 실제 접속 위치(IP 주소 또는 호스트 이름)를 의미하며, 계정의 일부로 간주한다. 예를 들어 다음 두 계정은 서로 다른 계정이다.

'user'@'127.0.0.1'

'user'@'%'

 

%는 모든 IP 주소 또는 호스트에서의 접속을 허용한다는 의미이다.

중첩 계정과 우선순위

MySQL은 동일한 사용자명에 대해 접속 지점만 다른 계정이 여러 개 존재할 수 있다. 이 경우 접속 시점에 일치하는 계정을 찾을 때 보다 구체적인 호스트명(IP 주소 등)이 우선적으로 선택된다. 예를 들어 다음과 같은 두 계정이 있다고 가정한다.

 

'user'@'192.1xx.xx.xx' 비밀번호: 1234
'user'@'%' 비밀번호: 5678

 

이때 클라이언트가 192.1xx.xx.xx에서 접속한다면 비밀번호는 1234를 사용해야 하며, 5678을 사용할 경우 인증에 실패한다.

이는 범위가 좁은 계정이 우선순위를 가지기 때문이다.

접속된 사용자 계정과 실제 인증된 계정이 다를 수 있으므로, SELECT user(), current_user(); 명령어로 현재 사용자 정보를 확인할 수 있다.

시스템 계정과 일반 계정

시스템 계정

MySQL 8.0에서는 시스템 계정의 개념이 도입되었다. 시스템 계정은 SYSTEM_USER 권한을 가지며, 일반 사용자 계정과 달리 다음과 같은 작업이 가능하다.

• 다른 사용자의 세션 종료
• 권한 변경 및 계정 관리
• 스토어드 프로그램 작성 시 DEFINER로 지정 가능

이러한 시스템 계정은 주로 데이터베이스 관리자(DBA)를 위한 계정이다. 

내장된 시스템 계정

MySQL은 기본적으로 몇 가지 내장된 시스템 계정을 제공하며, 이들은 모두 mysql. 접두사로 시작된다. 예를 들면 다음과 같다.

SELECT user, host, account_locked 
FROM mysql.user 
WHERE user LIKE 'mysql.%';

# 결과
+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
+------------------+-----------+----------------+

 

이들 계정은 삭제하거나 수정하지 않는 것이 원칙이며, 모두 account_locked 상태로 생성되기 때문에 보안 위협이 되지 않는다.

사용자 계정 생성 및 조회

MySQL 5.7 이전과 8.0의 차이점

MySQL 5.7까지는 GRANT 명령어 하나로 사용자 계정을 생성하고 권한까지 부여할 수 있었다.

하지만 MySQL 8.0부터는 반드시 CREATE USER 명령어로 계정을 먼저 생성한 후, GRANT 명령어로 권한을 부여해야 한다.

# 5.7 까지 사용가능한 구문 생성과 권한 부여를 동시에 진행 
GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%' IDENTIFIED BY '12345678';

# 8.0 이후는 아래와 같은 형식으로 CREATE USER -> GRANT 형식으로 사용해야한다
CREATE USER 'test_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%';

 

사용자 목록은 다음과 같은 명령어로 조회할 수 있다.

SELECT user, host FROM mysql.user;

 

인증 플러그인

MySQL 8.0에서는 기본 인증 플러그인이 caching_sha2_password로 변경되었다. 기존의 mysql_native_password보다 보안이 강화되었으며, SSL/TLS 또는 RSA 키를 필요로 한다. 현재 기본 인증 방식을 확인하려면 다음 명령어를 사용한다.

SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'default_authentication_plugin';

 

해당 변수는 읽기 전용이기 때문에 런타임 중 변경이 불가하다. 특정 사용자 계정의 인증 방식을 변경하려면 아래와 같이 개별 설정을 해주어야함. 

ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_password';

 

권한 관리 (Privilege)

권한의 종류

MySQL에서는 권한을 크게 두 가지로 나눈다.

• 정적 권한: 서버에 기본 내장된 권한 (예: SELECT, INSERT, CREATE)
• 동적 권한: 서버 실행 중 플러그인이나 구성 요소가 등록하면서 생성되는 권한 (예: SYSTEM_USER, BINLOG_ADMIN)

MySQL 8.0부터는 5.7에서 하나였던 SUPER 권한이 여러 동적 권한으로 나뉘어졌다.

권한 부여는 GRANT 명령어로 수행한다. 권한 범위에 따라 다음과 같이 지정한다.

 

 

글로벌 권한

GRANT SUPER ON *.* TO 'user'@'%';

 

데이터베이스 권한

GRANT SELECT, INSERT ON mydb.* TO 'user'@'%';

 

테이블 권한

GRANT SELECT ON mydb.customers TO 'user'@'%';

 

현재 계정이 가진 권한 확인

SHOW GRANTS FOR 'user'@'%';

역할(Role)

MySQL 8.0부터는 역할(Role) 기능이 도입되었다. 역할은 권한 묶음을 의미하며, 여러 권한을 하나의 단위로 관리할 수 있다.

 

역할 생성 방법

CREATE ROLE 'role_read_only'@'localhost';

 

 

역할은 사용자 계정과 동일하게 mysql.user 테이블에 저장되며, 생성 시 account_locked = Y 상태로 설정된다.

 

역할에 권한 부여

GRANT SELECT ON employees.* TO 'role_read_only'@'localhost';

 

역할을 사용자에게 부여

GRANT 'role_read_only'@'localhost' TO 'reader'@'127.0.0.1';

 

 

세션 내에서 역할 활성화

SET ROLE 'role_read_only'@'localhost';

 

기본 역할 지정

SET DEFAULT ROLE 'role_read_only'@'localhost' TO 'reader'@'127.0.0.1';

 

기본 역할이 지정된 사용자는 로그인 시 자동으로 해당 역할이 활성화된다.

정리

MySQL 8.0은 사용자 및 권한 관리 체계가 보다 보안 중심으로 개선되었으며, 역할 기반 권한 관리와 인증 플러그인의 다양화 등으로 유연성과 안정성을 함께 갖추게 되었다. 실습 환경에서는 인증 방식의 호환성(기존 5.7을 사용하고 있다고 가정할 때 )을 고려해mysql_native_password를 사용하는 것이 편리할 수 있으며, 운영 환경에서는 보안을 위해 기본 방식인 caching_sha2_password를 사용하는 것이 바람직하다.