In Oracle, what grants do I need as a non-schema user to view rows in all_objects?

Question

I have two instances of an oracle 11g database, both have the same object definitions in the schema: tables with indexes and constraints, a sequence, and a stored procedure.

The facts of the case:

1. The first instance I login as the schema user "MYDBOWNER".
2. The second instance I login as a role-permissioned user "MYDBUSER", though the schema is still owned by "MYDBOWNER".
3. I run the following query to see the objects owned by user "MYDBOWNER":

select * from all_objects where owner='MYDBOWNER';

1. As MYDBOWNER, I get a list of all the objects including the indexes (but not the constraints).
2. As MYDBUSER, I don't see the indexes, but I see the table, sequence and procedure.

The permissions for the role is correct (table has select, delete, update and insert, sequence has select, and procedure has execute). So why don't I see the indexes? Is there an additional grant that needs to be provided to the role for the table?

Answer 1

Oracle data dictionary views that have the ALL_ prefix provide information about objects that your user has access to. Views that have the DBA_ prefix provide information about all objects in the database. And views that have the USER_ prefix provide information about objects that you own.

In this case, MYDBUSER does not and can not have privileges on the indexes owned by MYDBOWNER because there are no privileges one can grant on individual indexes so it makes sense that MYDBUSER would not see the indexes in ALL_OBJECTS. If MYDBUSER were granted access to DBA_OBJECTS or DBA_INDEXES (either individually or via the SELECT ANY DICTIONARY privilege), you could query DBA_OBJECTS (or DBA_INDEXES) to get a list of all the indexes owned by MYDBOWNER.