Any way to capture USER_NAME of user who invoked a procedure?
I'd like to create a procedure which is accessible to public, but logs the USER_NAME of the user who invoked it. Is there any way to pass such context information, along with the arguments, to the external shell procedure?
0
Comments
Since any SQL within your external procedure is a separate connection it would not be easy but there might be a way to have it log the information by querying the SESSIONS table and tying it back with a related PID, timestamp, and the statement. The information, however, is already available to you in system tables. For example, V_MONITOR.QUERY_REQUESTS will allow you to find the user and time of the procedure and you will see the arguments passed within the request SQL string.
V_MONITOR.QUERY_REQUESTS seems the way to go.
To tie the knot (though not sure if it's airtight): I can make argument1 to the procedure be the users' USERNAME. Then procedure itself can query QUERY_REQUESTS for most recent call to procedure where USER_NAME= '$arg1' and all other arguments arg1,arg2, ... argN, match those passed into the procedure (with THROW_ERROR () if no match detected).
Perhaps a viable enhancement request: this would be much simpler if a procedure could be defined as an invocation of another procedure - users have access to outer procedure only; outer procedure calls inner procedure with additional argument USER. I could see value-added beyond this niche example... if the sytnax allowed the inner-procedure-arguments to be specified as any valid SQL expression, then wrapping procedures could quickly and directly handle all sorts of argument validation before control was even passed to the actual external call made by the inner-procedure.