Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle design for auditing tips

Oracle Tips by Burleson Consulting
September 8, 2015


More than ever, companies are demanding complete audit trails for everything that happens within an Oracle database. Managers want to see audits of all aspects of the database, and government regulations are spurring this interest.

The most important regulation is HIPAA, the Health Insurance Portability and Accountability Act of 1996. Under HIPAA, all Oracle databases in the health care industry must meet rigorous auditing standards, including auditing of the viewing of confidential patient information.

Auditing is not something that is strapped on to the Oracle database, and Oracle designers must carefully choose from among the auditing methods available in Oracle and implement the most appropriate method for their shops.

Oracle auditing falls into these areas:
 

  • 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;

 
If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.