Truncate table in another schema without DROP ANY TABLE Privilege

Developers used to write a Procedure to truncate the table which is in another schema. So developers require truncate table privileges to run their code. Now DBA needs to grant DROP ANY TABLE privileges to statisfy the developers code. DROP ANY TABLE is one of the most powerful and dangerous privilege and DBA can not grant to other schema's in Oracle.

Here is one way to accomplish the task. This article was written in oracle9i release 9.2.0.6.0

Let us say, We have two schemas, INVENT, INVENTADMIN. All the tables were created in INVENT and stored procedures were created in INVENTADMIN. We need to grant truncate privileges to INVENTADMIN to truncate the tables in INVENT.

SQL> connect invent/invent@db1
Connected.
SQL> create table test as select * from user_objects;
Table created.
SQL> select count(*) from test;

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

-- For some reson, the piple symbol is not appearing in the blog and
-- please use piple symbol in the third line of the below procedure.

SQL> create or replace procedure stp_truncate_table(p_table_name varchar2) is
2 begin
-- the below line, there is piple synobol before p_table_name.
3 execute immediate 'truncate table 'p_table_name;
4 end;
5 /

Procedure created.

SQL> grant execute on stp_truncate_table to inventadmin;

Grant succeeded.

SQL> connect inventadmin/inventadmin@db1
Connected.

SQL> create synonym stp_truncate_table for invent.stp_truncate_table;
Synonym created.

SQL> execute stp_truncate_table('TEST');
PL/SQL procedure successfully completed.
SQL>

Now login back to invent and check the table data.

SQL>
SQL> connect invent/invent@db1
Connected.
SQL> select * from test;

no rows selected

SQL>