Oracle APEX 24.1.6 | Oracle DB 19c
Hi, I have two servers, one with my Oracle APEX application and the other with all the tables that the Oracle APEX application references. I will refer to them as my “application” server vs. my “data” server.
I have audit columns CREATED_BY and UPDATED_BY. These are populated by Triggers (I am aware there is general hesitation to use triggers, but in this case I decided the pros outweighed the cons). The triggers populate the :NEW values of these columns using apex_util.get_session_state('APP_USER') in respective BEFORE INSERT and BEFORE UPDATE triggers.
The problem I am running into is that since the triggers are, of course, on the “data” server, when apex_util.get_session_state('APP_USER') runs, it is returning the name of the remote user / schema rather than the name of the actual user that is logged in to the application on the “application” server.
I could provide the APP_USER from the application side, but I set up the triggers in such a way that the fields don't accept input, rather, they reference ‘APP_USER’ to get their value - as a way of completely ruling out any kind of tampering.
I wouldn't mind architectural recommendations as to the solution I have come up with, but what I am mainly wondering is whether there is a way to get the correct value on the “data” server to begin with. Is there another variable that would capture this (as opposed to ‘APP_USER’)? I did notice in my Logger logs (logger is on the “data” server) that the CLIENT_IDENTIFIER column includes the actual username plus what seems to be a Session ID. Also, is there a variable from which I could deduce in my triggers whether it was being executed from what is ultimately a remote request?
Thanks for any insight and guidance.