RDS MySQL 로 Procedure 이관

definer 오류

mysql 에서 rds mysql 로 이관시 발생했던 문제이다. 이전에 mysql 에서 rds mysql 로의 호환성 테스트를 했을때 미쳐 생각을 못한것이 procedure 의 definer 에 관한거 였다. definer 란 owner 정도로 생각하면 되는데 create procere 구문을 수행한 mysql 계정을 뜻한다.

DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE SP_TEST()
BEGIN
END;;
DELIMITER ;

mysql jobdb 라는 데이터베이스를 rds mysql 로 이관한다고 하자. jobdb 내에는 sp_test() 이라는 프로시져가 있고 관리 차원에서 dba 라는 계정으로 데이터베이스 및 하위 오프젝트들을 생성했었다.

mysqldump 를 통해 ddl 구문을 추출해서 rds mysql 에 적용할때 구문실행 계정과 definer 가 다르다면 다음과 같이 오류가 발생한다.

mysql> DELIMITER ;;
mysql> CREATE DEFINER=`test`@`%` PROCEDURE SP_TEST()
    -> BEGIN
    -> END;;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql> DELIMITER ;
mysql> 

하지만 구문실행 계정과 definer 가 같다면 문제는 없다.

mysql> DELIMITER ;;
mysql> CREATE DEFINER=`root`@`%` PROCEDURE SP_TEST()
    -> BEGIN
    -> END;;
Query OK, 0 rows affected (0.00 sec)

definer 와 구문 실행 계정을 다르게 해야 한다면 좀 귀찮아 진다.
workaround 를 찾아보면 sed 를 많이 사용하는거 같고 나 같은 경우는 mysqlpump 의 skip-definer 를 사용했다. mysqldump 가 아닌 pump 라는게 있다는걸 이번에 알게됨

$ mysqlpump --help | grep skip-de
                      (Defaults to on; use --skip-defer-table-indexes to disable.)
  --skip-definer      Skip DEFINER and SQL SECURITY clauses for Views and
skip-definer                      FALSE

show create procedure 결과다름

rds mysql 에서 root 계정( rds 생성시 admin 계정) 으로 procedure 를 생성하고 create procedure 를 보면 다음과 같다.

mysql> show create procedure comment_test;
+--------------+------------------------+------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure    | sql_mode               | Create Procedure                                                       | character_set_client | collation_connection | Database Collation |
+--------------+------------------------+------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| comment_test | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `comment_test`()
BEGIN

END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------------+------------------------+------------------------------------------------------------------------+----------------------+----------------------+--------------------+

이제 다른 계정으로 comment_test() 의 create procedure 를 확인해 보면

mysql> show create procedure comment_test;
+--------------+------------------------+------------------+----------------------+----------------------+--------------------+
| Procedure    | sql_mode               | Create Procedure | character_set_client | collation_connection | Database Collation |
+--------------+------------------------+------------------+----------------------+----------------------+--------------------+
| comment_test | NO_ENGINE_SUBSTITUTION | NULL             | utf8                 | utf8_general_ci      | utf8_general_ci    |
+--------------+------------------------+------------------+----------------------+----------------------+--------------------+

root 계정 때와 다른게 'Create Procedure' 항목이 NULL 이다. rds mysql 이관을 마치고 담당부서에서 연락이 왔는데 workbench 에서 alter procedure 가 안된다는 것이다.

원인을 유추해보면 rds 생성시 admin 계정을 제외하면 mysql 데이터베이스가 보이지 않는다. 즉 mysql.proc 의 select 권한도 없다는 것이다. 이 이유로 NULL 로 보이는듯 하다.

참고로 root 가 생성을 하고 다른 계정에 create, drop 권한을 줘도 마찬가지다. 그냥 처음부터 사용할 계정을 고려해서 생성을 해야 한다.

Reference

• Privileges Provided by MySQL