Why doesn't PL / SQL respect the privileges granted by Roles?
Any privileges granted to roles are ignored when a PL / SQL block is executed. Instead, you must provide specific grants to specific users to run it. If I want to grant DBAs access to a package or function or procedure, I cannot grant the DBAs role. I have to give a grant to every user in the DBA role, I need to remove the user grant if they stop being a DBA and I have to add a grant to any new DBA.
It's very difficult for me to maintain this.
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? I have not been able to find an answer that is not "as it is".
source to share
This is probably a combination of laziness and command SET ROLE
.
I disagree that it is not allowed due to complex dependencies. Oracle already manages complex dependencies. And in 12c, you can grant a role to an object.
I think the real reason why objects don't inherit user roles has to do with the command SET ROLE
. A user can be assigned a role, but to be turned on and off in a session. This is a stupid trait and I have never seen her. But in theory, this would require recompilation during the same session or transaction, which would be really confusing.
source to share
Perhaps I am misunderstanding here because I did what you say cannot be done. In fact, the Oracle documentation says it can be done. Take a look at the "Procedure Security" section of this document . (@ ibre5041) Nothing needs to be recompiled because procedures are executed under owner privileges. The user's (or his role) rights are checked only to see if they are allowed to perform the procedure. What am I missing?
source to share
I think that this is some kind of historical heritage. When changing ROLE objects, Oracle will recompile a lot of stored PL / SQL code. PS: you can also create something called "SCHEMA".
See CREATE SCHEMA statement .
source to share
I think you can fight for Invokers vs Definers rights.
From Oracle Docs :
During a server call, when a DR block is pushed onto the call stack, the database stores the current roles and current values from CURRENT_USER and CURRENT_SCHEMA. It then changes both CURRENT_USER and CURRENT_SCHEMA to the owner of the DR block, and only allows the PUBLIC role . (Saved and new roles and values are not necessarily different.) When a DR bit block is popped from the call stack, the database restores the stored roles and values. In contrast, when an IR block is popped or called from the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the roles that are currently enabled do not change.
So, if you want Oracle to "respect the privileges granted by the roles", you might want to use the Invokers rights (AUTHID CURRENT_USER clause)
source to share