[DB] SQL SECURITY of PROCEDURE (DEFINER | INVOKER) (feat. mariadb | mysql)

SQL SECURITY란?

프로시저나 함수를 실행할 때 사용되는 보안 컨텍스트를 정의하는 것입니다. 이 컨텍스트는 기본적으로 두 가지 옵션, DEFINER와 INVOKER 중 하나를 선택하여 설정할 수 있습니다.

1. DEFINER

DEFINER 옵션은 프로시저나 함수가 그것을 생성한 사용자의 권한으로 실행됨을 의미합니다. 예를 들어, admin이라는 사용자가 어떤 테이블에 대한 SELECT 권한만을 가진 userA를 위해 프로시저를 생성했다고 가정해 봅시다. 이 프로시저는 테이블에서 데이터를 수정하는 작업을 수행합니다. 프로시저가 SQL SECURITY DEFINER로 설정되면 userA가 프로시저를 호출할 때도 admin의 권한으로 실행되므로 데이터 수정이 가능합니다.

★ 특정 사용자에게 직접적인 권한 부여 없이도 특정 작업을 수행할 수 있게 해줍니다.

2. INVOKER

INVOKER 옵션은 프로시저나 함수가 그것을 호출한 사용자의 권한으로 실행됨을 의미합니다. 위의 예제에서, 프로시저가 SQL SECURITY INVOKER로 설정되었다면, userA가 해당 프로시저를 호출할 때는 userA의 권한으로 실행됩니다. 따라서, userA에게 데이터 수정 권한이 없다면 프로시저 내에서의 데이터 수정도 불가능합니다.

 

★ 호출한 사용자의 권한에 따라 제한됩니다.

3. 사용 문법

프로시저를 생성할 때 SQL SECURITY를 지정하는 방법은 다음과 같습니다:

CREATE PROCEDURE UpdateData()
SQL SECURITY DEFINER  -- 또는 SQL SECURITY INVOKER
BEGIN
    -- 여기에 로직 작성
END;
            

4. 권한 예제

권한

SHOW GRANTS FOR 'admin'@'%';
GRANT ALL PRIVILEGES ON *.* TO `admin`@`%` IDENTIFIED BY PASSWORD '*abcd' WITH GRANT OPTION

SHOW GRANTS FOR 'test_user'@'%';
GRANT USAGE ON *.* TO `test_user`@`%` IDENTIFIED BY PASSWORD '*abcd'
GRANT SELECT ON `test`.* TO `test_user`@`%` WITH GRANT OPTION

프로시저

DELIMITER //
CREATE PROCEDURE INSERT_EMPLOYEE(IN employee_name VARCHER)
BEGIN
    INSERT INTO employees (name) value (employee_name);
END //
DELIMITER ;

실행가능 여부

프로시저 생성자 | 옵션 실행자	ADMIN | DEFINER	ADMIN | INVOKER	TEST_USER | DEFINER	TEST_USER | INVOKER
ADMIN	O	O	X	O
TEST_USER	O	X	X	X

 

요약

프로시저 실행 권한과는 별도임

DEFINER : 프로시저 만든 사용자의 권한에 따라 제한

INVOKER :  프로시저를 호출한 사용자의 권한에 따라 제한

 

프로시저 권한 주기

GRANT EXECUTE ON PROCEDURE test_db.INSERT_EMPLOYEE TO 'test_user'@'%';