|
Audit Trail
|
This is the last issue of the current topic, audit trail.
I have analyzed various audit processes and today, I would like to summarize
my analysis.
1. Range of auditing
2. Response slowdown caused by auditing
3. Administration after auditing
Range of auditing and response slowdown caused by auditing
Oracle supports three general types of auditing:
-statement auditing
-privilege auditing
-schema object auditing
Accountability (such as login and logout) audit is performed by auditing the statements, and
it does not affect performance so much.
By auditing the accountability, it enables identifying when (or how long) and who attempts
to establish a connection to Oracle and also enables specifying when and by whom the SQL statement
has been executed.
From a viewpoint of security issue, accountability audit is the first step to ensure security.
If you want to audit a specific object, schema object auditing helps you.
Schema object auditing is very focused and audits only a specific schema object.
A word focus is the key. If all of the schema objects are auditing target, it will surely affect
response.
If Oracle audits BY SESSION, response drops by 18% when compared with NOAUDIT, and if Oracle
audits BY ACCESS, response drops by 30% when compared with NOAUDIT.
In addition to three types of auditing above, Oracle9i supports fine-grained auditing
that allows the user to customize the auditing.
Administration after auditing
After auditing is performed, we further need to have proper administration to
maintain good performance. Otherwise, unnecessary errors occur and DBA has to do
extra work to resolve the problem.
This time, I set initial parameter AUDIT_TRAIL to DB. I create table AUD$ in tablespace
SYSTEM and all audit records are stored in this table.
If I check the table at regular basis, tablespace SYSTEM may be fragmented.
If I do not have proper administration after performing auditing, table AUD$ may be full.
You may consider the auditing as an additional process. This is not true.
audit session by AAA;
Suppose an AAA user's login/logout is audited and table AUD$ is full.
I log in as an AAA user where the table can be extended no more.
Then...
SQL> connect aaa/aaa
ERROR:
ORA-0604: Error occurred at recursive SQL level string
|
[ORA-0604: Error occurred at recursive SQL level string] occurs and login fails.
If DBA is not aware that Oracle audits BY SESSION, he/she has to spend a lot of
time in tracking down the root cause.
It is important to know the type of audit performed and the amount of audit records.
It is also very important to have database maintenance at regular basis.
When you set the initial parameter AUDIT_TRAIL to OS, large amount of ora_<pid>.aud or
sbbbb_<pid>.ora files are created in a directory specified by AUDIT_FILE_DEST.
ora_<pid>.aud is for dedicated connection.
sbbbb_<pid>.ora is for MTS connection.
Be sure to have regular maintenance of these files.
Next week, I will start a new topic.
Koji Shinkubo
|
|
