This discussion is archived
7 Replies Latest reply: Mar 21, 2012 9:53 AM by Mark Williams-Oracle RSS

What good are roles?

832104 Newbie
Currently Being Moderated
Greetings All,

I am trying to eliminate granting the DBA role to our application users.

In doing so, I (as the CM_MASTER user which has been granted the DBA role) created a role called SR_PROJECT_ROLE and granted that role select, insert, update, and delete privileges on several CM_MASTER schema tables.

A new user was created (B1), and granted the roles CONNECT, RESOURCE (same as before), and the new SR_PROJECT_ROLE. When this new user tried to compile a package/procedure that references one of the CM_MASTER schema tables it failed because of: "PLS-00201: identifier 'CM_MASTER.TABLE_NAME' must be declared".

I checked, and the new SR_PROJECT_ROLE was a default role for the B1 user.

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. After I revoked the SR_PROJECT_ROLE from the user, and explicitly granted access to the CM_MASTER tables directly to the user B1, the package compiled successfully.

Our application currently has over 250 users. Roles would be a nice thing to use, if they worked.

Am I missing something here?

Thanks,
Bob
  • 1. Re: What good are roles?
    Talip Hakan Ozturk Oracle ACE
    Currently Being Moderated
    *“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.

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/pl_sql.htm#i1004791



    Talip Hakan Ozturk
    http://taliphakanozturken.wordpress.com/
  • 2. Re: What good are roles?
    Justin Cave Oracle ACE
    Currently Being Moderated
    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.

    Justin
  • 3. Re: What good are roles?
    832104 Newbie
    Currently Being Moderated
    Justin,

    The users are not writing his or her own procedures. Each schema was established by importing a previously exported base-line schema using the remap_schema clause of the impdp. The packages and procedures are already established.

    Bob
  • 4. Re: What good are roles?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Bob,
    Snyds wrote:
    ... 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. ...
    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.

    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.
  • 5. Re: What good are roles?
    832104 Newbie
    Currently Being Moderated
    Frank,

    I am testing/developing this on a non-production system but will eventually implemented in production.

    My question was almost rhetorical in that if roles do not provide the privileges needed why use them.

    Bob
  • 6. Re: What good are roles?
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Here are some reasons for using roles from the documentation:

    http://docs.oracle.com/cd/E11882_01/network.112/e16543/authorization.htm#BABCADIB

    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.
  • 7. Re: What good are roles?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Snyds wrote:
    My question was almost rhetorical in that if roles do not provide the privileges needed why use them.
    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).

    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.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points