MySQL 데이터베이스에서 뷰(View), 저장 함수(Function), 저장 프로시저(Stored Procedure), 트리거(Trigger), 이벤트(Event)와 같은 객체를 생성할 때, DEFINER 옵션과 SQL SECURITY 속성은 중요한 역할을 합니다. 이들은 객체의 실행 권한 및 보안에 직접적인 영향을 미치므로, 이를 정확히 이해하는 것이 안정적인 데이터베이스 운영에 필수적입니다.

DEFINER 옵션과 SQL SECURITY 속성

MySQL에서 객체를 생성하는 기본 구문을 살펴보면 DEFINER와 SQL SECURITY 옵션을 확인할 수 있습니다. 예를 들어, 뷰 생성 구문은 다음과 같습니다.

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

DEFINER = user는 해당 객체를 정의한 사용자, 즉 "정의자"를 지정합니다. 만약 이 옵션을 명시적으로 지정하지 않으면, 객체를 생성한 사용자가 자동으로 정의자가 됩니다.

SQL SECURITY { DEFINER | INVOKER } 속성은 객체가 실행될 때 어떤 사용자의 권한을 사용할지 결정합니다. 두 가지 주요 모드가 있습니다:

• SQL SECURITY DEFINER: 객체를 정의한 사용자(DEFINER로 지정된 사용자)의 권한을 사용하여 객체를 실행합니다. 이 경우, 객체 실행자는 해당 객체에 대한 실행 권한만 있으면 되며, 객체가 참조하는 하위 객체(테이블 등)에 대한 직접적인 권한은 정의자가 가지고 있으면 됩니다. 이것이 기본값입니다.
• SQL SECURITY INVOKER: 객체를 호출하는 사용자(INVOKER)의 권한을 사용하여 객체를 실행합니다. 이 경우, 객체 실행자는 해당 객체에 대한 실행 권한뿐만 아니라, 객체가 참조하는 모든 하위 객체에 대한 권한도 직접 가지고 있어야 합니다.

DEFINER와 SQL SECURITY 동작 예시

다음 예시를 통해 SQL SECURITY DEFINER와 SQL SECURITY INVOKER의 차이점을 명확히 이해할 수 있습니다. 여기서는 product_info 테이블을 조회하는 두 개의 뷰를 생성하고, 권한이 제한된 사용자로 조회 시도를 합니다.

먼저, 테스트에 필요한 데이터베이스, 테이블, 사용자를 설정합니다. 이 과정은 root 권한을 가진 사용자로 진행합니다:

-- 데이터베이스 생성 및 사용
CREATE DATABASE sales_db;
USE sales_db;

-- 테스트 테이블 생성 및 데이터 삽입
CREATE TABLE product_info (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);
INSERT INTO product_info (product_id, product_name, price) VALUES
(101, 'Laptop', 1200.00),
(102, 'Mouse', 25.00),
(103, 'Keyboard', 75.00);

-- 뷰의 정의자로 사용될 사용자 생성 및 권한 부여
CREATE USER 'view_owner'@'localhost' IDENTIFIED BY 'owner_password';
-- view_owner에게 product_info 테이블에 대한 SELECT 권한을 부여합니다.
GRANT SELECT ON sales_db.product_info TO 'view_owner'@'localhost';

-- 뷰를 호출할(쿼리할) 일반 사용자 생성
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'app_password';

이제 root 사용자로 로그인하여 두 가지 다른 SQL SECURITY 속성을 가진 뷰를 생성합니다. 두 뷰 모두 DEFINER는 view_owner입니다. root 사용자는 SUPER 권한이 있으므로 DEFINER를 view_owner로 지정할 수 있습니다.

-- SQL SECURITY DEFINER 뷰 생성: 정의자(view_owner)의 권한으로 실행
CREATE DEFINER='view_owner'@'localhost' SQL SECURITY DEFINER VIEW product_view_definer AS
SELECT product_id, product_name FROM product_info;

-- SQL SECURITY INVOKER 뷰 생성: 호출자(INVOKER)의 권한으로 실행
CREATE DEFINER='view_owner'@'localhost' SQL SECURITY INVOKER VIEW product_view_invoker AS
SELECT product_id, product_name FROM product_info;

