How to Bypass DROP ANY TABLE Privilege for Truncate Table Command

advertisements

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats