데이터베이스 데이터 백업본 불러올때 에러 ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

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

db백업한데이터를 불러올 때 생긴에러다 

 

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

 

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

 

 

해결방법

저는 함수가 두개 밖에 없어서 두개 지우고 실행을 하였다. 하지만 사용자 함수가 많을경우 제거하지않고 하는 방법은 없을 까 찾아 보았다. 

1 : SUPER 권한 부여하여 DEFINER옵션이 있어도 생성되는 것을 확인 할 수 있었다. 

grant SUPER on *.* TO 'sf_test'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)




DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `sf_new_goods_code`() RETURNS varchar(50) CHARSET utf8
    COMMENT '상품 코드를 생성하는 함수'
BEGIN
	DECLARE newCode varchar(50) DEFAULT '';
	SELECT 
		case
		when COUNT(g.facility_goods_cd) = 0 then 'g001'
		when (MAX(CAST(SUBSTRING_INDEX(g.facility_goods_cd,'g',-1) AS UNSIGNED))<1000)
		then
			CONCAT('g', LPAD(MAX(CAST(SUBSTRING_INDEX(g.facility_goods_cd,'g',-1)AS UNSIGNED))+1,3,0))
		else
			CONCAT('g',MAX(CAST(SUBSTRING_INDEX(g.facility_goods_cd,'g',-1) AS UNSIGNED))+1)
		end into newCode
	FROM
		facility_goods AS g;
		
	return newCode;
END//
DELIMITER ;
Query OK, 0 rows affected (0.01 sec)