Re: alter index (Oracle Text index) rebuild on different schema => no rights
Date: Mon, 21 Jan 2008 03:17:45 -0800 (PST)
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
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:
Works for me. BTW: that was the answer I expected from the outset ;-)
> 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.
> - application admin user changes data (application makes insert or
> - 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.
Received on Mon Jan 21 2008 - 05:17:45 CST