Re: way to grant schema privilege

From: Nuno Souto <>
Date: Thu, 01 Oct 2009 22:07:53 +1000
Message-ID: <>

Thanks, I see what you mean now.

The problem I have with using schema owner as the target for proxy access is that unfortunately Oracle proxy login does not verify standard roles such as RESOURCE. IOW, the facility to stop owner-specific roles on a proxy connect is not really there for a schema owner. The proxy user simply becomes the owner and can do anything to the schema's objects, no matter what we say in the WITH ROLE clause. Including creating new ones even if RESOURCE is denied!

I tend to always use an "access user": a user that has access to the required schema(s) tables via a role, with a "set current_schema" via login trigger if I don't want to use private synonyms. That way there is no chance a proxy user for the access user doing any damage outside whatever is in the access role. All proxy users go through one or more of these access users, depending on security needs of each application.

Of course this does not resolve the OP's problem with having to grant select, update, delete only on all contents of the schema to a role, one object at a time. A little bit of SQL-script generation will always be needed to create a role content for an access user. Good thing is it's only needed once for the access user: proxies inherit all that as they become the access user.

This section of the doco talks about the WITH ROLES clause:

Apologies if my description was not clear.

Nuno Souto
in sunny Sydney, Australia

Goulet, Richard wrote,on my timestamp of 1/10/2009 1:49 AM:

> Nuno,
> 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-----
> From:
> 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----- >> From: >> [','','','')">oracle-l-bounce_at_freelists.or
> 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 >> to >> objects via a role and then the role to a logon, be that a proxy or for >> example, >> 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 - >> or >> 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
> logon.
>> You >> cannot grant an entire schema to a role, it has to be object by object. >> >> 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 Thu Oct 01 2009 - 07:07:53 CDT

Original text of this message