1

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?

4

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.

  • This confirmed my own conclusions based on empirical evidence. It's a shame that our own DBA couldn't provide this information when I asked. – Spencer K Jan 10 '12 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.