Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!a19g2000prb.googlegroups.com!not-for-mail
From: Noons <wizofoz2k@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle User Security via a middle layer
Date: Sun, 10 Oct 2010 21:33:17 -0700 (PDT)
Organization: http://groups.google.com
Lines: 36
Message-ID: <1b3da610-3bcb-40fd-8515-e4eadd28252b@a19g2000prb.googlegroups.com>
References: <6c527816-9a53-4ba5-8aad-90be3ecc9c05@e14g2000yqe.googlegroups.com>
 <593cad6b-dbf2-4f75-a5c6-92e9136b1494@x7g2000yqg.googlegroups.com> <f603c534-38b3-4e70-be71-bdef8916fa27@e14g2000yqe.googlegroups.com>
NNTP-Posting-Host: 203.202.124.168
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1286771597 25295 127.0.0.1 (11 Oct 2010 04:33:17 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 11 Oct 2010 04:33:17 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a19g2000prb.googlegroups.com; posting-host=203.202.124.168; posting-account=oJZDiQoAAAD-FXU2V1mvdIjuYivOHSlr
User-Agent: G2/1.0
X-HTTP-Via: 1.1 localhost.localdomain[0A201566]
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9)
 Gecko/20100824 Firefox/3.6.9 ( .NET CLR 3.5.30729),gzip(gfe)
Xref:  news.cambrium.nl

On Oct 10, 5:10=A0am, z1hou1 <z1h...@gmail.com> wrote:
>
> SELECT SYS_CONTEXT('USERENV','PROXY_USER') FROM DUAL;
>
> I think the above statement is valid only if you connect with the
> following syntax..
>
> connect <proxy_user>[real user]/<proxy user password>
>
> I do realize that we have to contend with roles etc that will have to
> be set for the proxy user via the ALTER USER command.
>
> But I know that we have to change the app as the number of user's is
> increasing and we have to go to a genuine mid-tier based connection
> pool. And I have decided to read up on the various methods that can be
> used for identifying/authenticating the user.

I've had good success with a login trigger, rather than a proxy user.
Proxy users work fine, but they still require setting up synonyms and
roles to access another schema's objects.  Doing that for every proxy
user is a recipe for synonym overload, and public synonyms are likely
not the best performance option nor are they flexible if you want to
consolidate applications into single db instances.

We have a login trigger that looks at the IP address and user login
via sys_context, then simply does a "alter session set current_schema
<whatever>', where <whatever> is the owner/schema of the target
application(s).  This changes all access to objects for this logon to
become <whatever>.object_name and still keeps that access under
control of whatever roles we grant to the initial login id.  There is
no need for an "alter user" to set roles anywhere or for setting roles
dynamically.
At any stage if we want to change access to any given application, all
we have to do is change the role.

HTH