Re: Oracle User Security via a middle layer
Date: Sat, 9 Oct 2010 09:23:31 -0700 (PDT)
Message-ID: <593cad6b-dbf2-4f75-a5c6-92e9136b1494_at_x7g2000yqg.googlegroups.com>
On Oct 9, 11:27 am, z1hou1 <z1h..._at_gmail.com> wrote:
> Hi,
>
> Database:Oracle 10g (10.2.0.4) on Solaris running on Opteron
>
> This is my issue. There are way too many options.
>
> This is what I would like to do.
>
> I have an application (a GUI) that connects to the database. But I
> really want to track who the o/s user.
> I know that proxy user is an option. But it requires the creation of a
> proxy user with create session privilege. I would like to avoid the
> creation of a myriad range of users, not to mention the hassles of
> setting up or going through enterprise level processes to approve the
> creation of a database account for a new application user.
>
> I would like to continue use Oracle table triggers that records who
> modified or created a row in a table. SYS_CONTEXT now offers a variety
> of options. Otherwise I have developers creating redundant procedures
> to manage this situation.
>
> When I attempted to read up on the options I get into Oracle's
> Enterprise Security that is so vast and it looks like the use of
> Oracle Internet Directory may be involved. Or Oracle Identity
> Management.
>
> May I know what my options are?
Either you application signs on as an application user in which case it needs to inform Oracle of who the real end user is or it signs on as each user.. With this second approach rdbms auditing and table level triggers using the psuedo column user can easily capture end user identify. With a proxy user I do not remember how the value of the psuedo column user is set but the end user information is available via v$session so some trigger coding changes might be necessary to pick up the information correctly.
If the application uses a single user id then depending on your Oracle version and what tool/language the application is coded in then you have options but these options vary. The use of connection pooling also effects your options since a series of SQL statements from one end user session may use different Oracle sessions to perform the SQL. Basically this means just setting the information via dbms_application_info will not work. I now with java that it is possible to set connection properties that can be used to pass data in. This may be what the new 11g enhancements to sys_context depend on. That is, some of the parameter values may only be available based on how the calling application is written.
Unfortunately, you really need to review all the information in the Security manual before making your decision though it should not be necessary to actually read all of it first. Reading the entire manual would be best, but two to three hours spent reviewing the authenication methods, basic auditing, and skimming every chapter should be enough to allow you to make a basic decision.
HTH -- Mark D Powell -- Received on Sat Oct 09 2010 - 11:23:31 CDT