[ORACLE] 특정 USER 에게 특정 테이블 TRUNCATE 권한 부여 procdure

오라클에서는 TRUNCATE 권한을 부여할 때 일반적으로 특정 테이블만 TRUNCATE 권한을 부여할수 없다.

truncate 권한 부여 방법
EX>
SQL>grant drop any table to scott;

그래서 procdure 을 이용하여 이를 해결 할수 있다.

1. 일단 두개의 테이블을 생성 한다. 
TRUNC_TABLE_HIST : 권한을 부여한 계정에 대한 truncate 의 증적을 남기는 테이블
TRUNC_TABLE_TARGET : 테이블에 계정 및 테이블 정보를 INSERT 하여 특정계정이 테이블을 TRUNCATE  가능하게 권한 부여 

<테이블 생성스크립트>
  CREATE TABLE SCOTT.TRUNC_TABLE_HIST
(
    EXEC_DATE       DATE NOT NULL,
    SESSION_USER    VARCHAR2(30) NOT NULL,
    OWNER           VARCHAR2(30) NOT NULL,
    TABLE_NAME      VARCHAR2(30) NOT NULL,
    PARTITION_NAME  VARCHAR2(30),
    MACHINE         VARCHAR2(64),
    OSUSER          VARCHAR2(30),
    TERMINAL        VARCHAR2(30),
    PROGRAM         VARCHAR2(48),
    EXEC_RESULT     VARCHAR2(2000)
)
TABLESPACE USERS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

CREATE TABLE SCOTT.TRUNC_TABLE_TARGET
(
    OWNER           VARCHAR2(30) NOT NULL,
    TABLE_NAME      VARCHAR2(30) NOT NULL,
    GRANTEE         VARCHAR2(30) NOT NULL,
    REGISTER_DATE   DATE,
    REQ_USER        VARCHAR2(30)
)
TABLESPACE USERS
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

2. 프로시져 생성

CREATE OR REPLACE PROCEDURE SCOTT.TRUNC_TABLE(pOwner IN VARCHAR2, pTabName IN VARCHAR2, pPartName IN VARCHAR2 DEFAULT NULL) IS -- 파티션인경우 파티션명을 넣어주고 아닌경우 입력안해도됨
  vSessUser VARCHAR2(30);
  vMachine VARCHAR2(64);
  vOsuer VARCHAR2(30);
  vTerminal VARCHAR2(30);
  vProgram VARCHAR2(48);
  vCnt NUMBER := 0;
  vDeg NUMBER;
  vErrm VARCHAR2(1000);
BEGIN
  SELECT SYS_CONTEXT('USERENV','SESSION_USER')
    INTO vSessUser
    FROM DUAL;
  SELECT MACHINE,
         OSUSER,
         TERMINAL,
         PROGRAM
    INTO vMachine, vOsuer, vTerminal, vProgram
    FROM V$SESSION SE
 WHERE AUDSID = USERENV('SESSIONID');
  IF pPartName IS NULL THEN
    BEGIN
      FOR tab IN (SELECT DISTINCT A.OWNER, A.TABLE_NAME
                    FROM DBA_TABLES A,
                         SCOTT.TRUNC_TABLE_TARGET B
                   WHERE A.OWNER = B.OWNER
                     AND A.TABLE_NAME = B.TABLE_NAME
                     AND B.OWNER = pOwner
                     AND B.TABLE_NAME = pTabName
                     AND B.GRANTEE = vSessUser)
      LOOP
        EXECUTE IMMEDIATE 'TRUNCATE TABLE '||tab.OWNER||'.'||tab.TABLE_NAME;
        vCnt := vCnt + 1;
      END LOOP;
    END;
  ELSE
    BEGIN
      FOR tab IN (SELECT A.TABLE_OWNER, A.TABLE_NAME, A.PARTITION_NAME
                    FROM DBA_TAB_PARTITIONS A,
                         SCOTT.TRUNC_TABLE_TARGET B
                   WHERE A.TABLE_OWNER = B.OWNER
                     AND A.TABLE_NAME = B.TABLE_NAME
                     AND A.PARTITION_NAME = pPartName
                     AND B.OWNER = pOwner
                     AND B.TABLE_NAME = pTabName
                     AND B.GRANTEE = vSessUser)
      LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE '||tab.TABLE_OWNER||'.'||tab.TABLE_NAME||' TRUNCATE PARTITION '||tab.PARTITION_NAME;
        vCnt := vCnt + 1;
      END LOOP;
    END;
  END IF;
  IF (vCnt = 0) THEN
    INSERT INTO SCOTT.TRUNC_TABLE_HIST
    VALUES (SYSDATE,vSessUser,pOwner,pTabName,pPartName,vMachine,vOsuer,vTerminal,vProgram,'Not registered table or Not Permitted user');
    COMMIT;
    RAISE_APPLICATION_ERROR(-20001,'You are not permitted to truncate this table');
  ELSE
    INSERT INTO SCOTT.TRUNC_TABLE_HIST
    VALUES (SYSDATE,vSessUser,pOwner,pTabName,pPartName,vMachine,vOsuer,vTerminal,vProgram,NVL2(pPartName,'1 Partition Truncated successfully','1 Table Truncated successfully'));
    COMMIT;
  END IF;
  end trunc_table;
  

3.권한 부여 (프로시져를 실행 하여 테이블을 truncate 할 계정에게 권한 부여)
EX>
SQL>grant execute on scott.TRUNC_TABLE to scott;

4. 권한 부여 
SQL> insert into scott.trunc_table_target
('테이블OWNER','TRUNCATE할테이블','truncate 권한 부여할 계정',sysdate,'커멘트 넣기');
SQL> commit;
EX>
SQL> insert into scott.trunc_table_target ('scott','test111','testuser',sysdate,'홍길동에게부여');
SQL> commit;

5. 프로시져 수행 방법
SQL> exec scott.trunc_table ('OWNER명','TABLE명','PARTITION TABLE명'(생각가능));

6. 수행 여부 확인
SQL>select * from scott.trunc_table_hist;