Privelege Question

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?

grants for procedures do not work through a role, u have to grant individually on each objects.

I did the following:

grant select_catalog_role to dba;

But it still didn't work.

gpsingh meant:

grant execute procedure_xyz to dba;

Not thru the role you are trying...

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,

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...