AUDITING :-
- Oracle stores information that is relevant to auditing in its data dictionary.
Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated . - Audit data can be stored in a database (SYS.AUD$) table or in an OS file residing in AUDIT_FILE_DEST (default is $ORACLE_HOME/rdbms/audit) directory.
- This table is, by default, located in the SYSTEM tablespace. This itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.
- Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file.
- From Oracle 11g, auditing is enabled for some system level privileges.
INIT Parameters:-
SQL> show parameter audit; NAME TYPE VALUE -------------------- ------- --------------------------- audit_file_dest string /oradb/app/oracle/admin/orcl /admin/orcl demo/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_qu integer 1048576
The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the OS, XML and XML_EXTENDED options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.
Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS (no need to run this, if you ran catalog.sql at the time of database creation).
AUDIT_TRAIL can have the following values :
AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}
The following list provides a description of each value:
- NONE or FALSE -> Auditing is disabled. Default until Oracle 10g.
- DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.
- DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.
- XML-> Auditing is enabled, with all audit records stored as XML format OS files.
- XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.
- OS -> Auditing is enabled, with all audit records directed to the operating system’s file specified by AUDIT_FILE_DEST.
SYS.AUD$ table : Make sure that the sys.aud$ table gets purged from time to time as connections and DML activity in the database might come to a stand still if it becomes full.
Three levels of Auditing:-
- Statement level
- Object level
- System/privilege level
Statement level auditing :-
Auditing will be done at statement level.
Statements that can be audited are found in STMT_AUDIT_OPTION_MAP.
SQL> audit table by hari; Audit succeeded. SQL> select * from DBA_STMT_AUDIT_OPTS; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE --------- ---------- ------------ --------- --------- HARI TABLE BY ACCESS BY ACCESS SQL> conn hari/hari; Connected. SQL> create table new(name varchar(20)); Table created. SQL> select USERNAME,TIMESTAMP,ACTION_NAME from dba_audit_trail where USERNAME='HARI'; USERNAME TIMESTAMP ACTION_NAME -------------------- --------- ------------ HARI 12-OCT-18 CREATE TABLE
Object level auditing :-
Auditing will be done at object level.
These objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.
SQL> audit insert on dbwr.testtab;
Audit succeeded.
SQL> insert into dbwr.testtab values('oracle');
1 row created.
SQL> select object_name,object_type,ins,sel from dba_obj_audit_opts where owner='DBWR';
OBJECT_NAME OBJECT_TYPE INS SEL
------------ ----------- ------- ----
TESTTAB TABLE S/S -/-
select username,timestamp,action_name,sys_privilege,priv_used from dba_audit_trail where username='DBWR'
USERNAME TIMESTAMP ACTION_NAME SYS_PRIVILEGE PRIV_USED -------- --------- ----------- ------------- -----------------
DBWR 12-OCT-18 SESSION_REC INSERT ANY TABLE
System/Privilege level auditing :-
Auditing will be done at privilege level.
All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
SQL> audit create any table by hari; Audit succeeded. SQL> select privilege,user_name from dba_priv_audit_opts where user_name='HARI'; PRIVILEGE USER_NAME ---------------------------------------- -------------------- CREATE ANY TABLE HARI SQL> grant dba to hari; Grant succeeded. SQL> create table testtab(name varchar(20)); Table created. select username,timestamp,action_name,sys_privilege,priv_used from dba_audit_trail where username='HARI' USERNAME TIMESTAMP ACTION_NAME SYS_PRIVILEGE PRIV_USED -------- --------- ----------- ------------- ----------------- HARI 12-OCT-18 CREATE TABLE CREATE ANY TABLE HARI 12-OCT-18 CREATE TABLE
FGA (FINE_GRAINED_AUDITING) :-
Fine-grain auditing (FGA) allows us to audit users accessing data of a certain criteria. As per standard auditing we can audit select, insert, update and delete operations. We use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that we can attach handlers (like a trigger) to the policies which can execute procedures.
There are many options that can be applied to the dbms_fga package, here are some simple examples :
Creating the FGA policy say “FGA_AUDIT”:
SQL> begin dbms_fga.add_policy ( object_schema=>'HARI', object_name=>'NEW', policy_name=>'FGA_AUDIT', audit_column=>'name', enable=>true, statement_types=>'select,update'); end; / 2 3 4 5 6 7 8 9 10 PL/SQL procedure successfully completed. SQL> select * from new; NAME -------- Bigdata Now we will perform some "select" and "update" statements to audit : SQL> update new set name='oracle' where name='Bigdata'; 1 row updated. SQL> select * from new; NAME ------ oracle SQL> commit; Commit complete. To audit the table and the statements executed by db_user, execute the below query : SQL> select db_user,scn,sql_text from dba_fga_audit_trail where db_user='HARI'; DB_USER SCN SQL_TEXT -------- ------- ------------------------------------------------- HARI 3756443 update new set name='oracle' where name='Bigdata' HARI 3756456 select * from new
Removing FGA policy :-
begin dbms_fga.drop_policy ( object_schema=>'HARI', object_name=>'NEW', policy_name=>'FGA_AUDIT'); end; /
Enabling FGA policy :-
begin dbms_fga.enable_policy ( object_schema=>'HARI', object_name=>'NEW', policy_name=>'FGA_AUDIT'); end; /
Disabling FGA policy :-
begin dbms_fga.disable_policy ( object_schema=>'HARI', object_name=>'NEW', policy_name=>'FGA_AUDIT'); end; /
Unified Auditing in Oracle Database 12c
Unified audit policy is like a group of audit options with different conditions. It is like a ROLE which is a group of privileges.
For enabling auditing , first need to create a policy with different audit options and then need to enable or disable for all or few users depending upon the requirement.
All the audit records will be stored in unified_audit_trail table. By default 7 audit policies will be present in a 12c database.
This serves as a good mediator for an easy and hassle free switch to the preferred Unified auditing.
2. Pure auditing – Once pure auditing is enabled. We cannot use the traditional auditing methods.
TRUE –> PURE AUDITING
Which unified auditing mode enabled for my database
SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-------
FALSE
How to change from MIXED to PURE auditing:(relink library)
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@orcl:~ orcldemo] cd $ORACLE_HOME/rdbms/lib
[oracle@orcl:lib orcldemo] make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
/usr/bin/ar d /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/libknlopt.a /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/kzaiang.o
chmod 755 /oradb/app/oracle/product/12.2.0.1/db_1/bin
- Linking Oracle
rm -f /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/oracle
SQL> startup;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 956302160 bytes
Database Buffers 637534208 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER='Unified Auditing';
VALUE
------
TRUE
DEFAULT POLICIES IN 12C DATABASE :-
SQL> select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES; POLICY_NAME ----------------------------------------------------------- ORA_CIS_RECOMMENDATIONS ORA_LOGON_FAILURES ORA_RAS_POLICY_MGMT ORA_DATABASE_PARAMETER ORA_DV_AUDPOL2 ORA_RAS_SESSION_MGMT ORA_ACCOUNT_MGMT ORA_DV_AUDPOL ORA_SECURECONFIG 9 rows selected.
But not all are enabled. Query AUDIT_UNIFIED_ENABLED_POLICIES to find, which policies are enabled.
Query AUDIT_UNIFIED_ENABLED_POLICIES to find, which policies are enabled.
SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES; POLICY_NAME ------------------ ORA_LOGON_FAILURES ORA_SECURECONFIG
Query to check the audit options included in a policy:
SQL> select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME='ORA_SECURECONFIG'; AUDIT_OPTION ----------------------------------------------------------------------- LOGMINING TRANSLATE ANY SQL EXEMPT REDACTION POLICY PURGE DBA_RECYCLEBIN ADMINISTER KEY MANAGEMENT DROP ANY SQL TRANSLATION PROFILE ALTER ANY SQL TRANSLATION PROFILE CREATE ANY SQL TRANSLATION PROFILE CREATE SQL TRANSLATION PROFILE CREATE EXTERNAL JOB CREATE ANY JOB AUDIT_OPTION -------------------------------------------------------------------- GRANT ANY OBJECT PRIVILEGE EXEMPT ACCESS POLICY CREATE ANY LIBRARY GRANT ANY PRIVILEGE DROP ANY PROCEDURE ALTER ANY PROCEDURE CREATE ANY PROCEDURE ALTER DATABASE GRANT ANY ROLE DROP PUBLIC SYNONYM CREATE PUBLIC SYNONYM AUDIT_OPTION -------------------------------------------------------------------- DROP ANY TABLE ALTER ANY TABLE CREATE ANY TABLE DROP USER BECOME USER CREATE USER AUDIT SYSTEM ALTER SYSTEM CREATE DATABASE LINK DROP DATABASE LINK ALTER USER AUDIT_OPTION ---------------------------------------------------------------------- CREATE ROLE DROP ROLE SET ROLE CREATE PROFILE DROP PROFILE ALTER PROFILE ALTER ROLE CREATE DIRECTORY DROP DIRECTORY ALTER DATABASE LINK CREATE PLUGGABLE DATABASE AUDIT_OPTION ---------------------------------------------------------------------- ALTER PLUGGABLE DATABASE DROP PLUGGABLE DATABASE EXECUTE EXECUTE 48 rows selected.
Even if no new policy is created in database, Audit action of the above audit options will be recorded in unified_audit_trail.
CASE 1:- (default audit option)
Connect to HARI and drop a directory
SQL> show user;
USER is "HARI"
SQL> drop directory LOG_DIR;
Directory dropped.
SQL> set lines 299
col SQL_TEXT for a23
col action_name for a18
col UNIFIED_AUDIT_POLICIES for a23
select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME='HARI' and EVENT_TIMESTAMP > sysdate -1/24;SQL> SQL> SQL>
SQL>
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
------------------ -------------------- -------------------------- ----------------
DROP DIRECTORY drop directory LOG_DIR ORA_SECURECONFIG 12-OCT-18 07.35.50.694312 PM
CASE 2:- CREATE AUDIT POLICY WITH MULTIPLE AUDIT OPTIONS
SQL> create audit policy test_case2 ACTIONS CREATE TABLE,INSERT ON HARI.NEW,TRUNCATE TABLE, select on HARI.NEW; Audit policy created. SQL> set lines 299 col POLICY_NAME for a23 col AUDIT_OPTION for a12 col AUDIT_CONDITION for a12 col OBJECT_SCHEMA for a23 col OBJECT_NAME for a14 select POLICY_NAME,audit_option,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM AUDIT_UNIFIED_POLICIES where POLICY_NAME='TEST_CASE2';SQL> SQL> SQL> SQL> SQL> SQL> POLICY_NAME AUDIT_OPTION AUDIT_CONDIT OBJECT_SCHEMA OBJECT_NAME ----------------------- ------------ ------------ ------------ TEST_CASE2 CREATE TABLE NONE NONE NONE TEST_CASE2 TRUNCATE TABLE NONE NONE NONE TEST_CASE2 INSERT NONE HARI NEW TEST_CASE2 SELECT NONE HARI NEW SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES where policy_name='TEST_CASE2'; no rows selected
Unless we enable the policy, auditing conditions wont be evaluated
SQL> audit policy TEST_CASE2; Audit succeeded. SQL> create table old (name varchar(20)); Table created. SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME='HARI' and EVENT_TIMESTAMP > sysdate -1/24; ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP ----------- -------- ---------------------- --------------- CREATE TABLE create table old (name varchar(20)) TEST_CASE2 12-OCT-18 07.53.07.542409 PM
SYS AUDITING:-
With mix auditing, sys audit records will be written to both os level and unified_trail also if audit action policy is enable.
FOR MIXED AUDITING:
Administrative user sessions generate SYS audit records. These records are written if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.This process writes the records only to the traditional audit trails. However, when unified audit policies are enabled for administrative users,
these unified audit records are also written to unified audit trail.
FOR PURE UNIFIED AUDITING:
All sys audit records will be written to UNIFIED_AUDIT_TRAIL TABLE ONLY
DROP AUDIT POLICY :-
SQL> DROP AUDIT POLICY TEST_CASE2; DROP AUDIT POLICY TEST_CASE2 * ERROR at line 1: ORA-46361: Audit policy cannot be dropped as it is currently enabled. SQL> NOAUDIT POLICY TEST_CASE2; Noaudit succeeded. SQL> DROP AUDIT POLICY TEST_CASE2; Audit Policy dropped.
PURGE AUDIT TRAIL:
exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( - AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, - LAST_ARCHIVE_TIME => sysdate-30)
Catch Me On:- Hariprasath Rajaram
LinkedIn: https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook: https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
