Ittichai at 17:28, 15 June 2012

2012-06-15T17:28:14Z

Ittichai: Created page with "== Standard Audit in Oracle Database ==

 AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }      none or false - Auditing is disabled.     db or true - Aud..."

2012-06-15T17:27:28Z

Created page with "== Standard Audit in Oracle Database == <pre> AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended } none or false - Auditing is disabled. db or true - Aud..."

New page

== Standard Audit in Oracle Database ==

<pre>
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

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. (10gR2)
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated. (10gR2)
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;
SQL> SHUTDOWN
SQL> STARTUP
</pre>

* Compare the output between AUDIT_TRAIL=db and db, extended
<pre>
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name,
sql_text
FROM dba_audit_trail
WHERE owner = 'USER1'
ORDER BY timestamp;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAM SQL_TEXT
---------- -------------------------------------- ---------- ---------- ---------- --------------------------------
USER2 15-JUN-12 11.12.15.955356000 AM AMERIC USER1 T_AUDIT SELECT
A/CHICAGO

USER2 15-JUN-12 11.13.04.955356000 AM AMERIC USER1 T_AUDIT SELECT select * from ittichai.t_audit
A/CHICAGO

</pre>

* Audit SYSDBA operations
<pre>
SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.

SQL> show parameter audit_sys_ope

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE

</pre>

All Audits by a User
<pre>
AUDIT ALL BY test_user BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY test_user BY ACCESS;
AUDIT EXECUTE PROCEDURE BY test_user BY ACCESS;

</pre>

* View Audits
Three main views are
<pre>
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.

</pre>

* Sample
<pre>
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

-- Audit query per user owner
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'AUDIT_TEST'
ORDER BY timestamp;

USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00 AUDIT_TEST TEST_TAB CREATE TABLE
AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00 AUDIT_TEST TEST_TAB INSERT
AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00 AUDIT_TEST TEST_TAB UPDATE
AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00 AUDIT_TEST TEST_TAB SELECT
AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00 AUDIT_TEST TEST_TAB DELETE
AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00 AUDIT_TEST TEST_TAB DROP TABLE

6 rows selected.

SQL>

</pre>

* More samples of the audit syntaxes
<pre>
AUDIT ALTER, INDEX, RENAME ON DEFAULT
BY SESSION;
CREATE VIEW jeff.employee AS SELECT * FROM jeff.emp;
AUDIT SESSION BY jward, swilliams;
AUDIT ALTER USER;
AUDIT LOCK TABLE
BY ACCESS
WHENEVER SUCCESSFUL;
AUDIT DELETE ON jeff.emp
BY ACCESS
WHENEVER SUCCESSFUL;

AUDIT SELECT, INSERT, UPDATE, DELETE on ittichai.t_audit
BY ACCESS;

</pre>

* Audit Maintenance
<pre>
AUDIT SELECT, INSERT, UPDATE, DELETE on t_audit by access;

SELECT * FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'USER1' AND OBJECT_NAME LIKE 'T_AUDIT%';

OOWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------ -------------- ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
USER1 T_AUDIT TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-

NOAUDIT DELETE on t_audit;

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME PRIVILEGE SUCCESS FAILURE
------------------- -------------------- --------- ----------
ALTER USER BY SESSION BY SESSION

</pre>

* Turn Off Statement and Privilege Auditing
<pre>
NOAUDIT session;
NOAUDIT session BY jeff, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
EXECUTE PROCEDURE;
</pre>

* Turn off all statement audit options:
<pre>
NOAUDIT ALL;
</pre>

* Turn off all privilege audit options:
<pre>
NOAUDIT ALL PRIVILEGES;
</pre>

* Turn off all object audit options on the emp table, enter the following statement:
<pre>
NOAUDIT ALL
ON emp;
</pre>

* Turn off all default object audit options, enter the following statement:
<pre>
NOAUDIT ALL
ON DEFAULT;
</pre>
* Turn off network auditing:
<pre>
NOAUDIT NETWORK;
</pre>

* List Active Statement Audit Options
<pre>
SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ------------------- ---------- ---------
JWARD SESSION BY SESSION BY SESSION
SWILLIAMS SESSION BY SESSION BY SESSION
LOCK TABLE BY ACCESS NOT SET
</pre>

* View all the privilege audit options that are set:
<pre>
SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME PRIVILEGE SUCCESS FAILURE
------------------- -------------------- --------- ----------
ALTER USER BY SESSION BY SESSION
</pre>

* List Active Object Audit Options for Specific Objects
<pre>
SELECT * FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'USER1' AND OBJECT_NAME LIKE 'T_AUDIT%';

OOWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------ -------------- ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
USER1 T_AUDIT TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
</pre>

Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

A dash (-) indicates that the audit option is not set.

The S character indicates that the audit option is set, BY SESSION.

The A character indicates that the audit option is set, BY ACCESS.

Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by a slash (/).

* List Default Object Audit Options
<pre>
SELECT * FROM ALL_DEF_AUDIT_OPTS;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /- -/-
</pre>

* Query lists audit records generated by statement and object audit options:
<pre>
SELECT * FROM DBA_AUDIT_OBJECT;
</pre>

* List Audit Records for the AUDIT SESSION Option
<pre>
SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD,
LOGOFF_LWRITE, LOGOFF_DLOCK
FROM DBA_AUDIT_SESSION;

USERNAME LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO
---------- --------- ---------- ---------- ---------- ----------
JWARD 02-AUG-91 53 2 24 0
SWILLIAMS 02-AUG-91 3337 256 630 0
</pre>

* Purge the Audit Trail Views
<pre>
SQL> truncate table sys.aud$;
Table truncated.
</pre>

* References
http://www.oracle-base.com/articles/10g/auditing-10gr2.php#fine_grained_auditing
http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm

Standard Audit in Oracle Database - Revision history

Ittichai at 17:28, 15 June 2012

Ittichai: Created page with "== Standard Audit in Oracle Database ==
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended } none or false - Auditing is disabled. db or true - Aud..."

@@ Line 252: / Line 252: @@
 * References
-http://www.oracle-base.com/articles/10g/auditing-10gr2.php#fine_grained_auditing
+** http://www.oracle-base.com/articles/10g/auditing-10gr2.php#fine_grained_auditing
-http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm
+** http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm