Re: alter index (Oracle Text index) rebuild on different schema => no rights

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Fri, 18 Jan 2008 13:46:38 +0100
Message-ID: <85d6c$47909fae$524b5c40$8146@cache1.tilbu1.nb.home.nl>


Krabatz wrote:
> Hi,
>
> Core question:
>
> I'd like to execute this statement as "appuser":
> ALTER INDEX adminuser.my_oracle_text_index REBUILD;
>

Why?!? Why not rebuild the index *as owner*?!?

> Error message:
> ORA-01418: specified index does not exist
>
> How is the syntax to give app_user the grants for alter index of
> schema_owner_user.my_oracle_text_index?

By using the correct syntax, which is of course online at http://tahiti.oracle.com :

alter index whatever rebuild parameters ('sync');

>
>
> More specialized question:
>
> That is how I created the index on "schema_owner_user". It is an
> Oracle Text index with index type CONTEXT to make it possible to
> search the column my_table.my_column with Oracle Text keywords
> "contains":
> CREATE INDEX schema_owner_user.my_oracle_text_index
> ON schema_owner_user.my_table(my_column) INDEXTYPE IS ctxsys.CONTEXT;
>
> Now the "app_user" is able to search with keyword "contains" because
> it has the grants for schema_owner_user.my_table:
> GRANT SELECT ON schema_owner_user.my_table TO app_user;
>
> But "app_user" is not allowed to alter the index.

No - and it shouldn't.
>
> The problem could be solved if giving "app_user" the grant for
> altering any index as "system" user:
> GRANT ALTER ANY INDEX TO app_user;
>
> But I am not allowed to do that (I don't have the system password on
> the production machine of my customer).

Pfew! Lucky customer!
>
> I need to give the right grant as schema_owner_user to app_user. But I
> have no idea how the syntax could be.
>

No, you don't. You should read up on Oracle, and forget SS2K. You should also always post your version of Oracle, up to (at least!) 3 digits, preferably 5. And 10G release 2 is not a version. 10.2.0 is.

Anyway, create your datastore(s) and procedure(s) as ctxsys, and grant execute rights to the data owner. Create index set(s) as ctxsys, lexer(s) as ctxsys, storage as ctxsys, stoplist(s) as ctxsys.

Then, create the index(es) as data owner.

Depending on your version of oracle, there are several automated methods of rebuilding your indexes, none of which requires your application user to have outrageous rights.

-- 

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up
Received on Fri Jan 18 2008 - 06:46:38 CST

Original text of this message