Tuesday, May 12, 2020

Oracle Database Auditing

                                           Oracle Database Auditing

Please note that from 12c  Unified Auditing has been introduced . Hence for 12c database and above  views and functionality will be different as pre defined audit policies were introduced  . 

 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.

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.

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
#########################
Unified Auditing in 12c 
#########################
If you enable the unified audit feature announced with 12c, all audit records will be in the UNIFIED_AUDIT_TRAIL table. Logon failure records will not be in this table because the corresponding policy is not enabled by default. The policy ORA_LOGON_FAILURES must be enabled to audit the Failed Logon attempts.
The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.
When the database is writable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the POSIX file system in the $ORACLE_BASE/audit/$ORACLE_SID directory.
Bug 17466854 – Cannot set Unified Auditing in Standard  Edition (MOS)

Patch 17466854

2 new roles were introduced 

 AUDIT_ADMIN
Manages audit configuration & audit trail

 AUDIT_VIEWER

Analysis audit data

Enable ORA_LOGON_FAILURES to track failed logon attempts . You can enable the ORA_LOGON_FAILURES policy as follows.

SQL> audit policy ORA_LOGON_FAILURES;

To turn off this audit, type the following:

NOAUDIT POLICY hr_emp_select BY dcollins;

Records will then be created. You can see the number of records related to this policy with a query like the one below.

   SELECT audit_type,
         unified_audit_policies,
         action_name,
         return_code,
         COUNT (*)
    FROM unified_audit_trail
   WHERE unified_audit_policies = 'ORA_LOGON_FAILURES'
GROUP BY audit_type,
         unified_audit_policies,
         action_name,
         return_code
ORDER BY COUNT (*);


LOGON success action is audited by unified audit policy ORA_LOGON_FAILURES after executing NOAUDIT & AUDIT command (Doc ID 2305054.1)


SQL> col DBUSERNAME for a10
SQL> col ACTION_NAME for a10
SQL> col UNIFIED_AUDIT_POLICIES for a20
SQL> col RETURN_CODE for 9999999
SQL> select DBUSERNAME,ACTION_NAME,UNIFIED_AUDIT_POLICIES,RETURN_CODE from unified_audit_trail where UNIFIED_AUDIT_POLICIES = 'ORA_LOGON_FAILURES';


To see all the audits captured for the HR user, type

 SELECT EVENT_TIMESTAMP, CLIENT_PROGRAM_NAME, ACTION_NAME,
UNIFIED_AUDIT_POLICIES
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME = 'HR'
ORDER BY EVENT_TIMESTAMP DESC;



To see which users or roles have been enabled to be audited by policies in the database, type
By default 7 audit policies will be present in a 12c database.

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;




 To see what privileges are being audited by default for specific policies 

 SELECT POLICY_NAME, AUDIT_OPTION, AUDIT_OPTION_TYPE
FROM AUDIT_UNIFIED_POLICIES
order by policy_name, AUDIT_OPTION; 
DEFAULT POLICIES IN 12C DATABASE :-

 SQL> select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES;


select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME='ORA_SECURECONFIG';





Manually creating  Audit policy :

 CREATE AUDIT POLICY MY_POLICY
 PRIVILEGES SELECT ANY TABLE
 ACTIONS CREATE TABLE, DROP TABLE;
AUDIT POLICY MY_POLICY BY HR;


CREATE AUDIT POLICY ORA_SELECT  ACTIONS All on owner.table;

CREATE AUDIT POLICY <POLICY_NAME> ACTIONS ALL ONLY TOPLEVEL;

CREATE AUDIT POLICY test_audit_policy
ACTIONS SELECT ON schema.tab1,
SELECT ON schema.tab2,
SELECT ON schema.tab1_VIEW,
SELECT ON schema.tab1_SYNONYM ;

AUDIT POLICY test_audit_policy

AUDIT POLICY <POLICY_NAME>;

AUDIT POLICY <POLICY_NAME> BY <Desired USER>; <<<<< Can add users separated by ','
OR
AUDIT POLICY <POLICY_NAME> except sys,system  <<<<<<< Can add any more.


NOAUDIT POLICY hr_emp_select BY sys,system  ;

