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