Friday, July 18, 2008

DBA_AUDIT_OBJECT

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================


Oracle 11gR1
DBA_AUDIT_OBJECT displays audit trail records for all objects in the database.

Related View
USER_AUDIT_OBJECT displays audit trail records for all objects accessible to the current user.
Column
Datatype
NULL
Description
OS_USERNAME
VARCHAR2(255)

Operating system login username of the user whose actions were audited
USERNAME
VARCHAR2(30)

Name (not ID number) of the user whose actions were audited
USERHOST
VARCHAR2(128)

Client host machine name
TERMINAL
VARCHAR2(255)

Identifier of the user's terminal
TIMESTAMP
DATE

Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone
OWNER
VARCHAR2(30)

Creator of the object affected by the action
OBJ_NAME
VARCHAR2(128)

Name of the object affected by the action
ACTION_NAME
VARCHAR2(28)

Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL
NEW_OWNER
VARCHAR2(30)

Owner of the object named in the NEW_NAME column
NEW_NAME
VARCHAR2(128)

New name of an object after a RENAME or the name of the underlying object
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: - for none, S for success, F for failure, and B for both).
COMMENT_TEXT
VARCHAR2(4000)

Text comment on the audit trail
SESSIONID
NUMBER
NOT NULL
Numeric ID for each Oracle session
ENTRYID
NUMBER
NOT NULL
Numeric ID for each audit trail entry in the session
STATEMENTID
NUMBER
NOT NULL
Numeric ID for each statement run
RETURNCODE
NUMBER
NOT NULL
Oracle error code generated by the action. Some useful values:
· 0 - Action succeeded
· 2004 - Security violation
PRIV_USED
VARCHAR2(40)

System privilege used to execute the action
CLIENT_ID
VARCHAR2(64)

Client identifier in each Oracle session
ECONTEXT_ID
VARCHAR2(64)

Application execution context identifier
SESSION_CPU
NUMBER

Amount of CPU time used by each Oracle session
EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE

Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone
PROXY_SESSIONID
NUMBER

Proxy session serial number, if an enterprise user has logged in through the proxy mechanism
GLOBAL_UID
VARCHAR2(32)

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

Instance number as specified by the INSTANCE_NUMBER initialization parameter
OS_PROCESS
VARCHAR2(16)

Operating System process identifier of the Oracle process
TRANSACTIONID
RAW(8)

Transaction identifier of the transaction in which the object is accessed or modified
SCN
NUMBER

System change number (SCN) of the query
SQL_BIND
NVARCHAR2(2000)

Bind variable data of the query
SQL_TEXT
NVARCHAR2(2000)

SQL text of the query
OBJ_EDITION
VARCHAR2(30)

???
Note:
The SQL_BIND and SQL_TEXT columns are only populated if the AUDIT_TRAIL initialization parameter is set to db_extended.
Some queries:
--check which objects are being audited
select * from DBA_OBJ_AUDIT_OPTS

--audit detail
select * from dba_audit_object
order by owner,obj_name, username

--distinct user
select distinct ora_database_name, username
from dba_audit_object
order by username ;

--access type
select distinct ora_database_name, decode(ses_actions,'---------S------','select',ses_actions) ses_actions
from dba_audit_object
order by ses_actions ;

--where did the acesses come from
select distinct ora_database_name, os_username, userhost
from dba_audit_object

--audit detail
select ora_database_name, username username,owner, obj_name, action_name,
decode(ses_actions,'---------S------','select',ses_actions) ses_actions,
decode(returncode, '0','Success',returncode) returncode,
to_char(timestamp,'DD-MON-YYYY HH24:MI')
from dba_audit_object
order by owner,obj_name, username ;

Oracle data dictionary views

DBA_AUDIT_SESSION

Oracle 11gR1

DBA_AUDIT_SESSION displays all audit trail records concerning CONNECT and DISCONNECT.

Related View

USER_AUDIT_SESSION contains audit trail records concerning connections and disconnections of the current user.

Column

Datatype

NULL

Description

OS_USERNAME

VARCHAR2(255)


Operating system login username of the user whose actions were audited

USERNAME

VARCHAR2(30)


Name (not ID number) of the user whose actions were audited

USERHOST

VARCHAR2(128)


Client host machine name

TERMINAL

VARCHAR2(255)


Identifier of the user's terminal

TIMESTAMP

DATE


Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone

ACTION_NAME

VARCHAR2(28)


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

LOGOFF_TIME

DATE


Date and time of user log off

LOGOFF_LREAD

NUMBER


Logical reads for the session

LOGOFF_PREAD

NUMBER


Physical reads for the session

LOGOFF_LWRITE

NUMBER


Logical writes for the session

LOGOFF_DLOCK

VARCHAR2(40)


Deadlocks detected during the session

SESSIONID

NUMBER

NOT NULL

Numeric ID for each Oracle session

RETURNCODE

NUMBER

NOT NULL

Oracle error code generated by the action. Some useful values:

· 0 - Action succeeded

· 2004 - Security violation

CLIENT_ID

VARCHAR2(64)


Client identifier in each Oracle session

SESSION_CPU

NUMBER


Amount of CPU time used by each Oracle session

EXTENDED_TIMESTAMP

TIMESTAMP(6) WITH TIME ZONE


Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone

PROXY_SESSIONID

NUMBER


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

GLOBAL_UID

VARCHAR2(32)


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

INSTANCE_NUMBER

NUMBER


Instance number as specified by the INSTANCE_NUMBER initialization parameter

OS_PROCESS

