Pro Oracle Database 11g Administration (104 page)

OS

Enables auditing and specifies that an operating system file will store auditing information.

XML

Enables auditing and writes audit records in XML format to an OS file.

NONE

Disables database auditing.

You must stop and start your database for the AUDIT_TRAIL parameter to take effect: SQL> shutdown immediate;

SQL> startup;

SQL> show parameter audit_trail;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_trail string DB

After you've set the AUDIT_TRAIL parameter, no actual auditing events are enabled. You have to use the AUDIT statement to control auditing for specific database events. Examples of enabling auditing are shown in the next few sections.

■
Tip
Set the database initialization parameter AUDIT_SYS_OPERATIONS to TRUE. This ensures that all SYS

activities are logged to files in the directory specified by AUDIT_FILE_DEST (regardless of the setting of AUDIT_TRAIL). This provides you with a simple and effective method for auditing the SYS user.

Auditing DML Usage

Sometimes it's handy when troubleshooting disk space or performance issues to know which tables in the database are actually being used by the application. If you've inherited a database that contains hundreds of tables, it may not be obvious which objects are being accessed. The idea is that if you can identify tables that are not being used, then you can rename and eventually drop them so that you can free up space and have less objects cluttering up your database.

627

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

Auditing allows you to capture the types of SQL statements being used to access a table. For example, the following statement enables auditing on all DML access to the EMP table owned by INV_MGMT:

SQL> audit select, insert, update, delete on inv_mgmt.emp;

From this point on, any DML access to the INV_MGMT.EMP table will be recorded in the SYS.AUD$ table.

You can use a query such as this to report on DML access to a table:

select

username

,obj_name

,timestamp

,substr(ses_actions,4,1) del

,substr(ses_actions,7,1) ins

,substr(ses_actions,10,1) sel

,substr(ses_actions,11,1) upd

from dba_audit_object;

In the prior SQL statement, notice the use of the SUBSTR function to reference the SES_ACTIONS

column of the DBA_AUDIT_OBJECT view. That column contains a 16-character string in which each character means that a certain operation has occurred. The 16 characters represent the following operations in this order: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved by Oracle for future use. The character of S

represents success, F represents failure, and B represents both success and failure.

Once you have identified tables that are not being used, you can simply rename the tables and see if this breaks the application or if any users complain. If there are no complaints, then after some time you can consider dropping the tables. Make sure you take a good backup of your database with both RMAN

and Data Pump before you drop any tables you might have to later recover.

To turn off auditing on an object, use the NOAUDIT statement:

SQL> noaudit select, insert, update, delete on inv_mgmt.inv;

■
Tip
If you simply need to know whether a table is being inserted, updated, or deleted from, you can use the USER_TAB_MODIFICATIONS view to report on that type of activity. This view has columns such as INSERTS, UPDATES, DELETES, and TRUNCATED that will provide information as to how data in the table is being modified.

Auditing Logon/Logoff Events

One basic security auditing technique is to record all user logon and logoff activity in the database. This allows the auditor to determine who was using the database and when. This information is critical when diagnosing security breaches and unauthorized actions.

Use the BY ACCESS clause of AUDIT to enable auditing of logon and logoff activities, like so: SQL> audit create session by access;

Now every user that logs on and logs off the database will result in a record being inserted into the AUD$ table. You can report on logon and logoff activity with a query such as this: 628

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

select

username

,action_name

,to_char(timestamp,'dd-mon-yyyy hh24:mi:ss') event_time

,to_char(logoff_time,'dd-mon-yyyy hh24:mi:ss') logoff_time

from dba_audit_trail;

Here is some sample output:

USERNAME ACTION_NAME EVENT_TIME LOGOFF_TIME

---------- --------------- -------------------- --------------------------

LARRY LOGON 08-oct-2010 16:18:45

LARRY LOGOFF 08-oct-2010 16:18:49 08-oct-2010 16:18:49

DW_MAINT LOGON 08-oct-2010 16:19:33

You can enable logon and logoff access for a specific user by specifying the username: SQL> audit session by larry by access;

You can disable logon/logoff auditing as shown:

SQL> noaudit create session;

To disable logon/logoff for individual sessions, specify the username: SQL> noaudit session by larry;

Another way to audit connections is via the CONNECT option of AUDIT, like so: SQL> audit connect;

To disable this option, run this SQL:

SQL> noaudit connect;

The prior techniques provide a way to quickly enable the auditing of connections to the database.

This can be useful when troubleshooting security violations or application issues.

NOTE:
Some DBAs enable auditing of logon and logoff through an AFTER LOGON ON DATABASE trigger and BEFORE

LOGOFF ON DATDABASE trigger that inserts into a custom table. I'm not a fan of this approach because it requires custom objects and custom code. Why write code to achieve something that Oracle already has a simple and effective solution? Furthermore, if there is an issue with the trigger or custom table, you may find that you can't logon to Oracle anymore. Not good.

Viewing Enabled Audit Actions

There are several data dictionary views that allow you to view what auditing actions have been enabled.

Use the following query to enabled auditing at the privilege level:

629

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

select

user_name

,privilege

,success

,failure

from dba_priv_audit_opts;

Here is some sample output:

USER_NAME PRIVILEGE SUCCESS FAILURE

--------------- ------------------------------ ---------- ----------

CREATE ANY PROCEDURE BY ACCESS BY ACCESS

CREATE PROCEDURE BY ACCESS BY ACCESS

DELETE ANY TABLE BY SESSION BY SESSION

Run the following query to view statement level auditing actions:

select

user_name

