역할까지 줬는데 SELECT 거부? CURRENT_ROLE()이 NONE이던 MySQL 계정

SHOW GRANTS에는 역할이 있는데 SELECT는 거부되는 계정
역할을 부여했다고 안내했는데 “여전히 권한이 없다는 오류가 난다”는 문의가 들어오면 질문은 둘로 나뉩니다. 역할 자체가 계정에 연결되지 않은 것인지, 연결은 됐지만 그 세션에서 역할이 켜지지 않은 것인지입니다. 두 경우 모두 오류 메시지는 똑같이 권한 부족으로 나오기 때문에 메시지만으로는 구분이 안 됩니다.
MySQL 8.4.11에서 GRANT 역할 TO 사용자만 실행한 계정을 만들고 로그인부터 기본 역할 지정까지 세션별로 상태를 추적했습니다. 확인한 값은 접속 ID, CURRENT_ROLE(), 같은 테이블에 대한 COUNT(*) 결과 세 가지입니다.
재현에 쓴 계정과 역할 구성
환경은 Windows의 MySQL Community Server 8.4.11, 클라이언트는 MySQL Workbench입니다. root 연결과 테스트 계정 연결을 따로 만들었고, 테스트 계정 연결의 기본 스키마는 비워 두었습니다.

activate_all_roles_on_login이 false라는 점이 이후 결과를 읽는 전제가 됩니다. 이 값이 켜져 있는 서버라면 아래와 다른 결과가 나올 수 있습니다.
데이터는 1,000건만 넣었습니다. 이번에 보는 것은 조회 속도가 아니라 조회가 허용되는지 여부라서, COUNT(*)로 성공을 확인할 수 있는 정도면 충분했습니다.
CREATE DATABASE rolechk_lab;
USE rolechk_lab;
CREATE TABLE partner_settlement (
settle_id INT PRIMARY KEY,
settle_amount DECIMAL(12,2) NOT NULL
);
INSERT INTO partner_settlement
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000)
SELECT n, (n * 137) % 90000 + 1000 FROM seq;
CREATE ROLE 'r_settle_read';
GRANT SELECT ON rolechk_lab.* TO 'r_settle_read';
CREATE USER 'rl_app'@'%' IDENTIFIED BY 'Lab81#App';
GRANT 'r_settle_read' TO 'rl_app'@'%';
이 시점에 root에서 확인한 계정 권한은 아래와 같습니다. 역할이 계정에 연결된 줄은 분명히 있습니다.
SHOW GRANTS FOR 'rl_app'@'%';
GRANT USAGE ON *.* TO `rl_app`@`%`
GRANT `r_settle_read`@`%` TO `rl_app`@`%`
접속 ID 19, 역할은 연결돼 있지만 세션에서는 꺼져 있었다
rl_app 계정으로 처음 접속한 세션에서 활성 역할과 조회 결과를 확인했습니다.
SELECT CONNECTION_ID(), CURRENT_ROLE();
SELECT COUNT(*) FROM rolechk_lab.partner_settlement;

접속 ID 19의 CURRENT_ROLE()은 NONE이었고, 조회는 MySQL Error (1142): SELECT command denied로 막혔습니다. 계정에 직접 부여된 권한은 USAGE뿐이고 SELECT 권한은 역할 안에만 있으므로, 역할이 켜지지 않으면 이 계정이 쓸 수 있는 권한은 사실상 없는 셈입니다. GRANT는 역할을 쓸 자격을 만들 뿐이고, 그 자격이 세션에서 실제로 적용되는지는 별개의 문제였습니다.
오류 메시지의 호스트와 계정의 호스트가 달랐다
계정은 'rl_app'@'%'로 만들었는데 오류 메시지에는 'rl_app'@'localhost'가 찍혔습니다. 메시지의 호스트 부분은 계정 정의가 아니라 접속한 클라이언트 쪽을 보여 주는 것으로 보입니다. 이 메시지만 보고 'rl_app'@'localhost' 계정을 찾으면, 실제로 만든 계정은 'rl_app'@'%'이므로 엉뚱한 곳을 조사하게 될 수 있습니다. 실제로 매칭된 계정은 해당 세션에서 SELECT CURRENT_USER();로 확인하는 것이 정확합니다. 이번 테스트에서는 rl_app 세션의 CURRENT_USER()를 따로 캡처하지 않았으므로, 이 부분은 SHOW GRANTS 결과와 계정 생성문을 근거로 한 판단입니다.
SET ROLE은 즉시 풀어 주지만 재접속하면 원점으로 돌아간다
같은 세션에서 역할을 직접 켰습니다.
SET ROLE 'r_settle_read';
SELECT CONNECTION_ID(), CURRENT_ROLE();
SELECT COUNT(*) FROM rolechk_lab.partner_settlement;

접속 ID는 그대로 19인데 CURRENT_ROLE()이 `r_settle_read`@`%`로 바뀌었고, 조금 전 거부됐던 조회가 1000을 돌려줬습니다. 권한 정의는 하나도 바꾸지 않았습니다. 바뀐 것은 이 세션에서 켜진 역할뿐입니다.
이어서 Workbench에서 Reconnect to Server로 다시 접속했습니다.
SELECT CONNECTION_ID(), CURRENT_ROLE();
CONNECTION_ID() CURRENT_ROLE()
21 NONE
새 접속 ID 21에서는 다시 NONE이 나왔습니다. SET ROLE은 그 세션에만 적용되고 계정에는 아무것도 남기지 않는다는 뜻입니다. 애플리케이션이 커넥션 풀을 쓰고 있다면, 이 결과를 그대로 적용할 때 풀이 새로 연결을 만들 때마다 역할이 빠진 세션이 생깁니다. 장애 대응 중에 SET ROLE로 문제가 풀린 것을 확인하고 끝내면, 다음 재기동 때 같은 문의가 다시 올 가능성이 큽니다.
SET DEFAULT ROLE 뒤 접속 ID 23에서 처음으로 그대로 유지됐다
root 연결에서 기본 역할을 지정한 뒤, rl_app 연결을 다시 맺었습니다.
-- root
SET DEFAULT ROLE 'r_settle_read' TO 'rl_app'@'%';
-- rl_app (재접속 후)
SELECT CONNECTION_ID(), CURRENT_ROLE();
SELECT COUNT(*) FROM rolechk_lab.partner_settlement;

