This article presents an overview of auditing in Oracle 10g Release 2. Many of the topics presented here have been covered in previous articles, but this serves to bring them all together.
Related articles.
Auditing is a default feature of the Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER
SQL*Plus command.
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 SQL>
Auditing is disabled by default, 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. In Oracle 10g Release 1, db_extended
was used in place of db,extended
. The XML options are new to Oracle 10g Release 2.
The AUDIT_SYS_OPERATIONS
static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.
The AUDIT_FILE_DEST
parameter specifies the OS directory used for the audit trail when the os
, xml
and xml,extended
options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS
parameter.
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. SQL>
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;
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.
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.
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 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 extended_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.
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. SQL>
Several fields were added to both the standard and fine-grained audit trails in Oracle 10g, including the following.
EXTENDED_TIMESTAMP
- A more precise value than the exising TIMESTAMP
column.PROXY_SESSIONID
- Proxy session serial number when an enterprise user is logging in via the proxy method.GLOBAL_UID
- Global Universal Identifier for an enterprise user.INSTANCE_NUMBER
- The INSTANCE_NUMBER
value from the actioning instance.OS_PROCESS
- Operating system process id for the oracle process.TRANSACTIONID
- Transaction identifier for the audited transaction. This column can be used to join to the XID
column on the
FLASHBACK_TRANSACTION_QUERY
view.SCN
- System change number of the query. This column can be used in flashback queries.SQL_BIND
- The values of any bind variables if any.SQL_TEXT
- The SQL statement that initiated the audit action.The SQL_BIND
and SQL_TEXT
columns are only populated when the AUDIT_TRAIL
parameter is set to db,extended
or xml,extended
.
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 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 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; /
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/10g/auditing-10gr2