Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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