Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

APP USER though Remote

NS150883Sep 7 2015 — edited Sep 10 2015


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.
This post has been answered by fac586 on Sep 8 2015
Jump to Answer

Comments

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.

1 person found this helpful
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
Answer 
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


  dbms_application_info.set_client_info@dblink(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


  dbms_application_info.set_client_info@dblink(null);


  apex_util.close_open_db_links();


end;
Marked as Answer by NS150883 · Sep 27 2020
1 person found this helpful
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;
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 8 2015
Added on Sep 7 2015
#apex-discussions, #trigger
5 comments
915 views