What is the Database Auditing in Oracle

Hi,

I will explain What is the Database Auditing in Oracle in this article.

 

 

Auditing is the monitoring and collection of some user database actions and record these data in some tables.

You can see what database users are doing in the Oracle Database with Auditing. Actually There are some activities or actions that Oracle Database always audits, whether auditing is enabled or not. These actions are like database startup and shutdown, administrative privilege connections

 

In general, companies need Database auditing in the following cases.

  • Monitoring and recording suspicious activities
  • Determining the responsible persons for the transactions.
  • Identify unauthorized user transactions and report them to the Audit department.
  • Identify problems related to Priviliges and access controls etc.

 

Audit Department can ask you such as following Questions.

For example; Who made the last update, delete in the Salary table?

Customer table was suddenly drop, who did drop it?

Someone truncated Account table in the Finance Database, who did it ?

 

You can find answers for above questions with Oracle Database Auditing.

 

There are 2 types of Auditing in Oracle database as follows.

 

Standart auditing; It can be used for auditing SQL statements, priviliges, schemas, objects and networks.

 

Fine Grained Auditing ; It can be used to monitor specific database activities based on factors such as actions on database tables or when these activities occur.

 

Database Standard Auditing is a feature that is available by default in all database versions.

To use auditing, Firstly activate auditing and then define what will you want to audit. Audit records are saved in the sys.aud$ table or in the operating system file.

 

Auditing is activated by setting AUDIT_TRAIL to a value instead of NONE ( like “DB”, “DB, EXTENDED”, “OS”, “XML”, “XML, EXTENDED”) and then restart the database.

 

  
NONEdisabled
DBEnabled and Audit records are saved into sys.aud$ table.
DB,EXTENDEDit is Like DB option, extra SQL_TEXT and SQL_BIND columns are filled
OSEnabled and Audit records are saved into operating system file as .aud extension ( directory in the AUDIT_FILE_DEST  parameter).
XMLEnabled and Audit records are saved into operating system file as .XML extension ( directory in the AUDIT_FILE_DEST  parameter).
XML,EXTENDEDEnabled and Audit records are saved into operating system file as .XML extension ( directory in the AUDIT_FILE_DEST  parameter) extra SQL_TEXT and SQL_BIND columns are filled

 

 

Standard auditing is the audit of SQL statements, priviliges, schema objects, and activities on the network. Standard auditing is enabled with the AUDIT command and disabled with the NOAUDIT command. Auditing can be done by any user who has AUDIT ANY or AUDIT SYSTEM priviliges.

AUDIT_TRAIL parameter defines how auditing perform and where database stores these records.

You can check the status of the audit_trail parameter as follows

 

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      NONE


Audit _Trail is NONE, so Auditing is not active. This parameter can take different values as shown in the table above. We’ll set it as a DB for trial. Since audit_trail is a static parameter, we need to restart database.

 

