Tuesday, June 14, 2011

Grant Create Index?

(This first part was tested on an Oracle 10 database.) I always thought that if you could create a table, you could create an index too:

SQL> create user andrew
  2  identified by reid

  3  default tablespace users
  4  quota unlimited on users;


User created.

SQL> grant create session, create table to andrew;

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create table test_table
  2  (col1 varchar2(4));


Table created.

SQL> create index test_index
  2  on test_table(col1);


Index created.

SQL>

But looking through a book about Oracle 11 on Google books, I came across a reference to granting a create index privilege. I never knew that such a privilege existed. I cannot show you the content as it is covered by copyright and I do not want to give the book's name as it comes from a series which are usually accurate.

I decided to try it out, this time on an Oracle 11 database, but I could not get it to work:

TEST11 > sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 14 12:20:05 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user andrew identified by reid;

User created.

SQL> grant create index to andrew;
grant create index to andrew
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

SQL>

No comments: