Retrieve oracle sysdba audit from table

This post is also available in: Português

Oracle will always generate audit files for some SYSDBA operations in audit_file_dest, no matter what you do. As Doc ID 1528170.1, "Some auditing of SYSDBA is mandatory and cannot be disabled (STARTUP, SHUTDOWN, and CONNECT)." If audit_sys_operations is currently set to TRUE, many files will be created in audit_file_dest by SYSDBA user activity.

In this article, I will explain and show you how to read this information easily within an Oracle table.

I will divide it in 3 steps:

1. Create a shell script process to generate temporary aud files in "loader" format.
2. Create a external table and a view to dynamically read this contents.
3. Create a crontab job to minutely convert new generated audit files.

So let's begin. First of all, create a folder on your OS to keep the shell script and the converted audit files.

In this example, my folder for audit script is /home/oracle/audsys and I will keep the transformed files in /home/oracle/audsys/out. My audit_file_dest is "/u02/admin/orcl/adump".

The script below will convert the audit files from lines to columns with fields separator:

#! /bin/ksh
# Created by Rodrigo Jorge (www.dbarj.com.br) in Oct/2014

audfiles=/u02/admin/orcl/adump/*.aud
outfolder=/home/oracle/audsys/out
outsufix=".ext" # Sufix for temp files

procfile ()
{
  cont=0
  seq=1
  delim="|||"
  ifile=$1
  ofile=$2

  rm -f $ofile

  while read var; do

    if [[ $var == ACTION* ]]
    then
      cont=1
      result="${linha_2ant}${delim}$(echo $linha_ant | sed 's/^[^:]*: //')"
    fi

    if [ $cont -ge 1 -a $cont -le 7 ]
    then
      if [[ $cont == 2 ]] && [[ $var != DATABASE* ]]
      then
         result="${result} ${var}"
      else
         result=${result}${delim}$(echo $var | sed 's/^[^:]*:\[[0-9]*\] //')
         cont=$((cont+1))
      fi
    fi

    if [[ $cont == 8 ]]
    then
      cont=0
      result="${result}${delim}$(basename $ifile)${delim}${seq}"
      seq=$((seq+1))
      echo $result >> $ofile
    fi

    linha_2ant=$linha_ant
    linha_ant=$var

  done <$ifile
}

for f in $audfiles
do
  outfile=$outfolder/$(basename $f)${outsufix}
  if [ ! -f $outfile -o "$outfile" -ot "$f" ]
  then
        procfile $f $outfile
        touch -m -r $f $outfile
  fi
done

shfile=${outfolder}/catfiles.sh
[ ! -f $shfile ] && echo "$(which cat) ${outfolder}/*.aud${outsufix}" > $shfile
[ ! -x $shfile ] && chmod +x $shfile
echo > $outfolder/null.txt

Run the script to generate the initial files. The first run can take a while. After the first, the next executions will be differential.

On the next step, we are creating the directory pointing to our temp folder:

SQL> CREATE DIRECTORY AUDSYSDIR AS '/home/oracle/audsys/out';

Directory created.

SQL>

Now, it's time to create the external table:

CREATE TABLE TB_AUDSYS
(
   LOGDATE VARCHAR2(2000),
   LENGTH NUMBER,
   ACTION CLOB,
   DATABASE_USER VARCHAR2(30),
   PRIVILEGE VARCHAR2(2000),
   CLIENT_USER VARCHAR2(2000),
   CLIENT_TERMINAL VARCHAR2(2000),
   STATUS NUMBER,
   DBID NUMBER,
   FILENAME VARCHAR2(2000),
   SEQ NUMBER
)
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY AUDSYSDIR
   ACCESS PARAMETERS
   (
      records delimited by newline
      preprocessor audsysdir:'catfiles.sh'
      badfile audsysdir:'query%a_%p.bad'
      logfile audsysdir:'query%a_%p.log'
      fields terminated by '|||'
      missing field values are null
      (
         LOGDATE CHAR(2000),
         LENGTH CHAR(2000) enclosed by X'27',
         ACTION CHAR(100000),
         DATABASE_USER CHAR(2000) enclosed by X'27',
         PRIVILEGE CHAR(2000) enclosed by X'27',
         CLIENT_USER CHAR(2000) enclosed by X'27',
         CLIENT_TERMINAL CHAR(2000) enclosed by X'27',
         STATUS CHAR(2000) enclosed by X'27',
         DBID CHAR(2000) enclosed by X'27',
         FILENAME CHAR(2000),
         SEQ CHAR(2000)
      )
   )
   LOCATION ('null.txt')
)
PARALLEL
REJECT LIMIT 0;

Note here that we use the "fields terminated by '|||'", the same pattern used on the shell script above. Some fileds are also enclosed by X'27', the HEX value for "single quotation". The "location" must be a null file (and it must exist) as we are going to use the preprocessor script, that will "cat" all the files dinamically inside the the temporary folder.

Let's check the output for the table:

SQL> set lines 1000 pages 1000 wrap off
SQL> col logdate format a32
SQL> col length format 9999
SQL> col action format a30
SQL> col database_user format a15
SQL> col privilege format a7
SQL> col client_user format a20
SQL> col client_terminal format a5
SQL> col status format 9999
SQL> col dbid format 9999999999
SQL> col filename format a15
SQL> col seq format 999
SQL> select * from tb_audsys sample (1);

LOGDATE                          LENGTH ACTION                         DATABASE_USER   PRIVILE CLIENT_USER          CLIEN STATUS        DBID FILENAME         SEQ
-------------------------------- ------ ------------------------------ --------------- ------- -------------------- ----- ------ ----------- --------------- ----
Wed Oct 1 16:26:34 2014 -03:00      169 'CONNECT'                      /               NONE    emc12                Not A   1017  2044245708 sirac_ora_15997    2
Sun Oct 12 16:26:48 2014 -03:00     155 'CONNECT'                      /               NONE    emc12                        1017  2044245708 sirac_ora_20302    1
Mon Sep 29 14:29:29 2014 -03:00     166 'CONNECT'                      MDOXDBA         SYSDBA  oracle               pts/0      0  2044245708 sirac_ora_29181    1
Sat Oct 11 16:26:38 2014 -03:00     155 'CONNECT'                      /               NONE    emc12                        1017  2044245708 sirac_ora_45362    1
Mon Sep 29 16:53:30 2014 -03:00     166 'CONNECT'                      MDOXDBA         SYSDBA  oracle               pts/1      0  2044245708 sirac_ora_53002    1
Thu Sep 18 03:00:03 2014 -03:00     165 'CONNECT'                      RMANBACKUP      SYSDBA  oracle                          0  2044245708 sirac_ora_57015    1
Thu Oct 2 16:26:33 2014 -03:00      169 'CONNECT'                      /               NONE    emc12                Not A   1017  2044245708 sirac_ora_62200    2
Fri Oct 10 16:26:37 2014 -03:00     155 'CONNECT'                      /               NONE    emc12                        1017  2044245708 sirac_ora_63673    1

8 rows selected.

SQL>

Good, everything is working. I can create now a view to convert the LOGDATE column from varchar2 to timestamp:

CREATE VIEW VW_AUDSYS AS
SELECT TO_TIMESTAMP_TZ(SUBSTR(LOGDATE, 5), 'Mon fmDDfm hh24:mi:ss YYYY TZH:TZM') LOG_TZ,
       LENGTH,
       TO_CHAR(SUBSTR(ACTION,1,4000)) ACTION,
       DATABASE_USER,
       PRIVILEGE,
       CLIENT_USER,
       CLIENT_TERMINAL,
       STATUS,
       DBID,
       FILENAME,
       SEQ
FROM   TB_AUDSYS;

The last step is to schedule the shell to run every X minutes. So we will have our table always updated table with the latest SYSDBA actions:

[oracle@serveraudsys]$ crontab -l
*/5 * * * * /home/oracle/audsys/procaud.sh

+8