계정·역할과 최소 권한

DB 제약이 올바른 데이터의 모양을 지킨다면 권한은 누가 어떤 작업을 요청할 수 있는지 제한합니다. 로그인에 성공한 계정이 모든 테이블을 읽고 바꿀 수 있다는 뜻은 아닙니다. 인증과 권한 부여를 분리합니다.

계정·역할·작업의 세 층

MySQL 계정은 사용자 이름과 접속 출처 host의 조합입니다. 'cs_reader_user'@'localhost'와 같은 이름의 다른 host 계정은 별개일 수 있습니다. USER()는 연결이 제시한 사용자/출처, CURRENT_USER()는 실제 인증에 사용된 계정을 확인하는 데 쓰입니다.

역할은 필요한 권한의 묶음입니다. 역할을 계정에 부여하는 것과 현재 연결에서 활성화하는 것을 구분합니다. 기본 역할은 로그인 때 활성화할 역할을 정하고 SET ROLE은 현재 세션을 변경합니다. 계정에 직접 부여된 권한·mandatory role 등 다른 경로도 있으므로 SET ROLE NONE을 모든 권한 제거 명령이라고 생각하지 않습니다.

여기서는 학습 전용 서버의 관리자 연결과 독립 reader 연결을 사용합니다. 아래 이름과 비밀번호는 공개된 실습 값이며 실제 서비스에 재사용하지 않습니다. 관리자 역할은 객체를 준비하는 단계에만 쓰고 애플리케이션의 상시 계정으로 쓰지 않습니다.

관리자 연결에서 fixture 준비

먼저 MySQL 실행 환경의 학습 전용 서버를 준비합니다. 이 절의 SQL 창 두 개를 관리자 A, reader B로 구별해 둡니다. 둘은 같은 서버에 연결하고 사용자만 달라야 합니다. A에서 SELECT VERSION(),@@hostname,@@port,@@socket,CURRENT_USER();를 실행해 서버와 계정을 기록합니다. 아래 CREATE USER/ROLE·GRANT를 실행할 관리자 권한도 필요합니다. 접속 실패를 권한 거절 1142와 혼동하지 않습니다.

터미널 접속과 SQL 입력을 나누기

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

관리자와 reader는 아래에서 환경에 맞는 같은 전송 방법 하나를 선택합니다. 호스트·포트·socket/pipe 이름은 실행 환경에서 확인한 값을 사용합니다.

TCP의 3306은 일반적인 기본값입니다. 이번 교재의 자동 격리 실행은 TCP 포트를 열지 않고 새 named pipe만 사용합니다. 이미 있는 서비스의 포트·데이터 경로나 계정을 바꾸지 않습니다. root의 접속 방식과 관리자 계정 이름은 설치 방법에 따라 다르므로 준비된 관리 계정으로 대체할 수 있습니다.

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

MySQL 8.4 기본 caching_sha2_password는 TLS·Unix socket·shared memory의 보안 연결을 사용할 수 있습니다. TLS 없는 TCP와 named pipe에서는 비밀번호 교환에 RSA가 필요합니다. 서버는 공개키를 클라이언트에 자동으로 보내지 않으므로 첫 인증에 ERROR 2061 ... Authentication requires secure connection이 날 수 있습니다. 이것은 SELECT 권한이 없다는 뜻이 아닙니다. 같은 환경에서 뒤의 연결이 캐시 때문에 우연히 성공하더라도 준비 조건을 생략하지 않습니다.

Windows named pipe의 reader 예시는 다음과 같습니다. 관리자에게 받은 해당 학습 서버의 신뢰할 수 있는 공개키 경로를 지정합니다. 서버의 private key나 비밀번호를 문서에 복사할 필요는 없습니다.

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 인증과 전송 조건

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';

이 문장은 실습 계정/역할을 다시 만들므로 이름이 충돌하지 않는 전용 환경에서만 실행합니다. 테이블 SELECT만 부여했으며 INSERT·DELETE·GRANT OPTION은 부여하지 않았습니다. 객체 작성자가 언제나 모든 권한을 자동으로 소유한다는 다른 DBMS의 설명을 MySQL에 적용하지 않습니다.

reader 연결에서 허용과 거절