,audit_option

,success

,failure

from dba_stmt_audit_opts;

The next query audits object level auditing actions. For this example, only a small subset of the columns available is selected (to keep the output on one page):

select

owner

,object_name

,object_type

,alt

,del

,upd

from dba_obj_audit_opts;

Here is some sample output:

OWNER OBJECT_NAME OBJECT_TYPE ALT DEL UPD

--------------- --------------- --------------- ---------- ---------- -------

SYS AUD$ TABLE -/- S/S -/-

In the prior output, a dash (-) means the audit option is not enabled, an S indicates that the audit option by session is enabled, and an A indicates that the audit action is enabled by access. For each option, there are two possible settings: WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL. These are separated by a forward slash. So in the prior output, auditing by delete from the AUD$ table is enabled by session for both successful and unsuccessful statements.

Turning Auditing Off

To permanently disable auditing, set the AUDIT_TRAIL initialization parameter to none: SQL> alter system set audit_trail='none' scope=spfile;

Now stop and restart your database to turn off Oracle's Standard Auditing feature.

630

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

■
Note
Some database events are always audited (regardless of the setting of AUDIT_TRAIL) such as connections as SYSDBA or SYSOPER and database startup and shutdown events.

If you only want to disable features that you have previously enabled with AUDIT, then use the NOAUDIT statement to turn off specific auditing events. For example, the following statement turns off all statement auditing that was enabled via an AUDIT ALL statement:

SQL> noaudit all;

This next example turns off the auditing of privileges:

SQL> noaudit all privileges;

You can turn off specific auditing actions. For example, say you had previously enabled auditing on a table like this:

SQL> audit delete any table;

You can turn off the prior auditing like this:

SQL> noaudit delete any table;

You can also use SQL to generate SQL to turn off auditing. The following script generates a script that can be used to turn off all statement auditing:

set head off pages 0

spo turn_off_audit.sql

select 'noaudit ' || audit_option || ';'

from dba_stmt_audit_opts;

spo off;

Purging the Audit Table and Files

You should periodically purge the AUD$ table so that it doesn’t consume inordinate amounts of space in your SYSTEM tablespace. (To move the AUD$ table out of the SYSTEM tablespace, see the next section.) The easiest way to purge audit records is to delete from or truncate the audit table. This example truncates the AUD$ table:

SQL> truncate table aud$;

If you need to save the AUD$ data, you can first export the table and then use the TRUNCATE command to remove records. If you want to preserve newer records in the AUD$ table, then use a DELETE

statement. For example, this deletes records over 21 days old:

SQL> delete from aud$ where timestamp# > sysdate + 21;

If you need to remove audit records from the operating system, use the Linux/Unix find command to identify files over a certain age and then remove them. First, determine the directory location of the audit files:

SQL> select value from v$parameter where name='audit_file_dest';

VALUE

--------------------------------------------------------------------------------

/oracle/app/oracle/product/11.2.0/db_1/rdbms/audit

631

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

Now from the operating system, navigate to the audit file directory and identify and remove audit files over a certain age:

$ cd /oracle/app/oracle/product/11.2.0/db_1/rdbms/audit

Use the find and rm command to remove any files over a certain age. The following command removes any files with the extension of .aud from the current working directory that are over seven days old:

$ find . -maxdepth 1 -type f -mtime +7 -name "*.aud" | xargs rm The prior technique permanently removes files from the operating system. Ensure that you're in the correct location and that the audit files are no longer needed.

Keep in mind that you can use the DBMS_AUDIT_MGMT package to purge audit trail records and audit trail files. I personally prefer to truncate and remove files manually as shown in this section because it's simple and efficient. If you want to use the DBMS_AUDIT_MGMT package to manage your audit space, see the
Oracle Database Security
guide (available on Oracle's OTN site) for more details.

Moving the Audit Table to a Non-System Tablespace

If you're going to do an extensive amount of auditing, I recommend moving the AUD$ table to a non-SYSETM tablespace. You may want to do this so that auditing actions doesn’t impact the space used in the SYSTEM tablespace or affect the performance of the SYSTEM tablespace.

Use the DBMS_AUDIT_MGMT package to move the AUD$ table to a separate tablespace. This example moves the AUD$ table to a tablespace named AUD_TBSP:

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

AUDIT_TRAIL_LOCATION_VALUE => 'AUD_TBSP');

END;

/

In the prior PL/SQL code, the AUDIT_TRAIL_TYPE parameter can have the following values:

• DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD moves just the standard audit trail AUD$

table.

• DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD moves just the fine-grained audit trail FGA_LOG$ table.

• DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD moves both standard and fine-grained audit trail tables.

Be aware that the previous PL/SQL can take a long time if there are numerous records in the AUD$

table. You can verify that the table was moved correctly via this query: SQL> select table_name, tablespace_name from dba_tables where table_name='AUD$'; 632

CHAPTER 22 ■ DATABASE TROUBLESHOOTING

■
Tip
If you want to manually move the AUD$ table to a non-SYSTEM tablespace, refer to My Oracle Support note 72460.1 for instructions. Be aware that manually moving the AUD$ table is not supported by Oracle Support and may cause issues when upgrading your database. I only mention it because I know many DBAs who manually move the AUD$ table. If you're maintaining a database that you inherited from other DBAs, you need to be aware of this type of activity.

Auditing at a Granular Level

Fine-grained auditing
(FGA auditing) is a separate feature from regular database auditing. Fine-grained auditing allows you to audit SQL at a more granular level than simple INSERT, UPDATE, DELETE, and SELECT