Truncate 권한 부여

Oracle Truncate 권한 부여 하기

Truncate 를 위해서는 "GRANT ANY TABLE" 권한 부여 , 그러나 보안상 위험하다.

프로시져를 통해서 우회적으로 권한 부여 하기

쥔장 : SYSTEM

권한 부여 받는 사람 : TGDEV

-- 테스트 계정 및 테이블 생성 + 권한 부여 
SYSTEM@TGDB=> create user tgdev identified by tgdev default tablespace users temporary tablespace temp ;
User created.
 
SYSTEM@TGDB=> grant create session, alter session to tgdev ;
Grant succeeded.
 
SYSTEM@TGDB=> create table t10 ( a1 number, a2 varchar2(10));
Table created.
 
SYSTEM@TGDB=> insert into t10 values ( 1, 'T');
1 row created.
 
SYSTEM@TGDB=> grant select on t10 to tgdev ;
Grant succeeded.
 
SYSTEM@TGDB=> create table s10 ( a1 number, a2 varchar2(10));
Table created.
 
SYSTEM@TGDB=> insert into s10 values ( 1, 'S');
1 row created.
 
SYSTEM@TGDB=> grant select on s10 to tgdev ; 
Grant succeeded.
 
SYSTEM@TGDB=> create table t20 ( a1 number, a2 varchar2(10));
Table created.
 
 
SYSTEM@TGDB=> grant select on t20 to tgdev ;
Grant succeeded.
 
 
-- 권한 확인 및 TRUNCATE 시도 
TGDEV@TGDB=> select * from system.t10 ;
 
        A1 A2
---------- ----------
         1 T
 
TGDEV@TGDB=> select * from system.s10 ;
 
        A1 A2
---------- ----------
         1 S
 
TGDEV@TGDB=> select * from system.t20 ;
 
no rows selected
 
TGDEV@TGDB=> truncate table system.t20;
truncate table system.t20
                      *
ERROR at line 1:
ORA-01031: insufficient privileges
 
 
 
-- TRUNCATE 권한 부여
SYSTEM@TGDB=> grant truncate to tgdev ;
grant truncate to tgdev
      *
ERROR at line 1:
ORA-01919: role 'TRUNCATE' does not exist
 
SYSTEM@TGDB=> grant drop any table to tgdev ;
Grant succeeded.
 
 
-- TRUNCATE 확인 
TGDEV@TGDB=>  truncate table system.t20;
 
Table truncated.
 
TGDEV@TGDB=> 
 
 
-- TRUNCATE 권한 회수
SYSTEM@TGDB=> revoke drop any table from tgdev ;
 
Revoke succeeded
 
-- TRUNCATE 권한 회수 확인
TGDEV@TGDB=> truncate table system.t20;
truncate table system.t20
                      *
ERROR at line 1:
ORA-01031: insufficient privileges
 
TGDEV@TGDB=> 
 
-- 프로시져를 통한 TRUNCATE 권한 부여
SYS@TGDB=> create table trunc_priv ( owner varchar2(30), table_name varchar2(30), grantee varchar2(30));
Table created.
 
SYS@TGDB=> insert into trunc_priv values ( 'SYSTEM','T10','TGDEV');
1 row created.
 
SYSTEM@TGDB=> COMMIT ;
Commit complete.
 
 
SYSTEM@TGDB=> create or replace procedure do_truncate(in_owner in varchar2, in_table in varchar2)
  2  AS
  3  SQLSTR varchar2(2500);
  4  CHK_V NUMBER;
  5  USER_V VARCHAR2(30);
  6  BEGIN
  7  
  8  SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')  INTO USER_V FROM DUAL ; 
  9      
 10  SELECT COUNT(*) INTO CHK_V
 11  FROM   trunc_priv
 12  WHERE  OWNER = in_owner
 13  AND    TABLE_NAME = in_table
 14  AND    GRANTEE    = USER_V ;
 15  
 16  IF CHK_V >= 1 THEN
 17     SQLSTR := 'TRUNCATE TABLE '||upper(in_owner)||'.'||upper(in_table);
 18     EXECUTE IMMEDIATE SQLSTR;   
 19      
 20  ELSIF CHK_V = 0 THEN 
 21     raise_application_error(-20001,'No Truncate Privilege ');     
 22  END IF ;
 23  exception
 24  when others then
 25  dbms_output.put_line(SQLERRM(SQLCODE));
 26  END ;
 27  /
 
Procedure created.
 
SYSTEM@TGDB=> SHOW ERROR
No errors.
SYSTEM@TGDB=> grant execute on do_truncate to tgdev ;
Grant succeeded
 
 
-- TRUNCATE 검증
TGDEV@TGDB=>  select * from system.t10 ;
 
        A1 A2
---------- ----------
         1 T
 
-- PROCEDURE를 통한 TRUNCATE 수행 
TGDEV@TGDB=>  execute system.do_truncate('SYSTEM','T10');
PL/SQL procedure successfully completed.
 
-- TRUNCATE 결과 확인 
TGDEV@TGDB=> select * from system.t10 ;
no rows selected
 
-- 미등록 테이블 TRUNCATE 시도 
TGDEV@TGDB=>  execute system.do_truncate('SYSTEM','S10');
ORA-20001: No Truncate Privilege
PL/SQL procedure successfully completed.
 
TGDEV@TGDB=>