Server Setup
Auditing
is disabled by default .
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
But can enabled by setting the AUDIT_TRAIL static parameter,
which has the following allowed values.
AUDIT_TRAIL
= { none | os | db | db,extended | xml | xml,extended }
The following list provides a
description of each setting:
- none or false -
Auditing is disabled.
- db or true -
Auditing is enabled, with all audit records stored in the database audit trial
(SYS.AUD$).
- db,extended -
As db,
but the SQL_BIND and SQL_TEXT columns
are also populated.
- xml-
Auditing is enabled, with all audit records stored as XML format OS files.
- xml,extended -
As xml,
but the SQL_BIND and SQL_TEXT columns
are also populated.
- os-
Auditing is enabled, with all audit records directed to the operating
system's audit trail.
To enable auditing and direct audit
records to the database audit trail, we would do the following.
SQL>
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SQL>
SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
STARTUP
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
Audit Options
One
look at the AUDIT command
syntax should give you an idea of how flexible Oracle auditing is. There is no
point repeating all this information, so instead we will look at a simple
example.
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;
Now we will do some activity 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;
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);
INSERT
INTO test_tab (id) VALUES (2);
INSERT
INTO test_tab (id) VALUES (3);
INSERT
INTO test_tab (id) VALUES (4);
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 shown below.
- 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.
When
the audit trail is directed to an XML format OS file, it can be read using a
text editor or via the V$XML_AUDIT_TRAIL view, which contains similar
information to the DBA_AUDIT_TRAIL view.
COLUMN
db_user FORMAT A10
COLUMN
object_schema FORMAT A10
COLUMN
object_name FORMAT A10
COLUMN
extended_timestamp FORMAT A35
SELECT
db_user,
extended_timestamp,
object_schema,
object_name,
action
FROM v$xml_audit_trail
WHERE object_schema = 'AUDIT_TEST'
ORDER
BY extended_timestamp;
DB_USER EXTENDED_TIMESTAMP OBJECT_SCH OBJECT_NAM ACTION
----------
----------------------------------- ---------- ---------- ----------
AUDIT_TEST 16-FEB-2006
14:14:33.417000 +00:00 AUDIT_TEST TEST_TAB 1
AUDIT_TEST 16-FEB-2006
14:14:33.464000 +00:00 AUDIT_TEST
TEST_TAB 2
AUDIT_TEST 16-FEB-2006
14:14:33.511000 +00:00 AUDIT_TEST
TEST_TAB 6
AUDIT_TEST 16-FEB-2006
14:14:33.542000 +00:00 AUDIT_TEST
TEST_TAB 3
AUDIT_TEST 16-FEB-2006
14:14:33.605000 +00:00 AUDIT_TEST
TEST_TAB 7
AUDIT_TEST 16-FEB-2006
14:14:34.917000 +00:00 AUDIT_TEST
TEST_TAB 12
6 rows selected.
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 independent 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.
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
Oracle 9i fine grained auditing was limited to 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;
/
