Truncate table of another user

I've noticed yesterday extensive redo generating by job that was running in late hours. After examination of procedure that was executed by this job it could be seen that procedure is running several deletes and inserts on various tables.

We had deletes:
delete from user1.table;
insert...

delete from user2.table;
insert...

etc...


Delete command generates lots of redo so I've decided to replace delete command with truncate. Truncate will speed up procedure execution and it will cut down redo generation. After placing truncate commands I've experienced error : ORA-01031: insufficient privileges.

Procedure owner was user1 and all tables owned by user1 were successfully truncated, but when user1 tried to truncate tables owned by user2 - that failed cause of insufficient privileges.

I could not just grant "truncate table" privilege to user1 because that privilege just doesn't exist in Oracle.



To solve my problem I've considered two options:

1) Grant "drop any table" to user1 - which is not very wise thing to do because that privilege is very powerful and it is dangerous give that privilege to some user.

2) Create procedure owned by user2 that truncates tables owned by user2 - which is more desirable option for me to solve my problem.


I'll demonstrate both cases.

Script to create two test users, grant appropriate privileges and create test table;

system@TESTDB> create user a identified by a;

User created.

system@TESTDB> grant connect to a;

Grant succeeded.

system@TESTDB> create user b identified by b;

User created.

system@TESTDB> grant connect to b;

Grant succeeded.

system@TESTDB> grant create table to b;

Grant succeeded.

system@TESTDB> alter user b default tablespace users;

User altered.

system@TESTDB> alter user b quota unlimited on users;

User altered.

Now to create table as a b user:

b@TESTDB> create table t (id number);

Table created.

b@TESTDB> insert into t values (1);

1 row created.

b@TESTDB> insert into t values (2);

1 row created.

b@TESTDB> commit;

Commit complete.

b@TESTDB> select * from t;

ID
----------
1
2

2 rows selected.

Grant all operations on b.t table to a:

system@TESTDB> grant all on b.t to a;

Grant succeeded.

Now I'll try to truncate table owned by b user as a user:

a@TESTDB> truncate table b.t;
truncate table b.t
*
ERROR at line 1:
ORA-01031: insufficient privileges


a@TESTDB> begin
2  execute immediate 'truncate table b.t';
3  end;
4  /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at line 2

As you can see I have problem with privileges.


If I grant "drop any table" to a - truncate statement will work:

system@TESTDB> grant drop any table to a;

Grant succeeded.


a@TESTDB> truncate table b.t;

Table truncated.

To revoke granted privilege and insert two rows again into b.t table:

system@TESTDB> revoke drop any table from a;

Revoke succeeded.


b@TESTDB> insert into t values (1);

1 row created.

b@TESTDB>  insert into t values (2);

1 row created.

b@TESTDB> commit;

Commit complete.

b@TESTDB> select * from t;

ID
----------
1
2

2 rows selected.

system@TESTDB> grant create procedure to b;

Grant succeeded.

Now I'll create very simple procedure that truncates tables owned by b schema:

b@TESTDB> create or replace procedure do_truncate(
in_owner in varchar2,
in_table in varchar2)
as
l_sql varchar2(2500);
begin
l_sql := 'TRUNCATE TABLE '||upper(in_owner)||'.'||upper(in_table);
execute immediate l_sql;
end;
/
b@TESTDB>

Procedure created.

Grant privilege to execute procedure to a user:

system@TESTDB> grant execute on b.do_truncate to a;

Grant succeeded.

Truncate table executing "do_truncate" procedure:

B USER:

b@TESTDB> select * from t;

ID
----------
1
2

2 rows selected.


A USER:

a@TESTDB> begin
2  b.do_truncate('b','t');
3  end;
4  /

PL/SQL procedure successfully completed.


B USER:

b@TESTDB> select * from t;

no rows selected

To clean up:

system@TESTDB> drop user a cascade;

User dropped.

system@TESTDB> drop user b cascade;

User dropped.