[oracle@deveci ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 1 17:09:48 2015

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter system set audit_trail=DB scope=spfile;

 

System altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.



SQL> startup

ORACLE instance started.

 

Total System Global Area  939495424 bytes

Fixed Size                  2258840 bytes

Variable Size             637536360 bytes

Database Buffers          293601280 bytes

Redo Buffers                6098944 bytes

Database mounted.

Database opened.



SQL> show parameter audit_trail;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      DB

 

DB; Audit records are saved in the database sys.aud $ table. In this case, we do not open the Database in read only mode because the audit records will write to the database. When we try to open the error will be as follows.

 

ORA-16006: audit_trail destination incompatible with database open mode.

 

You can set audit_trail parameter as DB with following steps.

  • SQL> alter system set audit_trail=DB scope=spfile;
  • SQL> shu immediate
  • SQL> startup
  • SQL> show parameter audit_trail; ( check it again )

DB, EXTENDED :  Audit records are saved in the database sys.aud $ table. it is Like DB option, extra SQL_TEXT and SQL_BIND columns are filled

 

You can set audit_trail parameter as DB,EXTENDED with following steps.

  • SQL>alter system set audit_trail=db,extended scope=spfile;
  • SQL> shu immediate
  • SQL> startup
  • SQL> show parameter audit_trail;

 

OS: Enabled and Audit records are saved into operating system file as .aud extension.

These .aud extension files are kept under AUDIT_FILE_DEST  directory.

 

You can set audit_trail parameter as OS with following steps.

  • SQL> alter system set audit_trail=OS scope=spfile;
  • SQL> alter system set audit_file_dest=’/.../…./audit’ scope=spfile;
  • SQL> alter system set audit_sys_operation=true scope=spfile;
  • SQL> shu immediate
  • SQL> startup
  • SQL> show parameter audit; ( check it )

 

XML: Enabled and Audit records are saved into operating system file as .XML extension ( directory in the AUDIT_FILE_DEST  parameter).

 

 

You can set audit_trail parameter as XMLwith following steps.

  • SQL>alter system set audit_trail=xml scope=spfile;
  • SQL> shu immediate
  • SQL> startup
  • SQL> show parameter audit_trail; ( kontrol etmek için )

 

XML,EXTENDED: Enabled and Audit records are saved into operating system file as .XML extension ( directory in the AUDIT_FILE_DEST  parameter) extra SQL_TEXT and SQL_BIND columns are filled. You can find these files under ‘$ORACLE_HOME/admin/$ORACLE_SID/adump directory

 

You can set audit_trail parameter as XML,EXTENDED with following steps.

  • SQL>alter system set audit_trail=xml,extended scope=spfile;
  • SQL> shu immediate
  • SQL> startup
  • SQL> show parameter audit_trail; ( kontrol etmek için )

 

AUDIT ve NOAUDIT Command Options:

 

  1. WHENEVER SUCCESSFUL, If this option is used at the end of any audit or noaudit command, the command is audited if it succeeds.
  2. WHENEVER NOT SUCCESSFUL,If this option is used at the end of any audit or noaudit command, the command is audited if it does not succeeds.

 

For example; Following command will audit that if someone drop table and it is successfull.

 

SQL> AUDIT DROP TABLE BY ACCESS WHENEVER SUCCESSFUL;


Or Following command will audit that if DEVECI execute select table and it is successfull.



You can enable auditing for any user for all DML like following.
audit update table, delete table,insert table by DEVECI by access;


If you want to disable auditing for any user, you can use following command.
NOAUDIT session BY DEVECI;



You can enable lots of priviliges auditing like following.

audit select any table by DEVECI by access;
audit update any  table by DEVECI by access;
audit delete any table by DEVECI by access;
audit insert any table by DEVECI by access;
audit grant table by DEVECI by access;
audit grant procedure by DEVECI by access;
audit alter any table by DEVECI by access;
audit alter database by DEVECI by access;
audit alter system by DEVECI by access;
audit GRANT DIRECTORY by DEVECI by access;
audit CREATE DATABASE LINK by DEVECI by access;
audit CREATE any DIRECTORY by DEVECI by access;
audit DROP any DIRECTORY by DEVECI by access;
audit CREATE any INDEX by DEVECI by access;
audit ALTER any INDEX by DEVECI by access;
audit DROP any INDEX by DEVECI by access;
audit CREATE any PROCEDURE by DEVECI by access;
audit DROP any PROCEDURE by DEVECI by access;
audit ALTER any TRIGGER  by DEVECI by access;
audit EXECUTE PROCEDURE by DEVECI by access;

 

 

You can query all Audit Records with following script.

 

select * from dba_audit_trail order by timestamp desc;



You can query and report the object and system based Audit options created with the following scripts.

 

select * from dba_audit_object;

select * from DBA_OBJ_AUDIT_OPTS;

select * from DBA_PRIV_AUDIT_OPTS;

select * from sys.dba_stmt_audit_opts;

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *