Privelege Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Privelege Question

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Unhappy

    I created a procedure that creates a cursor with index_name and owner from dba_indexes. When I run it as an anonymous block as the dba it works fine. Then I made the anonymous block into a procedure under the dba schema and it is saying that "sys.dba_indexes" is not declared. I think this is because it is saying that the procedure does not have rights to the data dictionary.

    I found in the oracle documentation this:
    Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.

    The dba user has the dba role granted to them. I tried granting the select_catalog_role to public and to the dba as a privilege instead of a role to see if my procedure would work but it didn't. Any ideas of how to give my procedure the rights it needs to query the dba_indexes?

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    grants for procedures do not work through a role, u have to grant individually on each objects.

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    I tried but it didn't work

    I did the following:

    grant select_catalog_role to dba;

    But it still didn't work.

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    gpsingh meant:

    grant execute procedure_xyz to dba;

    Not thru the role you are trying...
    Reddy,Sam

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, no, granting the exec on that procedure to DBA (or even to the executing user directly) will not solve anything. The problem are grants on tables that procedure references!

    Suppose we have a user A with DBA role. This user creates a procedure PROC1. Inside this procedure there is a query that selects from SCOTT.EMP. From SQL A can select from SCOTT.EMP without problems because he has SELECT ANY TABLE privilege granted through role DBA. Now, during the execution of a procedure user's A roles are *disabled*, so he can't select from SCOTT.EMP table from inside procedure. You must grant SELECT ANY TABLE or SELECT ON SCOTT.EMP *directly* to the user and everything will be fine.

    So the rules is quite simple:
    - Owners of the procedures must have *direct grants* to the objects referenced inside the procedures, grants through roles are useles during PL/SQL execution.
    - The users who want to execute the procedures that they don't own must have been granted EXECUTE privilege to those procedures *either dirrectly or through role*, both of this two ways of grants are OK for them.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Jmodic,

    Explaination purpose(what gpsingh trying to say), I have taken random example. Not as such a solution to his problem, as he is trying to grant the privilege again to role but, not the objects directly.

    Thanks for pointing out...
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width