NOAUDIT POLICY hr_emp_select ;




How to view audit information with Oracle 12c

 After configuring for and turning on auditing, see what audit data is being collected.

DBA_AUDIT_TRAIL shows all audit entries in the system.

DBA_AUDIT_OBJECT shows all audit entries in the system for objects.

DBA_AUDIT_STATEMENT shows audit entries for the statements GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM.


-- find statements audited in the database:
col user_name for a12 heading "User name"
col audit_option format a30 heading "Audit Option"
set pages 1000
prompt
prompt System auditing options across the system and by user
select user_name,audit_option,success,failure from sys.dba_stmt_audit_opts
order by user_name, proxy_name, audit_option
/



-- Find privileges audited in the database:
col user_name for a12 heading "User name"
col privilege for a30 heading "Privilege"
set pages 1000
prompt
prompt System Privileges audited across system
select user_name,privilege,success,failure from dba_priv_audit_opts
order by user_name, proxy_name, privilege
/


select parameter , value from gv$option where parameter like '%Aud%'; 

select * from dba_audit_mgmt_config_params where parameter_name in ('DB AUDIT TABLESPACE') and audit_trail ='UNIFIED AUDIT TRAIL'; 


SELECT table_name , partition_type , def_tablespace_name ,  interval  from dba_part_tables where table_name ='AUD$UNIFIED'; 


SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name   like 'UNIFIED_AUDIT%'; 

select  * from   DBA_SCHEDULER_JOB_details where job_name   like 'UNIFIED_AUDIT%'; 





Cleaning sysaux 


If the ORA_LOGON_FAILURES policy is enabled, your SYSAUX tablespace can be full in a short time, as too many records will be created. You can learn more about SYSAUX tablespace in the article named “SYSAUX Tablespace Usage”.

 You can create a job to automatically remove old records. The following command can be used to create a job that will delete every 24 hours. The first deletion will take place 24 hours after you run the command.

 BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
   AUDIT_TRAIL_PURGE_INTERVAL  => 24,
   AUDIT_TRAIL_PURGE_NAME      => 'Audit_Trail_Purge_Job',
   USE_LAST_ARCH_TIMESTAMP     => TRUE,
   CONTAINER                   => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/


 The following command can be used to change the job’s schedule.

 BEGIN
 DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
  AUDIT_TRAIL_PURGE_NAME       => 'Audit_Trail_Purge_Job',
  AUDIT_TRAIL_INTERVAL_VALUE   => 48);
END;
/





Mixed Auditing  Vs  Pure Auditing 

 1. Mixed auditing – By default it is enable in 12c. It enables to use both traditional auditing and unified auditing methods. I.e. apart from traditional auditing we can use all the features of unified auditing. Once we are comfortable with the unified concept, we can migrate exiting audit setup to unified policy , we can enable pure auditing.
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.

Which unified auditing mode enabled for my database

 SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

 FALSE – > MIXED AUDTING
TRUE –> PURE AUDITING
How to change from MIXED to PURE auditing:(relink library)

 SQL> shut immediate

 [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;

 SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER='Unified Auditing'






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



#########################
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 :

The FGA method was introduced in the Oracle 9i database release. However, this method provided support for only the SELECT statements.  With the 10g database release, it becomes possible to extend the FGA method to UPDATE, INSERT, and DELETE statements as well


For the FGA method to work, an audit condition is not needed. The database audits all statements that access the object if you do not specify an audit condition. DELETE statements are always audited because all columns are deleted together. It only creates an audit record if a statement manipulates or selects specific rows that meet the FGA policy.


Creating the FGA policy say “FGA_AUDIT”:

begin
dbms_fga.add_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT',
audit_column=>'name',
enable=>true,
statement_types=>'select,update');
end;
/




Removing FGA policy :-

begin
dbms_fga.drop_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT');
end;
/


Enabling FGA policy :-

begin
dbms_fga.enable_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT');
end;
/


Disabling FGA policy :-

begin
dbms_fga.disable_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT');
end;
/



Query  FGA audit 

select db_user,scn,sql_text from dba_fga_audit_trail where db_user='ABDUL';




