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

Comments embedded.
On Jan 18, 8:53 am, Krabatz <krab..._at_gmail.com> wrote:
> Hi,
>
> thanks for your quick answer!
>
> I didn't get some of your statements. So please don't bother if I have
> to ask again...
>
> > > 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 athttp://tahiti.oracle.com:
>
> > alter index whatever rebuild parameters ('sync');
>
> I asked here for the correct syntax for "grant", not for "alter index"
> which was obviously right as you repeated my statement. I looked up
> how "grant" works but couldn't find something like:
> grant alter schema_owner_user.my_oracle_text_index to app_user;
> (^^^ just as an example; this is NOT correct)
>
> Is it possible to give a user such a special right?

Certainly it is. Is it wise or prudent to do so? Not usually, and this doesn't appear to be a special case.

>
> > 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.
>
> OK, thanks. What would you recommend for Oracle 10.2.0.1.0?

First I'd recommend patching to 10.2.0.3. Next I'd recommend perusing the documentation for 10gR2 at http://tahiti.oracle.com.

>
> Thanks,
> Jan.
>
> > 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.
>
> On 18 Jan., 13:46, Frank van Bortel <frank.van.bor..._at_gmail.com>
> wrote:
>
>
>
> > 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*?!?

I second that question.

>
> > > 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 athttp://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.
>

And I heartily agree.

> > > 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;

No, you *think* that would solve your 'problem'. The actual problem, as I see it, is your wanting to alter an index by a user account which doesn't own it. Only the object owner should be altering its objects.

>
> > > 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- Zitierten Text ausblenden -
>
> > - Zitierten Text anzeigen -- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Fri Jan 18 2008 - 15:22:06 CST