giving another user "grant XXX" privs on my schema objects
niz
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.
Douglas Hawthorne
news:fc69f921.04042...@posting.google.com...
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
Niall Litchfield
I would consider writing a pl/sql routine to do this and grant lemon execute
on that procedure. I'd probably reject it in favour of having SLIME control
his own destiny but there you go.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/
Turkbear
If LEMON and only LEMON is to get access to objects in your schema, LEMON does not have to
grant anything..SLIME grants the desired privs to LEMON.
Or,
create a role that has the rights you want LEMON ( or others, eventually) and assign LEMON that role.