This content has been marked as final. Show 7 replies
*“The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.”*
Yes this statement is true. You must explicitly grant.
For detailed inf. please check.
Talip Hakan Ozturk
Are you stating that your application has 250 separate users each of which is writing his or her own packages? That would be, at a minimum, very unusual and would tend to be rather problematic to administer. Is there really no way to limit the number of schemas that have to actually own PL/SQL code? It would seem rather difficult to figure out where some piece of code was if you had to trawl through 250 separate schemas looking for it.
Snyds wrote:There is one exception. Privileges granted directly to PUBLIC are as good as privileges granted directly to an individual user. This sounds like a development (or student) database, so letting anyone in the database see what's in CM_MASTER.TABLE_NAME might be okay. Again, this lets everybody in the database see that table; not just the users who have the sr_project_role.
... While researching this issue I found the statement below:
“The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.”
Is this true? My testing seems to confirm this. ...
Another work-around is to write a procedure (or a SQL-from-SQL script) that queries dba_role_privs to see whio has sr_project_role, and then grant each one privileges on individual tables, such as CM_MASTER.TABLE_NAME.
Here are some reasons for using roles from the documentation:
See the section "Properties of Roles and Why They Are Advantageous".
You may also want to investigate Invoker's rights vs. Definer's rights. With Invoker's rights, roles are active. These are also discussed in that same link.
Snyds wrote:In most databases, you'll end up with a relatively small number of users that need to own PL/SQL objects and that need direct grants and boatloads of users that simply need permission to access objects owned by other users. Roles exist to simplify the task of managing the privileges of that vast majority of database user accounts and to provide additional security mechanisms. For example, roles can be made default or non-default or can require an additional password so your applications can easily enable or disable particular roles based on whatever logic you want or you can make it more difficult for users that fire up SQL*Plus connecting to the database to exercise their most dangerous privileges by requiring an extra step of enabling an additional role (with or without an additional password being required).
My question was almost rhetorical in that if roles do not provide the privileges needed why use them.
Since roles do not affect the privileges that PL/SQL code is compiled with, you also don't risk invalidating code when you grant or revoke roles from a user. On the other hand, if the DBA is granting or revoking system privileges from a user, Oracle may need to recompile the objects owned by that user.