Re: Can I create "private" index

The real question behind your question is "If I have a right to select from particular table, does it mean that I have a right to use ANY index on this table?"
I'm afraid
this issue is not dicussed in Oracle manuals because article on SELECT in SQL Reference does not metion indexes, while Oracle Server Tuning does not mention system or object privileges. In fact, there is no such system/object privilege as USE INDEX.

It is clear that if table and index are in the same schema, then the right to use the index is automatic, but what happens when table and index are in different schenas?

1. Create index in USER2 schama. Perfect option, as the index will be private to USER2. However, in this case USER2 needs additional privileges, either INDEX object privilege on this particular table USER1.TABLE1 or CREATE ANY INDEX system privilege. The only inconvenience of this option is that USER2 will be able to create additional indexes on this table (with object privilege) on on any table (with system privilege).

2.

Stanislav Kharab wrote:
>
> I have two users USER1 and USER2 and two corresponding schemes USER1
> USER2.
> There is a table TABLE1 under schema USER1 with few indexes IND_N1,
> IND_N2, IND_N3.
> USER2 has a "select" privilege for this table. I need to create an index
> for table USER1.TABLE1 which only USER2 can use. This index should not
> be visible, accessible or usable by USER1.
> Is it possible in Oracle 7.3.2 ? If yes, maybe someone know how to do
> it?
>
> Thanks. Stan.
Received on Wed Oct 15 1997 - 00:00:00 CDT