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

Thank you Jonathan,

The solution is quite simple: smash my forehead onto the docs & information available until my brain accepts its content.

To summarize the important bits:

The role then becomes enabled during the execution of the program unit, but not during the compilation of the program unit. This enables you to temporarily escalate privileges in the PL/SQL code without granting the role directly to the user. It also increases security for applications and helps to enforce the principle of least privilege. 4.8.5.3 Granting and Revoking Roles to and from Program Units <https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-privilege-and-role-authorization.html#GUID-73E30148-9B57-4CE8-9007-8FAFC7741638>

This makes quite sense for Invokers Rights - I see it as "Invokers Rights, but with some specific Definers Rights granted in addition". Even more useful is a sentence from Tom Kyte:

In the case of the definers rights routine, this new capability will only make sense when you use dynamic SQL.
...
Definers rights routines compile with the set of privileges granted directly to the owner of the procedure - roles are *never* enabled during the compilation of a compiled stored object. This is true in Oracle Database 12c still - and is the reason this new capability only makes sense with dynamic SQL in a definers rights routine. In order for the unit to compile, all of the privileges necessary for the static SQL and PL/SQL in the unit must be granted directly to the owner of the unit. Therefore - any privileges granted via roles cannot be used for static SQL or PL/SQL. The compilation would fail without the direct privilege. However, any dynamically executed code would not be security checked until runtime, the compiler would not "see" this code. And with CBAC - the set of privileges the dynamic SQL will be checked with will be all of the privileges granted directly to the owner of the unit *and* any privileges associated with roles granted to the unit.
12c - Code Based Access Control (CBAC) part 1 <https://asktom.oracle.com/Misc/12c-code-based-access-control-cbac-part.html>

In my test (and goal to achieve) I was thinking only in terms of *Definers Rights*, but at the same time, use "beautiful" PL/SQL like %TYPE and all the nice stuff. Of course I can re-write everything to dynamic SQL - but now I need to check if that meets the requirements.

thank you again to Niall, Rich & Chris for your answers & Jonathan for forcing me to accept them.

 Martin

Am Do., 9. Feb. 2023 um 11:41 Uhr schrieb Jonathan Lewis < jlewisoracle_at_gmail.com>:

>
> Martin,
> As Chris indicates, it's about who can do what when.
>
> B can't create a package body that references A.T1 unless B already has
> direct privilege access to A.T1 (at compile time).
>
> The point of "grant role to package" is that this allows A to create a
> package handling A's object and then allow B to execute the package without
> having direct access to A's objects.
>
>
> Regards
> Jonathan Lewis
>
>
>

-- 
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