Grant all to a user doesn't grant create index privilege

ewang1205
ewang1205 used Ask the Experts™
on
I did this: grant all on my_table to my_user.  But, my_user still cannot create an index on my_table.  Why?  I thought grant all include index privilege.  Thanks.

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON ,my_table TO my_user;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
To be able to create and index you must have the create index system privilege.  You can assign this using:

grant create index to my_user
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Why do you want a user creating their own index on a table they don't own?

Does the user have the privs to create objects in the database?

Are they trying to create a local index in their schema or in your schema?
Sorry, I'm talking rubbish, I'm thinking of the create any index system privilege.  As you correct say GRANT INDEX.... should be enough.

What error are you getting?
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

If you are connected to my_user and my_user owns the table my_table then there is no point issuing the following grant:

grant all on my_table to my_user

Providing you have the CREATE TABLE system privilege you should be able to create tables in your own schema and query, insert, update, delete and create indexes on those tables as you own them.

You only need to grant these privileges on your tables to "other" users.

Author

Commented:
grant all on me.my_table to other_user.  other_usercan create a table , but cannot create an index.  The following is the error.

ERROR at line 1:
ORA-01031: insufficient privileges


You have prefixed my_table with a schema name which suggests that the current Oracle schema from which you are running the statement doesn't own my_table.

You can only grant privileges to tables that you own or for which you have been given the GRANT OPTION e.g.

connect user_a

create table x.....;

grant all on x to user_b WITH GRANT OPTION;

connect user_b

grant all on user_a.x to user_c;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Again:  Are they trying to create a local index in their schema or in your schema?

They should be able to do:
create index my_index on me.my_table;

Unless you want to give them permission to create an object in your schema they won't be able to do:
create index me.my_index on me.my_table;

I am still curious why you want them creating indexes on your tables.

Author

Commented:
I want them to create table on my schema, and also create a index on my schema.  Strange thing is they can create a table, not a index.  
They will also require the CREATE ANY INDEX privilege.

I assume they already have the CREATE ANY TABLE privilege to allow them to create tables in a schema other than their own.

Author

Commented:
grant all on a table to a user doesn't grant the create index to the user. grant any index will do.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Remember, granting 'ANY' means any.  They can create indexes in the SYS schema.
Just to clarify, the GRANT ANY INDEX is very powerful and as slightwv says it will allow users to create indexes in any schema.

Providing a user has been granted index privilege on a table they should be able to create an index on that table e.g.

connect john;

create table accounts ......;

grant index on accounts to fred;

connect fred;

create index freds_index on john.accounts;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial