계정·역할과 최소 권한

데이터베이스의 제약이 올바른 데이터의 모양을 지킨다면, 권한은 누가 어떤 작업을 요청할 수 있는지를 제한합니다. 로그인에 성공한 계정이라고 모든 테이블을 읽고 바꿀 수 있다는 뜻은 아니므로, 인증(authentication)과 권한 부여(authorization)를 나누어 생각합니다.

이 글에서는 학습 전용 서버에 관리자 연결과 읽기 전용 연결을 따로 열고, 읽기 역할만 받은 계정이 무엇을 할 수 있고 무엇을 거부당하는지 확인합니다. 아래의 계정 이름과 비밀번호는 공개된 실습용 값이므로 실제 서비스에 다시 쓰지 않으며, 관리자 계정은 객체를 준비하는 단계에만 쓰고 애플리케이션이 늘 쓰는 계정으로 쓰지 않습니다.

계정과 역할, 작업

MySQL의 계정은 사용자 이름과 접속하는 출처의 호스트를 합친 것입니다. 'cs_reader_user'@'localhost'와, 이름은 같지만 호스트가 다른 계정은 서로 다른 계정일 수 있습니다. USER()는 연결이 내민 사용자와 출처를, CURRENT_USER()는 실제 인증에 쓰인 계정을 확인하는 데 씁니다.

역할(role)은 필요한 권한의 묶음입니다. 역할을 계정에 부여하는 것과 지금의 연결에서 활성화하는 것은 다르며, 기본 역할은 로그인할 때 활성화할 역할을 정하고 SET ROLE은 지금 세션의 활성 역할을 바꿉니다. 계정에 직접 부여한 권한이나 모든 계정에 강제되는 역할 같은 다른 경로도 있으므로, SET ROLE NONE을 모든 권한을 없애는 명령으로 생각하지 않습니다.

관리자 연결과 reader 연결

먼저 1장 2편의 학습 전용 서버를 준비하고, SQL 창 두 개를 관리자 A와 reader B로 구별해 둡니다. 둘은 같은 서버에 연결하되 사용자만 달라야 합니다. A에서 SELECT VERSION(), @@hostname, @@port, @@socket, CURRENT_USER();를 실행해 서버와 계정을 기록하고, 아래의 CREATE USER와 GRANT를 실행할 관리자 권한이 있는지도 확인합니다.

mysql ...은 운영체제의 터미널에 입력하고, SELECT ...;는 접속한 뒤 mysql> 프롬프트에 입력하며 끝에 세미콜론을 붙입니다. -p 뒤에 비밀번호를 붙이지 않으면 입력 프롬프트가 나오고, exit로 reader의 연결을 닫아도 관리자 A와 서버는 그대로 남습니다.

관리자와 reader는 아래 가운데 환경에 맞는 같은 전송 방법 하나를 고릅니다. 호스트와 포트, 소켓이나 파이프의 이름은 실행 환경에서 확인한 값을 씁니다.

TCP의 3306은 흔히 쓰는 기본값입니다. 이미 운영 중인 서비스의 포트와 데이터 경로, 계정은 바꾸지 않으며, root의 접속 방식과 관리자 계정의 이름은 설치 방법에 따라 다르므로 준비된 관리 계정으로 대신할 수 있습니다.

첫 인증의 오류 2061을 해석하기

MySQL 8.4의 기본 인증 방식인 caching_sha2_password는 TLS나 Unix 소켓, 공유 메모리처럼 안전한 연결에서 비밀번호를 주고받습니다. TLS가 연결을 암호화하고 서버의 신원을 확인하는 과정은 네트워크의 TLS 핸드셰이크에서 다룹니다. TLS가 없는 TCP와 named pipe에서는 비밀번호의 교환에 RSA가 필요한데, 서버가 공개키를 클라이언트에 저절로 보내 주지 않으므로 첫 인증에서 ERROR 2061 ... Authentication requires secure connection이 날 수 있습니다. 이것은 SELECT 권한이 없다는 뜻이 아닙니다. 같은 환경에서 뒤의 연결이 캐시 덕분에 우연히 성공하더라도, 준비할 조건을 생략하지 않습니다.

Windows named pipe에서 reader로 접속하는 예는 다음과 같습니다. 관리자에게서 받은, 그 학습 서버의 신뢰할 수 있는 공개키의 경로를 지정하며, 서버의 개인키나 비밀번호를 문서에 옮길 필요는 없습니다.

