Tag: role

Categories
plsql security

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.