ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

MySQL 관련 문의사항이 들어온 내용을 정리 한 내용입니다.

내용은 이렇습니다. 유저가 함수를 생성하는데 에러가 발생한다는 것입니다. 확인한 에러는 아래와 같습니다.

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

 

해석해보면 유저에 SUPER나 SET_USER_ID 권한이 없어서 그렇다고 나오는 에러였습니다. 테스트 결과 SUPER 나 SET_USER_ID 둘 중에 아무권한이나 있으면 생성이 가능하지만, 근본적인 문제는 이게 아니였습니다. 함수의 소스를 확인 결과 CREATE DEFINER=`root`@`%` FUNCTION ... 형식으로 DEFINER를 'root'@'%'로 지정해 놓았던 것입니다. 기본적으로 함수를 생성할 때에 DEFINER를 지정하지 않고 CREATE FUNCTION ...으로 생성하면 명령어를 친 유저 소유로 생성이 됩니다. 하지만 다른 유저 소유로 생성이 될 수 있게 하는 명령어 구문이 DEFINER=유저명 입니다.

 

아래는 Mysql 8.0 Manual 에서 발췌한 CREATE FUNCTION 구문입니다.

PROCEDURE나 FUNCTION모두 DEFINER 옵션을 사용할 수 있습니다.

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

출처 : https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

MySQL :: MySQL 8.0 Reference Manual :: 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

DBA가 아닌 개발자의 입장에서 root의 소유로 함수를 생성할 필요는 없기 때문에  DEFINER 구문은 없어도 된다고 생각하였습니다. DEFINER='root'@'%'를 빼고 다시 생성해보라고 알려드리고 마무리 지을 수 있었습니다.

 

 

아래에는 해결방법에 도움을 준 사이트와 내용입니다.

 

덤프 파일을 가져오거나 스크립트를 실행하는 과정에서 DEFINER 문을 사용하여 CREATE VIEW를 실행하는 동안 위의 오류가 발생할 수 있습니다. Azure Database for MySQL은 모든 사용자에게 SUPER 권한 또는 SET_USER_ID 권한을 허용하지 않습니다.

해결 방법:

가능하면 정의자 사용자를 사용하여 CREATE VIEW를 실행합니다. 다른 권한이 있는 다른 정의자를 가진 많은 뷰가 있을 수 있으므로 실행 가능하지 않을 수 있습니다. 또는
덤프 파일 또는 CREATE VIEW 스크립트를 편집하고 덤프 파일에서 DEFINER = 문을 제거하거나
덤프 파일 또는 CREATE VIEW 스크립트를 편집하고, 스크립트 파일을 가져오거나 실행하는 관리자 권한이 있는 사용자로 정의자 값을 바꿉니다.
 팁

sed 또는 perl을 사용하여 덤프 파일 또는 SQL 스크립트를 수정하여 DEFINER = 문을 바꿉니다.

 

출처 : https://docs.microsoft.com/ko-kr/azure/mysql/howto-troubleshoot-common-errors

일반적인 오류 문제 해결 - Azure Database for MySQL

 

 

아래에는 DEFINER 옵션을 제거하지 않고 다른 유저의 소유로 함수를 생성하는 테스트입니다.

 

1.유저 생성

sql>create user 'sp_test'@'localhost' identified by 'sp_test';


sql> create database sp_test default character set 'UTF8';

 

2.권한 부여

권한은 함수를 생성하는 최소한의 권한만 부여합니다

mysql> grant create routine,alter routine on sp_test.* to 'sp_test'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'sp_test'@'%';
+---------------------------------------------------------------------+
| Grants for sp_test@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sp_test`@`%`                                 |
| GRANT CREATE ROUTINE, ALTER ROUTINE ON `sp_test`.* TO `sp_test`@`%` |
+-----------------------------------

 

3.생성한 sp_test 유저로 접속해서 함수 생성

DEFINER='root'@'%' 없이 생성 시 생성이 되지만 DEFINER 옵션이 있는 경우에는 제목의 에러를 발생시키며 생성되지 않습니다.

mysql> DELIMITER ||
mysql> CREATE DEFINER=`root`@`%` FUNCTION functionname()
    -> RETURNS INT
    -> BEGIN
    ->     RETURN 12;
    -> END;
    -> ||
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation
mysql> DELIMITER ;

--DEFINER='root'@'%' 없이 생성 시
mysql> DELIMITER ||
mysql> CREATE FUNCTION functionname()
    -> RETURNS INT
    -> BEGIN
    ->     RETURN 12;
    -> END;
    -> ||
Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER ;

 

4. 해결법1) SUPER 권한 부여하기

SUPER 권한 부여시 DEFINER옵션이 있어도 생성되는 것을 확인할 수 있습니다.

--root 계정으로 수행
mysql> grant SUPER on *.* TO 'sp_test'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> show grants for current_user;
+--------------------------------------------------------------------------------------------+
| Grants for sp_test@localhost                                                               |
+--------------------------------------------------------------------------------------------+
| GRANT SUPER ON *.* TO `sp_test`@`localhost`                                                |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sp_test`.`functionname` TO `sp_test`@`localhost` |
+--------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


