Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

TRUNCATE TABLE giving error even with DROP ANY TABLE system privilege

sabyakgpDec 18 2020

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

Comments

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

1 - 5

Post Details

Added on Dec 18 2020
5 comments
442 views