mysql --protocol=PIPE --socket=확인한파이프이름 --server-public-key-path="C:\확인한경로\public_key.pem" -u cs_reader_user -p

서버에 공개키를 요청하는 --get-server-public-key도 있지만, 그렇게 받은 키가 정말 신뢰할 서버의 것인지는 따로 확인해야 합니다. TLS로 연결했다면 RSA의 옵션은 필요 없으며, 오류를 피하려고 인증 방식을 오래된 것으로 바꾸는 단계는 이 실습에 없습니다(MySQL의 SHA-2 인증과 전송 조건).

더 깊이: 2061이 첫 인증에서만 나는 이유

caching_sha2_password라는 이름의 caching은 서버 메모리의 캐시를 가리킵니다. 클라이언트가 처음 접속하면 서버는 mysql.user 시스템 테이블의 자격 증명으로 인증하고, 성공하면 계정 이름과 비밀번호 해시의 쌍을 캐시에 넣습니다. 그 뒤의 접속은 캐시와 맞춰 보는 빠른 인증을 쓰는데, 이때는 비밀번호를 평문으로 보내지 않는 질의응답(challenge-response) 방식이어서 안전한 연결도 RSA 키도 필요하지 않습니다. 그래서 같은 reader 계정이라도 한 번 TLS나 소켓으로 접속에 성공한 뒤에는, TLS 없는 TCP의 접속이 2061 없이 성공할 수 있습니다.

캐시가 비면 첫 인증의 조건이 다시 적용됩니다. 계정의 이름을 바꾸거나 계정을 지우면, 또는 비밀번호나 인증 플러그인을 바꾸면 그 계정의 항목이 지워지고, FLUSH PRIVILEGES를 실행하거나 서버를 종료하면 캐시 전체가 비워집니다. 캐시는 재시작하면 남지 않으므로, 실습 서버를 다시 시작한 뒤에는 어제 문제없던 접속에서 2061이 다시 날 수 있습니다. 계정을 만들거나 비밀번호를 바꾼 직후의 첫 접속도 같은 이유로 안전한 연결이나 RSA가 필요합니다.

인증 방식의 기본값도 버전에 따라 바뀌었습니다. MySQL 8.4의 기본 인증 플러그인은 authentication_policy가 정하며 기본은 caching_sha2_password이고, 예전 방식인 mysql_native_password는 사용 중단(deprecated) 상태입니다. 게다가 MySQL 8.4.0 릴리스 노트에 따르면 이 플러그인은 8.4.0부터 기본으로 꺼져 있어, 서버를 --mysql-native-password=ON으로 시작해야 쓸 수 있습니다. 오래된 클라이언트 때문에 예전 방식으로 돌아가려 해도 서버 설정부터 바꿔야 하므로, 클라이언트를 caching_sha2_password를 지원하는 버전으로 올리는 편이 맞습니다.

읽기 역할을 만든다

관리자 A에서 실습 테이블과 읽기 역할, reader 계정을 만듭니다. 이 문장은 실습용 계정과 역할을 다시 만들므로, 이름이 겹치지 않는 전용 환경에서만 실행합니다.

CREATE DATABASE IF NOT EXISTS cs_db_lab;
USE cs_db_lab;
DROP TABLE IF EXISTS cs_priv_posts;
CREATE TABLE cs_priv_posts(id INT PRIMARY KEY,title VARCHAR(80) NOT NULL) ENGINE=InnoDB;
INSERT INTO cs_priv_posts VALUES(1,'관계 모델'),(2,'권한 실습');
DROP USER IF EXISTS 'cs_reader_user'@'localhost';
DROP ROLE IF EXISTS 'cs_reader_role';
CREATE ROLE 'cs_reader_role';
GRANT SELECT ON cs_db_lab.cs_priv_posts TO 'cs_reader_role';
CREATE USER 'cs_reader_user'@'localhost' IDENTIFIED BY 'CS-lab-local-Only_84!';
GRANT 'cs_reader_role' TO 'cs_reader_user'@'localhost';
SET DEFAULT ROLE 'cs_reader_role' TO 'cs_reader_user'@'localhost';
SHOW GRANTS FOR 'cs_reader_role';
SHOW GRANTS FOR 'cs_reader_user'@'localhost';

Grants for cs_reader_role@%
GRANT USAGE ON *.* TO `cs_reader_role`@`%`
GRANT SELECT ON `cs_db_lab`.`cs_priv_posts` TO `cs_reader_role`@`%`