--sp_test 유저를 새로 접속 후 함수 생성

# mysql -u sp_test -p sp_test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql>
mysql>
mysql> DELIMITER ||
mysql> CREATE DEFINER=`root`@`%` FUNCTION functionname2()
    -> RETURNS INT
    -> BEGIN
    ->     RETURN 12;
    -> END;
    -> ||
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

 

5. 확인

DEFINER옵션이 있는 함수의 경우에는 Definer 컬럼의 값이 root@%인것을 확인할 수 있습니다.

mysql> show function status where db='sp_test';
+---------+---------------+----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db      | Name          | Type     | Definer           | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+---------------+----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sp_test | functionname  | FUNCTION | sp_test@localhost | 2021-07-04 19:41:11 | 2021-07-04 19:41:11 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| sp_test | functionname2 | FUNCTION | root@%            | 2021-07-04 20:11:19 | 2021-07-04 20:11:19 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
+---------+---------------+----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

 

 

6.해결법2) SET_USER_ID 권한 부여하기

우선 SUPER권한과 생성한 함수를 제거합니다.

--SUPER 권한 회수
mysql> REVOKE SUPER ON *.* FROM 'sp_test'@'localhost';
Query OK, 0 rows affected, 1 warning (0.04 sec)
root에서 확인
mysql> show grants for 'sp_test'@'localhost';
+---------------------------------------------------------------------------------------------+
| Grants for sp_test@localhost                                                                |
+---------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sp_test`@`localhost`                                                 |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sp_test`.`functionname2` TO `sp_test`@`localhost` |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sp_test`.`functionname` TO `sp_test`@`localhost`  |
+---------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

--함수 삭제
mysql> drop function functionname2;
Query OK, 0 rows affected (0.05 sec)

SET_USER_ID 권한에 대해서는 Manual의 원문을 첨부하겠습니다. 요약해보면 VIEW나 Stored Program(프로시저,함수)같은 오브젝트에 대해서 어떤 유저의 DEFINER로 설정할 수 있게끔 하는 권한이라고 되어 있습니다.

 

Enables setting the effective authorization ID when executing a view or stored program. A user with this privilege can specify any account as the DEFINER attribute of a view or stored program.

As of MySQL 8.0.22, SET_USER_ID also enables overriding security checks designed to prevent operations that (perhaps inadvertently) cause stored objects to become orphaned or that cause adoption of stored objects that are currently orphaned. For details, see Orphan Stored Objects.

 

권한 부여 및 함수 생성

root로 수행

mysql> GRANT SET_USER_ID ON *.* TO `sp_test`@`localhost`;
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for 'sp_test'@'localhost';
+--------------------------------------------------------------------------------------------+
| Grants for sp_test@localhost                                                               |
+--------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sp_test`@`localhost`                                                |
| GRANT SET_USER_ID ON *.* TO `sp_test`@`localhost`                                          |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sp_test`.`functionname` TO `sp_test`@`localhost` |
+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

sp_test 재접속 후 함수 생성
# mysql -u sp_test -p sp_test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show grants for current_user;
+--------------------------------------------------------------------------------------------+
| Grants for sp_test@localhost                                                               |
+--------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sp_test`@`localhost`                                                |
| GRANT SET_USER_ID ON *.* TO `sp_test`@`localhost`                                          |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sp_test`.`functionname` TO `sp_test`@`localhost` |
+--------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)


mysql> DELIMITER ||
mysql> CREATE DEFINER=`root`@`%` FUNCTION functionname2()
    -> RETURNS INT
    -> BEGIN
    ->     RETURN 12;
    -> END;
    -> ||
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;


확인
mysql> show function status where db='sp_test';
+---------+---------------+----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db      | Name          | Type     | Definer           | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+---------------+----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| sp_test | functionname  | FUNCTION | sp_test@localhost | 2021-07-04 19:41:11 | 2021-07-04 19:41:11 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| sp_test | functionname2 | FUNCTION | root@%            | 2021-07-05 00:34:40 | 2021-07-05 00:34:40 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
+---------+---------------+----------+-------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

 

각각의 권한 중 하나만 있어도 잘 생성되는 것을 확인할 수 있습니다.

 

 

참고 : https://m.blog.naver.com/tpgpfkwkem0/221997595407

[Mysql 8.0] Error Code: 1227. Access denied; you need (at least one of) the SUPER or SET_USER_ID..