Saturday, June 18, 2011

GRANT CREATE ANY INDEX TO and GRANT INDEX ON

If you grant create any index to a user, this allows him to create an index on any table in any schema:

SQL> conn system/manager
Connected.
SQL> create table any_table
  2  (col1 varchar2(4))
  3  /


Table created.

SQL> create table specific_table
  2  (col1 varchar2(4))
  3  /


Table created.

SQL> create user andrew identified by reid
  2  default tablespace users
  3  quota unlimited on users
  4  /


User created.

SQL> grant create session, create any index to andrew
  2  /


Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create index any_table_index
  2  on system.any_table(col1)
  3  /


Index created.

SQL> create index specific_table_index
  2  on system.specific_table(col1)
  3  /


Index created.

SQL> drop index any_table_index
  2  /


Index dropped.

SQL> drop index specific_table_index
  2  /


Index dropped.

SQL>

If you really need to allow users to create indexes in other schemas, you could consider limiting this privilege to specific tables with the grant index on command:

SQL> conn system/manager
Connected.
SQL> revoke create any index from andrew
  2  /

Revoke succeeded.

SQL> grant index on specific_table to andrew
  2  /

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create index any_table_index
  2  on system.any_table(col1)
  3  /
on system.any_table(col1)
          *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> create index specific_table_index
  2  on system.specific_table(col1)
  3  /

Index created.

SQL>

2 comments:

NullUserException said...

Oracle documentation states you must have CREATE ANY INDEX to be able to create indexes on other schemas:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11722

What you did might work with SYSTEM, but won't work with other schemas.

Andrew Reid said...

Yes, you are right, that was the point I was trying to make. But, looking back at the post now, I see that I could have explained it a bit better.