Grants for cs_reader_user@localhost
GRANT USAGE ON *.* TO `cs_reader_user`@`localhost`
GRANT `cs_reader_role`@`%` TO `cs_reader_user`@`localhost`

호스트 없이 만든 역할은 호스트가 %인 역할이 됩니다. USAGE는 아무 권한도 없다는 뜻이고, 역할에는 테이블 하나의 SELECT만, 계정에는 그 역할만 부여되었습니다. INSERT와 DELETE, 다른 계정에 권한을 넘길 수 있는 GRANT OPTION은 부여하지 않았습니다. 객체를 만든 사람이 언제나 모든 권한을 저절로 갖는다는 다른 DBMS의 설명을 MySQL에 그대로 적용하지 않습니다.

reader 연결에서 허용과 거부를 확인한다

고른 전송 방법으로 reader B에 따로 로그인한 뒤 다음을 실행합니다. 관리자 A를 그대로 쓰면 최소 권한을 확인한 것이 되지 않으므로, 먼저 CURRENT_USER()가 reader 계정인지와 CURRENT_ROLE()에 읽기 역할이 활성화되었는지 봅니다. 접속 자체가 안 되면 전송과 인증을, 접속한 뒤 1142가 나면 계정과 역할, 권한을 확인합니다.

SELECT USER(),CURRENT_USER(),CURRENT_ROLE();
SELECT id,title FROM cs_db_lab.cs_priv_posts ORDER BY id;

USER()                   | CURRENT_USER()           | CURRENT_ROLE()
cs_reader_user@localhost | cs_reader_user@localhost | `cs_reader_role`@`%`

id | title
1  | 관계 모델
2  | 권한 실습

기본 역할이 로그인과 함께 활성화되어 두 행을 읽었습니다. 이어서 쓰기를 따로 실행합니다.

INSERT INTO cs_db_lab.cs_priv_posts VALUES(3,'거절되어야 함');

ERROR 1142 (42000): INSERT command denied to user 'cs_reader_user'@'localhost' for table ...

INSERT 권한이 없으므로 오류 1142로 거부됩니다. 메시지 끝의 테이블 이름 표기는 버전에 따라 다를 수 있어 줄였습니다. 이어서 같은 reader 연결에서 역할을 비활성화하고 다시 조회합니다.

SET ROLE NONE;
SELECT CURRENT_ROLE();
SELECT id,title FROM cs_db_lab.cs_priv_posts;

CURRENT_ROLE()은 NONE을 돌려주고, 이 계정에는 다른 권한의 경로가 없으므로 SELECT도 1142(SELECT command denied)로 거부됩니다. SET ROLE 'cs_reader_role';로 다시 활성화하면 두 행을 다시 읽을 수 있습니다. 예상한 거부는 이 실습이 성공했다는 조건이므로, 오류를 무시하는 옵션으로 성공하는 SQL과 한꺼번에 실행하지 않습니다(MySQL의 역할 사용).

권한의 회수와 전달

관리자 연결에서 역할의 SELECT를 회수합니다. REVOKE는 GRANT로 준 권한을 같은 대상에서 거두는 문장입니다. 여기서는 사용자 계정이 아니라 역할 cs_reader_role에서 회수하므로, 이 역할을 받아 쓰는 모든 계정이 함께 영향을 받습니다.

REVOKE SELECT ON cs_db_lab.cs_priv_posts FROM 'cs_reader_role';

테이블과 열 단위의 권한 변경은 이미 열린 연결에도 그 클라이언트의 다음 문장부터 적용됩니다. 지금의 reader 연결과 새로 로그인한 reader 연결에서 각각 다음 SELECT를 실행해, 두 곳 모두 거부되는지 확인합니다. 역할이 활성화되어 있어도 그 역할에서 SELECT를 회수했다면 읽을 권한이 생기지 않습니다.

SELECT id,title FROM cs_db_lab.cs_priv_posts;

ERROR 1142 (42000): SELECT command denied to user 'cs_reader_user'@'localhost' for table ...

다음의 값 바인딩 실습을 reader로 이어 가려고, 관리자 연결에서 SELECT를 다시 부여합니다. 계정을 DROP하는 것과 권한 하나를 회수하고 되돌리는 것은 서로 다른 작업입니다.

GRANT SELECT ON cs_db_lab.cs_priv_posts TO 'cs_reader_role';

다시 reader로 새 연결을 열고, 활성 역할과 두 행의 조회를 확인한 뒤에만 다음 실습으로 넘어갑니다. SET ROLE은 역할을 활성화할 뿐, 관리자가 회수한 SELECT를 스스로 다시 부여하는 명령이 아닙니다.

