Great Football Managers and Oracle Unified Auditing

It’s quite a good time for English football at the moment. Not only have English clubs monopolised the finals of the two main European Club competitions this year, but Manchester City have made history by winning all three domestic competitions in the same season.
Note that this isn’t a British footballing first. Glasgow Rangers managed it way back in 1949. And whilst the European Cup ( Champions League if you must) has eluded City this season, Celtic managed that particular clean sweep in 1967.
In English football however, this particular treble is unprecedented. In fact, there are remarkably few managers who have been able to win every one of the major domestic honours in their entire career.
All of which will come in handy when looking for examples to illustrate the topic at hand, namely Oracle Unified Auditing.
With the aid of 18c Express Edition, we’ll be looking at :

  • The Oracle supplied Unified Auditing Policies that are enabled by default
  • Where to find the Audit Trail
  • How to create our own Unified Auditing Policy to monitor DML operations on specific objects

Unified Auditing default settings

Unified Autiding was introduced in 12c. By default, it’s configured to work alongside auditing from earlier database versions.
We can check that this the case – and that traditional auditing has not been disabled by checking that the Unified Auditing parameter is set to FALSE :

Looking at the audit initialization parameters is also useful at this point :

select name, value, description
from v$parameter
where name like '%audit%'
and value is not null
order by name
/

NAME                           VALUE                          DESCRIPTION                                       
------------------------------ ------------------------------ --------------------------------------------------
audit_file_dest                /opt/oracle/admin/XE/adump     Directory in which auditing files are to reside   
audit_sys_operations           TRUE                           enable sys auditing                               
audit_trail                    DB                             enable system auditing                            
unified_audit_sga_queue_size   1048576                        Size of Unified audit SGA Queue                   

We can see that we have a directory setup for audit files to be written to. However, the audit_trail is set to DB – i.e. a table in the database.
To start with, we can have a look at the audit_file_dest directory and see what, if anything, is being written there :

ls -lrt /opt/oracle/admin/XE/adump

...
-rw-r-----. 1 oracle oinstall     866 May 30 13:16 XE_ora_3880_20190530131639680705403060.aud
-rw-r-----. 1 oracle oinstall    1777 May 30 13:16 XE_ora_4375_20190530131639737578276396.aud
-rw-r-----. 1 oracle oinstall    1202 May 30 13:16 XE_ora_4381_20190530131643953498040745.aud

Closer inspection of these files reveal that they contain details of the operations performed when Oracle is started but before the database is open. For example :

cat XE_ora_3880_20190530131639680705403060.aud

Audit file /opt/oracle/admin/XE/adump/XE_ora_3880_20190530131639680705403060.aud
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Build label:    RDBMS_18.4.0.0.0DBRU_LINUX.X64_181017
ORACLE_HOME:    /opt/oracle/product/18c/dbhomeXE
System name:    Linux
Node name:	frea.virtualbox
Release:        3.10.0-957.1.3.el7.x86_64
Version:        #1 SMP Thu Nov 29 14:49:43 UTC 2018
Machine:        x86_64
Instance name: XE
Redo thread mounted by this instance: 0 <none>
Oracle process number: 299
Unix process pid: 3880, image: oracle@frea.virtualbox (TNS V1-V3)

Thu May 30 13:16:39 2019 +01:00
LENGTH : '247'
ACTION :[7] 'STARTUP'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'
DBID:[0] ''
SESSIONID:[1] '0'
USERHOST:[15] 'frea.virtualbox'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[3] '138'

Well that could come in useful, but is there any auditing of operations once the database is up and running ?

Oracle supplied Unified Audit Policies

There are a number of policies that are set up by default :

select owner, object_name
from dba_objects
where object_type = 'UNIFIED AUDIT POLICY'
and oracle_maintained = 'Y'
order by object_name;

OWNER      OBJECT_NAME                                       
---------- --------------------------------------------------
SYS        ORA_ACCOUNT_MGMT                                  
SYS        ORA_CIS_RECOMMENDATIONS                           
SYS        ORA_DATABASE_PARAMETER                            
SYS        ORA_DV_AUDPOL                                     
SYS        ORA_DV_AUDPOL2                                    
SYS        ORA_LOGON_FAILURES                                
SYS        ORA_RAS_POLICY_MGMT                               
SYS        ORA_RAS_SESSION_MGMT                              
SYS        ORA_SECURECONFIG                                  

