Program name not registered in Oracle when connecting from Unix

[Christophe MAILHE]

Dear all,

I know this question has probably been asked a hundred times, but I can't find any answer able to help me in any of the forums I have searched.

When I am looking at the records in the v$session, all connections made by programs running on HP-UX (v 11.00) and which are not part of the Oracle package, do not register properly with Oracle.

Below are some examples of it:

Table v$session
Column Program
RTS.EXE -> Connection from a Windows PC using Oracle SQL ODBC Driver
? @jupiter (TNS V1-V3) -> Connection from a HP-UX Server using Uniface Polyserver
sqlplus@jupiter (TNS V1-V3) -> Connection from a HP-UX Server using Oracle sqlplus
? @pita (TNS V1-V3) -> Connection from a HP-UX Server using a Perl Script (using DBD::Oracle module)

We are using Oracle 8.1.6 Standard version for HP-UX RISC.

We are planning to implement a logging trigger onto our Oracle database in order to set different roles to users dependant on the program they are using to access the data (e.g.: R/W if coming from Uniface Polyserver, Read-Only if coming from Access or Excel???)

Like the programs coming from Unix are not registering properly, I am a bit stuck to implement this kind of trigger.

Does someone have any idea on what could cause this issue?

Perhaps have I to export a variable under Unix for TNS to register the program name with the database?

Perhaps have I to modify something in the TNSNAME.ora?

All ideas are welcome??? :-)

Christophe.

[harry d brown jr]

Do you have the latest patch bundle installed?

Have you patched oracle?

I don't see the issue you are talking about in the example you posted? I see a M$ connection, and the other three from HP-UX. Is there something we are missing?




live free or die
harry

[Christophe MAILHE]

Hi Harry,

The first connection is made from a Windows NT 4.0 machine using RTS.exe. This program use the Oracle SQL ODBC driver for Microsoft Windows to connect the database. The value in v$session->program is "RTS.EXE", so no issue with this connection.

The second connection is made from the HPUX machine hosting the database (jupiter) using "Uniface Polyserver". The value in v$session->program is "? @jupiter (TNS V1-V3)", where the binary running on HPUX is pvs.

The 3rd connection is made from the HPUX machine hosting the database (jupiter) using sqlplus. Here again, the program has been registered properly with oracle v$session->program = "sqlplus@jupiter (TNS V1-V3)"

The last connection is made from another HPUX machine (pita) running Oracle 8.1.6 client and using a Perl script "using" DBI and DBD::Oracle modules to establish the connection. Here the value the v$session->program is again "? @pita (TNS V1-V3)".

Jupiter (the machine hosting the Oracle Server) is not at the latest patch bundle. But pita (the one running Oracle client) is at the latest patch bundle.

My issue is that I would need to get more information in Oracle from connections made from HPUX machines. The "? @..." is just not enough. Is the connection established by psv, perl, schedule, ????

At the moment all users are connecting to the database using the same Oracle users (1 user for psv and schedule with R/W access and another user for connections from access and excel with RO access), but we need to implement ASAP an Oracle Username per user. We would then create a logging trigger to "Set Role" to the user dependant on the software the user is using to access the data.

Cheers,

Christophe.

[benoit Bruckert]

Hi Christophe,
I think your trouble is more on the program side than the OS?
I try this :
sqlplus from HP-UX (the Unix tool) to the database :
the program column show :sqlplus@charlema (TNS V1-V3)
THen I try a php script which is using net8 , and the result is :
? @charlema (TNS V1-V3)

The same as you,
then I think that there should be something on the client side to send the program name !! but I don't know what. Try to call Oracle support about the value of this column and how to be sure to feed it !!!
hth
Benoit

[Peter van Rijn_1 · Accepted solution]

Indeed Benoit is quite right. If you register your program with an Oracle package like DBMS_APPLICATION_INFO the columns MODULE and ACTION of V$SESSION can be populated.

The column PROGRAM seems to be platform specific, some sources (even on Metalink) seem to indicate it is possible to really set it from an application, ,others indicate it isn't.

regards,
Peter