Audit Database

Type of Auditing
1.Standard Auditing
2.FGA (Fine Grained Auditing)
3.SYS ( sysdba or sysoper)


DBA_AUDIT_EXISTS
lists audit trail entries produced by AUDIT NOT EXISTS.

DBA_AUDIT_OBJECT
contains audit trail records for all objects in the system.

DBA_AUDIT_SESSION
lists all audit trail records concerning CONNECT and DISCONNECT.

DBA_AUDIT_STATEMENT
lists audit trail records concerning GRANT, REVOKE,AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database.

DBA_AUDIT_TRAIL or sys.aud$
lists all audit trail entries.

DBA_OBJ_AUDIT_OPTS
describes auditing options on all objects.

DBA_PRIV_AUDIT_OPTS
describes current system privileges being audited across the system and by user.

DBA_STMT_AUDIT_OPTS
describes current system auditing options across the system and by user.
Fine-Grained Auditing

DBA_FGA_AUDIT_TRAIL or sys.fga_log$

Audit Example.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> --add AUDIT_TRIAL=TRUE parameter in INIT.ora file
SQL> create spfile from pfile;

File created.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string TRUE
SQL>

SYS ( sysdba or sysoper) Auditing.
Set AUDIT_SYS_OPERATIONS = TRUE for every successful statement from SYS ( sysdba or sysoper) audited.
All audit records for SYS are written to the operating system file that contains the audit trail.
On Windows
audit records are written as events to the Event Viewer log file

On Solaris
AUDIT_FILE_DEST parameter is not specified, the default location is $ORACLE_HOME/rdbms/audit.
AUDIT_FILE_DEST is not supported in Windows
audit file name (unix) is named after the "session", like ora-20043.aud

1.Statement Auditing
DDL statements. As an example, AUDIT TABLE audits all CREATE and DROP TABLE statements. SQL> truncate table aud$;

Table truncated.

1.audit table by access;
BOTH successful or not successful statement
2.audit table by access whenever successful;
SUCCESSFUL statement only
3.audit table by access whenever not successful;
NOT SUCCESSFUL statement only
4.audit table by SESSION;
Oracle to write a single record for all SQL statements of the same type issued in the same session.
5.audit table by ACCESS;
Oracle to write one record for each access.
6.audit select any table by access;
all statements issued by users with the SELECT ANY TABLE privilege are audited.
7.audit [select any table / table ] by USER/SCHEMA;
specific user/schema audit.
8.audit session;
To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION
9.audit delete any table;
10.audit update any table;
11.audit select table, insert table, delete table, execute prodedure
by access whenever not successful;
12.audit session;
13.audit connect;
Note : there is no DIFFERENCE between CONNECT or SESSION auditing.

Disable Auditing
1.noaudit table;
2.noaudit alter table;
3.noaudit select any table;
4.noaudit session;
Note : don't use BY SESSION / BY ACCESS with noaudit command.
5.NOAUDIT ALL PRIVILEGES;
turns off all privilege audit options
6.NOAUDIT ALL;
turns off all statement audit options
Disable Standard Audit
set AUDIT_TRAIL=false


SQL> audit table by access;
Audit successed.

SQL> conn scott/tiger
Connected.
SQL> create table nolog ( no number);

Table created.

SQL> drop table nolog purge;

Table dropped.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT CREATE TABLE
SCOTT DROP TABLE

SQL> audit alter table by access;

SQL> alter table titi1 add ( name1 varchar2(20));

Table altered.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME ACTION_NAME
------------------------------ ----------------------------
SCOTT CREATE TABLE
SCOTT DROP TABLE
SCOTT ALTER TABLE
SCOTT ALTER TABLE
DML statements. As an example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view.
SQL> audit select table by access;

Audit succeeded.

SQL> conn scott/tiger;
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
14

1 row selected.

SQL> select count(*) titi1;
select count(*) titi1
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> column obj_name format a20
SQL> select username, action_name,obj_name from dba_audit_trail;
USERNAME ACTION_NAME OBJ_NAME
------------------------------ ---------------------------- --------------------
SCOTT SELECT EMP


Purging Audit Records from the Audit Trail
conn sys as sysdba
password :

Delete from SYS.AUD$;
delete all audit records

Deleting the Audit Trail Views
If auditing is DISABLE and no longer need then audit trail views.
1.conn SYS user
2.run CATNOAUD.SQL scripts

Trace Error Number.

SQL> select returncode from dba_audit_trail where rownum <= 1 and returncode <>
0;

RETURNCODE
----------
2004

1 row selected.

SQL> exec dbms_output.put_line ( sqlerrm(-2004));
ORA-02004: security violation

PL/SQL procedure successfully completed.

what are all the audit options enabled?
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS

Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.

SQL> select count(*) from STMT_AUDIT_OPTION_MAP;

COUNT(*)
----------
183