Search code examples
sqldatabaseoracle-databaseindexingsql-grant

Granting Table and Index Permissions to another User

User1 grants Select, Delete permissions on a Table(which has index say TableIndex) to a user2.

I can see that the user2 is not able to see the index existing on that table though.

User2 runs this SQL:

select DBMS_METADATA.GET_DDL('INDEX','TableIndex') from DUAL;
Result is:  The specified object was not found in the database.

When User2 executes Select SQL Queries on the Table, i hope Index will be used, without any further grants (altho index is not visible)?

Solution

  • in order to use dbms_metadata.get_ddl you need select_catalog_role granted to user2 (see here). But the index will be used (where applicable/possible) even though you cannot see it.