Audit Options

First we create a new user called AUDIT_TEST.

CONNECT sys/password AS SYSDBA

CREATE USER audit_test IDENTIFIED BY password

  DEFAULT TABLESPACE users

  TEMPORARY TABLESPACE temp

  QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;

GRANT create table, create procedure TO audit_test;

Next we audit all operations by the AUDIT_TEST user.

CONNECT sys/password AS SYSDBA

AUDIT ALL BY audit_test BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;

AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

These options audit all DDL and DML, along with some system events.

·        DDL (CREATE, ALTER & DROP of objects)

·        DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).

·        SYSTEM EVENTS (LOGON, LOGOFF etc.)

Next, we perform some operations that will be audited.

CONN audit_test/password

CREATE TABLE test_tab ( id  NUMBER);

INSERT INTO test_tab (id) VALUES (1);

UPDATE test_tab SET id = id;

SELECT * FROM test_tab;

DELETE FROM test_tab;

DROP TABLE test_tab;

In the next section we will look at how we view the contents of the audit trail.

View Audit Trail

The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views:

SELECT view_name

FROM   dba_views

WHERE  view_name LIKE 'DBA%AUDIT%'

ORDER BY view_name;

VIEW_NAME

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

DBA_AUDIT_EXISTS

DBA_AUDIT_OBJECT

DBA_AUDIT_POLICIES

DBA_AUDIT_POLICY_COLUMNS

DBA_AUDIT_SESSION

DBA_AUDIT_STATEMENT

DBA_AUDIT_TRAIL

DBA_COMMON_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL

DBA_OBJ_AUDIT_OPTS

DBA_PRIV_AUDIT_OPTS

DBA_REPAUDIT_ATTRIBUTE

DBA_REPAUDIT_COLUMN

DBA_STMT_AUDIT_OPTS

14 rows selected.

The three main views are:

·        DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).

·        DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).

·        DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

The most basic view of the database audit trail is provided by the DBA_AUDIT_TRAIL view, which contains a wide variety of information. The following query displays the some of the information from the database audit trail.

COLUMN username FORMAT A10

COLUMN owner    FORMAT A10

COLUMN obj_name FORMAT A10

COLUMN extended_timestamp FORMAT A35

SELECT username, extended_timestamp, owner, obj_name,  action_name FROM   dba_audit_trail WHERE  owner = 'AUDIT_TEST'

ORDER BY timestamp;

USERNAME   EXTENDED_TIMESTAMP                  OWNER      OBJ_NAME   ACTION_NAME

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

AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00  AUDIT_TEST TEST_TAB   CREATE TABLE

AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00  AUDIT_TEST TEST_TAB   INSERT

AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00  AUDIT_TEST TEST_TAB   UPDATE

AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00  AUDIT_TEST TEST_TAB   SELECT

AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00  AUDIT_TEST TEST_TAB   DELETE

AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00  AUDIT_TEST TEST_TAB   DROP TABLE

6 rows selected.

SQL>

Maintenance and Security

Auditing should be planned carefully to control the quantity of audit information. Only audit specific operations or objects of interest. Over time you can refine the level of auditing to match your requirements.

The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

The OS and XML audit trails are managed through the OS. These files should be secured at the OS level by assigning the correct file permissions.

Fine Grained Auditing (FGA)

Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.

First, create a test table.

CONN audit_test/password

CREATE TABLE emp (

 empno     NUMBER(4) NOT NULL,  ename     VARCHAR2(10),

 job       VARCHAR2(9), mgr       NUMBER(4),

 hiredate  DATE, sal       NUMBER(7,2), comm      NUMBER(7,2),

 deptno    NUMBER(2));

INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);

INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);

COMMIT;

The following policy audits any queries of salaries greater than £50,000.

CONN sys/password AS sysdba

BEGIN

  DBMS_FGA.add_policy(

    object_schema   => 'AUDIT_TEST',

    object_name     => 'EMP',

    policy_name     => 'SALARY_CHK_AUDIT',

    audit_condition => 'SAL > 50000',

    audit_column    => 'SAL');

END;

/

Querying both employees proves the auditing policy works as expected.

CONN audit_test/password

SELECT sal FROM emp WHERE ename = 'Tim';

SELECT sal FROM emp WHERE ename = 'Larry';

CONN sys/password AS SYSDBA

SELECT sql_text

FROM   dba_fga_audit_trail;

SQL_TEXT

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

SELECT sal FROM emp WHERE ename = 'Larry'

1 row selected.

SQL>

Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:

BEGIN

  DBMS_FGA.add_policy(

    object_schema   => 'AUDIT_TEST',

    object_name     => 'EMP',

    policy_name     => 'SALARY_CHK_AUDIT',

    audit_condition => 'SAL > 50000',

    audit_column    => 'SAL',

    handler_schema  => 'AUDIT_TEST',

    handler_module  => 'FIRE_CLERK',

    enable          => TRUE);

END;

/

The DBMS_FGA package contains the following procedures:

·        ADD_POLICY 

·        DROP_POLICY

·        ENABLE_POLICY

·        DISABLE_POLICY

In Oracle9i fine grained auditing was limited queries, but in Oracle 10g it has been extended to include DML statements, as shown by the following example.

-- Clear down the audit trail.

CONN sys/password AS SYSDBA

TRUNCATE TABLE fga_log$;

SELECT sql_text FROM dba_fga_audit_trail;

no rows selected.

-- Apply the policy to the SAL column of the EMP table.

BEGIN

  DBMS_FGA.add_policy(

    object_schema   => 'AUDIT_TEST',

    object_name     => 'EMP',

    policy_name     => 'SAL_AUDIT',

    audit_condition => NULL, -- Equivalent to TRUE

    audit_column    => 'SAL',

    statement_types => 'SELECT,INSERT,UPDATE,DELETE');

END;

/

-- Test the auditing.

CONN audit_test/password

SELECT * FROM emp WHERE empno = 9998;

INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);

UPDATE emp SET sal = 10 WHERE empno = 9998;

DELETE emp WHERE empno = 9998;

ROLLBACK;

-- Check the audit trail.

CONN sys/password AS SYSDBA

SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT

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

SELECT * FROM emp WHERE empno = 9998

INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)

UPDATE emp SET sal = 10 WHERE empno = 9998

DELETE emp WHERE empno = 9998

4 rows selected.

-- Drop the policy.

CONN sys/password AS SYSDBA

BEGIN

  DBMS_FGA.drop_policy(

    object_schema   => 'AUDIT_TEST',

    object_name     => 'EMP',

    policy_name     => 'SAL_AUDIT');

END;

/