MySQL 8.0 변경된 routine 조회 권한 - mysql.proc - SHOW_ROUTINE - information_schema

Last Updated on 12월 27, 2023 by Jade(정현호)

안녕하세요 
이번에는 포스팅에서는 생성된 Routine(Procedure, Function) 의 definer 의 일괄 변경 스크립트 작성 과정에서, 8.0에서 mysql.proc 이 없어진 것과 관련하여 알게 된 변경된 권한과 information_schema 에 대해서 확인해본 내용을 기술해보려고 합니다.      

MySQL 8 에서의 mysql.proc

MySQL 8.0 버전에서 달라진 여러 가지 중에서 mysql.proc 테이블이 remove 된 부분이 있습니다. 그래서 8.0 버전에서는 mysql.proc 에서 참조하였던 정보를 information_schema.routines 과 다른 딕셔너리를 통해 확인해야 합니다.

공동 작업 환경인 개발 환경에서는 Routine을 생성하는 계정 마다 definer 가 다르게 생성 될수 있기도 하고, 운영이나 준 운영 환경에서는 생성한 유저 삭제되거나 권한이 변경되었을 경우 문제가 발생될 수 있으므로 Procedure 나 Function 그리고 기타 PL/SQL 오브젝트에서 사용할 별도의 definer 용 유저를 생성하여 해당 유저로만 definer 를 지정하여 사용하기도 합니다

그래서 실행은 할 수 있지만 Procedure ,Function 의 속성이나 정의(Source)는 볼 수 없는 경우가 많습니다(definer가 달라서)

MySQL 5.7 버전에서는 이럴 경우 일반 사용자는 실행 권한을 부여받고 조회를 위해서는 mysql.proc 테이블의 조회권한을 받아 show create .. 나 mysql.proc 테이블에서 조회하여 사용해왔습니다. (grant select on mysql.proc to..)

MySQL 8.0 에서는 mysql.proc 테이블의 사라짐에 따라 변경사항에 대해서 몇 가지 확인해보려고 합니다.


테스트 Function 생성

IFNULL 또는 오라클의 NVL 기능을 하는 간단한 함수이고, Input Parameter 와 Returns 를 구분하기 위해서 사이즈를 다르게 생성하였습니다.

delimiter $$
CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`test_fn`(
    I_VALUE   VARCHAR(2000)
  , C_VALUE   VARCHAR(2000)
  ) RETURNS varchar(3000)
  COMMENT 'test function'
BEGIN

    DECLARE RETURNVAL  VARCHAR(3000) ;
    
    IF ISNULL(I_VALUE) THEN
        RETURN C_VALUE;
    ELSE 
        RETURN I_VALUE;
    END IF; 
        

END $$

delimiter ;

MySQL 5.7의 mysql.proc

MySQL 5.7 버전에서는 show create procedure 나 show create function 로 조회하기 위해서는 Routine 의 Definer 에 지정된 사용자 이거나 mysql.proc 테이블에 대한 select 권한이 있어야 합니다. 그렇기 때문에 일반 사용자는 mysql.proc 테이블을 조회 권한을 받아서 사용하였습니다.

5.7 버전에서는 show create.. 절을 사용하지 않고 직접 딕셔너리를 조회하여 생성된 Procedure 나 Function 구문 확인은 information_schema.routines 이나 mysql.proc 에서 확인할 수 있습니다.

information_schema.routines 를 확인하려고 한다면 먼저 Body 부분은 ROUTINE_DEFINITION 컬럼에서 확인할 수 있습니다.

mysql> select ROUTINE_DEFINITION 
     from information_schema.routines 
     where ROUTINE_NAME='test_fn'\G
*************************** 1. row ***************************
ROUTINE_DEFINITION: BEGIN

        DECLARE RETURNVAL  VARCHAR(3000) ;

        IF ISNULL(I_VALUE) THEN
                RETURN C_VALUE;
        ELSE 
                RETURN I_VALUE;
        END IF; 


END

다른 부분은 아래의 컬럼에서 확인할 수 있습니다.

• Definer 정보
     information_schema.routines.definer

• Security type
     information_schema.routines.security_type

• Input Parameter
   - 

• Comment 정보
    information_schema.routines.routine_comment

• returns 구문
  - 

• Routine Body
    information_schema.routines.routine_definition


information_schema.routines 에서 바로 구문을 확인하기 어려운 부분은 Input Parameter 와 returns 구문 두 곳입니다.