9 rows selected. 

We can confirm which of these are active by running :

select policy_name, user_name, 
    enabled_option, entity_name, entity_type, 
    success, failure
from audit_unified_enabled_policies
order by policy_name
/

POLICY_NAME          ENABLED_OPTION       ENTITY_NAME  ENTITY_TYPE  SUCCESS    FAILURE   
-------------------- -------------------- ------------ ------------ ---------- ----------
ORA_LOGON_FAILURES   ALL USERS            ALL USERS    USER         NO         YES       
ORA_SECURECONFIG     ALL USERS            ALL USERS    USER         YES        YES       

One point to note is that the ORA_LOGON_FAILURES policy is configured to record statements on failure and not on success.

ORA_LOGON_FAILURES

We can see which activities are covered by this policy by running :

select audit_option, audit_option_type 
from audit_unified_policies
where policy_name = 'ORA_LOGON_FAILURES';

AUDIT_OPTION         AUDIT_OPTION_TYPE 
-------------------- ------------------
LOGON                STANDARD ACTION   

From this, we can infer that this policy will record any failed logon attempts in the audit trail. Let’s test that hypothesis…

First off, I’m going to issue a failed login attempt :

sqlplus hr/thisisnotthepassword@xepdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 30 14:10:13 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 

Right, that should do it. Now to check the audit trail in the database :

select event_timestamp, audit_type, dbusername, action_name, return_code
from unified_audit_trail uat
where unified_audit_policies = 'ORA_LOGON_FAILURES'
and uat.event_timestamp > systimestamp - ((1/24/60) *5) -- happened in the last 5 minutes
order by event_timestamp desc
/

Note here that the record includes the return code, which looks to be the Oracle Error that was generated.
Also note that, in a multitenant environment, you need to be in the relevant container when you query the audit trail.
In this example, I was attempting to login to the XEPDB1 PDB. I would not see this logon failure if I was querying the audit trail from the CDB.

The UNIFIED_AUDIT_TRAIL contains 99 columns and the relevance of each of them will vary according to what you’re looking at in the audit trail.
As usual, the columns themselves have been documented with comments which you can find by running :

select column_name, comments
from dba_col_comments
where owner = 'AUDSYS'
and table_name = 'UNIFIED_AUDIT_TRAIL'
order by column_name
/

COLUMN_NAME                    COMMENTS                                          
------------------------------ --------------------------------------------------
ACTION_NAME                    Name of the action executed by the user           
ADDITIONAL_INFO                Text comment on the audit trail entry             
APPLICATION_CONTEXTS           SemiColon seperate list of Application Context Nam
                               espace, Attribute, Value information in (APPCTX_NS
                               PACE,APPCTX_ATTRIBUTE=<value>) format             

AUDIT_OPTION                   Auditing option set with the audit statement      
AUDIT_TYPE                     Type of the Audit Record                          
AUTHENTICATION_TYPE            Type of Authentication for the session user       
CLIENT_IDENTIFIER              Client identifier in each Oracle session        
***snip***
XS_SESSIONID                   Real Application User Session Identifier          
XS_TARGET_PRINCIPAL_NAME       Target principal name in Real Application Security
                                operations                                       

XS_USER_NAME                   Real Application User name                        

ORA_SECURECONFIG

By contrast, ORA_SECURECONFIG audits a number of system privileges :

select audit_option, audit_option_type, object_schema
from audit_unified_policies
where policy_name = 'ORA_SECURECONFIG'
order by 1
/

AUDIT_OPTION                             AUDIT_OPTION_TYPE  OBJECT_SCHEMA                 
---------------------------------------- ------------------ ------------------------------
ADMINISTER KEY MANAGEMENT                SYSTEM PRIVILEGE   NONE                          
ALTER ANY PROCEDURE                      SYSTEM PRIVILEGE   NONE                          
ALTER ANY SQL TRANSLATION PROFILE        SYSTEM PRIVILEGE   NONE                          
ALTER ANY TABLE                          SYSTEM PRIVILEGE   NONE                          
ALTER DATABASE                           SYSTEM PRIVILEGE   NONE                          
ALTER DATABASE DICTIONARY                STANDARD ACTION    NONE                          
ALTER DATABASE LINK                      STANDARD ACTION    NONE                          
ALTER PLUGGABLE DATABASE                 STANDARD ACTION    NONE                          
ALTER PROFILE                            STANDARD ACTION    NONE                          
ALTER ROLE                               STANDARD ACTION    NONE                          
ALTER SYSTEM                             SYSTEM PRIVILEGE   NONE                          
ALTER USER                               STANDARD ACTION    NONE                          
AUDIT SYSTEM                             SYSTEM PRIVILEGE   NONE                          
BECOME USER                              SYSTEM PRIVILEGE   NONE                          
CREATE ANY JOB                           SYSTEM PRIVILEGE   NONE                          
CREATE ANY LIBRARY                       SYSTEM PRIVILEGE   NONE                          
CREATE ANY PROCEDURE                     SYSTEM PRIVILEGE   NONE                          
CREATE ANY SQL TRANSLATION PROFILE       SYSTEM PRIVILEGE   NONE                          
CREATE ANY TABLE                         SYSTEM PRIVILEGE   NONE                          
CREATE DATABASE LINK                     STANDARD ACTION    NONE                          
CREATE DIRECTORY                         STANDARD ACTION    NONE                          
CREATE EXTERNAL JOB                      SYSTEM PRIVILEGE   NONE                          
CREATE PLUGGABLE DATABASE                STANDARD ACTION    NONE                          
CREATE PROFILE                           STANDARD ACTION    NONE                          
CREATE PUBLIC SYNONYM                    SYSTEM PRIVILEGE   NONE                          
CREATE ROLE                              STANDARD ACTION    NONE                          
CREATE SQL TRANSLATION PROFILE           SYSTEM PRIVILEGE   NONE                          
CREATE USER                              SYSTEM PRIVILEGE   NONE                          
DROP ANY PROCEDURE                       SYSTEM PRIVILEGE   NONE                          
DROP ANY SQL TRANSLATION PROFILE         SYSTEM PRIVILEGE   NONE                          
DROP ANY TABLE                           SYSTEM PRIVILEGE   NONE                          
DROP DATABASE LINK                       STANDARD ACTION    NONE                          
DROP DIRECTORY                           STANDARD ACTION    NONE                          
DROP PLUGGABLE DATABASE                  STANDARD ACTION    NONE                          
DROP PROFILE                             STANDARD ACTION    NONE                          
DROP PUBLIC SYNONYM                      SYSTEM PRIVILEGE   NONE                          
DROP ROLE                                STANDARD ACTION    NONE                          
DROP USER                                SYSTEM PRIVILEGE   NONE                          
EXECUTE                                  OBJECT ACTION      SYS                           
EXECUTE                                  OBJECT ACTION      REMOTE_SCHEDULER_AGENT        
EXEMPT ACCESS POLICY                     SYSTEM PRIVILEGE   NONE                          
EXEMPT REDACTION POLICY                  SYSTEM PRIVILEGE   NONE                          
GRANT ANY OBJECT PRIVILEGE               SYSTEM PRIVILEGE   NONE                          
GRANT ANY PRIVILEGE                      SYSTEM PRIVILEGE   NONE                          
GRANT ANY ROLE                           SYSTEM PRIVILEGE   NONE                          
LOGMINING                                SYSTEM PRIVILEGE   NONE                          
PURGE DBA_RECYCLEBIN                     SYSTEM PRIVILEGE   NONE                          
SET ROLE                                 STANDARD ACTION    NONE                          
TRANSLATE ANY SQL                        SYSTEM PRIVILEGE   NONE   

It so happens that I’ve installed the HR demo application on this database by following Oracle’s instructions and running $ORACLE_HOME/demo/schema/human_resources/hr_main.sql as SYSTEM.

This policy has captured the DDL from this activity :

select scn, dbusername, system_privilege_used, action_name, object_schema, object_name,
from unified_audit_trail
where unified_audit_policies = 'ORA_SECURECONFIG'
and scn >= 2038863
order by event_timestamp
/

       SCN DBUSERNAME   SYSTEM_PRIVILEGE_USED                    ACTION_NAME          OBJECT_SCHEM OBJECT_NAME         
---------- ------------ ---------------------------------------- -------------------- ------------ --------------------
   2038863 SYSTEM       CREATE USER                              CREATE USER                       HR                  
   2038869 SYSTEM       ALTER USER                               ALTER USER                        HR                  
   2038874 SYSTEM       ALTER USER                               ALTER USER                        HR                  
   2038880 SYSTEM       GRANT ANY PRIVILEGE                      GRANT                                                 
   2038886 SYSTEM       GRANT ANY ROLE, GRANT ANY PRIVILEGE      GRANT                                                 
   2038897 SYSTEM       CREATE ANY TABLE                         CREATE TABLE         HR           REGIONS             
   2038910 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           REGIONS             
   2038923 SYSTEM       CREATE ANY TABLE                         CREATE TABLE         HR           COUNTRIES           
   2038929 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           COUNTRIES           
   2038937 SYSTEM       CREATE ANY TABLE                         CREATE TABLE         HR           LOCATIONS           
   2038949 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           LOCATIONS           
   2038962 SYSTEM       CREATE ANY TABLE                         CREATE TABLE         HR           DEPARTMENTS         
   2038976 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           DEPARTMENTS         
   2038988 SYSTEM       CREATE ANY TABLE                         CREATE TABLE         HR           JOBS                
   2039000 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           JOBS                
   2039016 SYSTEM       CREATE ANY TABLE                         CREATE TABLE         HR           EMPLOYEES           
   2039030 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           EMPLOYEES           
   2039036 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           DEPARTMENTS         
   2039052 SYSTEM       CREATE ANY TABLE                         CREATE TABLE         HR           JOB_HISTORY         
   2039068 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           JOB_HISTORY         
   2040134 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           DEPARTMENTS         
   2040179 SYSTEM       ALTER ANY TABLE                          ALTER TABLE          HR           DEPARTMENTS         
   2040303 SYSTEM       CREATE ANY PROCEDURE                     CREATE PROCEDURE     HR           SECURE_DML          
   2040318 SYSTEM       CREATE ANY PROCEDURE                     CREATE PROCEDURE     HR           ADD_JOB_HISTORY     
   2041099 SYSTEM       ALTER USER                               ALTER USER                        HR                  

25 rows selected. 

NOTE that the audit trail contains the System Change Number (SCN) as well as a timestamp, which can make it easier to select the part of the audit trail you are interested in at any given time.

As well as the details of who did what, the audit trail holds the SQL that was executed as part of this operation. For example, if we wanted to check what statement was run when the HR.REGIONS table was created, we could run :

select sql_text
from unified_audit_trail
where scn = 2038897;

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
CREATE TABLE regions                                                            
    ( region_id      NUMBER                                                     
       CONSTRAINT  region_id_nn NOT NULL                                        
    , region_name    VARCHAR2(25)                                               
    )                            

Note that SCN is unique to a transaction. As we’re looking for a DDL statement, which is almost always contained in it’s own transaction (except when it isn’t), it’s probably not such a good idea to rely on it as a de facto Primary Key when you’re searching for DML statements, which may share a transaction ( and therefore an SCN) with several others. Obviously, an SCN is completely useless when you’re searching for SELECT statements.
Whilst we’re on the subject of predicates when selecting from the audit trail, it’s worth bearing in mind that the underlying table is actually partitioned by EVENT_TIMESTAMP. By default, these partitions cover one month each. Including EVENT_TIMESTAMP in the predicate will cause the optimizer to perform partition pruning so may help query performance.

Anyway, speaking of auditing DML, it’s time to introduce…

The Demo Application

We have an application to hold details of each manager who has won each of England’s major domestic trophies. The application is owned by MOTTY, who is a bit of a footie geek. We also have an application user called TEDDY who hasn’t been around long and still thinks that football didn’t exist before the Premier League…

create user motty identified by motty
/
alter user motty quota unlimited on users
/

grant connect, create table, create sequence, create procedure to motty
/

create user teddy identified by teddy
/

grant connect to teddy
/

The application objects are the WINNING_MANAGERS table…

create table winning_managers(
    wm_name varchar2(100) constraint wm_pk primary key,
    league_title number(4),
    fa_cup number(4),
    league_cup number(4))
/

comment on table winning_managers is 'Managers who have won a career treble and the years in which they first won each major English domestic trophy'
/

comment on column winning_managers.wm_name is 'The name of the manager'
/

comment on column winning_managers.league_title is 'The year the manager first won the Title'
/

comment on column winning_managers.fa_cup is 'The year the manager first won the FA Cup'
/

comment on column winning_managers.league_cup is 
    'The year the manager first won the League (Coca-Cola/Worthington/Carling/Capital One/EFL/Carabao) Cup'
/

…and a package…

create or replace package managers_api as
    procedure ins( 
        i_name winning_managers.wm_name%type, 
        i_title winning_managers.league_title%type, 
        i_fac winning_managers.fa_cup%type, 
        i_lc winning_managers.league_cup%type);
        
    procedure upd(
        i_name winning_managers.wm_name%type,
        i_title winning_managers.league_title%type, 
        i_fac winning_managers.fa_cup%type, 
        i_lc winning_managers.league_cup%type);
        
    procedure del( i_name winning_managers.wm_name%type);
end managers_api;
/

create or replace package body managers_api is

    procedure ins( 
        i_name winning_managers.wm_name%type, 
        i_title winning_managers.league_title%type, 
        i_fac winning_managers.fa_cup%type, 
        i_lc winning_managers.league_cup%type)
    is
    begin
        insert into winning_managers( wm_name, league_title, fa_cup, league_cup)
        values( i_name, i_title, i_fac, i_lc);
    end ins;
        
    procedure upd(
        i_name winning_managers.wm_name%type,
        i_title winning_managers.league_title%type, 
        i_fac winning_managers.fa_cup%type, 
        i_lc winning_managers.league_cup%type)
    is
    begin
        if i_name is null then
            raise_application_error(-20000, 'Manager name must be specified');
        end if;
        
        update winning_managers
        set league_title = nvl(i_title, league_title),
            fa_cup = nvl( i_fac, fa_cup),
            league_cup = nvl(i_lc, league_cup)
        where wm_name = i_name;
    end upd;
        
    procedure del(i_name winning_managers.wm_name%type)
    is
    begin
        if i_name is null then
            raise_application_error(-20000, 'Manager name must be specified');
        end if;
        delete from winning_managers
        where wm_name = i_name;
    end del;
end managers_api;
/

For reasons which will shortly become apparent, Teddy has been granted access to the application as follows :

grant select, insert, update, delete on winning_managers to teddy
/

grant execute on managers_api to teddy
/

Teddy is working on a project to identify all managers who have won each of the major English domestic trophies in their carrer.
He immediately takes advantage of these new privileges to add all of the Premier League winning managers to the application…

begin
    motty.managers_api.ins('SIR ALEX FERGUSON', 1993, 1994, 2006);
    motty.managers_api.ins('KENNY DALGLISH', 1995, null, 2012);
    motty.managers_api.ins('ARSENE WENGER', 1998, 1998, null);
    motty.managers_api.ins('JOSE MOURINHO', 2005, 2007, 2006);
    motty.managers_api.ins('CARLO ANCELOTTI', 2010, 2010, null);
    motty.managers_api.ins('ROBERTO MANCINI', 2012, 2011, null);
    motty.managers_api.ins('MANUEL PELLIGRINI', 2014, null, 2014);
    motty.managers_api.ins('CLAUDIO RANIERI', 2016, null, null);
    motty.managers_api.ins('ANTONIO CONTI', 2017, 2018, null);
    motty.managers_api.ins('PEP GUARDIOLA', 2018, 2019, 2018);

    commit;
end;
/

When Motty checks the table, he can see that there are a few problems :

select wm_name, league_title, fa_cup, league_cup
from winning_managers
/

