Re: way to grant schema privilege

  • From: Nuno Souto <dbvision@xxxxxxxxxxxx>
  • Date: Thu, 01 Oct 2009 22:07:53 +1000

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:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4003.htm#i2058312

Apologies if my description was not clear.

--
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision@xxxxxxxxxxxx

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: oracle-l-bounce@xxxxxxxxxxxxx
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
http://www.it-eye.nl/weblog/2005/09/12/oracle-proxy-users-by-example/ 

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[oracle-l-bounce@xxxxxxxxxxxxx','','','')">oracle-l-bounce@xxxxxxxxxxxx

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?

--




--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2009