Re: Another privileges question

I was not aware of this new option in 8i (authid current_user) That's what I needed!

And I add the Oracle version to my signature so that I never forget to include it.

Thanks

--
OpenVMS 7.2-1, Oracle 8.1.6.0

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove zulu from my address

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> a écrit dans le message de
news: aus53v0fqcmgo8aa4kknlf9frkgr2t138v_at_4ax.com...


> 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