WM_NAME              LEAGUE_TITLE     FA_CUP LEAGUE_CUP
-------------------- ------------ ---------- ----------
SIR ALEX FERGUSON            1993       1994       2006
KENNY DALGLISH               1995                  2012
ARSENE WENGER                1998       1998           
JOSE MOURINHO                2005       2007       2006
CARLO ANCELOTTI              2010       2010           
ROBERTO MANCINI              2012       2011           
MANUEL PELLIGRINI            2014                  2014
CLAUDIO RANIERI              2016                      
ANTONIO CONTI                2017       2018           
PEP GUARDIOLA                2018       2019       2018

10 rows selected. 

It looks like someone has not accounted for anything prior to the 1992/93 season.
Motty needs to find out who is doing this so that he can explain that the FA Cup has been going since 1872, the League since 1888, and the League Cup since 1961.

Auditing DML

In order to create a policy, you need to have the AUDIT SYSTEM or AUDIT_ADMIN role.
We’ll connect to the PDB as system and grant the role to MOTTY now :

grant audit_admin to motty;

Now, connected as MOTTY, we can create an audit policy for the table :

create audit policy wm_audit
    actions all on motty.winning_managers
    when 'sys_context(''userenv'', ''session_user'') not in (''SYS'', ''SYSTEM'')' evaluate per session
/

Audit created

…and enable it…

audit policy wm_audit
/

Audit succeeded.

Note that the when condition of the policy needs to be formatted as in this example. If you use the standard SQL method for escaping quotes – i.e. :

when q'['sys_context('userenv', 'session_user') not in ('SYS', 'SYSTEM')']' evaluate per session

you’ll find yourself on the wrong end of :

ORA-46368: Audit policy does not have a simple rule condition.

In terms of specifying which users should be included or excluded from this policy, you could re-create the it as follows :

noaudit policy wm_audit
/

drop audit policy wm_audit
/

create audit policy wm_audit
    actions all on motty.winning_managers
/

audit policy wm_audit except sys, system	
/

Either way, with the policy in place and enabled, the next time Teddy connects and checks the table…

select wm_name 
from motty.winning_managers
/

…MOTTY gets to see an entry in the audit log :


select dbusername, sql_text
from unified_audit_trail
where unified_audit_policies = 'WM_AUDIT'
/

DBUSERNAME           SQL_TEXT                                          
-------------------- --------------------------------------------------
TEDDY                select wm_name                                    
                     from motty.winning_managers                       

Looking at the policy, we can confirm that any activities by SYS and SYSTEM on this table are excluded from any logging under this policy :


select audit_condition
from audit_unified_policies
where policy_name = 'WM_AUDIT'
/

AUDIT_CONDITION                                                       
----------------------------------------------------------------------
sys_context('userenv', 'session_user') not in ('SYS', 'SYSTEM')

So when SYSTEM runs a query against the table :

select wm_name
from motty.winning_managers
where league_title is not null
and fa_cup is not null
and league_cup is not null
/

WM_NAME                                 
----------------------------------------
SIR ALEX FERGUSON
JOSE MOURINHO
PEP GUARDIOLA

It does not show up in the Audit Trail :

select event_timestamp, sql_text
from unified_audit_trail
where unified_audit_policies = 'WM_AUDIT'
and dbusername = 'SYSTEM'
/

no rows selected

At this point, you may be wondering why you don’t simply dispense with any journalling triggers you have hanging around in your application and just use the audit trail instead. Well, to illustrate one major difference in how these mechanisms might behave consider the following…

Teddy has overheard Motty grumbling in the office and decides to insert another record into the table …

insert into motty.winning_managers(wm_name, league_title, fa_cup, league_cup)
values('MOTTY', 1888, 1872, 1961);

…before having second thoughts…

rollback;

and checking that the record has indeed not been committed :

select league_title, fa_cup, league_cup
from motty.winning_managers
where wm_name = 'MOTTY'
/

no rows selected

Unfortunately, when Motty checks the audit trail again, Teddy is busted…

select dbusername, action_name, sql_text
from unified_audit_trail
where unified_audit_policies = 'WM_AUDIT'
and event_timestamp > systimestamp - ((1/24/60) * 10) 
order by event_timestamp
/

