| permission when accessing object in PL/SQL granted by role [message #52637] |
Fri, 02 August 2002 14:45  |
Radek
Messages: 33
Registered: April 2002
|
Member |
|
|
Hi,
I just found permission problem. I remember now I met this before but was not in position to have to deal with it as dba. I have schema owner called app_owner.
I have two roles in db: app_user, app_developer. App_user has permission for access app_owner's objects and execute app_owner's packages. App_developer is granted role app_user and has create package,procedure,function privilege. Problem is that when user with app_developer role does not have explicitly granted access to app_owner's objects she/he is not able to compile packages in her/his own schema. "identifier must be declared" shows up. If I grant access for app_owner's objects to particular user everything is fine. Also I found out that if grant privileges to access to PUBLIC everything is fine too. Currently I am using PUBLIC solution. I do not like idea that everybody can access app_owner objects. I have multiple app_owner's schema due to versioning and bug fixes in our app. Users have access to selected app_owner by private synonyms.
Finally the question, is there any way around granting access to PUBLIC for every object in every app_owner schema? I do not want deal with every user/every object permission. We are using Oracle 8.1.6 on Solaris. I remember this situation from 7.3 when I was a developer. Actually is there explanation why it is implemented this way?
Thank you,
Radek
|
|
|
|
| Re: permission when accessing object in PL/SQL granted by role [message #52638 is a reply to message #52637] |
Fri, 02 August 2002 15:02  |
Radek
Messages: 33
Registered: April 2002
|
Member |
|
|
I should search metalink first:
Doc ID: Note:1011899.6
Subject: Roles and creating stored objects/views
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 17-AUG-1995
Last Revision Date: 28-JUN-2002
Problem Description:
~~~~~~~~~~~~~~~~~~~~
All roles are disabled in any *named* PL/SQL block that
* is created in a user schema that does not own the object being
referenced in the PL/SQL block
* can be executed as a user other than the owner of the PL/SQL block
This applies to stored procedures and functions, as well as database triggers.
You can also see this by selecting from the data dictionary view called
SESSION_ROLES. This view shows all roles that are currently enabled. If you
query SESSION_ROLES from a stored object, the query will not return any rows.
This is not true in Oracle8i when the stored object is created with AUTHID
CURRENT_USER.
Roles vs. GRANTs and REVOKEs:
----------------------------
Do not confuse roles with GRANTs and REVOKEs -- Roles are meant to be toggled
on and off on a per session basis, whereas GRANTs and REVOKEs (as well as any
associated dependencies) updates the data dictionary because they are DDL
operations. This is why it becomes an issue of gross ambiguity: A user can
potentially log on in the form of two sessions, one with the role enabled, the
other one with the role disabled, and the outcome of both sessions will not be
the same.
Hence, the restriction that stored object creation cannot depend on privileges
granted through a role is added in the Oracle7 to avoid ambiguous cases of this
nature. Notice that anonymous PL/SQL blocks are not bound to this restriction,
therefore they are executed based on privileges granted through enabled roles.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
