TRUNCATE TABLE giving error even with DROP ANY TABLE system privilege

sabyakgp

Hi

I have a table called TEST1.T1:

create table test1.t1 (id int, constraint pk_id primary key(id));

and another called TEST1.T2:

create table test1.t2 (id int, constraint fk_id foreign key(id) references test1.t1(id) on delete cascade);

I have created a stored procedure in another schema TEST2.TPROC

create or replace procedure test2.tproc as

begin

execute immediate 'TRUNCATE TABLE TEST1.T1 CASCADE';

end tproc;

My user id is smitra and I have granted execute role to the user on this procedure:

grant execute on test2.tproc to smitra;

I also granted DROP ANY TABLE TEST2 user:

grant drop any table to test2;

When I execute this SP:

exec test2.tproc;

I get error:

BEGIN test2.tproc; END;

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "TEST2.TPROC", line 4

ORA-06512: at line 1

My question is when the TEST2 user, in which the procedure is defined, has the DROP ANY TABLE system privilege why it's throwing this error?

The only I was able to resolve the error is to change the DEFINER RIGHTS of the SP to INVOKER RIGHTS. Can somebody please explain why I am getting this error? I am using Oracle 12.1

Hemendra.Singh

grant drop any table to smitra;

and try again.

Thanks,

Hemendra Singh

Brett Calhoun

Is there any reason you are not creating the tproc procedure in TEST1 schema?

Thanks,

Brett

sabyakgp

Hi Hemendra - the user smitra has DROP ANY TABLE already.

sabyakgp

Hi Brett - Yes that would solve the issue. TEST1 and TEST2 are just example schema I have given here. TEST1 is meant to contain only table while TEST2 for procedures/functions/packages etc. Given that TEST2 has DROP ANY TABLE System privilege should it not be able to truncate any table (regardless if it's in its own schema or someone else's schema)? That's my question.

Brett Calhoun

Yes, you should be able to truncate the table. But for some reason it is not working. I was able to duplicate your problem and I think the problem is with the CASCADE option. I can successfully execute the following 2 statements as TEST2:

truncate table test1.t2; -- truncate the child table

truncate table test1.t1; -- truncate the parent table

However, I get the error when I execute this statement:

truncate table test1.t1 cascade;

Would it be possible to revise your procedure to use the above 2 statements? If not, I recommend you open a service request on My Oracle Support.

Thanks,

Brett