Auditing Oracle SQL*Loader Direct Load Path Events

You can use the CREATE AUDIT POLICY statement to audit Oracle SQL*Loader direct load path events.

About Auditing in Oracle SQL*Loader Direct Path Load Events

You must have the AUDIT_ADMIN role to audit Oracle SQL*Loader direct path events.

To create SQLLoader unified audit policies, you must set the CREATE AUDIT POLICY statement’s COMPONENT clause to DIRECT_LOAD. You can audit direct path load operations only, not other SQLLoader loads, such as conventional path loads.

To access the audit trail, you can query the DIRECT_PATH_NUM_COLUMNS_LOADED column in the UNIFIED_AUDIT_TRAIL data dictionary view.

Related Topics

Oracle SQL*Loader Direct Load Path Unified Audit Trail Events

The unified audit trail can capture SQL*Loader Direct Load Path events.

The unified audit trail captures information about direct path loads that SQLLoader performs (that is, when you set direct=true on the SQLLoader command line or in the SQL*Loader control file).

It also audits Oracle Call Interface (OCI) programs that use the direct path API.

Related Topics

Configuring a Unified Audit Trail Policy for Oracle SQL*Loader Direct Path Events

The CREATE AUDIT POLICY statement ACTIONS COMPONENT clause can create unified audit policies for Oracle SQL*Loader direct path events.

Use the following syntax to create an Oracle SQL*Loader unified audit policy:

CREATE AUDIT POLICY policy_name
ACTIONS COMPONENT=DIRECT_LOAD { LOAD };

For example:

CREATE AUDIT POLICY audit_sqlldr_pol
 ACTIONS COMPONENT=DIRECT_LOAD LOAD;

You can build more complex policies, such as those that include conditions. Remember that after you create the policy, you must use the AUDIT statement to enable it.

Related Topics

Example: Auditing Oracle SQL*Loader Direct Path Load Operations

The CREATE AUDIT POLICY statement can audit Oracle SQLLoader direct path load operations. The following example shows how to audit SQLLoader direct path load operations.

CREATE AUDIT POLICY audit_sqlldr_load_pol
 ACTIONS COMPONENT=DIRECT_LOAD LOAD;

AUDIT POLICY audit_sqlldr_load_pol;

How SQL*Loader Direct Path Load Audited Events Appear in the Audit Trail

The UNIFIED_AUDIT_TRAIL data dictionary view lists SQL*Loader direct path load audited events.

The DIRECT_PATH_NUM_COLUMNS_LOADED column of the UNIFIED_AUDIT_TRAIL view shows the number of columns that were loaded using the SQL*Loader direct path load method. For example:

SELECT DBUSERNAME, ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME, DIRECT_PATH_NUM_COLUMNS_LOADED FROM UNIFIED_AUDIT_TRAIL WHERE AUDIT_TYPE = 'DIRECT PATH API';

DBUSERNAME  ACTION_NAME OBJECT_SCHEMA OBJECT_NAME  DIRECT_PATH_NUM_COLUMNS_LOADED
----------- ----------- ------------- ------------ ------------------------------
RLAYTON     INSERT       HR            EMPLOYEES    4

Auditing Oracle XML DB HTTP and FTP Protocols

You can use the CREATE AUDIT POLICY statement to audit Oracle XML DB HTTP and FTP protocol messages.

About Auditing Oracle XML DB HTTP and FTP Protocols

You must have the AUDIT_ADMIN role to audit Oracle XDB HTTP and FTP protocol messages.

Oracle Database can audit all or failed HTTP messages, 401 AUTH HTTP return code messages, and all or failed FTP messages. The UNIFIED_AUDIT_TRAIL data dictionary view captures the result of the audit in the PROTOCOL_* columns.

Be aware that a unified audit policy for HTTP and FTP protocols can affect performance.

Configuring a Unified Audit Policy to Capture Oracle XML DB HTTP and FTP Protocols

The CREATE AUDIT POLICY statement can create a unified audit policy for Oracle XML DB HTTP and FTP protocols.

Use the following syntax to create a unified audit policy for Oracle XML DB HTTP and FTP protocols:

CREATE AUDIT POLICY policy_name
ACTIONS COMPONENT=PROTOCOL [ HTTP | FTP | AUTHENTICATION];

In this specification:

For example:

CREATE AUDIT POLICY http_pol
ACTIONS COMPONENT=PROTOCOL HTTP;

Example: Auditing Failed Oracle XML DB HTTP Messages

The CREATE AUDIT POLICY statement can audit failed Oracle XML DB HTTP messages. The following example shows how to create and enable a unified audit policy that tracks failed HTTP messages.

CREATE AUDIT POLICY failed_http_pol
ACTIONS COMPONENT=PROTOCOL HTTP;

AUDIT POLICY failed_http_pol WHENEVER NOT SUCCESSFUL;

Example: Auditing All Oracle XML DB FTP Messages

The CREATE AUDIT POLICY statement can audit all Oracle XML DB FTP messages. The following example shows how to create and enable a unified audit policy that tracks all FTP messages.

CREATE AUDIT POLICY all_ftp_pol
ACTIONS COMPONENT=PROTOCOL FTP;

AUDIT POLICY all_ftp_pol;

Example: Auditing Oracle XML DB HTTP Messages That Have 401 AUTH Errors

The CREATE AUDIT POLICY statement can audit HTTP messages that have 401 AUTH errors. The following example shows how to create and enable a unified audit policy that tracks 401 AUTH messages. When you enable this type of policy, you can set it without using the WHENEVER clause or set it using the WHENEVER SUCCESSFUL clause. Using a WHENEVER NOT SUCCESSFUL will not audit 401 AUTH errors.

CREATE AUDIT POLICY 401_error_pol
ACTIONS COMPONENT=PROTOCOL AUTHENTICATION;

AUDIT POLICY 401_error_pol;

How the Unified Audit Trail Captures Oracle XML DB HTTP and FTP Protocol Messages

The UNIFIED_AUDIT_TRAIL data dictionary view lists Oracle XML DB HTTP and FTP protocol messages.

The PROTOCOL_* columns capture HTTP- and FTP-specific information such as the session ID, the return code, the type of request, and the text of the request or reply.

For example, the following query shows that the HTTP-GET request/reply had a return code of 207, which means the reply may have multiple components with separate return codes:

SELECT PROTOCOL_RETURN_CODE, PROTOCOL_ACTION_NAME
FROM UNIFIED_AUDIT_POLICY
WHERE USERHOST = "HR_SRV";

PROTOCOL_RETURN_CODE  PROTOCOL_ACTION_NAME
--------------------  --------------------
207                   HTTP-GET-CMD
207                   HTTP-GET