Re: Oracle User Security via a middle layer
Date: Sun, 10 Oct 2010 21:33:17 -0700 (PDT)
Message-ID: <1b3da610-3bcb-40fd-8515-e4eadd28252b_at_a19g2000prb.googlegroups.com>
On Oct 10, 5:10 am, z1hou1 <z1h..._at_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 Received on Sun Oct 10 2010 - 23:33:17 CDT