Re: giving another user "grant XXX" privs on my schema objects

"niz" <niz_at_infidel.freeserve.co.uk> wrote in message news:fc69f921.0404230158.2affc441_at_posting.google.com...
> i am user SLIME, owning objects in SLIME schema.
>
> i would like user LEMON to have the ability to "grant
> [select|insert|delete|update] on" any object in my SLIME schema to
> LEMON (and only LEMON if possible).
>
> note i am NOT saying that LEMON should have
> select|insert|delete|update privileges on all my SLIME objects, just
> that he should, with the LEMON user, be able to "grant XXX on
> SLIME.YYY to LEMON" or "revoke XXX from SLIME.YYY to LEMON".
>
> what would be the best way to achieve this, without making LEMON a
> too-powerful user?
>
> thanks.

I am somewhat confused by your requirements. Are you saying that for LEMON to have SELECT access to a table in the SLIME schema, LEMON has to GRANT SELECT ON slime.test_table TO lemon ?

The Oracle security model does not work this way. You can only grant privileges (if you are authorised by the ADMIN OPTION) that you have been granted yourself unless you have the GRANT ANY OBJECT PRIVILEGE (see p.17-29 of "Oracle 9i SQL Reference" manual). If you try to do a GRANT or REVOKE against yourself, you will get the following message: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

What problem are you trying to solve by having these requirements? It sounds suspiciously like you want to hide away access to the SLIME schema inside some application code by doing a GRANT, perform some DML, and then do a REVOKE. If this is so, you consider procedures that execute under invoker rights of the definer (see p.14-68 of "Oracle 9i SQL Reference" manual for further details).

Douglas Hawthorne Received on Fri Apr 23 2004 - 06:41:56 CDT