Auditing Database Access

轉自：

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.