Real time Auditing in Oracle Database 11gR2
Posted by Mir Sayeed Hassan on December 23rd, 2017
Real time Auditing in Oracle Database 11gR2
There are major 4 types of auditing:
1 – Statement level auditing
2 – Object level auditing
3 – Privilege level auditing
4 – Fine granined auditing
In General:
The AUDIT_TRAIL parameter can take the following values.
- NONE/FALSE– Turns off auditing. This was default value in 10g and earlier versions
- DB/TRUE– Default in 11g. Audit records will be stored in AUD$ table.
- OS– Enables auditing, to write audit records to OS level files.
- DB_EXTENDED– Collects SQLBIND and SQLTEXT CLOB info in the AUD$ table.
- XML– Will store auditing information in the form of XML document.
- EXTENDED– Similar to XML but will also store the values of SQLBIND and SQLTEXT
====================
1 – Statement level auditing
====================
Statement: Audit all action at any type of objects.
We can enable auditing on different statements issued by different users. We will have to enable auditing for all statements for which we need to maintain audit data or we can simply enable it for all statements as per your requirement
Syntax:
AUDIT sql_statement_clause BY {SESSION | ACCESS} WHENEVER [NOT] SUCCESSFUL;
- sql_statement_clause : will contain the statement that we want to audit
- Session : one audited entry will be taken for each session
- Access: each & every statement will be audited
- Whenever successful : auditing for every successful execution
- Whenever not successful : auditing for every unsuccessful execution
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/prim/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB --------------Default set in Oracle 11g
Consider the below example as oracle to log every entry of “Mir” user doing something init,
SQL> create user mir identified by mir; User created.
SQL> grant connect,resource to mir; Grant succeeded.
SQL> connect mir/mir; Connected
SQL> create table audit_test(eno number(10),name varchar2(20)); Table created.
SQL> insert into audit_test values(1,'has'); ---- 1 row created.
SQL> select * from audit_test; ENO NAME ---------- -------------------- 1 has 2 sam 3 nar 4 sal
SQL> audit table by mir; Audit succeeded.
Try to perform some test operation:
SQL> conn mir/mir Connected.
SQL> create table audit_new(eno number(10),name varchar2(20)); Table created.
SQL> alter table audit_new add address varchar2(20); Table altered.
SQL> insert into audit_new values(1,'asd','af'); 1 row created.
SQL> truncate table audit_new; Table truncated.
SQL> drop table audit_new; Table dropped.
SQL> commit; Commit complete.
SQL> sho user USER is "MIR"
SQL> conn sys/sys as sysdba Connected.
Note:
Now if “MIR” tries to create, drop or alter any table a record of his actions will be maintained in AUD$ table.
Query to view:
SQL> select * from DBA_STMT_AUDIT_OPTS where user_name='MIR'; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ----------------------------------------------- MIR TABLE BY ACCESS BY ACCESS
Query to view:
SQL> select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp, obj_name, action_name, sql_text from dba_audit_trail where username = 'MIR'; USERNAME TIMESTAMP OBJ_NAME ACTION_NAME SQL_TEXT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MIR 12/17/17 15:48 NEW_AFT DROP TABLE MIR 12/17/17 15:48 LOGOFF MIR 12/17/17 15:48 AUDIT_ACTIONS SELECT MIR 12/17/17 15:48 DBA_AUDIT_TRAIL SELECT ---------------more similar lines etc
==================
2 – Object Level Auditing
====================
The below example i am enabling audit on “MIR” user who owned “aud_mir” table, therefore whenever “mir” do insert,update and delete it will be audited in database
Consider sample new user:
SQL> create table aud_mir(eno number(20),name varchar2(20)); Table created.
SQL> insert into aud_mir values(1,'sd'); -- continue few more 1 row created.
SQL> select * from aud_mir; ENO NAME ---------- -------------------- 1 sd 2 asf 3 afg 4 jkg
SQL> audit select,insert,update,delete on mir.aud_mir by access; Audit succeeded.
Now perform some more insert,delete,update etc
SQL> conn mir/mir Connected.
SQL> insert into aud_mir values(5,'afteraudenb'); 1 row created.
SQL> insert into aud_mir values(6,'aud_rec'); 1 row created.
SQL> commit; Commit complete.
SQL> select * from aud_mir; ENO NAME ---------- -------------------- 1 sd 2 asf 3 afg 4 jkg 5 afteraudenb 6 aud_rec 6 rows selected.
SQL> update aud_mir set name='audit_record' where eno=6; 1 row updated.
SQL> delete from aud_mir where eno=6; 1 row deleted.
SQL> commit; Commit complete.
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from Dba_Obj_Audit_Opts where owner='MIR'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ----------------------- MIR AUD_MIR TABLE MIR BIN$YIicexLaDJ3gVQJQVrJeIg==$0 TABLE MIR BIN$YIlyPtw/FMPgVQJQVrJeIg==$0 TABLE
SQL> select * from Dba_Obj_Audit_Opts where owner='MIR'; OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF ------------------------------ ------------------------------ ----------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- EXE CRE REA WRI FBK --------- --------- --------- --------- --------- MIR AUD_MIR TABLE S/S -/- -/- A/A S/S -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/- MIR BIN$YIicexLaDJ3gVQJQVrJeIg==$0 TABLE S/S -/- -/- S/S S/S -/- S/S -/- -/- S/S S/S -/- -/- -/- -/- -/- -/- MIR BIN$YIlyPtw/FMPgVQJQVrJeIg==$0 TABLE S/S -/- -/- S/S S/S -/- S/S -/- -/- S/S S/S -/- -/- -/- -/- -/- -/-
Sql> desc dba_audit_object; --------------- To view the complete details about the objects you require
Few are:
SQL> select OWNER,OBJ_NAME,action_name from Dba_Audit_Object where OBJ_NAME='AUD_MIR'; OWNER OBJ_NAME ACTION_NAME ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --- MIR AUD_MIR CREATE TABLE MIR AUD_MIR SESSION REC MIR AUD_MIR SESSION REC MIR AUD_MIR SESSION REC MIR AUD_MIR SESSION REC MIR AUD_MIR SELECT MIR AUD_MIR INSERT MIR AUD_MIR INSERT MIR AUD_MIR SELECT MIR AUD_MIR UPDATE MIR AUD_MIR DELETE 11 rows selected.
Hence you can enable the object level auditing by referring the above doc for your reference.
=====================
3 – Privilege Level Auditing
=====================
Enables you to audit the use of powerful system privileges that enable corresponding actions, such as AUDIT CREATE TABLE, Privilege auditing is more focused than statement auditing, which audits only a particular type of action. You can set privilege auditing to audit a selected user or every user in the database.
All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
SQL> desc SYSTEM_PRIVILEGE_MAP; Name Null? Type ----------------------------------------- -------- ---------------------------- PRIVILEGE NOT NULL NUMBER NAME NOT NULL VARCHAR2(40) PROPERTY NOT NULL NUMBER
SQL> select * from SYSTEM_PRIVILEGE_MAP; ------- This gives the complete details of the priv list or refer the below link :
http://www.dba-oracle.com/aud_privilege_auditing.htm
Some are example:
SQL> conn sys/sys as sysdba Connected
SQL> audit create table by mir; Audit succeeded.
SQL> audit delete table by mir; Audit succeeded.
SQL> audit alter user by mir; Audit succeeded.
SQL> audit drop any table by mir; Audit succeeded.
SQL> audit create tablespace by mir; Audit succeeded.
SQL> audit create procedure by mir; Audit succeeded.
SQL> audit alter any table by mir; Audit succeeded.
SQL> audit drop user by mir; Audit succeeded.
SQL> conn mir/mir; Connected
SQL> create table AUD_create(eno number(10)); Table created.
SQL> create table AUD_create2(eno number(10)); Table created.
SQL> commit; Commit complete
Or SQL> drop table AUD_CREATE2; Table dropped.
Or
SQL> alter table AUD_CREATE add ename varchar2(20); Table altered.
SQL> select username, obj_name,action_name from dba_audit_trail where owner='MIR' order by timestamp; USERNAME OBJ_NAME ACTION_NAME EXTENDED_TIMESTAMP ------------------------------ ------------------------------------------------------------------------------------------------------------- ---------------------------- MIR AUD_CREATE CREATE TABLE 17-DEC-17 04.32.37.842423 PM +03:30 MIR AUD_CREATE2 CREATE TABLE 17-DEC-17 04.32.49.746559 PM +03:30
As you can see – the table you are created are audited with the privilege level of auditing
==========================
Important Note on SYS.AUD$ :-
========================
– All the connections are being audited in the audit trail table (SYS.AUD$) by default in oracle database 11g, If this table us full then the database will be hung & it will not be allowed to log into the database until you release the size by clearing the audit files at OS Level
– The default tablespace used for auditing is reflect on system tablespace, we need to have the sufficient space on this tbs to perform the audit operation.
– We should perform a strategy to clean up the existing Audit-Trails on regular or weekly basis from sys.aud$.
===============================
AUDIT By Enable OS, DB_EXTENDED
===============================
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/prim/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB
SQL> alter system set audit_sys_operations=TRUE scope=SPFILE; System altered.
SQL> alter system set audit_trail=db_extended scope=SPFILE; System altered.
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> startup ORACLE instance started. Total System Global Area 2455228416 bytes Fixed Size 2255712 bytes Variable Size 771753120 bytes Database Buffers 1660944384 bytes Redo Buffers 20275200 bytes Database mounted. Database opened.
SQL> conn mir/mir; Connected.
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ TEST2 TEST1
SQL> conn sys/sys as sysdba Connected.
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/prim/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB_EXTENDED
SQL> audit delete,alter,rename on mir.test1 by access; Audit succeeded.
SQL> connect mir/mir; Connected
SQL> create table audit_test(id number); Table created.
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ TEST2 AUDIT_TEST TEST1
SQL> alter table test1 add address varchar2(20); Table altered.
SQL> alter table test1 rename column name to ename; Table altered.
SQL> alter table test1 drop column eno; Table altered.
SQL> select * from test1; ENAME ADDRESS -------------------- -------------------- mir sayeed mir sayeed
SQL> select USERNAME,TERMINAL,OWNER,OBJ_NAME,SQL_TEXT from user_audit_trail where OBJ_NAME='TEST1'; MIR pts/1 MIR TEST1 select * from test1 MIR pts/1 MIR TEST1 alter table test1 add column address varrchar2(20) MIR pts/1 MIR TEST1 alter table test1 add address varchcar2(20) MIR pts/1 MIR TEST1 alter table test1 rename column name to ename MIR pts/1 MIR TEST1 ater table test1 drop column eno MIR pts/1 MIR TEST1 select * from test1 6 rows selected.
==============
View Audit Trail
==============
The audit trail is stored in the base table SYS.AUD$ & its contents can be viewed in the following views with the dba level or user level. DBA_AUDIT_TRAIL/USER_AUDIT_TRAIL
· DBA_OBJ_AUDIT_OPTS / “Same for All” · DBA_PRIV_AUDIT_OPTS · DBA_STMT_AUDIT_OPTS · DBA_AUDIT_EXISTS · DBA_AUDIT_OBJECT · DBA_AUDIT_SESSION · DBA_AUDIT_STATEMENT · AUDIT_ACTIONS · DBA_AUDIT_POLICIES · DBA_AUDIT_POLICY_COLUMNS · DBA_COMMON_AUDIT_TRAIL · DBA_FGA_AUDIT_TRAIL (FGA_LOG$) · DBA_REPAUDIT_ATTRIBUTE · DBA_REPAUDIT_COLUMN
===============
Disabling Auditing
===============
The NOAUDIT statement turns off the various audit options of Oracle Database.
SQL> NOAUDIT; SQL> NOAUDIT session; SQL> NOAUDIT session BY mir,aud_test; SQL> NOAUDIT DELETE ON aud_test; SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE; SQL> NOAUDIT ALL; SQL> NOAUDIT ALL PRIVILEGES; SQL> NOAUDIT ALL ON DEFAULT;
==========Hence tested & Verified in our test db=========