APP USER though Remote

NS150883

Hi All,

I am having a challenge in getting the APP_USER name of the Remote DB.

Solution Built is: I have a Apex installed on DB "X" (X SID) and the base tables are on "Y", DB link is created on X for accessing "Y" (Y SID), we created triggers to log the User which has modified the data on Y tables into an Audit table, though Apex.

Issue: As data changes are happing over DB Link, always the username populated is DB link user instead of Apex user.

Alternatives: Pass the APP_USER (username) as a Value along with data (which needs structure changes )

Need help to provide any alternative which will capture this actual user name from Apex Login.

fac586

NS150883 wrote:

Thanks for the post, pardon for my ignorance. however I am not able to pick the actual logic need to be implemented .

The Audit Log Triggers are on Tables which are on the Target DB (which is accessed by Apex using DB Link), can you please help me with the details...!

This is the relevant post: Re: v('APP_USER') over database link

Set the APEX user information in the remote DB by executing the first block in the Initialization PL/SQL Code on the Security Attributes page of your application.

begin
  [email protected](v('APP_USER'));
end;

Use the second expression to get this information in the audit triggers:

...nvl(v('APP_USER'), sys_context('userenv', 'client_info'))...

Reset the remote user information and close the DB link in the Cleanup PL/SQL Code:

begin
  [email protected](null);
  apex_util.close_open_db_links();
end;

fac586

NS150883 wrote:

I am having a challenge in getting the APP_USER name of the Remote DB.

Solution Built is: I have a Apex installed on DB "X" (X SID) and the base tables are on "Y", DB link is created on X for accessing "Y" (Y SID), we created triggers to log the User which has modified the data on Y tables into an Audit table, though Apex.

Issue: As data changes are happing over DB Link, always the username populated is DB link user instead of Apex user.

Alternatives: Pass the APP_USER (username) as a Value along with data (which needs structure changes )

Need help to provide any alternative which will capture this actual user name from Apex Login.

See

If all or most of the pages in the app(s) are modifying data in the remote database then you should probably set the context in the Initialization PL/SQL Code on the Security Attributes page, and reset it and close the DB link in the Cleanup PL/SQL Code.

NS150883

Hi Fac586,

Thanks for the post, pardon for my ignorance. however I am not able to pick the actual logic need to be implemented .

The Audit Log Triggers are on Tables which are on the Target DB (which is accessed by Apex using DB Link), can you please help me with the details...!

fac586

NS150883 wrote:

Thanks for the post, pardon for my ignorance. however I am not able to pick the actual logic need to be implemented .

The Audit Log Triggers are on Tables which are on the Target DB (which is accessed by Apex using DB Link), can you please help me with the details...!

This is the relevant post: Re: v('APP_USER') over database link

Set the APEX user information in the remote DB by executing the first block in the Initialization PL/SQL Code on the Security Attributes page of your application.

begin
  [email protected](v('APP_USER'));
end;

Use the second expression to get this information in the audit triggers:

...nvl(v('APP_USER'), sys_context('userenv', 'client_info'))...

Reset the remote user information and close the DB link in the Cleanup PL/SQL Code:

begin
  [email protected](null);
  apex_util.close_open_db_links();
end;

NS150883

Exactly worked :-) thanks for the steps and details....!

Sven W.

I can't fully test that at the moment, since I do not have a remote DB available.

But Apex does set the context already automatically using CLIENT_IDENTIFER.

And as far as I remember this is also valid in the remote database session.

The structure of the client_identifier did change depending on apex version, so this might need to be adapted.

In Apex 5 it has the structure APP_USER:SESSION_ID. So to get only the app_user from it, we need to split the string.

select regexp_substr(sys_context('userenv','CLIENT_IDENTIFIER'),'^[^:]*') from dual;