With ever-changing regulatory and compliance requirements, auditing is becoming more important than ever. In this article, we will look at a common-method of enabling auditing within Oracle along with its benefits and drawbacks. In the next article, we will look at a new Oracle solution for auditing.
Let’s create a simple table with some data that will need auditing:
CREATE TABLE test AS
SELECT object_id, object_name, status, created
FROM all_objects;A common method of enabling auditing is to create a historical table with all of the same columns as the table to be audited (along with a few historical columns). For our purposes:
CREATE TABLE test_hist AS
SELECT object_id, object_name, status, created,
CAST (NULL as VARCHAR2(1)) oper,
CAST (NULL as TIMESTAMP) modified_date
FROM test
WHERE 1 = 0;Then we need a trigger to populate the historical table whenever the main table is changed. In this case, we are just going to audit any updates, but similar triggers can and should be added for inserts and deletes.
CREATE OR REPLACE TRIGGER audit_test
AFTER UPDATE
ON TEST
FOR EACH ROW
DECLARE
/**********************************************************************
NAME: Test
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ----------------------------
1.0 5/16/2012 dbolton 1. Created this trigger.
NOTES:
*********************************************************************/
BEGIN
IF ( (:NEW.object_id <> :OLD.object_id)
OR (:NEW.object_name <> :OLD.object_name)
OR (:NEW.status <> :OLD.status)
OR (:NEW.created <> :OLD.created))
THEN
INSERT INTO test_hist
(object_id,
object_name,
status,
created,
oper,
modified_date)
VALUES (:OLD.object_id,
:OLD.object_name,
:OLD.status,
:OLD.created,
'U',
SYSTIMESTAMP);
END IF;
END audit_test;
/With that, we have enabled simple auditing on our main table. As a quick test:
SQL> SELECT COUNT (*)
2 FROM test_hist;
COUNT(*)
----------
0
SQL> UPDATE test
2 SET object_name = SUBSTR( object_name, 1, 29) || '-'
3 WHERE MOD(object_id, 10) = 0;
5573 rows updated.
SQL> SELECT COUNT (*)
2 FROM test_hist;
COUNT(*)
----------
5573But let’s take a quick look behind the scenes at what is actually happening. If we turn on autotrace:
SQL> SET AUTOTRACE ON
SQL> UPDATE test
2 SET object_name = SUBSTR( object_name, 1, 29) || '-'
3 WHERE MOD(object_id, 10) = 0;
5573 rows updated.
Elapsed: 00:00:00.22
Execution Plan
----------------------------------------------------------
Plan hash value: 839355234
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 4997 | 214K| 111 (1)| 00:00:02 |
| 1 | UPDATE | TEST | | | | |
|* 2 | TABLE ACCESS FULL| TEST | 4997 | 214K| 111 (1)| 00:00:02 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6365 recursive calls
12121 db block gets
689 consistent gets
0 physical reads
3467532 redo size
838 bytes sent via SQL*Net to client
853 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5573 rows processed
SQL> ROLLBACK;
Rollback complete.
SQL> ALTER TRIGGER audit_test DISABLE;
Trigger altered.
SQL> UPDATE test
2 SET object_name = SUBSTR( object_name, 1, 29) || '-'
3 WHERE MOD(object_id, 10) = 0;
5573 rows updated.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 839355234
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 4997 | 146K| 111 (1)| 00:00:02 |
| 1 | UPDATE | TEST | | | | |
|* 2 | TABLE ACCESS FULL| TEST | 4997 | 146K| 111 (1)| 00:00:02 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
7 recursive calls
4232 db block gets
453 consistent gets
0 physical reads
1603712 redo size
832 bytes sent via SQL*Net to client
853 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
5573 rows processedSo the explain plan is exactly the same, but there are a lot more recursive calls and block gets with the trigger enabled. Looking at a tracefile, we see that is the result of the trigger operation:
*********************************************************************
UPDATE test
SET object_name = SUBSTR( object_name, 1, 29) || '-'
WHERE MOD(object_id, 10) = 0
call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 1 0
Execute 1 0.38 0.39 0 389 5573
Fetch 0 0.00 0.00 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
total 2 0.38 0.41 0 390 5573
*********************************************************************
INSERT INTO TEST_HIST
VALUES
(:B1 , :B2 , :B3 , :B4 , 'U', SYSTIMESTAMP)
call count cpu elapsed disk query rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
Execute 5573 0.10 0.10 0 52 5573
Fetch 0 0.00 0.00 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
total 5574 0.10 0.10 0 52 5573
*********************************************************************So the trigger has a good deal of overhead involved when using this auditing solution. Additionally, this method relies on the trigger being enabled. If that trigger is disabled, then the auditing does not take place. If the table is changed, the historical table and the trigger must be updated as well.
Finally, this method does not make retrieval of the audit information simple. In this example, we write the old record to the historical table. So any unmodified records are only in the main table. A UNION is needed to see the original state of the table. We could change this to write all the original records to the historical table and then only write the new values to the table. However, this requires storing duplicate data in the historical and main tables, which is not an ideal situation.
In the next article, we will look at Oracle Total Recall, which is an integrated audit solution release will Oracle Database 11g.
Link to script that contains the examples in this post.