Database: DB2 AIX V8.*
Question: I want to be able to get the 'Client login ID' aka 'Execution
ID' (as opposed to the authid or application_id) for the current
connection within a stored procedure and without requiring SYSADM,
SYSCTRL, SYSMAINT, or SYSMON authority. This would be for audit
purposes as several different people can share an authid.
Obviously I can create a stored procedure to do the SQL below and then
grant execute to the users authid but I don't want to give them SYSADM,
SYSCTRL, SYSMAINT, or SYSMON authority which means the call will fail
for security reasons. <==tested
************************************************** ************************************************
I figured out how to do it using the snapshot (see below) but I don't
want to give the prod id sysmon authority.
Explanation: This query returns the Client login ID aka Execution ID
for the current connection to database dbname
Query:
SELECT EXECUTION_ID FROM TABLE(SNAPSHOT_APPL_INFO('dbname',-1))
AS SNAPSHOT_APPL_INFO
WHERE APPL_ID = application_id();
Authorized users can capture snapshots of monitor information for a DB2
instance by using snapshot table functions in SQL queries.
Prerequisites
You must have SYSADM, SYSCTRL, SYSMAINT, or SYSMON authority to capture
a database snapshot.
| 
