Forum Stats

  • 3,837,697 Users
  • 2,262,286 Discussions
  • 7,900,365 Comments

Discussions

APP USER though Remote

NS150883
NS150883 Member Posts: 47
edited Sep 10, 2015 8:17AM in APEX Discussions


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.

Tagged:
Kiran Pawar

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,100 Red Diamond
    edited Sep 8, 2015 4:48AM 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
      [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;
    
    Kiran Pawar

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,100 Red Diamond
    edited Sep 7, 2015 3:07AM
    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.

    Kiran Pawar
  • NS150883
    NS150883 Member Posts: 47
    edited Sep 8, 2015 2:54AM

    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
    fac586 Senior Technical Architect Member Posts: 21,100 Red Diamond
    edited Sep 8, 2015 4:48AM 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
      [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;
    
    Kiran Pawar
  • NS150883
    NS150883 Member Posts: 47
    edited Sep 10, 2015 4:34AM

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

  • Sven W.
    Sven W. Member Posts: 10,537 Gold Crown
    edited Sep 10, 2015 8:17AM

    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;
    
This discussion has been closed.