Moin's DBA Blog: Auditing and its Features in Oracle Database

> Auditing is a method to record the activity of a user. from Oracle 11g auditing is turned on by default.

> Auditing is controlled be initilization parametre AUDIT_TRAIL and by default its set to DB which means the audit trail records are stored in the SYSTEM tablespace in the SYS.AUD$ table.

> If you set to OS means it will send the audit trail records to an operating system file location =audit_file_dest.

> It's possible to audit both successful and failed activities.

> It is advisable to copy the AUD$ table into a separate tablespace from other SYS-owned objects. In some cases, the AUD$ table should be owned by a user other than SYS. There are three reasons for these two statements.

1. If auditing is used, may grow to a very large size, depending on the udit options selected. Since the table will grow and shrink and be high activity, it is advisable to move it from the SYSTEM tablespace to avoid fragmentation.

2. Auditing uses more resources and there will be impact on performance and also it depends largely on the type of auditing you enabled.

3. Setting high level auditing such as connection activity will not have much performance impact as auditing on all SQL statements.

> Auditing can only be performed for users connected directly to the database not to actions for remote database.

When to Enable Auditing :-

1. Excessive deadlocks are encountered.

2. Records are being read, deleted, or changed without authorization.

Parameters :-

SQL> show parameter audit

NAME TYPE VALUE
------------------------------- ----------- ---------------------------------------
audit_file_dest string D:\APP\ORACLE\ADMIN\TESTDB\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB

> Audit_file_dest = Specifies the OS directory where the audit trail is written, when the AUDIT_TRAIL initialization parameter is set to os.

> Audit_sys_operations = AUDIT_SYS_OPERATIONS enables or disables the auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER
privileges. The audit records are written to the operating system's audit trail. The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to XML.

> Audit_trail = By default here you have 'DB' option from 11g which means audit trail records in database. if you keep 'OS' then audit trail files record at os level in Adump.

>>>>>>>> Some Examples of Auditing on DB level <<<<<<<<<

>>> Object Level Auditing <<<

# In this example i am enabling audit on SAM user who owned emp table. whenever sam do insert,update and delete it will be audited as shown below.

SQL> audit select,insert,update,delete on sam.emp by access;
Audit succeeded.

SQL> insert into emp select * from emp;
128 rows created.

SQL> update emp set empno=009 where empno=7788;
8 rows updated.

SQL> delete from emp where empno='7499';
8 rows deleted.

SQL> sho user
USER is "SAM"

SQL> conn / as sysdba
Connected.
Note :-
# We can observe here in below query all the audit_trails are storing in sys.aud$ table which is in system tablespace and audit_trail is default set to "DB" from which we are getting this audit outputs.

SQL> select username,obj_name,action_name from dba_audit_trail where owner='SAM' order by timestamp;

USERNAME OBJ_NAME ACTION_NAME
------------ ------------ --------------------------------------
SAM EMP INSERT
SAM EMP INSERT
SAM EMP INSERT
SAM EMP UPDATE
SAM EMP UPDATE
SAM EMP DELETE
SAM EMP DELETE
SAM EMP DELETE
9 rows selected.

>> >>>>> Privilege Level Auditing <<<<<<<<<<<<

SQL> audit create table by sam;
Audit succeeded.

SQL> create table sam.emp
(
empno number(5),
ename varchar2(5),
job varchar2(5)
);

Table created.

SQL> select username,obj_name,action_name from dba_audit_trail where owner='SAM' order by timestamp;

USERNAME OBJ_NAME ACTION_NAME
------------ ------------ --------------------------------------
SAM EMP CREATE TABLE

1 rows selected.

>>>>> AUDIT ENABLE WITH OS OPTION <<<<<<

SQL> ALTER System set audit_trail=os scope=spfile;

SQL> Shut immediate

SQL> Startup

SQL> show parameter audit;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\APP\ORACLE\ADMIN\TESTDB\ADUMP
audit_sys_operations boolean FALSE
audit_trail string OS

> Now we will create one table to generate the audit trails at OS level.

# In Windows audit trails are recorded in Windows Event Viewer.

SQL> audit create table by sam;
Audit succeeded.

SQL> create table sam.deptnew as select * from scott.emp;
Table created.

I Opened the Event Viewer and Check Whether trails are generating or not.

* You can seee in Action[52] audit trail is generated, sam user has created deptnew table.

> Audit trails generates in OS or DB it will consume space.
you have option to clear the logs and increase the size of Event viewer logs.

1. Right Click the event logs which you want to increase the size.

2. If you want to clear all the logs just click on Clear Logs....

MANAGING SYS.AUD$ :-

> If connections are being audited and the audit trail table (SYS.AUD$) is full, users will not be allowed to log into the database.

> If the audit trail record cannot be written, the audited action will not be executed and errors will be generated. The DBA can CONNECT INTERNAL using Server Manager in either GUI or command-line mode and clean out the audit trail.

> The maximum size allowed for an audit trail written to the database is determined at the time the database is created.By default, the size reflects the system tablespace default values.

> The sql.bsq script,which is executed when the database is created, sets the size of the SYS.AUD$ table.

> We should develop a strategy to cleaned up Audit-Trails on regular or weekily basis from sys.aud$.

PURGING AUDIT RECORDS FORM SYS.AUD$
=====================================

Disabling Audit:-

Noaudit Table;
Noaudit All Privileges;

Turn Off All Auditing:-

Noaudit All;
Noaudit All Privileges;
Noaudit All On Default;

Purge Auditing:-

Delete From Sys.Aud$;
Truncate From Sys.Aud$

Delete From Sys.Aud$ Where Obj$Name='Emp';

Scenario on Deleting SYS.AUD$

> Take the backup of sys.aud$ table before deleting using exp utility.
> In 11G we cannot run expdp to export sys.aud table we have to execute exp option rather than expdp.

C:\Users\oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 16:13:54 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from sys.aud$;
COUNT(*)
------------
50
SQL> EXIT

C:\Users\oracle\audit_exp> exp sytem/manager file=audit_exp tables=sys.aud$

Export: Release 11.2.0.3.0 - Production on Thu Mar 24 11:53:26 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SYS

. . exporting table AUD$ 49 rows exported

Export terminated successfully without warnings.

C:\Users\oracle>sqlplus / as sysdba

SQL> noaudit all;
Noaudit succeeded.

SQL> truncate table sys.aud$;