PLS-00201 in stored procedures

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.

Leave a Reply