Re: Who can grant Object Permissions ?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:2fvh9u8e7t2658c2gn30f3kah08ulthd2u_at_4ax.com...
> On Thu, 21 Mar 2002 06:33:01 +1100, "Howard J. Rogers"
> <dba_at_hjrdba.com> wrote:
>
> >Your understanding is correct. Only the user owning an object can grant
> >permissions on that object, unless s/he grants a permission 'with grant
> >option'... in which case, the grantee can then grant that permission on
to
> >others.
> >
> >How do you therefore grant those permissions without knowing the User's
> >password? Strictly speaking, you can't, and you shouldn't. So what
follows
> >is considered extremely bad form, and rather rude DBAing... but
occasionally
> >the need arises.
> >
>
> >Regards
> >HJR
>
>
> Not to nitpick, but there is a much more elegant solution to this one
> (c/o Thomas Kyte)
> - connect as an user with create any procedure and execute any
> procedure privilege
> - now create a procedure <remoteuser>.do_dll with a call do dbms_sql
> or execute immediate
> evidently remoteuser is the owner of the affected table
> - now exec remoteuser.do_ddl('grant select,insert,update, delete on
> emp to public')
> - and presto
>
> NO need to change the password.
>
> Regards
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

That's a good one! Indeed it is elegant. Taking it a step further, then drop procedure remoteuser.do_ddl.
Next step: encapsulate the whole thing as a a permanent procedure in a package in my own account, parameterising the various values...DDL from DDL from SQL.

Paul Received on Thu Mar 21 2002 - 14:23:04 CST