Re: Another privileges question

On Sat, 25 Jan 2003 14:44:04 -0500, "Syltrem" <syltremzulu_at_videotron.ca> wrote:

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

1 Your first statement is version dependent. In 8i and higher (and who is using 8.0 and 7.3 anyway) you can create the procedure with definer's rights (the default) and invokers rights, by using authid current_user in the procedure definition 2 I would set up a DBA like account with 'CREATE ANY INDEX' granted directly and 'ALTER ANY INDEX' directly
3 Secondly the owner of the index should end up as a parameter to the procedure defaulting to the current user

Doing so, one procedure would be sufficient. It is how dbms_utility runs anyway.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jan 25 2003 - 14:33:33 CST