Auditing is disabled by default, but
can enable by setting the AUDIT_TRAIL static parameter, which has the following
allowed values.
AUDIT_TRAIL = { none | os | db | db_extended
| xml | xml_extended }
The following list provides a description of each setting:
none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit
trial (SYS.AUD$).
db_extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS
files.
Xml_extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's
audit trail.
The AUDIT_SYS_OPERATIONS static parameter
enables or disables the auditing of operations issued by users connecting with
SYSDBA or SYSOPER privileges, including the SYS user. All audit records are
written to the OS audit trail.
The AUDIT_FILE_DEST parameter specifies the
OS directory used for the audit trail when the os, xml and xml,extended options
are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS
parameter.
SQL> ALTER SYSTEM SET audit_trail=db_extended
SCOPE=SPFILE;
SQL> AUDIT INSERT, UPDATE, DELETE ON
ultimus.temp_tab BY ACCESS;
(Note: setting BY ACCESS you tell oracle to audit
statement by statement where by SESSION provides info as bundle)
Next we audit all operations by the AUDIT_TEST user.
CONNECT sys/password AS SYSDBA
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE,
DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
The audit trail is stored in the SYS.AUD$ table.
Its contents can be viewed directly or via the following views.
SELECT view_name
FROM
dba_views
WHERE
view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
14 rows selected.
DBA_AUDIT_TRAIL - Standard auditing only (from
AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only
(from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and
fine-grained auditing.
The most basic view of the database audit trail is
provided by the DBA_AUDIT_TRAIL view, which contains a wide variety of information.
The following query displays the some of the information from the database
audit trail.
COLUMN username FORMAT A10
COLUMN owner
FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM
dba_audit_trail
WHERE owner
= 'AUDIT_TEST'
ORDER BY timestamp;
The following query lists audit records generated
by statement and object audit options:
SQL> SELECT * FROM DBA_AUDIT_OBJECT where
obj_name='TEMP_TAB';
To turn audit option off, issue the following command:
SQL> NOAUDIT INSERT, UPDATE, DELETE ON ultimus.temp_tab BY ACCESS;
We can also query the dba_audit_trail view. Here are the column descriptions from the Oracle documentation:
| 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 | NUMBER | NOT NULL | Numeric action type code. The corresponding name of the action type is in theACTION_NAME column. |
ACTION_NAME | VARCHAR2(28) | Name of the action type corresponding to the numeric code in the ACTIONcolumn | |
NEW_OWNER | VARCHAR2(30) | 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 | |
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 | |
ADMIN_OPTION | VARCHAR2(1) | Indicates whether the role or system privilege was granted with the ADMINoption | |
GRANTEE | VARCHAR2(30) | Name of the grantee specified in a GRANT or REVOKE statement | |
AUDIT_OPTION | VARCHAR2(40) | Auditing option set with the AUDIT statement | |
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:
| |
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 | |
COMMENT_TEXT | VARCHAR2(4000) | Text comment on the audit trail entry, providing more information about the statement auditedAlso indicates how the user was authenticated. The method can be one of the following:
| |
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:
|
PRIV_USED | VARCHAR2(40) | System privilege used to execute the action | |
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 | |
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 |
This query by Arup Nanda, co-author of "Oracle Privacy Security Auditing", shows a sample query against dba_audit_trail for standard Oracle auditing.
select 'standard audit', sessionid,
proxy_sessionid, statementid, entryid, extended_timestamp, global_uid,
username, client_id, null, os_username, userhost, os_process, terminal,
instance_number, owner, obj_name, null, new_owner,
new_name, action, action_name, audit_option, transactionid, returncode,
scn, comment_text, sql_bind, sql_text,
obj_privilege, sys_privilege, admin_option, grantee, priv_used,
ses_actions, logoff_time, logoff_lread, logoff_pread, logoff_lwrite,
logoff_dlock, session_cpu
from
dba_audit_trail;
-------------------------------------------------------------------------------------------
tHANKS fOR rEADING tHIS aRTICLE.
Comments
Post a Comment