Custom env variable in Oracle

Folks,

 

I've got a need to somehow capture information about a session at the OS
level and alter an Oracle's session based on that previously found info.
Something to the effect of checking an env. variable and setting
DBMS_APPLICATION_INFO.SET_CLIENT_INFO accordingly.  I think my only
option is to create an external procedure that is called from a logon
trigger, but maybe I'm missing something.

 

The environment is RHEL, 4.x, running Oracle 10.2.0.4.

 

The specifics behind this effort are related to overcoming issues in
tracking usage of a shared account, so please understand that I know
this isn't the best way things should be set up and that I've done my
best to lock down the account (and have been eventually blocked each
time due to need/effort).  The shared account is at the OS level and no
one can log in directly as that account, but a scheduler can execute
jobs as that account, which is does for all jobs.  But, support folks
have a type of "sudo" access where they connect as the shared account
(let's call it "dshare").  If they have the proper group, they "become"
dshare, providing their own account's password in the process.

 

At this point, if they execute "sqlplus" as dshare, within their Oracle
session the osuser now shows up as "dshare" instead of the originating
one, such as "dherri".  My thought was to have our sysadmins add a line
to this shared account's .bash_profile which will run something like:

 

ORIG_ACCT=`ps -p $$ h -o ppid | xargs -n1 ps h -o ppid -p | xargs -n1 ps
h -o user -p`

 

Then have a logon trigger which would execute an external procedure any
time the Oracle session has an osuser value of "dshare".  That external
procedure would return the value of $ORIG_ACCT and then the logon
trigger would set CLIENT_INFO to the returned value.

 

To me it seems like a lot of extra work to be done each time an Oracle
session is started, but I can't think of any other way of handling this.
I guess I could have an alias for "sqlplus" and have it resolved to a
script which generates a login.sql script, sets $SQLPATH to that spot,
and in the login.sql script set CLIENT_INFO, but that seems like an
uglier hack.

 

Hopefully I'm missing something obvious, even embarrassingly obvious.

 

David C. Herring  | DBA, Acxiom Automotive

 

630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com

 

 

 

***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************

GIF image

Other related posts: