Skip to content
Home » Oracle » Grant Create Index to User?

Grant Create Index to User?

CREATE INDEX Privilege?

Tried to grant a CREATE INDEX system privilege to an user, it failed with ORA-00990.

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

ORA-00990 told us that the system privilege you specified is not a valid privilege.

Apparently, there is no CREATE INDEX system privilege in Oracle. In fact, the ability to create your own index is inherited from CREATE TABLE, so there's no need to grant such privilege if you want to create your own index.

Can we Create Index on Other's Table?

The answer is positive, you can create indexes for tables belonging to other users in your own schema, but you need more privileges. Try the following example:

SQL> conn oe/oe@orcl
Connected.
SQL> create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender);
create index customer_id_gen_idx on sh.customers (cust_id, cust_gender)
                                       *
ERROR at line 1:
ORA-00942: table or view does not exist

ORA-00942 means that the table is not in the scope of user. That is to say, the user need to access the table.

Solution

We take 2 steps to make the user able to index on other's table.

GRANT SELECT or SELECT ANY TABLE

But first thing first, we should enable the user to access (SELECT) the table, we have 2 granting options, one is to grant an object privilege, the other is to grant a system privilege.

SQL> show user
USER is "SH"
SQL> grant select on customers to oe;

Grant succeeded.

Alternatively, you can also get SELECT ANY TABLE, a system privilege to see other's table.

SQL> show user
USER is "SYSTEM"
SQL> grant select any table to oe;

Grant succeeded.

Let's create the index again.

SQL> create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender);
create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender)
                                          *
ERROR at line 1:
ORA-01031: insufficient privileges

Now it's ORA-01031, which means you didn't use the correct privilege to create the index. In fact, the right privilege to this issue is INDEX object privilege.

GRANT INDEX or CREATE ANY INDEX

We should grant the right privilege to the grantee.

SQL> show user
USER is "SH"
SQL> grant index on customers to oe;

Grant succeeded.

Turn to OE.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);

Index created.

At least, you need INDEX object privilege to create an index for the table belonging to another user. Additionally, you may need SELECT object privilege for data retrieval.

Alternatively, you can also get CREATE ANY INDEX, a system privilege to create indexes for other user's table in your own schema.

SQL> show user
USER is "SYSTEM"
SQL> grant create any index to oe;

Grant succeeded.

To know all available object privileges, you may refer to Oracle Database 21c Object Privileges List.

Leave a Reply

Your email address will not be published. Required fields are marked *