#########################
Audit Management till 11g 
#########################
Levels of auditing  

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 Abdul;

 Audit succeeded.
SQL> select * from DBA_STMT_AUDIT_OPTS;

 USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS   FAILURE
--------- ---------- ------------ --------- ---------
ABDUL               TABLE        BY ACCESS BY ACCESS
SQL> conn ABDUL/ABDUL;
Connected.

 SQL> create table new(name varchar(20));

 Table created.

 SQL> select USERNAME,TIMESTAMP,ACTION_NAME from dba_audit_trail where USERNAME='ABDUL';

 USERNAME             TIMESTAMP ACTION_NAME
-------------------- --------- ------------
ABDUL                 12-AUG-19 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 ABDUL.testtab;

 Audit succeeded.

 SQL> insert into ABDUL.testtab values('oracle');

 1 row created.
SQL> select object_name,object_type,ins,sel from dba_obj_audit_opts where owner='ABDUL';

 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='ABDUL'

 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 ABDUL;

 Audit succeeded.

 SQL> select privilege,user_name from dba_priv_audit_opts where user_name='ABDUL';

 PRIVILEGE                                USER_NAME
---------------------------------------- --------------------
CREATE ANY TABLE                         ABDUL
SQL> grant dba to ABDUL;

 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='ABDUL'

 USERNAME TIMESTAMP ACTION_NAME SYS_PRIVILEGE PRIV_USED                       
-------- --------- ----------- ------------- -----------------
HARI     12-OCT-19 CREATE TABLE              CREATE ANY TABLE






Audit Failed Logins till 11g 

 The Oracle auditing utility has this command:

 audit create session whenever not successful;

 To enable auditing of failed sign-on attempts:

 1 - Add initialization parameters & bounce instance:

 audit_trail=true

 audit_file_dest='/u01/app/oracle/mysid/mydir/'

 2 - Enable auditing of failed logion attempts as SYSDBA:

 SQL> audit create session whenever not successful;

 3 - You can now view failed login attempts in dba_audit_trail:

 select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0


select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;




Purge Audit records :

 Purging of  audit records can be done either using  traditional purge method or using New Feature DBMS_AUDIT_MGMT 

Purging using traditional Method :
1)  Take  export  backup of  2019  data  that will be delete using below scrpt 

exp '"/ as sysdba"' FILE=cpp_audit_2019-12-31.dmp log=cpp_audit_2019-12-31.log tables=AUD\$ query=\"WHERE TO_CHAR\(NTIMESTAMP\#,\'YYYY\'\) like \'2019\'\" statistics=none


2) Enable  restricted session 

alter system enable restricted session;


2) create  backup  table with  required  data that needs to be retained  and purge  from aud$


select sum(bytes/1024/1024/1024)  from   dba_segments where segment_name like 'AUD$';    

select count(*)  from aud$ where TO_CHAR(NTIMESTAMP# , 'YYYY') like '%2020%'   ;


( Verify tablespace  has enough space before proceeding )

create table temp_aud tablespace AUDIT_EFP as select * from sys.aud$  where TO_CHAR(NTIMESTAMP# , 'YYYY') like '%2020%'; 

select count(*)  from temp_aud  ;
truncate table sys.aud$;

select count(*) from SYS.AUD$;

insert into sys.aud$ select * from temp_aud;

commit ;

select count(*) from SYS.AUD$;


alter index SYS.I_AUD1 rebuild online;
exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5);


 3) disable  restricted session ;

alter system disable restricted session;



 4) drop backup table created .


Drop table temp_aud ;


Purging using  New New Feature DBMS_AUDIT_MGMT

New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information (Doc ID 731908.1)

Manually Purging 

 SELECT * FROM dba_audit_mgmt_config_params;

 sql>BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);   NOTE:- (Set FALSE to purge all records/files)
END;
/

 OR

 sql>BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-5);
END;

Setting auto purge

 COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

 sql>SELECT * FROM dba_audit_mgmt_last_arch_ts;

 sql>BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);     NOTE- (Set TRUE to only purge records/files older than the timestamp specified for the audit trail)
END;
/






Generate script to check enabled audit in database  :