DBUSERNAME           ACTION_NAME          SQL_TEXT                                
-------------------- -------------------- ----------------------------------------
TEDDY                INSERT               insert into motty.winning_managers(wm_na
                                          me, league_title, fa_cup, league_cup)   
                                          values('MOTTY', 1888, 1872, 1961)       
                                                                                 

TEDDY                SELECT               select league_title, fa_cup, league_cup 
                                          from motty.winning_managers             
                                          where wm_name = 'MOTTY'                 

So, even though the INSERT statement was not committed, it still appears in the audit trail. Probably not the sort of thing you want from a journalling trigger.

DML from stored procedure calls

Teddy has now started to clean up the data using the MANAGERS_API package :

begin
    -- Correct the dates for Sir Alex Ferguson's first FA Cup and League Cup wins as a manager
    motty.managers_api.upd(i_name => 'SIR ALEX FERGUSON', i_title => null, i_fac => 1990, i_lc => 1992);
    
    -- Delete Kenny Dalglish record...
    motty.managers_api.del(i_name => 'KENNY DALGLISH');
    
    --Re-insert the record to reflect the fact that he has been knighted
    motty.managers_api.ins(i_name => 'SIR KENNY DALGLISH', i_title => 1986, i_fac => 1986, i_lc => 2012);
end;
/

commit;

We can see each of the DML statements resulting from these stored program unit calls. However, whilst the SQL_TEXT shows the bind variable placeholders, the bind variable values themseleves can be found in the SQL_BINDS column :

select dbusername, action_name, sql_text, sql_binds
from unified_audit_trail
where unified_audit_policies = 'WM_AUDIT'
and event_timestamp > systimestamp - ((1/24/60) * 10)
order by event_timestamp
/

DBUSERNAME      ACTION_NAME     SQL_TEXT                                                     SQL_BINDS                     
--------------- --------------- ------------------------------------------------------------ ------------------------------
TEDDY           UPDATE          UPDATE WINNING_MANAGERS SET LEAGUE_TITLE = NVL(:B4 , LEAGUE_  #1(0):  #2(4):1990 #3(4):1992
                                TITLE), FA_CUP = NVL( :B3 , FA_CUP), LEAGUE_CUP = NVL(:B2 ,   #4(17):SIR ALEX FERGUSON     
                                LEAGUE_CUP) WHERE WM_NAME = :B1                                                           

TEDDY           DELETE          DELETE FROM WINNING_MANAGERS WHERE WM_NAME = :B1             #1(14):KENNY DALGLISH        
TEDDY           INSERT          INSERT INTO WINNING_MANAGERS( WM_NAME, LEAGUE_TITLE, FA_CUP,  #1(18):SIR KENNY DALGLISH #2(
                                 LEAGUE_CUP) VALUES( :B4 , :B3 , :B2 , :B1 )                4):1986 #3(4):1986 #4(4):2012 

Realising that not completing the list of managers will annoy anyone who has read this far in the hope of finding out, Teddy knuckles down and finishes the job. Once he’s done, we can see the full list :

select wm_name, league_title, fa_cup, league_cup, 
    greatest( league_title, fa_cup, league_cup) as "Career Treble"
from motty.winning_managers
order by 5
/

MANAGER                        LEAGUE_TITLE       FA_CUP   LEAGUE_CUP Career Treble
------------------------------ ------------ ------------ ------------ -------------
Joe Mercer                             1968         1969         1961          1969
Bill Nicholson                         1961         1961         1971          1971
Don Revie                              1969         1972         1968          1972
Sir Alex Ferguson                      1993         1990         1992          1993
George Graham                          1989         1993         1987          1993
Jose Mourinho                          2005         2007         2006          2007
Sir Kenny Dalglish                     1986         1986         2012          2012
Pep Guardiola                          2018         2019         2018          2019

8 rows selected. 

Useful Links and Acknowledgements

As ever, the Oracle documentation is a mine of useful information.

The page for the AUDIT command is a useful starting point, as is the page for CREATE AUDIT POLICY.

As usual, Tim Hall’s Oracle Base has a number of useful articles on this topic, this one being of particular use to me in putting this post together.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.