다음으로, app_user에게 이 두 뷰에 대한 SELECT 권한만 부여합니다. app_user는 product_info 테이블에 대한 직접적인 SELECT 권한은 없습니다.

-- app_user에게 뷰 조회 권한만 부여
GRANT SELECT ON sales_db.product_view_definer TO 'app_user'@'localhost';
GRANT SELECT ON sales_db.product_view_invoker TO 'app_user'@'localhost';

이제 app_user로 로그인하여 두 뷰를 조회해 봅니다:

-- app_user로 로그인
-- mysql -u app_user -p app_password sales_db

-- SQL SECURITY DEFINER 뷰 조회 시도
SELECT * FROM product_view_definer;
-- 이 쿼리는 정상적으로 작동합니다.
-- DEFINER인 'view_owner'가 product_info 테이블에 대한 SELECT 권한을 가지고 있기 때문입니다.

-- SQL SECURITY INVOKER 뷰 조회 시도
SELECT * FROM product_view_invoker;
-- 이 쿼리는 다음 오류를 발생시킵니다:
-- ERROR 1356 (HY000): View 'sales_db.product_view_invoker' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
-- INVOKER인 'app_user'가 product_info 테이블에 대한 SELECT 권한이 없기 때문입니다.

위 예시에서 볼 수 있듯이, SQL SECURITY DEFINER 뷰는 정의자(view_owner)의 권한으로 실행되므로 app_user는 product_info 테이블에 대한 직접 권한 없이도 뷰를 조회할 수 있습니다. 반면, SQL SECURITY INVOKER 뷰는 호출자(app_user)의 권한으로 실행되므로, app_user가 product_info 테이블에 대한 SELECT 권한이 없어 조회에 실패합니다.

DEFINER 관련 주요 고려사항

DEFINER는 데이터베이스 객체의 보안과 마이그레이션에 중요한 영향을 미치므로, 다음 사항들을 고려하는 것이 좋습니다:

• SUPER 권한의 중요성: root 사용자와 같이 SUPER 권한을 가진 사용자만이 DEFINER를 자신 이외의 다른 사용자로 지정하여 객체를 생성할 수 있습니다. 일반 사용자는 CREATE VIEW와 같은 객체 생성 권한이 있더라도 DEFINER를 자신으로만 지정할 수 있습니다.
• 정의자 사용자 관리: DEFINER로 지정된 사용자가 존재하지 않거나, 필요한 권한을 가지고 있지 않으면 SQL SECURITY DEFINER로 설정된 객체는 정상적으로 작동하지 않을 수 있습니다. 특히, DEFINER 사용자를 삭제하거나 권한을 변경할 때는 해당 사용자가 정의한 모든 객체에 미칠 영향을 신중하게 검토해야 합니다.
• SQL SECURITY 속성 선택:
  • 일반적으로 SQL SECURITY DEFINER를 사용하여 객체의 정의자가 필요한 모든 권한을 갖도록 하고, 호출자에게는 해당 객체에 대한 최소한의 실행 권한만 부여하는 것이 보안상 유리합니다. 이는 권한 관리를 간소화하고, 호출자가 민감한 하위 객체에 직접 접근하는 것을 막을 수 있습니다.
  • SQL SECURITY INVOKER는 호출자의 권한을 그대로 사용하므로, 호출자가 하위 객체에 대한 모든 권한을 직접 가져야 합니다. 이는 경우에 따라 더 세밀한 권한 제어가 필요할 때 사용될 수 있습니다.
• 데이터베이스 마이그레이션: 데이터베이스를 다른 환경으로 마이그레이션할 때는 DEFINER로 지정된 사용자가 새 환경에도 동일하게 존재하고, 필요한 권한을 가지고 있는지 반드시 확인해야 합니다. 마이그레이션 전에 관련 사용자 계정 및 권한을 새 환경에 먼저 설정하는 것을 권장합니다.

DEFINER 및 SQL SECURITY 속성에 대한 이해는 MySQL 데이터베이스 객체의 안정적인 운영과 보안 강화에 필수적인 요소입니다.