ORACLE AUDIT

Table of Contents

• 1 Init params
  • 1.1 AUDIT_TRAIL
• 2 Turn on
• 3 VIEWS for SYS.AUD$
• 4 Options
  • 4.1 default audit options
  • 4.2 ALL
  • 4.3 ADDITIONAL
  • 4.4 Objects available to audit
• 5 Truncate audit table
  • 5.1 DBMS_AUDIT_MGMT

1 Init params

initoption	values	desc
AUDIT_TRAIL	NONE/DB/OS/DB,EXTENDED/XML/XML,EXTENDED	turn on audit and set type
AUDIT_FILE_DEST		set directory for audit files when AUDIT_TRAIL=OS, default: $ORACLE_HOME/rdbms/audit
AUDIT_SYS_OPERATIONS	TRUE/FALSE	for sys operations

1.1 AUDIT_TRAIL

• in 11g audyt is set default on DB value

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.

2 Turn on

• set param audit_trail

Commands:

AUDIT	turn on audit
NOAUDIT ALL	turn off all audit operations for current user
NOAUDIT ALL BY [username]
NOAUDIT SELECT TABLE BY [username]
AUDIT ALL BY [username] BY ACCESS/SESSION	ACCESS - log everytime the event heppen, SESSION - log only at first time
NOAUDIT TABLE BY [username]
AUDIT select table, insert table, delete table, update table BY [username] BY ACCESS

Views:

STMT_AUDIT_OPTION_MAP	Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
AUDIT_ACTIONS	Contains descriptions for audit trail action type codes
ALL_DEF_AUDIT_OPTS	Contains default object-auditing options that will be applied when objects are created

3 VIEWS for SYS.AUD$

DBA_STMT_AUDIT_OPTS	show running audits for user
DBA_PRIV_AUDIT_OPTS	Describes current system privileges being audited across the system and by user
DBA_OBJ_AUDIT_OPTS	Describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
DBA_AUDIT_TRAIL	Lists all audit trail entries USER view shows audit trail entries relating to current user.
DBA_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user
DBA_AUDIT_EXISTS	Lists audit trail entries produced BY AUDIT NOT EXISTS
DBA_AUDIT_SESSION	Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
DBA_AUDIT_OBJECT	Contains audit trail records for all objects in the database. USER view lists audit trail records for statements concerning objects that are accessible to the current user.

• DDL (CREATE, ALTER & DROP of objects)
• DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
• SYSTEM EVENTS (LOGON, LOGOFF etc.)
• SELECT * FROM dba_stmt_audit_opts ORDER BY 1,3;

col obj_name format a30
col owner format a15
col username format a15
SELECT owner,username,obj_name,action_name,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') FROM dba_audit_trail 
  WHERE timestamp >= trunc(sysdate-1) and username='CCI' order by timestamp;
prompt ###zajetosc_tabeli_audytu
SELECT sum(bytes)/1024/1024 as MB FROM dba_segments WHERE segment_name='AUD$';

4 Options

4.1 default audit options

• rdbms/admin/secconf.sql
• rdbms/admin/undoaud.sql #wylaczenie

4.2 ALL

Object	SQL Statements and Operations Audited
ALTER SYSTEM	ALTER SYSTEM
CLUSTER	CREATE, ALTER, DROP, TRUNCATE
CONTEXT	CREATE, DROP
DATABASE LINK	CREATE, ALTER, ALTER PUBLIC DATABASE LINK, DROP DATABASE LINK
DIMENSION	CREATE, ALTER, DROP
DIRECTORY	CREATE, DROP
INDEX	CREATE INDEX, ALTER, ANALYZE INDEX, DROP
MATERIALIZED VIEW	CREATE, ALTER, DROP
NOT EXISTS	All SQL statements that fail because a specified object does not exist.
OUTLINE	CREATE, ALTER, DROP
PROCEDURE	CREATE FUNCTION, CREATE LIBRARY, CREATE PACKAGE, CREATE PACKAGE BODY
	CREATE PROCEDURE, DROP FUNCTION, DROP LIBRARY, DROP PACKAGE, DROP PROCEDURE
PROFILE	CREATE, ALTER, DROP
PUBLIC DATABASE LINK	CREATE, DROP
PUBLIC SYNONYM	CREATE, DROP
ROLE	CREATE, ALTER, DROP, SET
ROLLBACK SEGMENT	CREATE, ALTER, DROP
SEQUENCE	CREATE, DROP
SESSION	Logons
SYNONYM	CREATE, DROP
SYSTEM AUDIT	AUDIT sql_statements, NOAUDIT sql_statements
SYSTEM GRANT	GRANT system_privileges_and_roles, REVOKE system_privileges_and_roles
TABLE	CREATE, DROP,TRUNCATE TABLE
TABLESPACE	CREATE, TABLESPACE, ALTER, DROP
TRIGGER	CREATE, ALTER with ENABLE and DISABLE clauses, DROP, ALTER TABLE with ENABLE ALL TRIGGERS clause and DISABLE ALL TRIGGERS clause
TYPE	CREATE, CREATE TYPE BODY,ALTER,DROP,DROP TYPE BODY
USER	CREATE, ALTER, DROP
VIEW	CREATE, DROP

Notes:

• AUDIT USER #audits three SQL statements: CREATE, ALTER, DROP Use AUDIT ALTER USER to audit statements that require the ALTER USER system privilege. An AUDIT ALTER USER statement does not audit a user changing his or her own password, as this activity does not require the ALTER USER system privilege.

4.3 ADDITIONAL

ALTER SEQUENCE	ALTER SEQUENCE
ALTER TABLE	ALTER TABLE
COMMENT TABLE	COMMENT ON TABLE table, view, materialized view,COMMENT ON COLUMN table.column, view.column, materialized view.column
DELETE TABLE	DELETE FROM table, view
EXECUTE PROCEDURE	CALL
	Execution of any procedure or function or access to any variable, library, or cursor inside a package.
GRANT DIRECTORY	GRANT privilege ON directory,REVOKE privilege ON directory
GRANT PROCEDURE	GRANT privilege ON procedure, function, package,REVOKE privilege ON procedure, function, package
GRANT SEQUENCE	GRANT privilege ON sequence,REVOKE privilege ON sequence
GRANT TABLE	GRANT privilege ON table, view, materialized view,REVOKE privilege ON table, view, materialized view
GRANT TYPE	GRANT privilege ON TYPE,REVOKE privilege ON TYPE
INSERT TABLE	INSERT INTO table, view
LOCK TABLE	LOCK TABLE table, view
SELECT SEQUENCE	Any statement containing sequence.CURRVAL or sequence.NEXTVAL
SELECT TABLE	SELECT FROM table, view, materialized view
UPDATE TABLE	UPDATE table, view

4.4 Objects available to audit

Object	SQL Operations
Table	ALTER, AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE
View	AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INSERT, LOCK, RENAME, SELECT, UPDATE
Sequence	ALTER, AUDIT, GRANT, SELECT
Procedure, Function, Package	AUDIT, EXECUTE,GRANT
Materialized View	ALTER, AUDIT, COMMENT, DELETE, INDEX, INSERT, LOCK, SELECT, UPDATE
Mining Model	AUDIT, COMMENT, GRANT, RENAME, SELECT
Directory	AUDIT, GRANT, READ
Library	EXECUTE, GRANT
Object Type	ALTER, AUDIT, GRANT

5 Truncate audit table

• truncate table SYS.AUD$;

5.1 DBMS_AUDIT_MGMT

• DBA_AUDIT_MGMT_CONFIG_PARAMS;