Auditing

You can extend the Oracle Database unified audit trail to capture application attributes by configuring auditing for application context values. The application context namespace is populated with the required attributes, and those values are captured in the unified audit trail.

The application attributes provided by Database Tools can be used to:

Identify the authenticated IAM principal that initiated the database access using the Database Tools service.
Correlate a unified audit trail record with an OCI audit event.

Identify the Authenticated IAM Principal

To capture the application context attributes in the unified audit trail, you must first run the AUDIT CONTEXT command specifying the additional attributes that you want to include in the audit record.

For example, the following statement captures the values of namespace CLIENTCONTEXT attributes IAM_PRINCIPAL_OCID to RESOURCE_COMPARTMENT_OCID in all audit records.

AUDIT CONTEXT NAMESPACE CLIENTCONTEXT ATTRIBUTES IAM_PRINCIPAL_OCID,IAM_PRINCIPAL_TYPE, 
IAM_PRINCIPAL_SUB_TYPE,IAM_PRINCIPAL_RESOURCE_TYPE,IAM_PRINCIPAL_TENANCY_OCID, 
OPC_REQUEST_ID,RESOURCE_OCID,RESOURCE_COMPARTMENT_OCID

After you have added the attributes, you can use the following statement to check the application context attributes that are captured in the unified audit trail.

SELECT * FROM AUDIT_UNIFIED_CONTEXTS;

Audit records populated in the UNIFIED_AUDIT_TRAIL by the Database Tools service provide information for the following columns.

Table 7-1 Audited Columns

Column	Value
`OS_USERNAME`	IAM authenticated principal identifier possibly truncated
`CLIENT_IDENTIFIER`	Remaining characters of the IAM authenticated principal identifier
`USERHOST`	Middle-tier hostname
`TERMINAL`	Unknown
`CLIENT_PROGRAM_NAME`	Database Tools ORDS
`EXECUTION_ID`	opc-request-id truncated at 64 characters
`APPLICATION_CONTEXTS`	See Table 7-2

The APPLICATION_CONTEXTS column is populated with the values of the following CLIENTCONTEXT attributes, which enables you to identify the authenticated IAM principal that initiated the database operation.

Table 7-2 Application_Contexts Column

Context	Attribute	Description
`CLIENTCONTEXT`	`IAM_PRINCIPAL_OCID`	IAM prinicipal id
	`IAM_PRINCIPAL_TENANCY_OCID`	IAM prinicipal tenancy id
	`IAM_PRINCIPAL_TYPE`	IAM prinicipal type user resource instance service
	`IAM_PRINCIPAL_SUB_TYPE`	IAM principal sub-type natv (native user) natf (native federated user) fed (federated user) none
	`IAM_PRINCIPAL_RESOURCE_TYPE`	IAM principal resource type
	`OPC_REQUEST_ID`	OPC request id
	`RESOURCE_OCID`	Resource id
	`RESOURCE_COMPARTMENT_OCID`	Resource compartment id

Correlate a Unified Audit Trail Record with an OCI Audit Event

Using the OPC_REQUEST_ID and RESOURCE_COMPARTMENT_OCID attributes provided in the APPLICATION_CONTEXTS column and the EVENT_TIMESTAMP, it is possible to find the audit event matching the unified audit trail record.

Using the Oracle Cloud Infrastructure Console

In the Console, open the navigation menu and click Observability & Management, select Logging, and then select Audit.
In the left pane, select the Compartment.
In the right pane, in Filters, enter data.request.id= '<opc-request-id value>'.
In Filter by time, select the time interval from the drop-down list.
Click Apply.
The results appear in the Explore Events tab.

Using the Command Line Interface (CLI)

Enter the <REGION ID>, <RESOURCE_COMPARTMENT_OCID>, and <OPC_REQUEST_ID> in the following script to find the audit event matching the unified audit trail record.

#!/bin/sh
 
read -r -d '' body <<EndOfBody
{
  "searchQuery": "search \"<RESOURCE_COMPARTMENT_ID>/_Audit\" | (data.request.id='<OPC_REQUEST_ID>') | sort by datetime desc",
  "timeStart":"2024-07-19T11:03:56.167Z",
  "timeEnd":"2024-07-19T14:03:56.167Z",
  "isReturnFieldInfo":false
}
EndOfBody
 
oci raw-request --target-uri https://logging.<REGION_ID>.oci.oraclecloud.com/20190909/search --http-method POST --request-body "${body}"

Oracle Cloud Infrastructure Documentation