CODEWITHSUNDEEP
sqloracle-database
Djaballah Mohammed DJEDID
Djaballah Mohammed DJEDID

Reputation: 599

grant create index on specific table

I'm using oracle and I want to create an index to a specific table belongs to another user so how I grant the privilege to do so without using the requete

grant create any index

Upvotes: 4

Views: 34000

Answers (3)

user5683823
user5683823

Reputation: 

grant index on [tablename] to [user]

What privileges the table owner him/herself must have in order to be allowed to grant create index on a table to another user will depend on your version of Oracle; check the documentation.

Upvotes: 5

Kacper
Kacper

Reputation: 4818

Create procedure in table owner schema

create or replace 
procedure create_index(col_list varchar2, index_name varchar2) as
begin
execute immediate 'create index ' || index_name || ' on ' || 'PUT_TABLENAME_HERE'|| '(' || col_list || ')';
end;

GRANT EXECUTE ON table_owner.create_index TO user;

Call

begin
table_owner.create_index('ID','IDX1');
end;

I think it should work.

Upvotes: 0

Durante
Durante

Reputation: 305

If you want to create a index to a table in another schema first you need to grant the system privilege to the user you want use to create de index.

To create an index in another schema, you must have the CREATE ANY INDEX system privilege.

More reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm

Here the SQL:

/*No tested*/
Grant create any index to user;

Upvotes: 0

Related Questions