ORACLE 10g 的 AUDIT 功能

Audit Options

One look at the 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;

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.

SQL>

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>

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.