Standard Audit in Oracle Database
Jump to navigation
Jump to search
Standard Audit in Oracle Database
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
- Compare the output between AUDIT_TRAIL=db and db, extended
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
- Audit SYSDBA operations
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
All Audits by a User
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;
- View Audits
Three main views are
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.
- Sample
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>
- More samples of the audit syntaxes
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;
- Audit Maintenance
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
- Turn Off Statement and Privilege Auditing
NOAUDIT session;
NOAUDIT session BY jeff, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
EXECUTE PROCEDURE;
- Turn off all statement audit options:
NOAUDIT ALL;
- Turn off all privilege audit options:
NOAUDIT ALL PRIVILEGES;
- Turn off all object audit options on the emp table, enter the following statement:
NOAUDIT ALL ON emp;
- Turn off all default object audit options, enter the following statement:
NOAUDIT ALL ON DEFAULT;
- Turn off network auditing:
NOAUDIT NETWORK;
- List Active Statement Audit Options
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
- View all the privilege audit options that are set:
SELECT * FROM DBA_PRIV_AUDIT_OPTS; USER_NAME PRIVILEGE SUCCESS FAILURE ------------------- -------------------- --------- ---------- ALTER USER BY SESSION BY SESSION
- List Active Object Audit Options for Specific Objects
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 -/- -/- -/- -/- -/- -/-
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
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 -/- -/- -/- -/- /- -/-
- Query lists audit records generated by statement and object audit options:
SELECT * FROM DBA_AUDIT_OBJECT;
- List Audit Records for the AUDIT SESSION Option
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
- Purge the Audit Trail Views
SQL> truncate table sys.aud$; Table truncated.