Example for statement auditing :
AUDIT ADMINISTER KEY MANAGEMENT  BY ACCESS ;
AUDIT ALTER ANY PROCEDURE  BY ACCESS ;
AUDIT ALTER ANY SQL TRANSLATION PROFILE  BY ACCESS ;
AUDIT ALTER ANY TABLE  BY ACCESS ;
AUDIT ALTER DATABASE  BY ACCESS ;
AUDIT ALTER PROFILE  BY ACCESS ;
AUDIT ALTER SYSTEM  BY ACCESS ;
AUDIT ALTER USER  BY ACCESS ;
AUDIT AUDIT SYSTEM  BY ACCESS ;
AUDIT BECOME USER  BY ACCESS ;
AUDIT COMMENT TABLE  BY ACCESS ;
AUDIT CONTEXT  BY ACCESS ;
AUDIT CREATE ANY JOB  BY ACCESS ;
AUDIT CREATE ANY LIBRARY  BY ACCESS ;
AUDIT CREATE ANY PROCEDURE  BY ACCESS ;
AUDIT CREATE ANY SQL TRANSLATION PROFILE  BY ACCESS ;
AUDIT CREATE ANY TABLE  BY ACCESS ;
AUDIT CREATE EXTERNAL JOB  BY ACCESS ;
AUDIT CREATE LIBRARY  BY ACCESS ;
AUDIT CREATE PUBLIC DATABASE LINK  BY ACCESS ;
AUDIT CREATE SESSION  BY ACCESS ;
AUDIT CREATE SQL TRANSLATION PROFILE  BY ACCESS ;
AUDIT CREATE USER  BY ACCESS ;
AUDIT DATABASE LINK  BY ACCESS ;
AUDIT DIMENSION  BY ACCESS ;
AUDIT DIRECTORY  BY ACCESS ;
AUDIT DROP ANY PROCEDURE  BY ACCESS ;
AUDIT DROP ANY SQL TRANSLATION PROFILE  BY ACCESS ;
AUDIT DROP ANY TABLE  BY ACCESS ;
AUDIT DROP PROFILE  BY ACCESS ;
AUDIT DROP USER  BY ACCESS ;
AUDIT EXEMPT ACCESS POLICY  BY ACCESS ;
AUDIT EXEMPT REDACTION POLICY  BY ACCESS ;
AUDIT GRANT ANY OBJECT PRIVILEGE  BY ACCESS ;
AUDIT GRANT ANY PRIVILEGE  BY ACCESS ;
AUDIT GRANT ANY ROLE  BY ACCESS ;
AUDIT GRANT DIRECTORY  BY ACCESS ;
AUDIT GRANT PROCEDURE  BY ACCESS ;
AUDIT GRANT SEQUENCE  BY ACCESS ;
AUDIT GRANT TABLE  BY ACCESS ;
AUDIT GRANT TYPE  BY ACCESS ;
AUDIT INDEX  BY ACCESS ;
AUDIT LOCK TABLE  BY ACCESS ;
AUDIT LOGMINING  BY ACCESS ;
AUDIT MATERIALIZED VIEW  BY ACCESS WHENEVER NOT SUCCESSFUL  ;
AUDIT NOT EXISTS  BY ACCESS ;
AUDIT PLUGGABLE DATABASE  BY ACCESS ;
AUDIT PROCEDURE  BY ACCESS ;
AUDIT PROFILE  BY ACCESS ;
AUDIT PUBLIC DATABASE LINK  BY ACCESS ;
AUDIT PUBLIC SYNONYM  BY ACCESS ;
AUDIT PURGE DBA_RECYCLEBIN  BY ACCESS ;
AUDIT RESTRICTED SESSION  BY ACCESS ;
AUDIT ROLE  BY ACCESS ;
AUDIT ROLLBACK SEGMENT  BY ACCESS ;
AUDIT SELECT ANY DICTIONARY  BY ACCESS ;
AUDIT SELECT TABLE  BY ACCESS WHENEVER NOT SUCCESSFUL  ;
AUDIT SEQUENCE  BY ACCESS ;
AUDIT SYNONYM  BY ACCESS ;
AUDIT SYSTEM AUDIT  BY ACCESS ;
AUDIT SYSTEM GRANT  BY ACCESS ;
AUDIT TABLE  BY ACCESS ;
AUDIT TRANSLATE ANY SQL  BY ACCESS ;
AUDIT TRIGGER  BY ACCESS ;
AUDIT TYPE  BY ACCESS ;
AUDIT USER  BY ACCESS ;
AUDIT VIEW  BY ACCESS ;






