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: November 28, 2023 - 3:38 am UTC

Version: 12.1.0.2

Viewed 1000+ 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 Connor 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





Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Unused sessions in the unified audit log

Ted, November 10, 2023 - 4:27 pm UTC

Connor,

On the same lines, I am trying to figure out a way to find connections to the DB which connect, alter session, and disconnect (logoff). Unified auditing is capturing logon and logoff (19c). The question is to find out how many sessions were created and never used or created and only 1 SQL commend executed (not alter session) before the session ended. This is in a 3 node RAC environment. Per this thread, trying to capture only 1 SQL statement or 1 select, insert, update or delete on any object per user, session and machine would require a scan of the audit log. I also considered the ASH data (yep, not all SQL are captured) but there is no link to sid and session in the audit log to link back to the ASH data. Any idea on the best method to find sessions which did nothing other than connect and disconnect?

Thank you
Ted
Connor McDonald
November 28, 2023 - 3:38 am UTC

Depending on load you could simply audit all logon, select and logoff events.

SQL>  with t as
  2  ( select 100 sid, 1 seq, 'logon' action from dual union all
  3    select 100 sid, 2 seq, 'select' action from dual union all
  4    select 100 sid, 3 seq, 'select' action from dual union all
  5    select 100 sid, 4 seq, 'logoff' action from dual union all
  6    --
  7    select 101 sid, 1 seq, 'logon' action from dual union all
  8    select 101 sid, 2 seq, 'select' action from dual union all
  9    select 101 sid, 3 seq, 'logoff' action from dual union all
 10    --
 11    select 101 sid, 1 seq, 'logon' action from dual union all
 12    select 101 sid, 2 seq, 'logoff' action from dual
 13  )
 14  select *
 15  from t
 16  match_recognize (
 17   partition by sid
 18   order by seq
 19   measures log_on.sid as on_sid, log_on.seq seq_start, log_off.seq  seq_end
 20   pattern (log_on sel{1} log_off )
 21   define
 22     log_on as action = 'logon',
 23     sel as action = 'select',
 24     log_off as action = 'logoff'
 25     ) x;

       SID     ON_SID  SEQ_START    SEQ_END
---------- ---------- ---------- ----------
       101        101          1          2


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library