MySQL Procedure definer 변경

1. 사용자 권한이슈

MySQL 5.7 버전 ALTER PROCEDURE 문은 DEFINER를 수정을 지원하지 않는다.
ALTER PROCEDURE문은 프로시저의 특성을 변경하기 위해 사용되는데, SQL SECURITY, COMMENT를 변경할 때만 사용되고, 프로시저의 본문이나 DEFINER 수정에 사용 할 수 없다.

이처럼 DEFINER, 본문 수정을 위해선 DROP 후 CREATE 하는 방식을 사용해야 하는데,
실행권한이 부여된 계정은 다음과 같이 해당 프로시저가 삭제되면 권한을 잃게 된다.

mysql ((none))>SHOW GRANTS FOR 'proc_test'@'%';
+-------------------------------------------------------------------+
| Grants for proc_test@%                                            |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'proc_test'@'%'                             |
| GRANT EXECUTE ON PROCEDURE `test`.`proc_test1` TO 'proc_test'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql ((none))>drop procedure `test`.`proc_test1`;
Query OK, 0 rows affected (0.00 sec)

mysql ((none))>SHOW GRANTS FOR 'proc_test'@'%';
+---------------------------------------+
| Grants for proc_test@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'proc_test'@'%' |
+---------------------------------------+
1 row in set (0.00 sec)

2. 메타데이터 테이블을 사용한 업데이트

mysql 스키마는 MySQL 서버의 메타데이터를 저장하는 여러 테이블을 포함하고 있는데, 사용자 계정, 권한, 플러그인, 프로시저 및 함수 등에 대한 정보를 담고 있다.
그중 mysql.proc 테이블은 'FUNCTION','PROCEDURE' 타입의 메터데이터를 저장하며, 다음과 같이 DEFINER 컬럼을 업데이트 해 DROP 없이 변경 할 수 있다.

mysql ((none))>SHOW GRANTS FOR 'proc_test'@'%';
+-------------------------------------------------------------------+
| Grants for proc_test@%                                            |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'proc_test'@'%'                             |
| GRANT EXECUTE ON PROCEDURE `test`.`proc_test1` TO 'proc_test'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql ((none))>UPDATE mysql.proc
    -> SET DEFINER = 'minstone@%'
    -> WHERE 1 =1
    -> AND NAME  = 'proc_test1'
    -> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql ((none))>SHOW GRANTS FOR 'proc_test'@'%';
+-------------------------------------------------------------------+
| Grants for proc_test@%                                            |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'proc_test'@'%'                             |
| GRANT EXECUTE ON PROCEDURE `test`.`proc_test1` TO 'proc_test'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

트리거의 경우엔 MySQL 5.7버전에 별도의 메타데이터 테이블이 없기에, DEFINER 변경이 필요하다면 DROP, CREATE를 수행해야 한다.