IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I will explain What is the Oracle Database Auditing in this article.
Oracle Database Auditing
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.
Oracle Auditing Types
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.
| NONE | disabled |
| DB | Enabled and Audit records are saved into sys.aud$ table. |
| DB,EXTENDED | it is Like DB option, extra SQL_TEXT and SQL_BIND columns are filled |
| OS | Enabled and Audit records are saved into operating system file as .aud extension ( directory in the AUDIT_FILE_DEST parameter). |
| XML | Enabled and Audit records are saved into operating system file as .XML extension ( directory in the AUDIT_FILE_DEST parameter). |
| 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 |
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:
- WHENEVER SUCCESSFUL, If this option is used at the end of any audit or noaudit command, the command is audited if it succeeds.
- 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;
Do you want to learn Oracle Database for Beginners, then Click and read the following articles.
Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
