In my example SELECT was issued 4 times by 2 SQLID's from 3 OS userid's:
Userid 1 executed SELECT 2 times;
Userid 2 executed SELECT 1 time;
Userid 3 executed SELECT 1 time;
SQLID A executed SELECT 3 times;
SQLID B executed SELECT 1 time;
I am not sure Db2 is interested in OS userid - it cares about SQLID (a.k.a SQL Authorization Id).
An example:
C:\>whoami
azuread\janelnken
C:\>db2 connect to sample user db2admin
Enter current password for db2admin:
Database Connection Information
Database server = DB2/NT64 12.1.0.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
C:\>db2 values current user
1
-------------------------------------
DB2ADMIN
1 record(s) selected.
------------------------------
Jan Nelken
------------------------------
Original Message:
Sent: Mon May 08, 2023 08:30 AM
From: PITIRIM C TAN
Subject: Table/View Usage per userid
Hi All,
Thanks for reviewing my question.
In Jan's example, the value I need would be the # of times the table was used (ie: select stmt).
I believe the audit.checking might be the answer.
The purpose is defined as "Generates records during authorization checking of attempts to access or manipulate Db2 database objects or functions."
My assumption is since an auth check is going to be done for every query then it might be close enough.
If I create a stored procedure with a for loop repeating a SELECT 5-times, will db2 be checking authorization 5 times or 1 time?
I'll test.
select USERID, AUTHID, OBJSCHEMA, OBJNAME from AUDIT.CHECKING where OBJTYPE = 'TABLE';
------------------------------
PITIRIM C TAN
Original Message:
Sent: Sat May 06, 2023 03:28 AM
From: Mark Barinstein
Subject: Table/View Usage per userid
Unfortunately, it's really not possible to get the values of SYSCAT.AUDITUSE.[OBJECTSCHEMA | OBJECTNAME] columns (the auditable object name) in the audit log.
That is, if we, say, have:
CREATE TABLE AUDIT1 (I INT);
CREATE VIEW AUDIT1_V AS SELECT * FROM AUDIT1;
CREATE AUDIT POLICY EXEC
CATEGORIES
EXECUTE STATUS BOTH
ERROR TYPE NORMAL;
AUDIT TABLE AUDIT1 USING POLICY EXEC;
SELECT COUNT (1) FROM AUDIT1_V;
then we won't see the AUDIT1
string in the audit log. There is an original statement text referring different object in the auditlobs
file only.
Too many fields which you won't ever need except the one really needed...
------------------------------
Mark Barinstein
Original Message:
Sent: Mon May 01, 2023 08:30 AM
From: PITIRIM C TAN
Subject: Table/View Usage per userid
Does anyone know how to get a number of times a table/view is used per userid per day, say, for the last 6-12 months?
I looked at audit events EXECUTE policy and it provides sql statements but it is difficult to break it down into table/view since it is text.
For the audit event SECMAINT, I do not see it provide, say, an event stating it systematically granted permission to the table/view. Or even a check if a user is allowed to use table/view.
Any tips appreciated.
Thanks.
------------------------------
PITIRIM C TAN
------------------------------