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

From: Krabatz <>
Date: Mon, 21 Jan 2008 03:17:45 -0800 (PST)
Message-ID: <>

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;


	PROCEDURE rebuild_index IS
		 -- 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

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

Jan Mutter.

Krabatz schrieb:
> 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 (
> docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015) but
> couldn't find the correct syntax for it.
> Thanks,
> Jan.
Received on Mon Jan 21 2008 - 05:17:45 CST

Original text of this message