RE: way to grant schema privilege

From: Goulet, Richard <>
Date: Wed, 30 Sep 2009 11:49:06 -0400
Message-ID: <>


        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.

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International

-----Original Message-----
[] On Behalf Of
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
>Dick Goulet
>Senior Oracle DBA/NA Team Lead
>PAREXEL International
>-----Original Message-----
g] On
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
>privileges, it
>complements them.
>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
>granted to
>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.
>Nuno Souto
>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?


Received on Wed Sep 30 2009 - 10:49:06 CDT

Original text of this message