프로시져, 함수 AUTHID 이해
DBADM 계정(DBA용)으로 프로시져를 생성하였다.
프로시져 생성 목적은 타 스키마(HUAM_OWN)의 OBJECT를 관리하기 위함이다.
예시로써, 타 스키마의 인덱스를 재생성하려고 한다. ( DROP & CREATE )
프로시져를 호출했으나 ERROR가 발생한다.
DBADM은 DBA ROLE 권한을 가졌는데 왜 안되는 걸까?
[1] HUAM_OWN 계정 = 스키마
1.1 테이블 & 인덱스 생성
CREATE TABLE HUAM_OWN.TB_TEST_007
(
COL1 VARCHAR2(100)
);
CREATE INDEX HUAM_OWN.IX_TB_TEST_007_01 ON HUAM_OWN.TB_TEST_007 ( COL1 ) ;
CREATE PUBLIC SYNONYM TB_TEST_007 FOR HUAM_OWN.TB_TEST_007;
[2] DBADM 계정
2.1 권한
- SYSTEM 권한 : UNLIMITED TABLESPACE
- ROLE 권한 : CONNECT, RESOURCE, DBA
2.2 OBJECT
- 프로시져 : PRC_INDEX_DROP
CREATE OR REPLACE PROCEDURE PRC_INDEX_DROP
IS
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX HUAM_OWN.IX_TB_TEST_007_01';
EXECUTE IMMEDIATE 'CREATE INDEX HUAM_OWN.IX_TB_TEST_007_01 ON HUAM_OWN.TB_TEST_007 ( COL1 ) ';
END PRC_INDEX_DROP;
/
[3] 테스트
3.1 DBADM -> PRC_INDEX_DROP 실행
: WHY? 에러가 발생하는 걸까?
EXEC dbadm.PRC_INDEX_DROP;
[ 에러 발생 ]
ORA-01418: specified index does not exists;
ORA-06512: at "DBADM.PRC_INDEX_DROP", line 4ORA-06512: at line 1
3.2 AUTHID 이해
: PL/SQL 프로시져, 함수가 실행될때 어느 사용자의 권한으로 실행 할 지 지정하는 AUTHID 키워드 이해가 필요하다.
어느 사용자는 "프로시져 소유자"와 "프로시져 호출자" 2명의 사용자로 구분된다.
핵심만 정리하면 다음과 같다.
|
|
AUTHID DEFINER(소유자)
|
AUTHID CURRENT_USER(호출자)
|
|
SYSTEM 권한
|
적용
|
적용
|
|
ROLE 권한
|
X
|
적용
|
|
OBJECT 권한
|
적용
|
적용
|
※ 프로시져 생성시 AUTHID를 명시하지 않으면, AUTHID DEFINER로 실행됨 (Default DEFINER)
3.3 에러 발생 원인
: DBADM 계정으로 프로시져 실행시 AUTHID DEFINER(소유자 권한 기준)으로 실행이 된다.
DBADM이 DBA ROLE 권한은 있지만, DEFINER는 ROLE 권한을 적용하지 않는다.
그러므로 프로시저 내부에 HUAM_OWN의 인덱스를 DROP & CREATE 하니 에러가 발생한다.
ROLE 내에 SYSTEM 권한(DROP,CREATE ANY INDEX)과 개별 시스템 권한은 명칭과 역할은 같다.
하지만, 부여된 타입(Role / System)이 다르기 때문에 발생하는 원인으로 판단하자!
3.4 조치 방법 2가지
3.4.1 프로시저에 AUTHID CURRENT_USER 명시
- 호출자 권한으로 실행되기때문에, ROLE 권한까지 적용되므로, 에러 없이 수행 됨
( 소유자 "DBADM"이 아닌, 호출자 "DBADM"으로 실행함 )
CREATE OR REPLACE PROCEDURE PRC_INDEX_DROP
authid current_user -- <--------------------------명시!!!
IS
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX HUAM_OWN.IX_TB_TEST_007_01';
EXECUTE IMMEDIATE 'CREATE INDEX HUAM_OWN.IX_TB_TEST_007_01 ON HUAM_OWN.TB_TEST_007 ( COL1 ) ';
END PRC_INDEX_DROP;
/
3.4.2 프로시져 수정 없이 SYSTEM 권한 부여
- DRA ROLE에 CREATE/DROP ANY INDEX 권한은 있으나,
AUTHID DEFINER 상태에서는 ROLE 권한이 배제되므로 별도 SYSTEM 권한을 부여하는 방법이다.
아래 수행 후 프로시져 실행시 정상 수행 됨
GRANT CREATE ANY INDEX, CREATE DROP INDEX TO DBADM;
※ 참고
: 프로젝트 상황에 따라 사용하면 되겠지만, DBA 계정으로 OBJECT를 관리하는 경우에는
1) AUTHID CURRENT_USER로 사용하는 것이 권장된다.