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?

On Oct 9, 11:27*am, z1hou1 <z1h... (AT) gmail (DOT) com> wrote:
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 --

Thanks Mark.

For the moment, I was exploring the use of
SYS_CONTEXT('USERENV','OS_USER') and it returns consistently the
username of the client who has logged in via the network, and not the
user who is connected to the database. I did this by simply asking a
couple of people to login via any client, TOAD, PL/SQL Developer or
even sqlplus and then execute the following:

SELECT SYS_CONTEXT('USERENV','OS_USER') FROM DUAL;

The above works fine in Oracle 10g 10.2.0.4 as well.

I next plan to modify the audit triggers that are used to update
created_by_user, modified_by_user in the application tables to use the
SYS_CONTEXT function and ask the developers to do the same with some
simple inserts/update and/or deletes.

In essence for the application is not using a connection pool - not
yet anyway. Yes, and I agree that if we go the connection pool route,
I wonder what the above statement will return. I think in that case, I
will have to explore all the other options.

If we go the proxy user route (and I probably will not), the proxy
user can also be determined by the SYS_CONTEXT function as well as
follows:

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.

Thank you for your response.

z1hou1

On Oct 10, 5:10*am, z1hou1 <z1h... (AT) gmail (DOT) com> wrote:
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