[Mysql] create routine definer 에 따른 권한 발생 문제

 

create routine = Procedure를 definer 를 설정하여 만들고 정상적으로 작동하지 않는 문제가 발생했다.

 

상황

1. 일반 usr계정으로 프로시저 생성 후, definer 에 dba사용자권한이 있는 계정을 할당함.

2. 프로시저 내부에 information_schema.table를 이용하여 테이블 존재여부를 체크하는 로직이 존재

3. 그 후 turncate table 수행

 

결과

위의 2번 스키마 테이블에서 테이블이 존재하지 않는다고 나와, 정상적으로 로직이 수행되지 않음.

 

문제원인

 

definer의 계정은 dba권한이 있는 사용자로 excute권한밖에 기본적으로 가지고 있지 않았다.

그렇기때문에 dba권한이 있는 사용자 계정에서 select를 할때는 테이블을 명시적으로 지정해줘야 information_schema.table에서 검색이 가능하다.

 

definer를 dba권한이 있는 사용자 계정으로 설정해놓고, 테이블을 명시적으로 설정해주지 않아 정상적으로 작동하지 않았다.

사실 excute권한밖에 없었기때문에 select 로직이 존재하지 않았다고 해도, 결국에는 해당 테이블의 truncate 를 진행하지 못했을거다.

 

해결방법

 

테이블을 명시적으로 지정하지 않아  발생한 문제이기때문에 테이블을 명시적으로 지정해주면 된다

테이블을 명시적으로 지정하기 위해서는 grant 문법을 이용해  특정 테이블에 대한 select  및 drop 권한을 부여하면된다.

grant select on 스키마.table_name  to '계정'@'host';
grant drop on 스키마.table_name  to '계정'@'host';

 

 

권한을 부여하고 information_schema.table_privileges를 검색해보면 특정 테이블에대한 권한이 부여되었음을 확인 할 수 있다.

select * from information_schema.table_privileges