접속 ID 23에서는 SET ROLE을 실행하지 않았는데도 `r_settle_read`@`%`가 활성 상태였고, 조회 결과는 1000이었습니다. 네 단계를 나란히 놓으면 차이가 분명합니다.
| 단계 | 접속 ID | CURRENT_ROLE() | COUNT(*) |
|---|---|---|---|
| GRANT만 하고 첫 로그인 | 19 | NONE | 1142 오류 |
| 같은 세션에서 SET ROLE | 19 | r_settle_read | 1000 |
| 재접속 | 21 | NONE | 확인 안 함 |
| SET DEFAULT ROLE 후 재접속 | 23 | r_settle_read | 1000 |
접속 ID 21에서는 조회를 다시 실행하지 않았습니다. 역할이 NONE이니 19번 세션과 같은 결과가 나올 것으로 판단했지만, 측정한 값은 아닙니다.
권한 문의가 들어오면 확인하는 순서
역할을 쓰는 계정에서 권한 오류가 나면 아래 순서로 범위를 좁힙니다.
- 문제가 생긴 세션에서
SELECT CURRENT_USER(), CURRENT_ROLE();를 실행합니다. 역할이NONE이면 권한 정의를 들여다보기 전에 활성화 문제부터 의심합니다. - root에서
SHOW GRANTS FOR로 역할이 연결된 줄이 있는지 확인합니다. 줄이 없으면 GRANT가 빠진 것이고, 있으면 활성화 단계의 문제입니다. SELECT * FROM mysql.default_roles WHERE USER = '계정명';으로 기본 역할이 지정돼 있는지 봅니다.@@activate_all_roles_on_login값을 확인합니다. 서버마다 이 값이 다르면 같은 계정 설정이라도 서버별로 결과가 갈립니다.- 기본 역할을 지정한 뒤에는 기존 세션이 아니라 새로 접속한 세션에서 다시 확인합니다. 커넥션 풀을 쓰는 애플리케이션이라면 풀의 기존 연결도 고려해야 합니다.
운영하는 환경에서는 서비스 계정 권한을 스키마 단위로 부여하고 있습니다. 신규 테이블을 추가해도 서비스 계정에 권한이 자동으로 적용되는 구조라 관리가 편하기 때문입니다. 이번 테스트에서 역할에 rolechk_lab.*를 준 것도 같은 방식입니다. 다만 같은 스키마 단위 권한을 역할에 담으면, 테이블 범위의 편리함은 그대로인 대신 활성화라는 단계가 하나 더 생깁니다. 계정에 직접 GRANT할 때는 없던 확인 항목이 추가되는 셈입니다.
activate_all_roles_on_login을 켜는 것으로 끝내지 않는 이유
매번 기본 역할을 지정하는 대신 이 변수를 켜면 로그인 시 부여된 역할이 모두 활성화됩니다. 하지만 이 설정은 서버 전역에 적용되고, 일부 계정에만 적용하는 방법이 없습니다. 조회용 역할과 관리용 역할을 함께 받은 계정이라면 평소에도 관리 권한까지 켜진 채로 접속하게 되어, 역할을 나눠 둔 의미가 약해집니다. 이번 테스트에서는 이 값을 켠 상태를 직접 확인하지 않았으므로, 켰을 때의 정확한 동작은 별도로 검증한 뒤에 판단할 대상으로 남겨 둡니다.
계정과 역할을 정리할 때도 시점 문제가 남는다
운영에서 퇴사자나 사용하지 않는 계정은 바로 삭제하지 않습니다. 먼저 락을 걸고, 일정 기간 동안 서비스 영향도를 확인한 뒤 삭제합니다. 그 계정을 누가 아직 쓰고 있는지 확인할 시간이 필요하기 때문입니다. 역할도 같은 관점으로 봐야 합니다. 이번 결과에서 역할의 활성 여부는 로그인 시점에 정해졌습니다. 그렇다면 역할을 회수하거나 지웠을 때 이미 접속해 있는 세션이 언제 영향을 받는지도 따로 확인해야 합니다. 이 부분은 이번 테스트 범위에 포함하지 않았습니다.
접속 ID 19에서 23까지 다시 따라가 보면
접속 ID 19에서는 권한 정의를 하나도 바꾸지 않았는데 SET ROLE 한 줄로 조회가 풀렸습니다. 이것은 권한 정의에는 처음부터 문제가 없었다는 뜻입니다. 그런데 접속 ID 21에서는 같은 계정이 다시 막혔고, 접속 ID 23에서 기본 역할을 지정한 뒤에야 로그인 직후부터 권한이 유지됐습니다. 역할을 부여한 계정에서 1142 오류가 보이면 GRANT 문을 다시 실행하기 전에 그 세션의 CURRENT_ROLE()부터 확인하고, 해결책은 SET ROLE이 아니라 SET DEFAULT ROLE 쪽에서 찾는 것이 맞다는 판단입니다.




댓글 0
첫 댓글을 남겨보세요.