Why doesn't PL/SQL respect privileges granted by Roles?

Question

When executing a PL/SQL block, any privileges granted to roles are ignored. Instead you must give specific users specific grants to run it. If I want to give DBAs access to a package or a function or a procedure, I can't give the DBA role a grant. I have to give a grant to each user in the DBA role, I have to remove the user's grant if they cease to be a DBA, and I have to add the grant to any new DBA.

I find this very hard to maintain.

My question is why does PL/SQL work this way? What design considerations did Oracle make to decide that this is how Roles and PL/SQL should work together? I've been unable to find an answer that isn't "that's just the way it is".

Answer 1

I think you may be fighting over Invokers rights vs Definers rights.

From Oracle docs:

During a server call, when a DR unit is pushed onto the call stack, the database stores the currently enabled roles and the current values of CURRENT_USER and CURRENT_SCHEMA. It then changes both CURRENT_USER and CURRENT_SCHEMA to the owner of the DR unit, and enables only the role PUBLIC. (The stored and new roles and values are not necessarily different.) When the DR unit is popped from the call stack, the database restores the stored roles and values. In contrast, when an IR unit is pushed onto, or popped from, the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the currently enabled roles do not change

So if you want Oracle to "respect the privileges granted by roles", then perhaps you want to use Invokers rights ( AUTHID CURRENT_USER clause)

Answer 2

It's probably a combination of laziness and the SET ROLE command.

I disagree that it's not allowed because of complex dependencies. Oracle already manages complex dependencies. And in 12c it is possible to grant a role to an object.

I think the real reason why objects don't inherit the roles of the user is because of the SET ROLE command. It's possible for a user to be assigned a role but to turn it on and off within a session. That's a silly feature and I've never seen it used. But theoretically it would require recompiling within the same session or transaction, which would be really confusing.