How to Bypass DROP ANY TABLE Privilege for Truncate Table Command

You have to create a procedure with truncate table command (dynamic SQL) and grant the execute procedure privilege to the other user. This method will help you to avoid the granting of drop any table privilege to the target user. Here is the example for the same.
Example
I have 2 users called test and sthomas. Test wanted to drop the table from the sthomas schema. Dept table has 4 records.
Generally it you try to truncate a table from the other user, you will end up with insufficient privilege error message.

STHOMAS@esbtst>conn test/test
Connected.
TEST@esbtst>truncate table sthomas.dept;
truncate table sthomas.dept
                       *
ERROR at line 1:
ORA-01031: insufficient privileges


TEST@esbtst>conn sthomas
Enter password:
Connected.
STHOMAS@esbtst>select count(*) from dept;

  COUNT(*)
----------
         4

Create a procedure with truncate table dynamic sql and grant the execute privilege to the other user.

CREATE OR REPLACE procedure PR_TRUNCATE_TAB (TAB_NAME varchar2) is
BEGIN
 execute immediate 'TRUNCATE TABLE '||TAB_NAME;
END;
/
STHOMAS@esbtst>  /

Procedure created.

STHOMAS@esbtst>GRANT EXECUTE ON PR_TRUNCATE_TAB TO TEST;

Grant succeeded.

Now you connect to TEST user and execute the procedure with the table name which will intern execute the truncate command.
STHOMAS@esbtst>CONN TEST/TEST
Connected.
TEST@esbtst>
TEST@esbtst>EXEC STHOMAS.PR_TRUNCATE_TAB('DEPT');

PL/SQL procedure successfully completed.

TEST@esbtst>CONN sthomas
Enter password:
Connected.
STHOMAS@esbtst>select count(*) from dept;

  COUNT(*)
----------

         0
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google