The Oracle Database Auditing Options

I don’t know what auditing requirements may be in place in other parts of the world, but here in the U.S. we have the Sarbanes-Oxley Act (resulting from the Enron debacle from a few years back) which basically calls for audit trails for all financial activity in an organization, to prevent such cover-ups from recurring. That being said it appears that everyone, performing financial and non-financial tasks, is being audited under the aegis of SOx (as Sarbanes-Oxley is known). Many DBAs are being asked to supply such audit trails; Oracle (depending upon the release) makes this fairly straightforward. [I will not be posting an exhaustive treatise on auditing, I’ll simply be providing some examples and references for further reading.] Starting with 8i (I’ll consider 8.1.6 through 8.1.7.4) connection auditing was offered with the advent of the AFTER LOGON and BEFORE LOGOFF triggers. The code I’ve pasted below, modified a bit by myself, has been around for a long while, and I can’t remember where I first saw it [if anyone can provide proper attribution I’ll gladly give the author due recognition]. Notice it uses the SYS_CONTEXT function, which should be investigated for the Oracle version you’re using, as implementations and available information can change with each new release:

--
-- Create the connection log table
--

create table
   stats$user_log
(
   user_id           varchar2(30),
   session_id           number(8),
   sid   number,
   serial#  number,
   host              varchar2(64),
   ip_address        varchar2(16),
   last_program      varchar2(48),
   last_action       varchar2(32),
   last_module       varchar2(48),
   logon_day                 date,
   logon_time        varchar2(10),
   logoff_day                date,
   logoff_time       varchar2(10),
   elapsed_minutes       number(8),
   elapsed_seconds       number(8),
   last_sql_address         raw(4),
   last_sql_hash_value      number,
   prior_sql_address        raw(4),
   prior_sql_hash_value     number
)
tablespace tools
;

--
-- Create the logon trigger to populate the audit trail
--
-- SYS_CONTEXT('USERENV','IP_ADDRESS') will be NULL for local connections
--
create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
DECLARE
 lo_dt date;
BEGIN
 select max(logon_time)
 into lo_dt
 from v$session
 where username = user;

 dbms_output.put_line(lo_dt);

-- if user  'SYS' then
if user not in ('SYS','SYSTEM','P2000AP') then
insert into stats$user_log
(  user_id       ,
   session_id    ,
   sid   ,
   serial#  ,
   host          ,
   ip_address  ,
   last_program  ,
   last_action   ,
   last_module   ,
   logon_day     ,
   logon_time    ,
   logoff_day    ,
   logoff_time   ,
   elapsed_minutes,
   elapsed_seconds,
   last_sql_address,
   last_sql_hash_value,
   prior_sql_address,
   prior_sql_hash_value)
values(
   user,
   sys_context('USERENV','SESSIONID'),
   null,
   null,
   sys_context('USERENV','HOST'),
   sys_context('USERENV','IP_ADDRESS'),  
   null,
   null,
   null,
   lo_dt,
   to_char(lo_dt, 'hh24:mi:ss'),
   null,
   null,
   null,
   null,
   null,
   null,
   null,
   null
);

update stats$user_log
set sid = (select sid from v$session where sys_context('USERENV','SESSIONID') = audsid),
    serial# = (select serial# from v$session where sys_context('USERENV','SESSIONID') = audsid)
where session_id = sys_context('USERENV','SESSIONID');

end if;
END;
/

show errors

create or replace trigger logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
DECLARE
 lo_dt date:=sysdate;
BEGIN
-- ***************************************************
-- Update the user record
--
-- Set last_action, last_program, last_module,
-- logoff day and time and total minutes connected
-- ***************************************************
-- if user  'SYS' then
if user not in ('SYS','SYSTEM','P2000AP') then
update
stats$user_log
set
last_action = (select action from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_program = (select program from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_module = (select module from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_sql_address = (select sql_address from v$session where sys_context('USERENV','SESSIONID') = audsid),
last_sql_hash_value = (select sql_hash_value from v$session where sys_context('USERENV','SESSIONID') = audsid),
prior_sql_address = (select prev_sql_addr from v$session where sys_context('USERENV','SESSIONID') = audsid),
prior_sql_hash_value = (select prev_hash_value from v$session where sys_context('USERENV','SESSIONID') = audsid),
logoff_day = lo_dt,
logoff_time = to_char(lo_dt, 'hh24:mi:ss'),
elapsed_minutes = round((lo_dt - logon_day)*1440),
elapsed_seconds = round((lo_dt - logon_day)*86400)
where
sys_context('USERENV','SESSIONID') = session_id;
end if;

END;
/

This works well in 8i (but, hopefully, you’re not running a museum and your version is currently supported) creating a connection audit trail for all users, including SYS and SYSTEM (which is necessary for any serious auditing scenario). It also provides the last ACTION and MODULE the connected account used to allow some traceability and limited accountability. [11g (and releases down to 9i2) offers the AUDIT SESSION option, which should replace the code listed above and provides logon time, logoff time and various session-level statistics. These records are written to the SYS.AUD$ table (and are also available in a ‘massaged’ format in the DBA_AUDIT_TRAIL view). SYS.AUD$ can change from release to release, but includes, among other columns, ACTION#, which can be ‘translated’ into an action using the AUDIT_ACTIONS table, which lists the ACTION (mapped to ACTION# in SYS.AUD$) and the NAME. 10g and later releases provide the AUDIT CONNECT option, populating the same tables, but also providing logon and logoff information and the return code, which allows the DBA to investigate abnormal session termination. It is recommended in 9i and later releases to use the provided AUDIT options rather than create the connection audit table and triggers necessary in 8i.] This, however, is not enough of a trail to trap any ‘suspect’ activity in the database; All releases since 9iR2 provide Fine-grained auditing of ‘qualified’ Select statements (those constructed with a WHERE clause) producing an audit trail of ‘offending’ queries, queries accessing sensitive information general users should not see. Fine-grained auditing works in this way:

A policy is set up on a table providing a ‘triggering’ mechanism based upon a column in that table. If a user queries this table for the audited column and supplies the necessary audit condition a record is written to the SYS.FGA_LOG$ table. From the on-line documentation: The following example shows how you can audit SELECT statements on table hr.emp to monitor any query that accesses the salary column of the employee records which belong to sales department:

begin
     DBMS_FGA.ADD_POLICY(
     object_schema => 'hr',
     object_name => 'emp',
     policy_name => 'chk_hr_emp',
     audit_condition => 'dept = ''SALES'' ',
     audit_column => 'salary');
end;
/

Then, either of the following SQL statements will cause the database to log an audit event record.

SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;

or

SELECT salary FROM hr.emp WHERE dept = 'SALES';

Notice that SELECT salary from hr.emp; would NOT generate an audit record as the audit condition (dept = ‘SALES’) is not supplied. Thus, any query not ‘qualified’ with the ‘proper’ [read that as ‘defined in the audit policy’] WHERE clause will not produce a trail, even if the desired column is queried. To make this possibly more confusing this query won’t produce a trail, either: SELECT salary from hr.emp where dept = ‘MARKETING’; since MARKETING isn’t a department triggering any audit action. One can, though, establish an audit policy which doesn’t require a WHERE clause:

begin
     DBMS_FGA.ADD_POLICY(
     object_schema => 'hr',
     object_name => 'emp',
     policy_name => 'chk_hr_emp'
     );
end;
/

With the above policy in place any query against the EMP table will produce a trail:

SQL> select *
  2  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          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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>
SQL> column obj$name format a10
SQL> column obj$schema format a10
SQL> column lsqltext format a30
SQL>
SQL> select
  2  SESSIONID,
  3  OBJ$SCHEMA,
  4  OBJ$NAME,
  5  LSQLTEXT,
  6  NTIMESTAMP#
  7  from sys.fga_log$;

 SESSIONID OBJ$SCHEMA OBJ$NAME   LSQLTEXT                       NTIMESTAMP#
---------- ---------- ---------- ------------------------------ ---------------------------------
   1095613 BING       EMP        select *                       17-APR-09 05.40.10.264000 PM
                                 from emp


SQL>

Of course producing such an extensive trail also consumes disk space, as records are written to audit tables located, by default, in the SYSTEM tablespace; these tables include AUDIT$ and AUD$ for basic auditing data, FGA$ and FGA_LOG$ for the fine-grained auditing data and RLS$, RLS_CTX$ and RLS_GRP$ for fine-grained access control (row-level security, discussed briefly below). It has been suggested by Oracle these tables be moved to a separate tablespace. [However Oracle also states that moving these tables can affect database upgrades and before upgrading a database these tables should be moved back to the SYSTEM tablespace. Interesting.] A tablespace solely for auditing can be created and, by using ALTER TABLE … MOVE TABLESPACE … , these tables can be relocated.

How do you know what actions are being audited? The ACTION# column in AUD$ lists the associated code for the action, and AUDIT_ACTIONS associates a name with the action. A query to return who did what based upon the ACTION# is shown below:

SQL> select sessionid, userid, statement, name
  2  from aud$, audit_actions
  3  where action = action#;

 SESSIONID USERID                          STATEMENT NAME
---------- ------------------------------ ---------- ----------------------------
    387143 BING                                    1 LOGON
    387143 BING                                    7 SESSION REC
    387143 BING                                    8 SESSION REC
    387143 BING                                   11 AUDIT OBJECT
    387143 BING                                   12 SELECT
    387143 BING                                   12 SELECT
 
6 rows selected.
 
SQL>

Versions since 9i (9.0.1.x) and 9iR2 (9.2.0.x) also offer Fine-grained access control, with 10g and later releases providing Virtual Private Database, where data can be restricted automatically (depending upon table structure). An example of fine-grained access control would be allowing companies to ‘see’ only their data by establishing a security policy that would dynamically modify the query submitted by adding a WHERE clause limiting the data to only that for the user’s company. The current documentation is an excellent source of information for both Virtual Private Database and an older option, Oracle Label Security. VPD is discussed here, and Oracle Label Security is discussed here. Both topics are too involved to discuss in this article, hence the links to the online documentation.

Okay, so you can’t easily restrict data in some tables, so fine-grained access control won’t work, and fine-grained auditing won’t be of any use as management has decided implementing such a strategy would not be cost-effective. Plain-vanilla auditing can help by providing a basic knowledge of the activities of a session or against particular objects. This doesn’t provide any actual SQL statements (you’ll need to be creative and write your own mechanism to extract SQL from V$SQLTEXT based upon information found in V$SESSION and V$PROCESS, which might be more work than configuring fine-grained auditing) but it does provide a sort of ‘history’ of actions against an object, who performed them and when they occurred. Auditing by access produces a record every time the object is accessed, for any session and without limit to the number of accesses, which can produce a rather bloated audit trail. Such auditing requires the audit_trail initialization parameter be set to db so that records can be written to the SYS.AUD$ table. To audit select statements for the DEPARTMENT table by access:

AUDIT SELECT ON DEPARTMENT BY ACCESS;

Auditing by session writes a record to the audit trail once for each monitored object accessed in a session, regardless of the number of times that object is accessed. To perform this level of auditing on select statements against the DEPARTMENT table:

AUDIT SELECT ON DEPARTMENT BY SESSION;

In the first case, audit by access, if the DEPARTMENT table is being monitored and SUE issues six select statements against the DEPARTMENT table then six audit records are written to the trail. If audit by session is used instead, those six select statements would write only one record in the audit trail, indicating the table was accessed by SUE’s session but not showing how many times this access occurred. It all depends upon how management wishes to track such actions; it’s up to the DBA to provide a workable solution using the tools Oracle provides.

Using the ‘plain vanilla’ audit trail any SELECT statement will generate an audit record; it depends only upon which method, by access or by session, is selected to determine how much of a trail is produced. In fact, any SELECT, INSERT, UPDATE or DELETE action can be audited on an object or schema and you can audit successful attempts, unsuccessful attempts, or both (the default). To disable auditing use the NOAUDIT command:

NOAUDIT SELECT ON DEPARTMENT;

Note this turns off all auditing, by session and by access, for the given object. The documentation at http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c25audit.htm#538 has a wealth of information on this topic.

The auditing options haven’t changed much in 11g — everything available in 9i and 9iR2 is available in both releases of 10g, and a new wrinkle, ‘Label Security auditing’, is offered. Not having a 10g database with which to play I’ll refer you again to the on-line documentation:

http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14267/audit.htm

How can you keep track of which objects are being audited? In 9i and later releases the DBA_OBJ_AUDIT_OPTS view exists, which contains the owner, object_name, object_type and audit options in effect for all audited objects. A simple…

SQL> select owner, object_name
  2  from dba_obj_audit_opts
  3  /

OWNER                          OBJECT_NAME                   
------------------------------ ------------------------------
BING                           EMPLOYEE
BING                           DEPARTMENT


SQL>

…will display all objects and owners currently being audited in any manner.

Auditing is not a task to be taken lightly, and it can require fairly extensive management depending upon which auditing path is chosen. I hope I’ve provided a basic overview and a general idea of what options are available and what those options do.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles