Oracle Tips and Tricks — David Fitzjarrell

May 30, 2006

Audit this!

Filed under: General — dfitzjarrell @ 12:34

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 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
   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
 lo_dt date;
 select max(logon_time)
 into lo_dt
 from v$session
 where username = user;


-- 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   ,
   to_char(lo_dt, 'hh24:mi:ss'),

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;

show errors

create or replace trigger logoff_audit_trigger
 lo_dt date:=sysdate;
-- ***************************************************
-- 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
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)
sys_context('USERENV','SESSIONID') = session_id;
end if;


This works well in 8i and later releases 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. [9i offers the AUDIT SESSION option, which can 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; 9i and 9iR2 provided 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:

     object_schema => 'hr',
     object_name => 'emp',
     policy_name => 'chk_hr_emp',
     audit_condition => 'dept = ''SALES'' ',
     audit_column => 'salary');

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;


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:

     object_schema => 'hr',
     object_name => 'emp',
     policy_name => 'chk_hr_emp'

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> column obj$name format a10
SQL> column obj$schema format a10
SQL> column lsqltext format a30
SQL> select
  4  OBJ$NAME,
  7  from sys.fga_log$;

---------- ---------- ---------- ------------------------------ ---------------------------------
   1095613 BING       EMP        select *                       17-APR-09 PM
                                 from emp


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.

9i (9.0.1.x) and 9iR2 (9.2.0.x) also offer Fine-grained access control, 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 which would dynamically modify the query submitted by adding a WHERE clause limiting the data to only that for the user’s company. A good example is provided here:

It isn’t a short example, but it is worth the time to read and understand the mechanism involved.

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:


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:


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:


Note this turns off all auditing, by session and by access, for the given object. The documentation at has a wealth of information on this topic.

The auditing options haven’t changed much in 10g — 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:

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


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. Contact me if you have specific questions; check my profiles for email addresses.

May 29, 2006


Filed under: General — dfitzjarrell @ 16:22

Welcome to Oracle Tips, a blog where I will post tips, tricks and other tidbits related to Oracle relational databases. You will NOT find misappropriated documents of other authors posted here (unlike some other blogs I’ve visited).

So, if there’s anything you want to see let me know. I’ll do what I can to accomodate you.


Create a free website or blog at