VARCHAR2(16)


Operating System process identifier of the Oracle process

Oracle data dictionary views

DBA_AUDIT_EXISTS

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================


Oracle 11gR1
DBA_AUDIT_EXISTS displays audit trail entries produced by AUDIT EXISTS and AUDIT NOT EXISTS.

Column
Datatype
NULL
Description
OS_USERNAME
VARCHAR2(255)
Operating system login username of the user whose actions were audited
USERNAME
VARCHAR2(30)
Name (not ID number) of the user whose actions were audited
USERHOST
VARCHAR2(128)
Client host machine name
TERMINAL
VARCHAR2(255)
Identifier of the user's terminal
TIMESTAMP
DATE
Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone
OWNER
VARCHAR2(30)
Intended creator of the non-existent object
OBJ_NAME
VARCHAR2(128)
Name of the object affected by the action
ACTION_NAME
VARCHAR2(28)
Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL
NEW_OWNER
VARCHAR2(30)
Owner of the object named in the NEW_NAME column
NEW_NAME
VARCHAR2(128)
New name of an object after a RENAME or the name of the underlying object
OBJ_PRIVILEGE
VARCHAR2(16)
Object privileges granted or revoked by a GRANT or REVOKE statement
SYS_PRIVILEGE
VARCHAR2(40)
System privileges granted or revoked by a GRANT or REVOKE statement
GRANTEE
VARCHAR2(30)
Name of the grantee specified in a GRANT or REVOKE statement
SESSIONID
NUMBER
NOT NULL
Numeric ID for each Oracle session
ENTRYID
NUMBER
NOT NULL
Numeric ID for each audit trail entry in the session
STATEMENTID
NUMBER
NOT NULL
Numeric ID for each statement run
RETURNCODE
NUMBER
NOT NULL
Oracle error code generated by the action. Some useful values:
· 0 - Action succeeded
· 2004 - Security violation
CLIENT_ID
VARCHAR2(64)
Client identifier in each Oracle session
ECONTEXT_ID
VARCHAR2(64)
Application execution context identifier
SESSION_CPU
NUMBER
Amount of CPU time used by each Oracle session
EXTENDED_TIMESTAMP
TIMESTAMP(6) WITH TIME ZONE
Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone
PROXY_SESSIONID
NUMBER
Proxy session serial number, if an enterprise user has logged in through the proxy mechanism
GLOBAL_UID
VARCHAR2(32)
Global user identifier for the user, if the user has logged in as an enterprise user
INSTANCE_NUMBER
NUMBER
Instance number as specified by the INSTANCE_NUMBER initialization parameter
OS_PROCESS
VARCHAR2(16)
Operating System process identifier of the Oracle process
TRANSACTIONID
RAW(8)
Transaction identifier of the transaction in which the object is accessed or modified
SCN
NUMBER
System change number (SCN) of the query
SQL_BIND
NVARCHAR2(2000)
Bind variable data of the query
SQL_TEXT
NVARCHAR2(2000)
SQL text of the query
OBJ_EDITION
VARCHAR2(30)
???
Note:
The SQL_BIND and SQL_TEXT columns are only populated if the AUDIT_TRAIL initialization parameter is set to db_extended.

Oracle data dictionary views

DBA_AUDIT_POLICIES

Oracle 11gR1

DBA_AUDIT_POLICIES describes all fine-grained auditing policies in the database. Its columns are the same as those in ALL_AUDIT_POLICIES.

Related Views

· ALL_AUDIT_POLICIES describes the fine-grained auditing policies on the tables and views accessible to the current user.

· USER_AUDIT_POLICIES describes the fine-grained auditing policies on the tables and views owned by the current user. This view does not display the OBJECT_SCHEMA column.

Column

Datatype

NULL

Description

OBJECT_SCHEMA

VARCHAR2(30)

NOT NULL

Owner of the table or view

OBJECT_NAME

VARCHAR2(30)

NOT NULL

Name of the table or view

POLICY_NAME

VARCHAR2(30)

NOT NULL

Name of the policy

POLICY_TEXT

VARCHAR2(4000)


Audit condition

POLICY_COLUMN

VARCHAR2(30)


Relevant column

PF_SCHEMA

VARCHAR2(30)


Owner of the audit handler function

PF_PACKAGE

VARCHAR2(30)


Name of the package containing the audit handler function

PF_FUNCTION

VARCHAR2(30)


Name of the audit handler function

ENABLED

VARCHAR2(3)


Indicates whether the policy is enabled (YES) or disabled (NO)

SEL

VARCHAR2(3)


Indicates whether the policy is applied to queries on the object (YES) or not (NO)

INS

VARCHAR2(3)


Indicates whether the policy is applied to INSERT statements on the object (YES) or not (NO)

UPD

VARCHAR2(3)


Indicates whether the policy is applied to UPDATE statements on the object (YES) or not (NO)

DEL

VARCHAR2(3)


Indicates whether the policy is applied to DELETE statements on the object (YES) or not (NO)

AUDIT_TRAIL

VARCHAR2(11)


Indicates whether the SQL_TEXT and SQL_BIND columns in the fine-grained audit trail are populated for this policy (DB_EXTENDED) or not (DB)

POLICY_COLUMN_OPTION

VARCHAR2(11)


Indicates whether all columns in the AUDIT_COLUMN parameter (ALL_COLUMNS) or any of the columns in the AUDIT_COLUMN parameter (ANY_COLUMNS) are considered for triggering fine-grained auditing

Oracle data dictionary views