위에서 고른 전송 방법으로 reader B로 별도로 로그인하고 다음을 실행합니다. CURRENT_USER()가 fixture의 reader 계정인지, CURRENT_ROLE()에 reader 역할이 활성화되어 있는지 먼저 봅니다. 관리자 A를 그대로 사용하면 최소 권한 검사가 되지 않습니다. 접속이 안 되면 전송/인증을, 접속 뒤 오류 1142라면 계정·역할·권한을 확인합니다.

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

조회는 두 행을 반환해야 합니다. 다음 INSERT는 별도 실행합니다.

INSERT INTO cs_db_lab.cs_priv_posts VALUES(3,'거절되어야 함');
-- 예상: ERROR 1142, INSERT command denied

같은 reader 연결에서 역할을 비활성화한 뒤 다시 조회합니다.

SET ROLE NONE;
SELECT CURRENT_ROLE();
SELECT id,title FROM cs_db_lab.cs_priv_posts;
-- 이 fixture의 다른 권한 경로가 없다면 SELECT도 ERROR 1142

거절 확인 후 SET ROLE 'cs_reader_role';로 활성화하면 읽기 두 행이 다시 가능해야 합니다. 예상 거절은 실습의 성공 조건이므로 성공 SQL과 섞어 오류를 무시하는 옵션으로 한 번에 실행하지 않습니다. MySQL 역할 사용

권한 회수와 전달을 구분하기

먼저 관리자 연결에서 역할의 SELECT를 회수합니다.

REVOKE SELECT ON cs_db_lab.cs_priv_posts FROM 'cs_reader_role';

적용 시점은 권한 범위와 세션 동작을 함께 확인합니다. 현재 reader의 재조회 결과를 관찰하고, reader로 새로 로그인한 연결에서도 다음 SELECT를 별도 실행해 오류 1142를 확인합니다. 역할 이름이 활성화돼 있어도 그 역할에서 SELECT를 회수했다면 읽기 권한이 생기지 않습니다.

SELECT id,title FROM cs_db_lab.cs_priv_posts;
-- 예상: ERROR 1142, SELECT command denied

다음 값 바인딩 실습을 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;

이 fixture에서는 id 1·2 두 행이 다시 보여야 합니다. 쓰기 권한은 새로 부여하지 않았으므로 앞의 INSERT는 여전히 거절되어야 합니다.

WITH GRANT OPTION은 데이터를 읽는 권한을 넘어 다른 계정에 권한을 전달하는 책임을 줍니다. 예제에 필요하지 않으므로 부여하지 않습니다. 최소 권한은 업무에 필요한 명령·객체·접속 출처를 좁혀 정하고, 성공 경로와 금지 경로를 모두 검사하는 과정입니다.

내장 SQL에서 값 바인딩으로

내장 SQL(embedded SQL)은 호스트 언어와 SQL을 연결하는 방식을 설명합니다. 현대 애플리케이션에서도 중요한 원리는 코드인 SQL 구조와 입력 값의 분리입니다. 문자열을 이어 붙여 입력을 SQL 조건으로 해석하게 만들지 않습니다.

위에서 권한을 복구하고 두 행을 확인한 새 reader 연결에서 다음을 실행합니다. 관리자 연결로 바꿔 문제를 숨기지 않고, 같은 최소 권한 안에서 서버 측 준비문의 값 바인딩을 관찰합니다.

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 2 한 행, 두 번째는 이 제목과 정확히 같은 행이 없어 0행입니다. 따옴표를 포함한 입력도 값으로 비교됩니다. 준비문 매개변수는 열 이름이나 ORDER BY 방향을 대신하는 만능 자리표시자가 아닙니다. 동적인 식별자는 별도 허용 목록으로 선택합니다. MySQL 준비문

독립 문제

reader에게 제목 수정만 허용해야 한다면 테이블 전체 UPDATE와 열별 UPDATE 중 무엇을 고를까요? 역할 SELECT를 회수했는데 계속 읽힌다면 어떤 다른 권한 경로를 확인해야 하나요? 값 바인딩이 있는데도 관리자 DB 계정으로 서비스에 접속하면 남는 문제는 무엇인가요?

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