转自：http://www.dbspecialists.com/december-2003.html
Ask DB Specialists: Auditing Database Access
We've been testing the AUDIT feature on our Oracle 8.1.7 instance. We entered AUDIT ALL, AUDIT INSERT TABLE BY username, AUDIT UPDATE TABLE BY username, AUDIT DELETE TABLE BY username, and a number of other statements not included in the "ALL" designation.
Logging on as "username", we then ran a number of SQL statements against the database via SQL*Plus. An entry for most of them shows up in SYS.AUD$ for each test we ran, but out of the nine tests ran over three days, only one entry for INSERT, UPDATE, and DELETE can be found. We have spool files of the SQL statements run to verify that the statements did actually execute. What could be the problem?
Terry Sutton of the Database Specialists team responds: When you establish auditing of statements for a user using AUDIT INSERT TABLE BY username, etc., the default of "BY SESSION" auditing is used. This means that in the AUD$ table (or DBA_AUDIT_TRAIL view), one row appears for each table on which an audited action is performed in each of the user's sessions. If 1000 inserts and 70 deletes are performed on a table in one session, only one row will appear in the DBA_AUDIT_TRAIL view.
If you query DBA_AUDIT_TRAIL like this:
SELECT obj_name, sessionid, username, ses_actions, timestamp
FROM dba_audit_trail;you'll get a result like (columns have been shortened for readability):
OBJ_NAME SESSIONID USERNAME SES_ACTIONS TIMESTAMP
-------- ---------- -------- ------------------- ------------------
EMP 328523 TSUTTON ---S--S---S----- 04-NOV-03 14:28:12
DEPT 328523 TSUTTON ------S--------- 04-NOV-03 14:28:17
DEPT 328549 TSUTTON ---S------------ 04-NOV-03 15:28:41
DEPT 328551 TSUTTON ------S---B----- 04-NOV-03 15:30:49
EMP 328551 TSUTTON ------S--------- 04-NOV-03 15:32:22
DEPT 328552 TSUTTON ----------F----- 04-NOV-03 15:35:02
EMP 328552 TSUTTON ------S--------- 04-NOV-03 15:39:44
The TIMESTAMP column indicates the time of the first audited action within the session. The SES_ACTIONS column is a session summary—a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, EXECUTE, READ. (Positions 15, and 16 are reserved for future use). The characters are: - for none, S for success, F for failure, and B for both.
So we can see that the first session performed delete(s), insert(s), and update(s) on the EMP table and insert(s) on the DEPT table. The fourth session (328552) failed trying to update the DEPT table (eleventh character of the string is 'F'). The third session (328551) had both successes and failures while performing updates (eleventh character of the string is 'B'). There is no way of knowing if there were one or one million of any of these actions.
If you want more detail on the user actions, then you can use the "BY ACCESS" auditing option, as in AUDIT INSERT TABLE BY username BY ACCESS.
If you query DBA_AUDIT_TRAIL like this:
SELECT action_name, obj_name, username, timestamp
FROM dba_audit_trail;
you'll get a result like:
ACTION_NAME OBJ_NAME SESSIONID USERNAME TIMESTAMP
------------ --------- --------- ----------------------------
SESSION REC EMP 328523 TSUTTON 04-NOV-03 14:28:12
SESSION REC DEPT 328523 TSUTTON 04-NOV-03 14:28:17
SESSION REC DEPT 328549 TSUTTON 04-NOV-03 15:28:41
SESSION REC DEPT 328551 TSUTTON 04-NOV-03 15:30:49
SESSION REC EMP 328551 TSUTTON 04-NOV-03 15:32:22
SESSION REC DEPT 328552 TSUTTON 04-NOV-03 15:35:02
SESSION REC EMP 328552 TSUTTON 04-NOV-03 15:39:44
INSERT PRODUCT 328561 BWILLIAMS 04-NOV-03 15:47:53
DELETE PRODUCT 328561 BWILLIAMS 04-NOV-03 15:48:16
INSERT PRODUCT 328587 BWILLIAMS 04-NOV-03 16:50:31
INSERT PRODUCT 328587 BWILLIAMS 04-NOV-03 16:50:37
INSERT PRODUCT 328587 BWILLIAMS 04-NOV-03 16:51:56
The user TSUTTON had auditing set up BY SESSION and we see that the ACTION_NAME appears as 'SESSION REC' in each case, which shows that auditing is being done at the session level. The user BWILLIAMS had auditing set at the access level, and here we see a listing of every insert and delete, along with the timestamp of the action, even though several inserts were performed on the same table in the same session.
You can see that you get much more detail with BY ACCESS auditing. But, of course, there is a price to pay. Every audited action adds a row to the AUD$ table (which DBA_AUDIT_TRAIL is a view of), and this table will get huge if there is a lot of audited activity. So, it will need to be watched and probably pruned occasionally.
