|
Audit Trail
|
Object auditing enables keeping track of actions such as SELECT, INSERT, and UPDATE
which are made to a specific object. A statement is as follows.
AUDIT action name ON schema name.object name [ BY SESSION|ACCESS ]
This time, I set the object audit to keep track of actions (i.e. SELECT,
INSERT, UPDATE, DELETE) which are made to six tables owned by ORABM user.
BY SESSION|ACCESS indicates if records are created in AUD$ per session or access.
BY SESSION creates only one audit record in the audit trail.
BY ACCESS creates record every time an access is made to an object.
Before I move on, I would like to check if object auditing affects performance.
I use TPC-C benchmark again to measure performance.
1. NOAUDIT: 131 TPS
2. AUDIT BY SESSION: 108 TPS
3. AUDIT BY ACCESS: 93 TPS
When audit is made per session, response drops by 17.6% when compared with NOAUDIT.
When audit is made per action, response drops by 29% when compared with NOAUDIT.
The result above shows that object auditing causes a major response slowdown.
It is therefore important to determine the target object for auditing.
Now, I examine the point which should be considered to use object auditing
First, I indicate information that are obtained by object auditing.
----------------------------------------------------------------------------
select os_username os_usrnm
,terminal
,owner||'.'||obj_name obj
,ses_actions
,count(*) cnt
from dba_audit_object
group by owner||'.'||obj_name, ses_actions
order by obj,cnt
OS_USRNM TERMINAL OBJ SES_ACTIONS CNT
-------- -------- ------------------------ --------------------- --------
ORACLE pts/0 ORABM.CUSTOMER ---------SS----- 1 <-(1)
ORACLE pts/0 ORABM.CUSTOMER ---------S------ 4
ORACLE pts/0 ORABM.DISTRICT ---------SS----- 5
ORACLE pts/0 ORABM.HISTORY ------F--------- 1 <-(2)
ORACLE pts/0 ORABM.NEW_ORDER ---S-----S------ 1
ORACLE pts/0 ORABM.ORDER_LINE ---------S------ 5
ORACLE pts/0 ORABM.WAREHOUSE ---------SS----- 2
ORACLE pts/0 ORABM.WAREHOUSE ---------S------ 3
----------------------------------------------------------------------------
|
SES_ACTIONS column is a string of 16 characters and contains information
about activities (e.g. success or failure). Position of characters represents
following action types. Minus sign (-) in SES_ACTIONS column means no
action (called a placeholder), S means success, F means failure,
and B means both success and failure.
1. ALTER
2. AUDIT
3. COMMENT
4. DELETE
5. GRANT
6. INDEX
7. INSERT
8. LOCK
9. RENAME
10. SELECT
11. UPDATE
12. REFERENCE
13. EXECUTE
14. Reserved
15. Same as above
16. Same as above
Take a look at (1) in the table above. S flag is on at the 10th and 11th from the left,
which means that SELECT and UPDATE are successfully executed
to CUSTOMER table.
Take a look at (2) in the table above. F flag is on at the 7th from the left, which
means that INSERT process to HISTORY table fails.
(If there are activities with success and failure, B flag will be on.)
Unfortunately, object auditing does not give us the details of SQL statement.
Object auditing just follows up the actions. Thus, it is very important
to get grasp of application and object characteristics.
For example, if audit is on to hot object with heavy updates, large number of
audit records will be created. As a result, response slows down.
On the other hand, for reference object, response may not slow down by monitoring
possible data collapse caused by DELETE and UPDATE.
When you enable auditing, be sure to examine characteristics of an object and
let object auditing maximize its effect with minimum number of audit records.
That's it for today.
Koji Shinkubo
|
|
