Skip to Main Content
  • Questions
  • audit once per session (unified auditing)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Julius.

Asked: January 13, 2021 - 2:41 pm UTC

Last updated: January 18, 2021 - 2:30 am UTC

Version: 12.1.0.2

Viewed 100+ times

You Asked

Hi,

is it possible to audit (unified auditing) access to tables only once per session? I don't need to catch all selects from audited tables in a session, I just want to to know if a table was at querried at least once. Currently it generates huge amount of audit data, from which only fraction is needed.

Thanks,
Julius

and we said...

Not to my knowledge, and you can see why it would be an expensive thing to do. Once you've captured an audit record, then every single subsequent audit operation would need to check to see if an audit has already taken place.

Some things you could explore:

1) Periodically query V$SEGMENT_STATISTICS for typical wait events such as

logical reads
physical reads
physical reads direct
segment scans

to see if the table is being accessed

2) Similarly, check V$SQL_PLAN for an object name of the table and/or its indexes.

3) Use a little trickery to pause the auditing when an entry is detected. For example, here's a standard audit capture

SQL> conn /@db19_pdb1 as sysdba
Connected.
SQL>
SQL> create audit policy grab_scott_emp
  2  actions select on scott.emp
  3  container = current;

Audit policy created.

SQL>
SQL> audit policy grab_scott_emp;

Audit succeeded.

SQL> select object_schema,
  2         object_name,
  3         audit_option,
  4         condition_eval_opt
  5  from   audit_unified_policies
  6  where  policy_name = 'GRAB_SCOTT_EMP';

OBJECT_SCHEMA   OBJECT_NAME     AUDIT_OPTION    CONDITION_
--------------- --------------- --------------- ----------
SCOTT           EMP             SELECT          NONE

1 row selected.

SQL> conn scott/tiger@db19_pdb1
Connected.
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

1 row selected.

SQL> select max(empno) from emp;

MAX(EMPNO)
----------
      7934

1 row selected.

SQL>
SQL> conn /@db19_pdb1 as sysdba
Connected.
SQL>
SQL> exec dbms_audit_mgmt.flush_unified_audit_trail;

PL/SQL procedure successfully completed.

SQL> select event_timestamp,
  2         action_name,
  3         object_name
  4  from   unified_audit_trail
  5  where  dbusername = 'SCOTT';

EVENT_TIMESTAMP                ACTION_NAME          OBJECT_NAME
------------------------------ -------------------- --------------------
18-JAN-21 10.09.21.230000 AM   SELECT               EMP
18-JAN-21 10.09.21.247000 AM   SELECT               EMP
18-JAN-21 10.09.21.254000 AM   SELECT               EMP




Now we'll adjust that in order to only capture the "first" occurrence (or at least limit the number). I'll drop the policy and create a global context and package to control it

SQL> noaudit policy grab_scott_emp;

Noaudit succeeded.

SQL> drop audit policy grab_scott_emp;

Audit Policy dropped.

SQL> create or replace
  2  package set_ctx is
  3    procedure table_on(p_table varchar2);
  4    procedure table_off(p_table varchar2);
  5  end;
  6  /

Package created.

SQL>
SQL>
SQL> create or replace
  2  package body set_ctx is
  3    procedure table_on(p_table varchar2) is
  4    begin
  5      dbms_session.set_context('AUD_CTX',upper(p_table),'1');
  6    end;
  7
  8    procedure table_off(p_table varchar2) is
  9    begin
 10      dbms_session.set_context('AUD_CTX',upper(p_table),'1');
 11    end;
 12  end;
 13  /

Package body created.

SQL> create context aud_ctx using set_ctx accessed globally;

Context created.



A global context lets all sessions share a common context value. So I'll add that into my policy

SQL> conn /@db19_pdb1 as sysdba
Connected.
SQL>
SQL> create audit policy grab_scott_emp
  2  actions select on scott.emp
  3  when 'SYS_CONTEXT(''AUD_CTX'', ''EMP'') is null'
  4  evaluate per statement
  5  container = current;

Audit policy created.

SQL> audit policy grab_scott_emp;

Audit succeeded.

SQL> select object_schema,
  2         object_name,
  3         audit_option,
  4         condition_eval_opt
  5  from   audit_unified_policies
  6  where  policy_name = 'GRAB_SCOTT_EMP';

OBJECT_SCHEMA   OBJECT_NAME     AUDIT_OPTION    CONDITION_
--------------- --------------- --------------- ----------
SCOTT           EMP             SELECT          STATEMENT

1 row selected.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.


So at this point I will have capture one audit record. Now I can using the scheduler create a background job that will regularly check the audit trail for existence of tables already capture, and then set a context value for those tables. All existing sessions will then no longer capture audit records

SQL> begin
  2    for i in (
  3      select distinct object_name
  4      from   unified_audit_trail
  5      where  dbusername = 'SCOTT'
  6      and    object_name is not null
  7    )
  8    loop
  9      set_ctx.table_on(i.object_name);
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.


Now for those tables in the audit trail, the context is no longer null, so when I run more queries

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

1 row selected.

SQL> select max(empno) from emp;

MAX(EMPNO)
----------
      7934

1 row selected.


and then check the audit trail, I see that I only capture the original 3 plus the 1 additional one

SQL> select event_timestamp,
  2         action_name,
  3         object_name
  4  from   unified_audit_trail
  5  where  dbusername = 'SCOTT'
  6  order by 1;

EVENT_TIMESTAMP                ACTION_NAME          OBJECT_NAME
------------------------------ -------------------- --------------------
18-JAN-21 10.09.21.230000 AM   SELECT               EMP
18-JAN-21 10.09.21.247000 AM   SELECT               EMP
18-JAN-21 10.09.21.254000 AM   SELECT               EMP
18-JAN-21 10.18.50.659000 AM   SELECT               EMP





More to Explore

DBMS_AUDIT_MGMT

More on PL/SQL routine DBMS_AUDIT_MGMT here