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

Krabatz

unread,

Jan 18, 2008, 5:58:37 AM1/18/08

to

Hi,

Core question:

I'd like to execute this statement as "appuser":
ALTER INDEX adminuser.my_oracle_text_index REBUILD;

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?

Frank van Bortel

unread,

Jan 18, 2008, 7:46:38 AM1/18/08

to

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

Krabatz

unread,

Jan 18, 2008, 9:53:54 AM1/18/08

to

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?

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

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...@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*?!?
>
> > 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:

> Top-posting in UseNet newsgroups is one way to shut me up- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

fitzj...@cox.net

unread,

Jan 18, 2008, 4:22:06 PM1/18/08

to

Comments embedded.

On Jan 18, 8:53 am, Krabatz <krab...@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...@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

Krabatz

unread,

Jan 21, 2008, 5:18:40 AM1/21/08

to

Hi,

thanks for your answer.

You write:

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

I don't see another alternative than that when the customer wants to
check if the changes he made went into the system.

scenario:
- application admin user changes data (application makes insert or
update)
- application admin user wants to test it and uses the search
functionality (application uses Oracle Text)
- no search results (customer dials my number, swearing)

Every automated solution has a delay which will be suitable in most
cases but sometimes the customer wants a result immediatley. Thus he
needs a bottom to invoke the rebuild of the index manually and that's
why I need this right for the application user.
I see and understand your concerns, but I can't see a different
solution than that.

You say, it is possible to give that special right to the application
user. I looked up the grant documentation (http://download.oracle.com/
docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015) but
couldn't find the correct syntax for it.

Thanks,
Jan.

Krabatz

unread,

Jan 21, 2008, 6:17:45 AM1/21/08

to

Hi everyone,

I found a solution for myself now. No idea if that is the easiest way.
However, it works.

I put the "alter index" statement in a stored procedure and put it in
a package owned by the data_user. The execution of the package has to
be granted to the app_user.

Here is how it works in detail:

CREATE OR REPLACE PACKAGE data_user_package AS
PROCEDURE rebuild_index;
END data_user_package;
/

CREATE OR REPLACE PACKAGE BODY data_user_package AS
PROCEDURE rebuild_index IS
BEGIN
-- Not allowed to execute DDL statements within a
-- procedure. Execute it dynamically as a workaround.
EXECUTE IMMEDIATE 'ALTER INDEX data_user.my_oracle_text_index
REBUILD';
END;
END data_user_package;
/

Grant to app_user as data_user:
grant execute on data_user_package to app_user;

Call procedure as app_user:
exec data_user.data_user_package.rebuild_index;

Works for me. BTW: that was the answer I expected from the outset ;-)

Cheers,
Jan Mutter.

Krabatz schrieb:

Vladimir M. Zakharychev

unread,

Jan 22, 2008, 11:18:17 AM1/22/08

to

This IS NOT a correct way to synchronize a Text index (which is what
you essentially try to achieve.) Every time you execute that ALTER
INDEX ... REBUILD statement without proper PARAMETERS clause (which
should be 'SYNC',) you LOCK the table and the index and rebuild the
index completely, that is, essentially, drop and recreate it. As data
will be added to the table, your users will experience longer and
longer delays while the index is being rebuilt - and they will be
unable to perform *any* queries against the table meanwhile (and no
DML, too, since you didn't include ONLINE keyword as well.)

What you should've done is simply create the index with proper
parameters:

CREATE INDEX idx$blah ON blahblah.blah INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('SYNC ON COMMIT');

This would result in the index being automatically synchronized on
commit. No need for extra code or grants, minimal delays on commits
(as Text will need to do its work on new/updated data before commit
returns control and it takes some time,) no hassle.

I would highly recommend that you thoroughly read through the Oracle
Text Reference, understand the concepts and familiarize yourself with
available options and procedures before you start implementing
incorrect, inefficient and potentially damaging solutions to a problem
that doesn't actually exist.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Krabatz

unread,

Jan 23, 2008, 8:01:28 AM1/23/08

to

Thanks, that helps. Just for readers of this thread with similar
problems. The correct syntax is:
... PARAMETERS ('SYNC(ON COMMIT)');

Cheers,
Jan

On 22 Jan., 17:18, "Vladimir M. Zakharychev"

> http://www.dynamicpsp.com- Zitierten Text ausblenden -

Vladimir M. Zakharychev

unread,

Jan 23, 2008, 2:14:53 PM1/23/08

to

On Jan 23, 4:01 pm, Krabatz <krab...@gmail.com> wrote:
> Thanks, that helps. Just for readers of this thread with similar
> problems. The correct syntax is:
> ... PARAMETERS ('SYNC(ON COMMIT)');
>
> Cheers,
> Jan
>

I stand corrected - sync mode should indeed be enclosed with
parentheses.

Cheers,

Frank van Bortel

unread,

Jan 24, 2008, 3:08:15 PM1/24/08

to

Which I indicated about a week ago, but reading
seems hard these days...