Stored procedure 와 Stored function 그리고 View 의 권한 처리

MySQL에서 Procedure나 Function 및 Trigger를 정의하고 호출하는 권한 방식은 조금 익숙하지 않은 방식으로 처리된다.

여기에서는 Procedure 와 Function의 권한 처리에 대해서 알아보고, Trigger는 다음 기회에 더 알아보도록 하겠다.

(Trigger의 권한 방식은 Procedure나 Function과는 조금 다르기 때문에...)


View의 생성 및 사용 권한은 Procedure나 Function과 거의 비슷하기 때문에 여기서 같이 언급하고자 한다.
(여기 내용은 주로 Routine에 기준해서 작성되었기 때문에 호출 방식이나 사용법은 View에 적용되지 않을 수 있다.)

-- // 프로시져 (Stored procedure) 생성 문장

CREATE

DEFINER = { user | CURRENT_USER } 

PROCEDURE sp_process_ranking()

SQL SECURITY { DEFINER | INVOKER }

BEGIN

  ...

  ...

END;

-- // 함수 (Stored function) 생성 문장

CREATE

DEFINER = { user | CURRENT_USER } 

FUNCTION fn_get_usercount() RETURN INT UNSIGNED

SQL SECURITY { DEFINER | INVOKER }

BEGIN

  ...

  ...

END;

CREATE or REPLACE

DEFINER = { user | CURRENT_USER } 

SQL SECURITY { DEFINER | INVOKER }

VIEW view_name

AS SELECT ...

MySQL에서는Stored procedure, Stored function, Trigger를 모두 모아서 Stored routine (Stored program)이라고 부른다.

(이하, 간략히 줄여서 Routine으로 명명함)

• DEFINERDEFINER란 해당 Routine을 정의한 사용자를 의미하며, 기본적으로 DEFINER는 해당 Routine에 대한 Ownership을 가지게 된다. 3가지 Routine 모두 DEFINER라는 구문을 명시할 수 있는데, 만약 DEFINER 구문을 명시하지 않으면 현재 Routine을 생성하는 사용자가 DEFINER로 정의된다. 만약 별도로 DEFINER를 명시하고자 하는 경우 MySQL 표준 계정 표기법대로 아래와 같이 정의해야 한다. DEFINER = 'account'@'host_name or ip_address or domain_name' SUPER 권한을 가지고 있는 유저만 자기 자신 이외의 계정을 명시할 수 있다.
• SQL SECURITYRoutine 중에서 Procedure와 Function 만 SQL SECURITY 구문을 명시할 수 있는데,
  SQL SECURITY 구문은 해당 Routine의 실행 권한을 명시할 때 사용한다.
  SQL SECURITY는 DEFINER 또는 INVOKER 둘 중에서 하나를 명시할 수 있으며,
  SQL SECURITY를 명시하지 않으면 SQL SECURITY='DEFINER' 로 기본적으로 설정된다.

Procedure와 Function의 경우에는 DEFINER와 SQL SECURITY에 의해서 Routine의 실행 권한을 결정하게 되는데,

• SQL SECURITY 가 DEFINER로 명시된 경우
이 경우, 어느 계정으로 실행하느냐에 관계없이 그 Routine의 DEFINER의 계정으로 Routine을 실행하게 된다. 마치 UNIX의 SetUID와 비슷한 개념으로 이해하면 될 것 같다. (실제 목적도 거의 비슷하다.) 예로, A 유저가 생성하고 DEFINER를 A 유저로 설정해 둔 Routine이 있다고 가정 해보자. 이 Routine을 B 유저가 실행한다 하더라도, 이 Routine이 실행되는 동안은 A 유저의 권한 컨텍스트(Context)에서 실행된다.
• SQL SECURITY가 INVOKER로 명시된 경우
  이 경우, Routine의 생성한 사용자나 DEFINER에 관계없이 현재 해당 Routine을 호출하는 사용자의 권한으로 실행하게 된다. 위 예제를 이 케이스에 맞춰 본다면, 그 Routine은 B 유저의 권한 컨텍스트(Context)로 실행된다.

어떤 케이스이든지, 해당 Routine을 실행하기 위해서는, Routine을 실행하는 계정(A유저가 될지 B유저가 될지는 DEFINER와 SQL SECURITY에 의해서 가변적임)의 사용자가 Routine이 소속된 데이터베이스의 EXECUTE 권한을 가지고 있어야 하며, Routine내의 모든 작업들에 대해서 적절한 권한(예를 들어서 테이블 SELECT 또는 INSERT 등등)들을 가지고 있어야 한다.

SQL SECURITY를 DEFINER로 설정해야 하는 경우

중요한 데이터베이스의 테이블에 접근해서 데이터를 조작하는 특수한 작업만 일반 사용자(B)에게 제공해야 하는 경우에는 DEFINER를 DBA 계정으로 설정하고, SQL SECURITY를 DEFINER로 지정하면, 일반 사용자(B)는 중요한 테이블에 대해서 Routine에서 제공하는 제한된 작업만 할 수 있게 된다.

그 이외의 경우에는 SQL SECURITY를 INVOKER를 설정하는 것이 보안적으로 안전하다.