Oracle Database Audit using SYSLOG to capture

Oracle Database can be configured to log events into a database table, XML
files or syslog. To configure Oracle Database to log events using syslog:

Configure SYSLOG

1. Execute the following commands:

mkdir -p /var/log/oracledb/
touch /var/log/oracledb/oracledb.log

2. Add the following line to /etc/rsyslog.conf:

local1.info /var/log/oracledb/oracledb.log

3.Configuring Logging on Network Elements

Note: The separator between local1.info and /var/log/oracledb/oracledb.log must be tab, not space.

4. Restart the syslog service. In Linux

service rsyslog stop

service rsyslog start

Configure in FGA audit in DB level if required.

Please enable FGA for particular table

Create a policy on a table & column to be audited

BEGIN
 dbms_fga.add_policy
 (
 object_schema=>'CUST',
 object_name=>'CUST_DETAILS',
 policy_name=>'TEST_AUDIT',
 audit_column => 'PASSPORT',
 statement_types => 'UPDATE, DELETE, SELECT',
 audit_condition => 'PASSPORT IS NOT NULL'
 );
 END;

 

Here we are asking to audit PASSPORT column in CUST_DETAILS  table for SELECT, UPDATE & DELETE statements.

It will not audit operations, where PASSPORT is NULL.

Policy name for this access control is TEST_AUDIT.

STEP 2

Check the policy details from

SQL> select policy_name, object_name, object_schema, policy_text, policy_column from dba_audit_policies
/

STEP 3

Now if anyone uses statement containing PASSPORT column

SQL> select * from cust_details
/

SQL> select PASSPORT from CUST_DETAILS
/

It will be logged in sys.fga_log$ OR dba_fga_audit_trail views

SQL> select timestamp, db_user, os_user,object_schema, object_name,sql_text from dba_fga_audit_trail
/

STEP 4

To delete the audit log

SQL> delete from sys.fga_log$

OR

SQL> delete from dba_fga_audit_trail

STEP 5

To delete the policy

begin
DBMS_FGA.DROP_POLICY
(
object_schema => ‘CUST’,
object_name => ‘CUST_DETAILS’,
policy_name => ‘TEST_AUDIT’
);
end;

DB level parameter change for enabling SYSLOG audit capturing

5. Log in to sqlplus and execute:

SQL> ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE
SCOPE=SPFILE;
SQL> ALTER SYSTEM SET AUDIT_SYSLOG_LEVEL=’local1.info’
SCOPE=SPFILE;

Please note local1.info is same whatever it is there in /etc/rsyslog.conf

The audit_file_dest parameter should be NULL.

5. Restart the Oracle database instance.

6. Log in to the database, execute some arbitrary SQL statements and
verify that /var/ecalaudit/oracledb/oracledb.log is updated
accordingly.

Sample output of syslog:-

Dec 21 22:06:26 localhost Oracle Audit[32231]: LENGTH: “313” SESSIONID:[7] “4902919” ENTRYID:[1] “9” STATEMENT:[2] “35” USERID:[2] “CW” USERHOST:[7] “GSYC622” TERMINAL:[7] “unknown” ACTION:[3] “103” RETURNCODE:[1] “0” OBJ$CREATOR:[2] “CW” OBJ$NAME:[15] “CW_CONFIG_AUDIT” SES$ACTIONS:[16] “——S———” SES$TID:[5] “56505” OS$USERID:[7] “eprppak” DBID:[9] “673878994”