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

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;

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:
> 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.
Received on Mon Jan 21 2008 - 05:17:45 CST