Oracle design for auditing tips

• Auditing errors: Audits all server errors, audits of alert log messages, and trace file generation. This is usually done with shell scripts and the Oracle system-level servererror trigger.
   
• Auditing database changes: Oracle provides a DDL trigger to audit all schema changes and can report the exact change, when it was made, and by which user.
   
• Auditing data updates: U.S. government regulations for banks and financial institutions require that all changes to the database be recorded and kept for a period of several years. Oracle provides the archived redo logs for this purpose and the Oracle9i LogMiner for extraction of database change details.
   
• Auditing data viewing: With confidential information being commonplace within Oracle, many companies are requiring audit trails of who views confidential information, such as personnel files, student grades, and hospital patient information. Oracle provides the SQL audit command and Oracle9i fine-grained auditing to track viewing of Oracle rows.

Auditing tools

Oracle is arguably the world's most flexible database and offers the Oracle designer many auditing choices. Regardless of the choices, the Oracle designer must carefully plan the auditing scheme. There are several ways to audit within Oracle and the following auditing tools are provided:
 

• SQL audit command (for DML)
• Auditing with object triggers (DML auditing)
• Auditing with system-level triggers (DML and DDL)
• Auditing with LogMiner (DML and DDL)
• Fine-grained auditing (select auditing)

Additional information
There are entire books devoted to Oracle security and auditing.

While it is impossible to review all the possible auditing tools in this short article, let?s briefly explore the Oracle system-level triggers and see how they are used for Oracle auditing:
 

• Database startup triggers: Database startup triggers can be used to invoke the dbms_shared_pool.keep procedure to pin all frequently referenced PL/SQL packages into RAM.
   
• Logon triggers: The logon triggers can be used to store logon information directly inside Oracle tables, thereby providing a complete audit of all those times when users entered your system.
   
• Logoff triggers: Oracle logoff triggers can automatically capture elapsed session duration times for all users who have accessed your Oracle database.
   
• Servererror triggers: With the servererror trigger, the Oracle administrator can automatically log all serious PL/SQL errors into an Oracle STATSPACK extension table. This table, in turn, has an insert trigger that e-mails the server error directly to the Oracle professional for immediate notification.
   
• DDL triggers: Using the Data Definition Language (DDL) trigger, the Oracle DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

Unlike traditional triggers that invoke a procedure when a specific schema object has changed, system-level triggers are global and are not tied to any schema objects.

Because the Oracle system-level triggers can interface with Oracle tables, the logical approach is to create Oracle tables to hold the data. Let's take a close look at how these triggers work as auditing mechanisms.

Auditing DDL

Auditing database changes is easy with the DDL trigger. You start by defining a table to hold the changes, as shown below:

connect sys/manager;

drop table perfstat.stats$ddl_log;

create table
   perfstat.stats$ddl_log

 (
   user_name        varchar2(30),
   ddl_date         date,
   ddl_type         varchar2(30),
   object_type      varchar2(18),
   owner            varchar2(30),
   object_name      varchar2(128)

 )
 tablespace perfstat;

The DDL trigger executes every time a DDL statement is executed, and adds new entries to your new table, as shown below:

connect sys/manager

create or replace trigger
   DDLTrigger
AFTER DDL ON DATABASE
BEGIN
 insert into
   perfstat.stats$ddl_log
 (
   user_name,
   ddl_date,
   ddl_type,
   object_type,
   owner,
   object_name
 )
 VALUES
 (
   ora_login_user,
   sysdate,
   ora_sysevent,
   ora_dict_obj_type,
   ora_dict_obj_owner,
   ora_dict_obj_name
 );

END;
/

Laurent Schneider also has this alternative DDL triggers script for DDL auditing:

CREATE TABLE AUDIT_DDL (
  d date,
  OSUSER varchar2(255),
  CURRENT_USER varchar2(255),
  HOST varchar2(255),
  TERMINAL varchar2(255),
  owner varchar2(30),
  type varchar2(30),
  name varchar2(30),
  sysevent varchar2(30));
  
create or replace trigger audit_ddl_trg after ddl on schema
begin
  if (ora_sysevent='TRUNCATE')
  then
    null; -- I do not care about truncate
  else
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') ,
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent
    );
  end if;
end;
/



Now whenever a change is made, the details will be written to your table, and you can create audit reports for management. This report is critical for the Oracle DBA who must track changes to his or her production database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes, as shown below:

            Date         Type     Object      Object
 User       of DDL       of DDL   Type        Name
---------- ------------ -------- ----------- --------------------
 SYS        03-07 10:11  DROP     TRIGGER     ERROR_AUDIT_TRIGGER
 APPS       03-07 10:12  ALTER    TRIGGER     LOG_ERRORS_TRIG
 APPS       03-07 10:14  ALTER    TRIGGER     LOG_ERRORS_TRIG
 SYS        03-07 10:23  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
 SYS        03-07 10:24  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
 SYS        03-07 10:25  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
 SYS        03-07 10:27  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
 SYS        03-07 10:28  CREATE   TRIGGER     ERROR_AUDIT_TRIGGER
 SYS        03-07 10:33  DROP     TRIGGER     LOG_ERRORS_TRIG
 OPS$ORACLE 03-07 12:56  CREATE   TABLESPACE  JANEDI
 OPS$ORACLE 03-07 12:57  CREATE   TABLESPACE  JANHF
 APPS       03-07 13:10  ALTER    PACKAGE     PA_MC_CURRENCY_PKG
 JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_HEADERS
 JANEDI     03-07 14:15  CREATE   INDEX       JAN_EDI_HEAD_N1
 JANEDI     03-07 14:15  CREATE   TABLE       JAN_EDI_LINES
 JANEDI     03-07 14:15  CREATE   INDEX       JAN_EDI_LINE_N1

Auditing server errors with the servererror trigger

The code snippet below creates an Oracle table that will capture all information relating to PL/SQL errors.
 

connect sys/manager;

drop table perfstat.stats$servererror_log;

create table
   perfstat.stats$servererror_log
 (
   error     varchar2(30),
   timestamp date,
   username  varchar2(30),
   osuser    varchar2(30),
   machine   varchar2(64),
   process   varchar2(8),
   program   varchar2(48)
 )
 tablespace perfstat;

The servererror trigger takes whatever server error was generated from Oracle PL/SQL and places it into an Oracle table. Note below that by capturing the user ID and the time of the error, the Oracle administrator can build an insert trigger on the stats$servererror log table and immediately be notified via e-mail whenever a server error occurs.
 

connect sys/manager

create or replace trigger
   log_errors_trig
after servererror on database

declare
   var_user      varchar2(30);
   var_osuser    varchar2(30);
   var_machine   varchar2(64);
   var_process   varchar2(8);
   var_program   varchar2(48);
 begin
   select
      username,
      osuser,
      machine,
      process,
      program
   into
      var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program
   from
      v$session
   where
      audsid=userenv('sessionid');

   insert into
     perfstat.stats$servererror_log
   values(
      dbms_standard.server_error(1),
      sysdate,
      var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program);
end;

Now that you have the data inside an Oracle table, you can easily query the table to provide a server error summary report. The report below shows daily details, but you can also create a weekly rollup of server errors.

                         Production Server Error Report
                      Investigate and correct faulty code

                                                                    Error      

 Date     Hr   MESSAGE                                              Count      

 ------------- -------------------------------------------------- --------      

 03-05-28      ORA-01003: no statement parsed                          118 

               ORA-00936: missing expression                           75      
               ORA-01086: savepoint 'XX_MAINT' never established        53          
                                                                               
                                                                               

 03-05-27      ORA-00931: missing identifier                        36,901      
               ORA-04043: object HRI1_DUMP.MV_STAGE_SEARCH2 does     1,781      
               ORA-01400: cannot insert NULL into ("HRI1_OWNER"."    1,288       
               ORA-03001: unimplemented feature                        423      
               ORA-01722: invalid number                               228      
               ORA-00054: resource busy and acquire with NOWAIT s      178      
               ORA-01003: no statement parsed                          106      
               ORA-00904: invalid column name                           80 

Reader comments:

Robert Koltai notes an issues in Oracle 10g release 2:

I found a small bug on the page above. (Oracle10gR2, Solaris10)

 

In case you have more than one '/ as sysdba' connections the following gives more than one record:

 

select count(*) from v$session
where  audsid=userenv('sessionid')

 

  COUNT(*)
----------
         2
 

 

=> This select fails, because it expects only one record from v$session.

   select
      username,
      osuser,
      machine,
      process,
      program
   into
      var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program
   from
      v$session
   where
      audsid=userenv('sessionid');
 

Here is a script to track the logon activity of a specific user:

CREATE OR REPLACE TRIGGER "LOGON_AUDIT_TRIGGER" AFTER
LOGON ON DATABASE
DECLARE
sess number(10);
prog varchar2(70);
BEGIN
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'MYUSERNAME' THEN
   sess := sys_context('USERENV','SESSIONID');
   SELECT program INTO prog FROM v$session WHERE audsid = sess
   and rownum<=1;
   INSERT INTO stats$user_log VALUES (
   user,sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   prog,
   sysdate,
   sys_context('USERENV','OS_USER'));
END IF;
END;