SET ROLE 'cs_reader_role';
SELECT CURRENT_USER(),CURRENT_ROLE();
SELECT id,title FROM cs_db_lab.cs_priv_posts ORDER BY id;

id 1과 2의 두 행이 다시 보이며, 쓰기 권한은 새로 부여하지 않았으므로 앞의 INSERT는 여전히 거부됩니다. WITH GRANT OPTION은 데이터를 읽는 권한을 넘어 다른 계정에 권한을 넘겨줄 수 있게 하므로, 이 예제처럼 필요하지 않다면 부여하지 않습니다. 최소 권한(least privilege)은 업무에 필요한 명령과 객체, 접속 출처를 좁혀 정하고, 성공하는 경로와 금지된 경로를 모두 확인하는 과정입니다.

내장 SQL에서 값 바인딩으로

내장 SQL(embedded SQL)은 호스트 언어와 SQL을 잇는 방식을 설명하는 개념입니다. 지금의 애플리케이션에서도 중요한 원리는, 코드인 SQL의 구조와 입력된 값을 나누는 것입니다. 문자열을 이어 붙여 입력이 SQL의 조건으로 해석되게 만들지 않습니다.

권한을 되살리고 두 행을 확인한 새 reader 연결에서 다음을 실행합니다. 관리자 연결로 바꾸어 문제를 감추지 말고, 같은 최소 권한 안에서 서버의 준비된 문장(prepared statement)이 값을 바인딩하는 모습을 봅니다.

PREPARE cs_title_lookup FROM 'SELECT id,title FROM cs_db_lab.cs_priv_posts WHERE title = ?';
SET @cs_title = '권한 실습';
EXECUTE cs_title_lookup USING @cs_title;
SET @cs_title = 'x'' OR ''1''=''1';
EXECUTE cs_title_lookup USING @cs_title;
DEALLOCATE PREPARE cs_title_lookup;

id | title
2  | 권한 실습

첫 입력은 id 2의 한 행을 찾고, 두 번째 입력인 x' OR '1'='1은 그 문자열과 정확히 같은 제목이 없으므로 빈 결과를 돌려줍니다. 따옴표가 든 입력도 SQL의 조건이 아니라 하나의 값으로 비교되었기 때문입니다. 다만 준비된 문장의 ?는 열의 이름이나 ORDER BY의 방향을 대신하는 만능 자리가 아니므로, 동적인 식별자는 허용하는 목록에서 고르는 방식으로 따로 다룹니다(MySQL의 준비된 문장).

검사의 결과에는 접속한 계정과 활성 역할, 허용된 결과와 거부된 오류를 함께 남깁니다. 성공한 조회 하나만으로 최소 권한을 확인했다고 하지 않습니다.

정리

• MySQL의 계정은 사용자 이름과 접속 출처를 합친 것이고, 역할은 권한의 묶음입니다.
• 역할은 계정에 부여하는 것과 연결에서 활성화하는 것이 다르며, CURRENT_ROLE()로 활성 역할을 확인합니다.
• 오류 2061은 전송과 인증의 문제이고, 오류 1142는 권한의 문제이므로 원인을 나누어 봅니다.
• 테이블과 열 단위의 권한 변경은 이미 열린 연결에도 다음 문장부터 적용됩니다.
• 준비된 문장은 입력을 값으로 바인딩해 SQL의 구조가 바뀌지 않게 하지만, 계정의 과도한 권한까지 줄여 주지는 않습니다.

연습 문제

1. 제목만 고칠 수 있게 하기

reader에게 게시글의 제목 수정만 허용해야 한다면, 테이블 전체의 UPDATE와 열 단위의 UPDATE 가운데 무엇을 고르나요?

2. 회수했는데도 읽힌다면

역할에서 SELECT를 회수했는데 reader가 계속 테이블을 읽을 수 있다면, 어떤 다른 권한의 경로를 확인해야 하나요?

3. 값 바인딩과 관리자 계정

모든 쿼리에서 값을 바인딩하는데도 서비스가 관리자 계정으로 데이터베이스에 접속한다면, 어떤 문제가 남나요?

다음 글 데이터 모델링 단계부터는 회원 게시판의 요구 사항을 엔티티와 관계로 옮겨, 처음부터 불가능한 구조와 이상 현상을 줄이는 설계의 단계로 넘어갑니다.