10.2 Where to Audit Once you've decided to enable some form of auditing, you next have to decide where the audit information will be stored. If the operating system supports an audit trail that's stored outside the database, you can write the audit trail either directly to an operating system file or to the database. Two INIT.ORA parameters control the auditing actions: - AUDIT_FILE_DEST
-
Tells Oracle the directory name in which the audit trail is to be written. The default value for this parameter is $ORACLE_HOME/RDBMS/AUDIT. - AUDIT_TRAIL
-
Enables or disables auditing. As a value for AUDIT_TRAIL, specify one of these values: "NONE," "OS," or "DB." If you specify "NONE" (the default), no non-default auditing will occur. If you specify "OS," system-wide auditing will be turned on and the results written to a file in the AUDIT_FILE_DEST directory. The information written to the operating system file will be encoded and is not readable. If you specify "DB," system-wide auditing will be enabled and the results stored in the SYS.AUD$ table in the sys schema in an unencoded, readable format. Oracle supplies several views against the SYS.AUD$ table to make viewing of the audit information easier. Oracle-supplied tools, such as SQL*Plus, can be used to generate reports about the auditing outcome. 10.2.1 About the SYS.AUD$ Table Because the SYS.AUD$ table is owned by sys , the values are stored in the system tablespace. High audit activity results in fragmentation of the system tablespace not a good thing to have happen. Therefore, if you decide to use the database to hold the audit information, be sure to move the SYS.AUD$ table to another tablespace.  | You only to need change the location of the SYS.AUD$ table once for each database at the time you create the database or when you decide to enable auditing on a database. | | To accomplish this, you must be logged on as sys . However, you can work from SQL*Plus if you wish. In other words, you do not have to be connected from the Server Manager utility to perform this work. Create a new tablespace with a meaningful name like "audit_storage" or "audit_info." Sizing the tablespace to ensure that you can hold enough information for the period of time you require auditing to be enabled can get tricky. You may need to play around with the storage parameters; you might initially size the tablespace large and adjust it down later as you get a feel for just how much space is required for your particular auditing needs. Here are some things to consider in determining the size of the tablespace: -
The volume of different activities you are auditing -
The amount of information you need to retain online -
The amount of time you want to spend maintaining this table; if the table is sized too small, you have to archive and/or delete from the table more often than you might really want Once the tablespace is created, you create a temporary table in the new tablespace using the command AS SELECT * FROM SYS.AUD$, then drop the current SYS.AUD$ table, and rename the temporary table to AUD$. Here is an example of what your script will look like: SQL> connect sys/<appropriate_sys_password> Connected. SQL> CREATE TABLESPACE audit_storage 2 DATAFILE 'E:\ORANT\DATABASE\AUDSTOR01.ORA' 3 SIZE 40M; Tablespace created. SQL> CREATE TABLE audit_temp 2 TABLESPACE audit_storage 3 STORAGE (INITIAL 10M 4 NEXT 1M 5 MINEXTENTS 1 6 MAXEXTENTS 10 7 PCTINCREASE 1) 8 AS SELECT * 9 FROM sys.aud$; Table created. SQL> DROP TABLE sys.aud$; Table dropped. SQL> RENAME audit_temp TO aud$; Table renamed. SQL> SELECT table_name, tablespace_name 2 FROM user_tables 3 WHERE table_name = 'AUD$'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ AUD$ AUDIT_STORAGE 10.2.2 A Problem There is one problem with the approach described in the last section. When you create the table as SELECT * from AUD$, you do not carry over any of the views based on the AUD$ table. When you drop the real AUD$ table, the views become invalid. After renaming the temporary table to AUD$, the views will still be invalid and must be recompiled. To manually recompile the views, use a command with the following syntax: SQL> ALTER VIEW dba_audit_exists COMPILE; View altered. To determine the list of invalid views, you can perform the following query logged on as the user sys . The results shown here are from an Oracle8 version 8.0.4 database: SQL> COLUMN object_name FORMAT a30 SQL> SELECT object_name, status 2 FROM user_objects 3 WHERE status = 'INVALID'; OBJECT_NAME STATUS ------------------------------ ------- DBA_AUDIT_EXISTS INVALID DBA_AUDIT_OBJECT INVALID DBA_AUDIT_SESSION INVALID DBA_AUDIT_STATEMENT INVALID USER_AUDIT_OBJECT INVALID USER_AUDIT_SESSION INVALID USER_AUDIT_STATEMENT INVALID USER_AUDIT_TRAIL INVALID 8 rows selected. Recompiling the views with the new SYS.AUD$ table will result in the views becoming valid for the renamed table. 10.2.3 Default Auditing Privileges Even if you enable the auditing parameters through the INIT.ORA parameter file, only those areas you specifically declare will actually be audited . In order for an account to be able to activate auditing, the AUDIT SYSTEM privilege must be granted to that account. Even without privileges granted to any account, there are some actions that will be stored to operating system files whether auditing is enabled or not. When a database is created, a file, commonly referred to as the alert log , is created. The location and name of this file are operating system-dependent. On a Windows NT system running Oracle8 with a database named ORCL, for example, the alert log is called orclALRT.LOG and is found in the C:\orant\RDBMS80\TRACE directory. The security- related actions stored in the alert log for the database include: -
Database startup -
Database shutdown -
Any connection made by an account that has administrator privileges -
Any database structure changes that are performed, like creating a tablespace or adding a datafile Note that when you use sys or internal to connect to the database, a record is not generated in the alert log. The only way you can connect to a database as sys or internal is through an operating system account that either belongs to the DBA group or has the OPSYS privilege. These accounts skirt the normal database security and, therefore, their connection to the database as privileged users is assumed to be normal and is not tracked. With auditing enabled and the information being stored in either the database or the operating system (or both), disk space becomes a very real consideration. If the tablespace or disk to which Oracle is writing the audit trail becomes full, the audit trail will cease to be logged and information will be lost. Therefore, you must ensure that ample room exists on the tablespace or disk to which the audit information will be written. | | We noted in Chapter 4, that the only view from which Oracle permits data to be deleted is AUDIT_HISTORY. This is to let you periodically reduce the storage area required for auditing. | | | 
