Re: grant <ROLE> to Package - where is my fault?

As far as I know, pl/sql requires direct grants on objects referenced in other schemas while inside pl/sql code.

HTH, Chris

On Wed, Feb 8, 2023 at 3:52 PM Martin Berger <martin.a.berger_at_gmail.com> wrote:

> Dear List,
>
> I try to do some little tests how to properly grant a role to a procedure.
> I'm even failing for a simple table.
> Checking the docs for GRANT [1] and Code Based Access Control [2] did not
> help (I'm probably blind) - so I'm happy with any suggestion.
>
> My testcase (already prepared for more than only a table):
>
> -- grant_to_package.sql
>
> -- user A
>
> drop user A cascade;
> create user A identified by A;
>
> grant create session to A;
>
> grant create table to A;
> grant create procedure to A;
>
> create table A.T (C1 number);
>
> create view A.V as select * from A.T;
>
> create or replace package A.P authid definer as
> procedure p1;
> end P;
> /
>
> create or replace package body A.P as
> procedure p1 as
> begin
> dbms_output.put_line('x');
> end;
> end P;
> /
>
> -- Role R
> drop role R;
> CREATE ROLE R;
> grant read on A.T to R;
> grant read on A.V to R;
> grant execute on A.P to R;
>
> -- User B
> drop user B cascade;
> create user B identified by B;
>
> grant create procedure to B;
> grant R to B;
>
> create or replace package B.Pac authid definer as
> procedure proc1;
> end Pac;
> /
>
> Grant R to PACKAGE B.Pac;
>
> create or replace package body B.Pac as
> procedure proc1 as
> var1 A.T.C1%type := 1;
> begin
> dbms_output.put_line(var1);
> end;
>
> end Pac;
> /
>
> And I get the error
>
> INE/COL ERROR
> --------- -------------------------------------------------------------
> 0/0 PL/SQL: Compilation unit analysis terminated
> 3/8 PLS-00201: identifier 'A.T' must be declared
> Errors: check compiler log
>
> With a direct Grant read on A.T to B;
> the package compiles quite fine.
>
> So please can anyone tell me how to do this GRANT <ROLE> TO PACKAGE right
> so my line
> var1 A.T.C1%type := 1;
> works?
>
> thanks in advance,
> Martin
>
>
> [1]
> https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/GRANT.html#GUID-20B4E2C0-A7F8-4BC8-A5E8-BE61BDC41AC3
>
> [2]
> https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/managing-security-for-definers-rights-and-invokers-rights.html#GUID-45E77E8E-587F-42AF-A163-D814264341E2
>
>
> --
> Martin Berger
> Oracle ♠
> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
> ^∆x http://berxblog.blogspot.com
>
>

--
http://www.freelists.org/webpage/oracle-l