When you grant table access thru a role, you cannot use that role in a stored procedure or view.
create role r; create user u1 identified by ***; grant create procedure, create session to u1; create user u2 identified by ***; grant create procedure, create session, r to u2; conn u1/*** create procedure u1.p1 is begin null; end; / grant execute on u1.p1 to r; conn u2/*** create procedure u2.p2 is begin u1.p1; end; / sho err procedure u2.p2 Errors for PROCEDURE U2.P2: L/COL ERROR ----- ------------------------------------------- 1/26 PL/SQL: Statement ignored 1/26 PLS-201: identifier U1.P1 must be declared
However, If i run it in an anonymous block, it works
declare procedure p2 is begin u1.p1; end; begin p2; end; / PL/SQL procedure successfully completed.
But this only works when my role is active. If my role is no longer active, then it obviously fails.
set role none; declare procedure p2 is begin u1.p1; end; begin p2; end; / ERROR at line 1: ORA-06550: line 4, column 5: PLS-00201: identifier 'U1.P1' must be declared ORA-06550: line 4, column 5: PL/SQL: Statement ignored
It is all written in the doc,
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer’s rights
I knew the behavior but not the reason behind it. Thanks to Bryn for bringing me so much knowledge on plsql.