Db2

 View Only
  • 1.  Table/View Usage per userid

    Posted Mon May 01, 2023 09:47 AM
    Edited by Kristen Park Wed May 03, 2023 09:45 AM

    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
    ------------------------------



  • 2.  RE: Table/View Usage per userid

    IBM Champion
    Posted Thu May 04, 2023 10:41 AM
    Edited by Jan Nelken Fri May 05, 2023 05:46 AM

    I am not sure what you are looking for; let's consider this example:

    Database D contains table SCHEMA.TABLE.

    Userid 1 connects to database using SQLID A and does select count(*) from schema.table;

    Userid 1 connects to database using SQLID B and does select count(*) from schema.table;

    Userid 2 connects to database using SQLID A and does select count(*) from schema.table;

    Userid 3 connects to database using SQLID A and does select count(*) from schema.table;

    What values for "number of times a table/view is used per userid" you expect?



    ------------------------------
    Jan Nelken
    ------------------------------



  • 3.  RE: Table/View Usage per userid

    Posted Sat May 06, 2023 03:29 AM
    Edited by Mark Barinstein Sat May 06, 2023 03:32 AM

    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
    ------------------------------



  • 4.  RE: Table/View Usage per userid

    Posted Mon May 08, 2023 08:30 AM

    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
    ------------------------------



  • 5.  RE: Table/View Usage per userid

    IBM Champion
    Posted Mon May 08, 2023 08:51 AM

    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
    ------------------------------