Using an Index created by a different user

Can a query by user "C" on a table owned by user "A" actually use an Index owned and created by "B", although on "A"'s table ?
YES. "C" needs the SELECT privilege on "A"'s table as would be necessary to run the query but does not need any reference/privilege on "B"'s index.


Here's an example :

Table owner : HEMANT
Index owner : AN_INDEX_OWNER
Query run by : A_QUERY_USER

Account "A_QUERY_USER" has SELECT on the table "MY_COPY_OF_OBJECTS" in HEMANT's schema but no privileges on the index in AN_INDEX_OWNER's schema (there's no such thing as granting SELECT on an Index)
Account "AN_INDEX_OWNER" has SELECT on "MY_COPY_OF_OBJECTS" in HEMANT's schema and the privilege to create an Index

When "A_QUERY_USER" runs a query on HEMANT's table, the query does use the Index created by "AN_INDEX_OWNER" !

SQL> connect hemant/hemant
Connected.
SQL> create table my_copy_of_objects as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('','MY_COPY_OF_OBJECTS',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> 
SQL> create user a_query_user identified by a_query_user;

User created.

SQL> grant create session to a_query_user;

Grant succeeded.

SQL> grant plustrace to a_query_user;

Grant succeeded.

SQL> grant select on my_copy_of_objects to a_query_user;

Grant succeeded.

SQL> 
SQL> create user an_index_owner identified by an_index_owner;

User created.

SQL> -- needs "CREATE ANY INDEX" and  SELECT  to be able to create an index
SQL> grant create session,create any index to an_index_owner;

Grant succeeded.

SQL> grant select on my_copy_of_objects to an_index_owner;

Grant succeeded.

SQL> alter user an_index_owner default tablespace users;

User altered.

SQL> alter user an_index_owner quota unlimited on users;

User altered.

SQL> 
SQL> connect an_index_owner/an_index_owner
Connected.
SQL> create index hemant_m_c_o_o_ndx_1 on hemant.my_copy_of_objects(object_id);

Index created.

SQL> create index hemant_m_c_o_o_ndx_2 on hemant.my_copy_of_objects(owner);

Index created.

SQL> 
SQL> -- Verify indexes owned by AN_INDEX_OWNER
SQL> select index_name, table_owner, table_name from user_indexes order by 1;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
HEMANT_M_C_O_O_NDX_1           HEMANT                         MY_COPY_OF_OBJECTS
HEMANT_M_C_O_O_NDX_2           HEMANT                         MY_COPY_OF_OBJECTS

SQL> 
SQL> -- Verify that indexes on HEMANT's tables are owned by AN_INDEX_OWNER
SQL> connect / as sysdba
Connected.
SQL> select owner, index_name, table_owner, table_name from dba_indexes where table_name = 'MY_COPY_OF_OBJECTS' order by 1,2;

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
AN_INDEX_OWNER                 HEMANT_M_C_O_O_NDX_1           HEMANT                         MY_COPY_OF_OBJECTS
AN_INDEX_OWNER                 HEMANT_M_C_O_O_NDX_2           HEMANT                         MY_COPY_OF_OBJECTS

SQL> 
SQL> REM REM REM ################
SQL> 
SQL> -- now we query the table
SQL> connect a_query_user/a_query_user
Connected.
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT                         MY_COPY_OF_OBJECTS                  54123

SQL> select count(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

  COUNT(*)
----------
        17

SQL> 
SQL> -- rerun the queries, we avoid parse overheads now
SQL> 
SQL> set autotrace on
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT                         MY_COPY_OF_OBJECTS                  54123


Execution Plan
----------------------------------------------------------
Plan hash value: 2159204631

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |    34 |  1224 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_COPY_OF_OBJECTS   |    34 |  1224 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | HEMANT_M_C_O_O_NDX_1 |    34 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">54087)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

  COUNT(*)
----------
        17


Execution Plan
----------------------------------------------------------
Plan hash value: 360370019

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |     6 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                      |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| HEMANT_M_C_O_O_NDX_2 |  1876 | 11256 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='HEMANT')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

.
.
.