4.167 DBA_COMMON_AUDIT_TRAIL

DBA_COMMON_AUDIT_TRAIL displays all standard and fine-grained audit trail entries, mandatory and SYS audit records written in XML format.

Note:

This view is populated only in an Oracle Database where unified auditing is not enabled. When unified auditing is enabled in Oracle Database, the audit records are populated in the new audit trail and can be viewed from UNIFIED_AUDIT_TRAIL.

Column Datatype NULL Description

AUDIT_TYPE

VARCHAR2(22)

Audit trail type:

  • Standard Audit

  • Standard XML Audit

  • Fine Grained Audit

  • Fine Grained XML Audit

  • SYS XML Audit

  • Mandatory XML Audit

SESSION_ID

NUMBER

Numeric ID for the Oracle session

PROXY_SESSIONID

NUMBER

Proxy session serial number, if an enterprise user has logged in through the proxy mechanism

STATEMENTID

NUMBER

Numeric ID for the statement run; a statement may cause multiple audit records

ENTRYID

NUMBER

Numeric ID for the audit trail entry in the session

EXTENDED_TIMESTAMP

TIMESTAMP(6) WITH TIME ZONE

Timestamp of the audited operation (timestamp of user login for entries created by AUDIT SESSION) in the session's time zone

GLOBAL_UID

VARCHAR2(32)

Global user identifier for the user, if the user has logged in as an enterprise user

DB_USER

VARCHAR2(128)

Database user name of the user whose actions were audited

CLIENT_ID

VARCHAR2(128)

Client identifier in the Oracle session

ECONTEXT_ID

VARCHAR2(64)

Application execution context identifier

EXT_NAME

VARCHAR2(4000)

User external name

OS_USER

VARCHAR2(255)

Operating system login user name of the user whose actions were audited

USERHOST

VARCHAR2(128)

Client host machine name

OS_PROCESS

VARCHAR2(16)

Operating system process identifier of the Oracle process

TERMINAL

VARCHAR2(255)

Identifier of the user's terminal

INSTANCE_NUMBER

NUMBER

Instance number as specified by the INSTANCE_NUMBER initialization parameter

OBJECT_SCHEMA

VARCHAR2(128)

Owner of the audited object

OBJECT_NAME

VARCHAR2(128)

Name of the object affected by the action

POLICY_NAME

VARCHAR2(128)

Name of the Fine-Grained Auditing Policy

NEW_OWNER

VARCHAR2(128)

Owner of the object named in the NEW_NAME column

NEW_NAME

VARCHAR2(128)

New name of the object after a RENAME or the name of the underlying object

ACTION

NUMBER

Numeric action type code. The corresponding name of the action type is in the STATEMENT_TYPE column.

STATEMENT_TYPE

VARCHAR2(28)

Name of the action type corresponding to the numeric code in the ACTION column

AUDIT_OPTION

VARCHAR2(40)

Auditing option set with the AUDIT statement

TRANSACTIONID

RAW(8)

Transaction identifier of the transaction in which the object was accessed or modified

RETURNCODE

NUMBER

Oracle error code generated by the action (0 if the action succeeded)

SCN

NUMBER

System change number (SCN) of the query

COMMENT_TEXT

VARCHAR2(4000)

Text comment on the audit trail entry, providing more information about the statement audited

Also indicates how the user was authenticated:

  • DATABASE - Authentication was done by password

  • NETWORK - Authentication was done by Oracle Net Services or the Advanced Networking Option

  • PROXY - Client was authenticated by another user. The name of the proxy user follows the method type.

SQL_BIND

NVARCHAR2(2000)

Bind variable data of the query

SQL_TEXT

NVARCHAR2(2000)

SQL text of the query

OBJ_PRIVILEGE

VARCHAR2(32)

Object privileges granted or revoked by a GRANT or REVOKE statement. The value of this column is a 32-character string of Y and dash (-) characters. Each character corresponds to a numbered privilege in the following list. The left-most character corresponds to privilege 0, the next character corresponds to privilege 1, and so on. The right-most character corresponds to privilege 31.

  • 0 - ALTER
  • 1 - AUDIT
  • 2 - COMMENT
  • 3 - DELETE
  • 4 - GRANT
  • 5 - INDEX
  • 6 - INSERT
  • 7 - LOCK
  • 8 - CREATE
  • 9 - SELECT
  • 10 - UPDATE
  • 11 - REFERENCES
  • 12 - EXECUTE
  • 13 - VIEW
  • 14 - DROP
  • 15 - ANALYZE
  • 16 - CREATE
  • 17 - READ
  • 18 - WRITE
  • 19 - KEEP SEQUENCE
  • 20 - ENQUEUE
  • 21 - DEQUEUE
  • 22 - UNDER
  • 23 - ON COMMIT
  • 24 - REWRITE
  • 25 - UPSERT
  • 26 - DEBUG
  • 27 - FLASHBACK
  • 28 - MERGE
  • 29 - USE
  • 30 - FLASHBACK ARCHIVE
  • 31 - DIRECTORY EXECUTE

A Y indicates that the privilege was granted or revoked by the statement. A dash indicates that the privilege was not affected by the statement. For example, the following value indicates that the MERGE privilege was granted or revoked by the statement:

----------------------------Y---

SYS_PRIVILEGE

VARCHAR2(40)

System privileges granted or revoked by a GRANT or REVOKE statement

ADMIN_OPTION

VARCHAR2(1)

Indicates whether the role or system privilege was granted with the ADMIN option

OS_PRIVILEGE

VARCHAR2(7)

Operating privilege (SYSDBA or SYSOPER), if any, used in the session. If no privilege is used, it will be NONE.

GRANTEE

VARCHAR2(128)

Name of the grantee specified in a GRANT or REVOKE statement

PRIV_USED

VARCHAR2(40)

System privilege used to execute the action

SES_ACTIONS

VARCHAR2(19)

Session summary (a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE). Positions 14, 15, and 16 are reserved for future use. The characters are:

  • - - None

  • S - Success

  • F - Failure

  • B - Both

LOGOFF_TIME

DATE

Timestamp of user log off

LOGOFF_LREAD

NUMBER

Number of logical reads in the session

LOGOFF_PREAD

NUMBER

Number of physical reads in the session

LOGOFF_LWRITE

NUMBER

Number of logical writes for the session

LOGOFF_DLOCK

VARCHAR2(40)

Number of deadlocks detected during the session

SESSION_CPU

NUMBER

Amount of CPU time used by the Oracle session

OBJ_EDITION_NAME

VARCHAR2(128)

Name of the edition containing the audited object

DBID

NUMBER

Database identifier of the audited database

RLS_INFO

CLOB

Stores virtual private database (VPD) policy names and predicates separated by delmiter

COMMON_USER

VARCHAR2(128)

Effective user for the statement execution

Note:

The SQL_BIND and SQL_TEXT columns are only populated if the AUDIT_TRAIL initialization parameter is set to db, extended or xml, extended or if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.