10.5 Default Auditing
As we mentioned earlier in this chapter, some actions will be stored to operating system files whether auditing is enabled or not. These actions are:
Connection to the database from a privileged account
Structural changes made to the database, like adding a tablespace datafile, etc.
When the database is started up, a record is written automatically to an operating system file. If the database was started with either sys or internal , the user information will not be recorded. The information recorded is the operating system username of the process starting the database, the terminal identifier, the timestamp (date and time) when the database was started, and whether or not auditing was enabled. The purpose of writing this information is to create a record of anyone attempting to start the database and disable auditing in order to hide their actions. At the time of database startup, the database audit trail is not yet available, so the startup information is always written to an operating system audit file.
In all of the auditing situations listed above, the information is recorded to an operating system log. If the operating system does not enable Oracle to access its audit facility, Oracle will record the information in a log in the same directory in which the background processes record their activities.
10.5.1 Auditing During Database Startup
The first type of default auditing occurs during database startup. An example of operating system audit entries stored automatically for a Windows NT system running Oracle version 8.0.4 is shown here:
Audit trail: ACTION : 'startup' OS_AUTHENT_PREFIX : OPS$. (3:55:41 a.m.) Audit trail: ACTION : `startup' AUDIT_TRAIL : none. (3:55:40 a.m.) Audit trail: ACTION : `connect INTERNAL' OSPRIV : OPER CLIENT USER: SYSTEM CLIENT TERMINAL: MLT-PC. (3:55:31 a.m.)
These three entries were found in the Event Viewer, at the Start Programs Administrative Tools Event Viewer menu option on a Window NT system. There are three event logs System, Security, and Application into which anyone can insert an event. Oracle will log events in both the System and the Application event logs. The time notations in parentheses were added by us to show you more clearly the sequence of events.
The first entry in the sequence above is actually the one listed last. This entry shows the initial connection made to the database in order to start it. Of special interest in the third entry is the notation of the client terminal from which the database was started MLT-PC and the system privilege used OSPRIV. The three audit notations were present in the Windows NT Administrative Tools Event Viewer, in the Application log, after the database was started. As each of the individual detached processes (PMON, SMON, DBWR, LGWR, CKPT, RECO) was started, an individual entry was inserted in the event log. There was also an entry for the time at which the SGA was initialized .
10.5.2 Auditing During Database Shutdown
The second form of default auditing that may occur is at the time of database shutdown. Each time the database is shut down, a record may be written to the audit trail indicating the operating system username, the user's terminal identifier, and the date and timestamp when the action occurred. The use of the words "may be" in the last sentence is intentional. Depending on the operating system involved, if a privileged user, like SYSDBA or SYSOPER , shuts the database down, the event might not be registered in the System event log. On a Windows NT version 4.0 system running Oracle8 version 8.0.4, if the database is shut down using the command:
net stop OracleStart<db_name>
no record of the database shutdown is made either to the Windows NT Application event log or to the database alert log.
If your operating system/database automatically records the shutdown attempts performed by non-privileged users, you will find this information very valuable if you are investigating why your database unexpectedly shut down. The absence of an event entry for the shutdown could help you eliminate the fear that your database had been intentionally shut down by an outsider.
10.5.3 Auditing During Database Connection with Privileges
The third default action recorded to the operating system audit trail occurs when a user connects to the database with administrative privileges. The operating system user information is recorded. This information is very valuable in helping you detect whether someone has managed to acquire privileges they should not have.
10.5.4 Auditing During Database Structure Modification
When a command is issued from the database to modify the structure of the database, the command and its outcome are captured to the alert log for that database. This is the fourth default action. Some examples of commands that will be captured in the alert log follow:
CREATE TABLESPACE <tablespace_name> ALTER TABLESPACE <tablespace_name> ADD DATAFILE ALTER TABLESPACE OFFLINE DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS CREATE ROLLBACK SEGMENT <rollback_segment_name>
In all of these commands, the successful completion of the command will in some way alter the structure of the database.