프로시져, 함수 AUTHID 이해

 

DBADM 계정(DBA용)으로 프로시져를 생성하였다.

프로시져 생성 목적은 타 스키마(HUAM_OWN)의 OBJECT를 관리하기 위함이다.

예시로써, 타 스키마의 인덱스를 재생성하려고 한다. ( DROP & CREATE )

프로시져를 호출했으나 ERROR가 발생한다.

DBADM은 DBA ROLE 권한을 가졌는데 왜 안되는 걸까?

DBA ROLE에 INDEX 관련 SYSTEM 권한 조회

 

 

 

 

 

 

[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로 사용하는 것이 권장된다.