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:
- The first instance I login as the schema user "MYDBOWNER".
- The second instance I login as a role-permissioned user "MYDBUSER", though the schema is still owned by "MYDBOWNER".
- I run the following query to see the objects owned by user "MYDBOWNER":
select * from all_objects where owner='MYDBOWNER';
- As MYDBOWNER, I get a list of all the objects including the indexes (but not the constraints).
- 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?