Reference :

 https://docs.oracle.com/cd/E25054_01/network.1111/e16543/auditing.htm



#########################
Pointing Audit to syslog 

#########################

 Recently we moved our  audit pointing to syslog  since we onboard Splunk in our environment to read  audit . 

 Below was steps  for  pointing audit to syslog and settings from splunk agent side 
References :
How To Set the AUDIT_SYSLOG_LEVEL Parameter? (Doc ID 553225.1)
How To Enable Database Syslog Auditing On Solaris (Doc ID 1450842.1)

SYSLOG Auditing Fails To Write Entries To The File Destination Set In SYSLOG.CONF (Doc ID 1314077.1)



For Syslog :


A)  As  root  user


                1) take backup of /etc/syslog.conf

                2) Modify  /etc/syslog.conf   as under ( note to  use tab and not space)

                                local1.warning /var/log/oracle/oracleaudit.log

                3) the syslog services needs restarting after this file is changed

             svcadm restart svc:/system/system-log:default



B) add below database parameters and  restart database as oracle user

                1) table backup of database spfile


        2) Verify below 2 parameters are set 

alter system set audit_file_dest='/oradata/ora12c/product/admin/test/audit' scope=spfile;
alter system set audit_sys_operations=true scope=spfile;


       update database syslog parameters as  under
                               
                                alter system set audit_trail=os scope=spfile;
                                alter system set enable_ddl_logging=TRUE scope=spfile;
                                alter system set audit_syslog_level='local1.warning' scope=spfile;
                               

exec dbms_audit_mgmt.set_audit_trail_property( audit_trail_type=>dbms_audit_mgmt.audit_trail_os,audit_trail_property=>dbms_audit_mgmt.os_file_max_size,audit_trail_property_value=>50);
                               
exec dbms_audit_mgmt.set_audit_trail_property(audit_trail_type=>dbms_audit_mgmt.audit_trail_os,audit_trail_property=>dbms_audit_mgmt.os_file_max_age,audit_trail_property_value=>1);

                                                Shutdown immediate;
                                                Startup;



For Splunk :


A) as splunk user

    1) Ensure  attached Splunk_TA_oracle  directory  is present under /opt/splunkforwarder/etc/apps/  and permission is  owned by  splunk

    2) /opt/splunkforwarder/etc/apps/Splunk_TA_oracle/local/inputs.conf   file  must have below  format if using audit file monitoring

[monitor:///var/log/oracle/oracleaudit.log]
sourcetype = oracle:audit:text
disabled = false
index = moe
crcSalt = <SOURCE>


   3) Need to ensure "  useACK = true" line is  not present in file    /opt/splunkforwarder/etc/apps/org_all_forwarder_outputs/local/outputs.conf
[tcpout]
defaultGroup = primary_indexers
maxQueueSize = 7MB
#useACK = true
forceTimebasedAutoLB = true
forwardedindex.2.whitelist = (_audit|_introspection|_internal)
#IP of Heavy Forwarder
[tcpout:primary_indexers]
server = XX.XXX.XX.XX:XXXX



4) Restart  splunk services with splunk user and  check if audit files are listed 

/opt/splunkforwarder/bin/splunk restart 
/opt/splunkforwarder/bin/splunk list monitor 


Verification: 

Verifiy  entres are getting written in  /var/log/oracle/oracleaudit.log and get confirmation from splunk  team




Rollback steps  : 

1) restore backup of  /etc/syslog  taken   and restart  syslog services

2) restore backup of database spfile and  restart database





References : 


SCRIPT: Generate AUDIT and NOAUDIT Statements for Current Audit Settings (Doc ID 287436.1)

SRDC - How to Collect Standard Information for Database Auditing (Doc ID 1901113.1)





Posted by at

2 comments:

Subscribe to: Post Comments (Atom)