그래서 MySQL 5.7에서 show create... 구문을 사용하지 않고 별도로 조회 시 mysql.proc 를 사용하는 이유는 사용 편의성이 더 좋기 때문입니다. 아래와 같이 mysql.proc 에는 Procedure 나 Function 의 생성 구문에 대한 정보를 더 쉽게 확인할 수 있습니다.

mysql> select name,definer, 
    param_list, returns ,comment, body
    from mysql.proc
    where name='test_fn'\G
*************************** 1. row ***************************
      name: test_fn
   definer: root@localhost
param_list: 
    I_VALUE   VARCHAR(2000)
  , C_VALUE   VARCHAR(2000)
  
   returns: varchar(3000) CHARSET utf8mb4
   comment: test function
      body: BEGIN

        DECLARE RETURNVAL  VARCHAR(3000) ;

        IF ISNULL(I_VALUE) THEN
                RETURN C_VALUE;
        ELSE 
                RETURN I_VALUE;
        END IF; 


END

MySQL 8.0(~8.0.19)

먼저 MysQL 8.0에서 mysql.proc 이 존재하지 않지만 5.7 버전과 8.0 information_schema.routines 의 컬럼의 변화(추가정보 유무)는 없습니다.

mysql.proc 이 없으므로 information.schema 에서 생성된 Procedure 나 Function 의 Source 정보 조회한다면 위에서 언급한 Input Parameter 와 returns 구문 를 다른 딕셔너리 에서 확인해야 하며 information_schema.parameters 에서 파라미터 정보를 확인할 수 있습니다. (5.7 에서도 동일)

mysql> SELECT ORDINAL_POSITION, PARAMETER_MODE,
    PARAMETER_NAME, DTD_IDENTIFIER
    FROM information_schema.parameters
    WHERE specific_name = 'test_fn';

+------------------+----------------+----------------+----------------+
| ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DTD_IDENTIFIER |
+------------------+----------------+----------------+----------------+
|                0 | NULL           | NULL           | varchar(3000)  | <-- returns
|                1 | IN             | I_VALUE        | varchar(2000)  | <-- input
|                2 | IN             | C_VALUE        | varchar(2000)  | <-- input
+------------------+----------------+----------------+----------------+

ORDINAL_POSITION 이 0 은 returns 절 이며, 1 번 부터 Input parameter 가 됩니다.

이 정보를 이용해서 조금 더 포맷팅을 변경하여 아래와 같이 조회해볼 수 있습니다( sql_mode의 변경이 필요)

-- sql_mode 를 먼저 변경
mysql> set session sql_mode='pipes_as_concat';


mysql> SELECT 
-- routine_name, routine_body,
convert(concat('(',(
    SELECT group_concat(parameter_name || ' ' || dtd_identifier)
      FROM information_schema.parameters p1
      WHERE p1.specific_name = outertable.routine_name
      AND p1.ordinal_position > 0),')',' returns ',
      (SELECT concat(DTD_IDENTIFIER,' CHARSET ',CHARACTER_SET_NAME)
      from information_schema.parameters p2
      WHERE p2.specific_name = outertable.routine_name
       AND p2.ordinal_position=0)) using utf8)
       AS param_returns_list
FROM information_schema.routines outertable
WHERE routine_name = 'test_fn';

+-------------------------------------------------------------------------------------+
| param_returns_list                                                                  |
+-------------------------------------------------------------------------------------+
| (I_VALUE varchar(2000),C_VALUE varchar(2000)) returns varchar(3000) CHARSET utf8mb4 |
+-------------------------------------------------------------------------------------+


-- sql_mode default 로 원복
mysql> set session sql_mode=default;

mysql.proc 를 통해 조회하는 것보다는 불편하지만 그래도 information_schema 에서 조회는 할 수 있습니다. 

일반 사용자가 show create .. 구문을 이용하거나 information_schema 를 통해서 조회를 하기 위해서 사용되는 information_schema.routines 를 조회하기 위해서 select  권한을 부여하고자 할 수 있지만 information_schema.routines 는 mysql.proc 과 달리 별도로 권한 부여가 불가능 합니다.

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

그래서 MySQL 8.0.19 버전까지는 일반 유저가 다른 definer 의 오브젝트 Source 내용을 확인하기 위해서는 Global Select Privileges 가 필요 하였습니다.

mysql> grant select on *.* to '유저'@'host';

