how to get granted indexes,triggers and synonyms of granted tables

Question

I have create two user, user1 and user2 and grant all privileges to user2 for some of tables,functions,procedure,views,sequences and packages of user1. I am using following query for getting the list of granted objects:-

SELECT * FROM USER_TAB_PRIVS where grantor = 'user1' and grantee = 'user2'

above mention query gives list of all objects like tables,functions,procedure,view,sequence and package. Its not give the index name, synonyms name and triggers name.

Can anyone suggest me to how grant privileges to index, trigger and synonym. or how to get triggers, index and synonyms of granted tables.

Answer 1

There is no privileges like SELECT for indexes or triggers. Read https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm about possible privilges for any kind of database objects, especially Tables 18-1 and 18-2. If trigger exists and enabled it executed when condition are met (ON INSERT, UPDATE and so on). If index exists it used if optimizer want it.

You can get list of object from views, just add some WHERE on OWNER column:

SELECT *
  FROM all_triggers;

SELECT *
  FROM all_synonyms;

SELECT *
  FROM all_indexes;