[MySQL/Stored Procedure] 접근 제어 설정 (SQL SECURITY)

MySQL의 Stored Procedure는, 실행한 user의 privilege 또는 Stored Procedure를 생성한 user의 privilege에 영향을 받습니다.

물론 개발자가 선택할 수 있는데, SQL SECURITY 특성 값에 따라 좌우되죠.

SQL SECURITY DEFINER : 생성한 user의 권한을 따름

SQL SECURITY INVOKER : 실행한 user의 권한을 따름

SP에서 이 구문을 생략하면 DEFINER가 기본값이기 때문에 SP를 생성할 때 사용한 user의 권한으로 SP가 실행됩니다.

MS-SQL에서는 SP를 호출한 계정의 권한이 사용되는 것과 대조를 이루는데요.

아마도 EXECUTE 권한의 차이에서 비롯된 것 같습니다.

MS-SQL에서는 SP에 대한 EXECUTE 권한만 있으면 - 동적 쿼리 같은 예외가 있긴 하지만 - SP 안에 사용된 각각의 Object에 대한 별도의 권한 - SELECT, UPDATE, DELETE와 같은 것들 - 이 필요하지 않죠.

하지만, MySQL의 경우 EXECUTE 권한이란.. 단지 SP가 존재하는지 여부를 확인하는 정도의 권한에 불과해서, SP가 정상적으로 실행되려면 그 안에 포함된 각각의 Object에 대한 권한을 별도로 가지고 있어야 합니다.

이렇게되면 사실상 INVOKER가 SP 외에 다른 Object에 권한을 가져야하기 때문에, 관리도 어렵고 보안적 실효성에도 의문이 생깁니다.

그.래.서.. 다른 특별한 이유가 없다면 아래의 방식을 권장합니다.

• SQL SECURITY DEFINER 를 사용한다.
  
  
• DEFINER는 충분한 권한을 가진 계정을 사용한다.
• SP 실행에 사용할 user를 별도로 생성하여 DEFINER로 직접 지정하거나, All Privileges 를 가진 관리자 계정을 직접 지정 사용한다.
• 또는 DEFINER로 특정 user를 지정하지 않고, DEFINER=CURRENT_USER() 라고 작성한다.
• 이 방법은 일장 일단이 있는데, 장점은 인스턴스마다 권한 있는 user의 이름이 다른 경우에도 따로 코드 수정이 필요 없다는 점, 단점으로 계정의 권한이 충분하지 않은 user로 SP를 배포하면, SP 실행 시 에러가 발생할 수 있다는 점이다.
  
  
• DB에 접근하여 SP를 실행하는 미들웨어는 Stored Procedure에 대한 Execute Privilege 만을 가진 계정을 사용한다.

CREATE DEFINER=CURRENT_USER() PROCEDURE `usp_add_shipping_address_for_campaign`(
      IN pi_int_marketing_event_id int UNSIGNED -- 캠페인 ID
    , IN pi_int_social_user_id int UNSIGNED     -- 소셜 유저 ID
    , IN pi_vch_real_name varchar(50)           -- 성명
    , IN pi_vch_phone_number varchar(15)        -- 전화 번호
    , IN pi_vch_zip_code varchar(15)            -- 우편 번호
    , IN pi_vch_address_1 varchar(100)          -- 주소 1
    , IN pi_vch_address_2 varchar(50)           -- 주소 2
    , IN pi_vch_key_password varchar(64)        -- 암호화 키 생성에 사용한 패스워드
    , IN pi_dt5_now datetime(0)                 -- 현재 서버 시각
    , OUT po_int_return int                     -- 리턴 값
)
SQL SECURITY DEFINER