RE: way to grant schema privilege
Date: Wed, 30 Sep 2009 11:49:06 -0400
What is incorrect is that a proxy user has direct access to all objects in the schema similar to if they actually logged into that schema directly. To your point though this will not affect the insert, drop, index, or other privileges that an owner enjoys, because the user has become the owner.
Point taken though that this does not address proxy users at the application server tier, mainly because it is a completely different subject.
Senior Oracle DBA/NA Team Lead
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of dbvision_at_iinet.net.au
Sent: Tuesday, September 29, 2009 7:33 PM To: 'Oracle L'
Subject: RE: way to grant schema privilege
What exactly is incorrect, Richard?
I stated very clearly that proxy users relate to authentication, not role
granting. You provide an example to show how to setup authentication by proxy
and you call what I said incorrect?
Care to re-read what I said?
Please recall that the OP wanted to know how to grant ONLY select,update,delete
to all objects. Not insert. Giving him a proxy user to schema owner is rather
NOT what he asked for, I'd dare say?
On Tue Sep 29 23:54 , "Goulet, Richard" sent:
>Sorry, Nuno, but that is incorrect. Please see
>Senior Oracle DBA/NA Team Lead
Behalf Of Nuno Souto
>Sent: Monday, September 28, 2009 11:57 PM
>Cc: Oracle L
>Subject: Re: way to grant schema privilege
>Not directly, no. Even through proxies, you still need to grant access
>objects via a role and then the role to a logon, be that a proxy or for
>any logon that does a "ALTER SESSION SET CURRENT_SCHEMA=".
>In other words: the proxy user is not a replacement for granted
>Your choice if you use a proxy logon - relevant for three-tier access -
>something like a login trigger setting current_schema. Then a role is
>that logon. The role defines the access privileges, not the user
>cannot grant an entire schema to a role, it has to be object by object.
>in sunny Sydney, Australia
>dba1 mcc wrote,on my timestamp of 29/09/2009 4:07 AM:
>> On ORACLE 10GR2 and 11G is it possible grant access privileges on
>schema level NOT table/view level.
>> for example, I want grant 'select, update, delete" on one schema (all
>object under that schema) to another person. Is it possible?