Another privileges question

I have a db with multiple users in it.
I would like to create a procedure that would reorganize the indexes for all tables, owner by any user.
I thought of compiling this procedure only once for all, into my DBA schema (the actual name is not really DBA) and to grant execute to this procedure to all users.

Problems:
- I understand Oracle privileges in effect when the procedure is executed,
are those of the DBA user, not those of the user calling the procedure.
- So fetching the USER_INDEXES table returns indexes owned by DBA, not the
user running the procedure (or the one who called it, however you want to see this). I can use the SYS.DBA_INDEXES table instead with a WHERE OWNER=USER (because on the other hand, USER contains the name of the calling user, not DBA). That's confusing a bit nut it works.
- Now that the procedure finds an index to rebuild, it issues an EXECUTE
IMMEDIATE ('alter index owner.indexname rebuild;') but since DBA has not been granted privilege to alter this index (only thru a role) then it cannot run that command in a procedure, but only from the SQLPLUS prompt.

How would you do such a thing?
Is the only way to compile the procedure into each schema? Grant privileges to DBA so that it can play with any indexes? Can I have the procedure only in the DBA schema like I intended to?

Thanks for your assistance.

Syltrem Received on Sat Jan 25 2003 - 13:44:04 CST