• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • Powerserver Oracle DB Connection Audit User with OS USER

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Powerserver Oracle DB Connection Audit User with OS USER

  1. Issue
0
Votes
Undo
  1. Ravibabu Katna Madhanakamaraj
    Ravibabu Katna Madhanakamaraj
  2. PowerServer
  3. Wednesday, 16 March 2022 01:03 AM UTC

Challenge we have

The DB Connections are pooled by IIS, Oracle 19c as BACKEND and we would not able to capture the OS USER ID for Auditing the actions.

Audit of user actions are captured using bcakend triggers and user name is used as "OS USER", since API in POWERSERVER using APIUSERS as OSUSER, we have challenges in getting the actual user name for auditing. Any suggestions

 

Background,

we are migrating Powerbuilder application from Citrix to Power Server on Cloud with out any changes in Powerbuilder application.

  1. Oracle
  2. PowerBuilder (Appeon)
  3. PowerServer
  4. PowerServer Web
Problemas con tipo de datos en la construccion del...
PowerServer not encapsulating SQL in Transactions
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 16 March 2022 01:43 AM UTC
  2. PowerServer
  3. # 1

Hi Ravibabu;

  In PowerServer 2021 apps, the DB connections are not IIS based. They are managed by the PS2021 "server" itself. You can set PS to pass through the UserID & Password from the Transaction Object (ie: SQLCA) settings from the PB App itself. 

  The question I have for you though is how the O/S user Id is passed through the current C/S PB App to Oracle to capture? For example in ASE DBMS, you can code ... SQLCA.DBParm="Host='" + ls_compname + "'"

  So how does Oracle see the OS user name currently?

Regards ... Chris

Comment
There are no comments made yet.
Ravibabu Katna Madhanakamaraj
Ravibabu Katna Madhanakamaraj Accepted Answer Pending Moderation
  1. Wednesday, 16 March 2022 03:27 AM UTC
  2. PowerServer
  3. # 2

Hi Chris,

Thanks for your swift response. We use generic DB USER ID to connect from PB App to Oracle, the citrix server are installed with Oracle client, we use TNSNAME.ORA to establish the connectivity between Application and Databases. When I check my connections in oracle using V$SESSION, each user is allocated with unique Session ID and OS USER. 

All our backend triggers uses the OS USER Name to each actions  (insert or update or delete) in DB.

In the case Power Server connections, the OS USER is showing as APIUSER in Oracle session table always, it creates multiple sessions all with same APIUSER Name as OS User, Hence we are struggling to get the actual user name to log.

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 16 March 2022 18:01 PM UTC
Hi Ravibabu .. The PB App to PowerServer2021 interface is currently not designed to pass through the OS User ID. I suspect that this is a feature of the way Oracle is setup to run under Citrix. As I mentioned before, you can pass the Oracle UserID & Password through PS2021. Anything else, would require an enhancement (if feasible) - AFAIK.
  1. Helpful
Ravibabu Katna Madhanakamaraj
  1. Ravibabu Katna Madhanakamaraj
  2. Wednesday, 16 March 2022 22:30 PM UTC
Thanks Chris & Mike, We have enabled SSO since 2015 onwards, we moved away from individual login ID for each user in Oracle, maintenance of 2 passwords for each user, maintenance of DBA activities of creating/removing the users in Oracle. We would like to avoid this change and let us try with different options.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 17 March 2022 18:15 PM UTC
Hi Ravibabu .. I would suggest that you open a Support Ticket for this issue so that we can get the PowerServer Engineering Team involved to see if Appeon can find a solution for you immediately and also if we need a feature enhancement for this OS User support issue. Thanks

Regards ... Chris
  1. Helpful
There are no comments made yet.
Jason Frost
Jason Frost Accepted Answer Pending Moderation
  1. Wednesday, 16 March 2022 22:47 PM UTC
  2. PowerServer
  3. # 3

Hi

I work with Ravibabu and I have come to the conclusion that we actually have a different issue:

We have been trying to get around this problem by setting a value on the Oracle Session with the user id so that all audit triggers can pick up that value.  We are currently setting that value in the Updatestart event of the dw.  This works perfectly in PB Classic.

However, it doesn't work when we deploy to PS, and after a lot of tracing sessions and transactions etc. I have come to the conclusion that PS is not performing multiple updates in the same transaction.  It isn't even doing them in the same session.  It is almost as though Autocommit is set to true, but actually it isn't set.

This gives us a wider problem with the application where we might want to update two or more DWs in a transaction and be able to roll back all of them if there is a problem, but as they are currently in different sessions that won't be possible.

Is there another setting we might be using that cause this behaviour other than Autocommit?

Comment
Jason Frost
  1. Jason Frost
  2. Thursday, 17 March 2022 03:51 AM UTC
I've bumped the above to a separate question as it is more than audit details we are talking about.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.