일부 사이트에서도 이런 부분에 대해서 show create .. 로 속성과 정의(Source) 를 보기 위해서 너무 넓은 범위의 권한이 필요함을 언급하였습니다.

MySQL 8.0 Manual [L]

Prior to MySQL 8.0.20, for a user to access definitions of routines the user did not define, the user must have the global SELECT privilege, which is very broad. As of 8.0.20

MySQL 8.0(8.0.20~)

MysQL 8.0.20 에서는 그래서 SHOW_ROUTINE 이라는 권한이 새로 추가가 되었습니다.

사용자가 Routine의 Definer 로 지정되지 않았을 경우에도 Routine 의 정의 및 속성을 엑세스 할 수 있는 권한입니다.

해당 권한에는 아래의 딕셔너리 테이블의 접근 권한이 포함되어 있습니다.

The contents of the INFORMATION_SCHEMA.ROUTINES table.
The SHOW CREATE FUNCTION and SHOW CREATE PROCEDURE statements.
The SHOW FUNCTION CODE and SHOW PROCEDURE CODE statements.
The SHOW FUNCTION STATUS and SHOW PROCEDURE STATUS statements.
       

mysql> GRANT SHOW_ROUTINE ON *.*  TO '유저명'@'호스트';

그래서 아래 메뉴얼의 내용과 같이 SHOW_ROUTINE 권한은 Routine 정보에 대한 엑세스를 허용하면서 제한된 범위의 권한을 부여할 수 있게 됩니다.

SHOW_ROUTINE may be granted instead as a privilege with a more restricted scope that permits access to routine definitions. (That is, an administrator can rescind globalSELECT from users that do not otherwise require it and grant SHOW_ROUTINE instead.) This enables an account to back up stored routines without requiring a broad privilege.

정리

내용을 정리하면 8.0에서 Routine 의 속성과 정의에 대한 조회 권한을 일반 유저에게 부여 시 버전에 따라(8.0.20 이상 인지 여부) 부여해야 하는 권한의 범위와 종류가 달리 지게 됩니다.

그리고 MySQL 5.7버전에서 mysql.proc 통해서 Routine 의 내용을 백업 하거나 별도의 Script 에서 참조/사용하였던 부분은 information_schema 를 통해 참조하는 것으로 내용 변경을 정리하면 될 것 같습니다.
                   

Reference

Reference Link

dev.mysql.com/#priv_show-routine [Link]
ocelot.ca/no-more-mysql-proc-in-mysql-8-0 [Link]


관련된 다른 글

INFORMATION_SCHEMA.PROCESSLIST Deprecated in version 8.0.35 or 8.2.0

포스팅에는 8.0.35,8.2.0에서 INFORMATION_SCHEMA.PROCESSLIST 기능의 Deprecated 된 내용에 대해서 간략하게 살펴보도록 하겠습니다.

MySQL - 정상 수행되던 쿼리가 IN 절에서 Index Scan 을 하지 않을 경우 Table Full Scan

테이블에 인덱스도 구성이 잘 되어있고, 쿼리도 인덱스를 사용할 수 있는 WHERE 조건의 쿼리인데 분명히 이전에는 Index Scan 으로 수행된 쿼리가 갑자기 Table Full Scan 으로 수행이 된다 였습니다.

MySQL - Block Nested-Loop and Batched Key Access Joins

MySQL 에서 Block Nested-Loop 와 Batched Key Access Joins 그리고 Multi Range Read(MRR) 을 도입 및 추가한 이유로는 쉽게 Join Method 인 Nested Loop 조인의 한계점을 보완하고 개선하기 위해서 입니다.

MySQL EXPLAIN ANALYZE - 8.0.18

MySQL 8.0.18 버전은 2019년 10월 14일에 GA 가 되었으며 8.0.18 의 New Feature 로 EXPLAIN ANALYZE 기능이 추가 되었습니다.

MySQL 8.0 invisible columns

MySQL 은 MySQL 8.0.23부터 invisible columns을 지원합니다 invisible columns은 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 참조되는 경우 액세스 할 수 있습니다.

MySQL 파티션 추가 삭제 - Error Code: 1493 - 파티션 테이블 - REORGANIZE PARTITION

월별로 되어있는 range 파티션에 파티션 추가 관련하여 maxvalue 유무, 앞에 추가 또는 뒤에 추가에 따른 명령어 사용 등을 확인 해보도록 하겠습니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io