Truncate table of another user

Wednesday, January 14, 2009

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.


Share/Bookmark

3 comments:

  • I have a project that I think will benefit from this. Very cool.

  • You just forgot to mention that delete and truncate don't act the same. In many real-life projects/companies it's not so easy to just truncate table. What if the following insert fails? The answear is simple - you loose your data.
    That's why you can sometimes see things like you mentioned - first delete everything, then insert data. All in one transaction. And usually it's like this not because someone wasn't smart enough....

  • Marko Sutic

    Hello Anonymous,

    of course, truncate table is not valid option for all real-life projects. Yes, I could mention that truncate table is very dangerous command and you should be very careful when using it in production.

    But I never said that you should always replace delete with truncate command and that you are not smart enough if you are using delete.

    Intention of this blog post was to show how to overcome ORA-01031 error when you need to truncate table of another user.

    Thanks for commenting.

    Regards,
    Marko

  • Post a Comment