oracle audit vault

This blog post illustrates the host agent registration and configuring the audit collection for the database. To collect the audit data from the database it is required to install the agent on the database server. Agent binary is available in the DV web console.

Acronyms:

AV: Audit Vault
DF: Database Firewall

After registering the hosts on the Audit Vault Server perform the following steps to be able to collect audit records:

  1. Download the Audit Vault Agent software from the Audit Vault Server console
  2. Deploy the Audit Vault Agent
  3. Activate the Audit Vault Agent
  4. Register one or more targets from which the audit data needs to be collected
  5. Start audit trails using the Audit Vault Server console

 

1. Download Jar File.

 Login to the DV (database vault) console and download the agent jar file. Once the download completes copy this file to the database server.
This is a sample of the log URL.

####### console url
https://192.168.56.20/console/f?p=7700:LOGIN::::::

oracle audit vault 2

 

2. Install Agent 

To install the agent on the database server we need java 1.8, set the java home, and verify the version using the java -version.

Verify the Java version.

chown oracle:oinstall agent.jar
export JAVA_HOME=$ORACLE_HOME/jdk
export PATH=$JAVA_HOME/bin:$PATH

java -version

[oracle@crs01 config]$ java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@crs01 config]$

The next step is to install the agent on the database server.

chown oracle:oinstall agent.jar
export JAVA_HOME=$ORACLE_HOME/jdk
export PATH=$JAVA_HOME/bin:$PATH


[oracle@crs01 dbhome_1]$ java -jar /home/Stage/agent.jar -d /u01/app/oracle/avdf_agent
Agent installed successfully.
If deploying hostmonitor please refer to product documentation for additional installation steps.
[oracle@crs01 dbhome_1]$ 

[oracle@crs01 dbhome_1]$ java -jar /home/Stage/agent.jar -d /u01/app/oracle/avdf_agent
Agent installed successfully.
If deploying hostmonitor please refer to product documentation for additional installation steps.

 

3. Register Agent on the Database Server

Register agent with database vault server we need to obtain the key. This key can be obtained from the database vault console. Register database using ./agentctl start -k (we need to specify -k for insert the key), next restart we do not need -k option.

[oracle@crs01 dbhome_1]$ cd /u01/app/oracle/avdf_agent/bin
[oracle@crs01 bin]$ ./agentctl start -k
Enter Activation Key:
Checking for updates...
Agent is updating. This operation may take a few minutes. Please wait...
Agent updated successfully.
Agent started successfully.
[oracle@crs01 bin]$

Command to monitor agent

oracle audit vault 3 

[oracle@crs01 bin]$ ./agentctl start
Agent started successfully.

[oracle@crs01 bin]$ ./agentctl status
Agent is running.

[oracle@crs01 bin]$ ./agentctl stop
Stopping Agent...

 

4. Verify the Agent Registration

Login to database vault web console and navigate to agent tab. Agent tab verifies the agent status.

This should display in green colour and show status as ‘running’.

oracle audit vault 4 

 

5. Register Target Database

Now next step is to register the target database in the database vault. Navigate the target tab and click the register tab.

oracle audit vault 5

oracle audit vault 6

To configure the target registration we need to perform the below tasks.

  1. Create user
  2. Grant required privileges

 

5.1 User Creation

In this scenario, we are creating a user called AVD Fuser.

create user avdfuser identified by Oracle_4U;

 

5.2 Grant Required Privilege

To complete these grants we need to execute the passing SETUP, SPA and ENTITLEMENT mentioned below: @/u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql avdfuser eg: @/u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql avdfuser SETUP

[oracle@crs01 config]$ echo $ORACLE_PDB_SID
TWHSE_PDB
[oracle@crs01 config]$

[oracle@crs01 config]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 27 14:35:31 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 TWHSE_PDB                      READ WRITE NO
SQL> @/u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql AVDFUSER SETUP

Session altered.

Granting privileges to "AVDFUSER" ... Done.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@crs01 config]$

SQL>  @/u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql avdfuser SPA

Session altered.

Granting privileges to "AVDFUSER" ... Done.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @/u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql avdfuser ENTITLEMENT

Session altered.

Granting privileges to "AVDFUSER" ... Done.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

 

5.3 Verify the Grants

Execute the mention script to validate the grant.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 TWHSE_PDB                      READ WRITE NO

############# check privileges

SQL> select granted_role from dba_role_privs where grantee='AVDFUSER';

GRANTED_ROLE
--------------------------------------------------------------------------------
AUDIT_ADMIN
AUDIT_VIEWER
RESOURCE

SQL> select privilege from dba_sys_privs where grantee='AVDFUSER';

PRIVILEGE
----------------------------------------
AUDIT ANY
AUDIT SYSTEM
CREATE SESSION

 

6. Configure Auditing in the Database

Configure auditing in the database involves two steps:

  1. Create new tablespace auditing
  2. Execute procedures to set the appropriate parameters settings

Let’s verify the current audit settings using the mentioned SQL query before performing any changes.

Current Settings:

set lines 600
col OWNER for a10
col TABLE_NAME for a30
col INTERVAL for a20
select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';
OWNER      TABLE_NAME                     INTERVAL             PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
---------- ------------------------------ -------------------- --------- --------------- ------------------------------
AUDSYS     AUD$UNIFIED                    INTERVAL '1' MONTH   RANGE             1048575 SYSAUX

Create a tablespace to record this auditing data.

SQL> create tablespace avdf_aud_data datafile '/oradata/TWHSE01/TWHSE_PDB/avdf_aud_data01.dbf' size 2048m;

Tablespace created.

Set audit parameter settings for new tablespace.

SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_location_value => 'AVDF_AUD_DATA');
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 1 );
END;
/  2    3    4    5    6



PL/SQL procedure successfully completed.

SQL> SQL> SQL> BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 1,
AUDIT_TRAIL_PURGE_NAME => 'CLEANUP_OS_DB_AUDIT_RECORDS',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

Verification

set lines 600
col owner for a10
col table_name for a30
col interval for a30
select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';


OWNER      TABLE_NAME                     INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
---------- ------------------------------ ------------------------------ --------- --------------- ------------------------------
AUDSYS     AUD$UNIFIED                    INTERVAL '1' MONTH             RANGE             1048575 AVDF_AUD_DATA

 

6.1 Add Audit Collection Data

To collect audit trails from targets, you must deploy the Audit Vault Agent on a standalone host computer which is usually the same computer where the target resides. The Audit Vault Agent includes plug-ins for each target type.

  1. For audit trail collection perform the following:
  2. Register the host
  3. Deploy the Audit Vault Agent
  4. Register the target
  5. Add audit trails for the targets

oracle audit vault 7

Once the database side configurations are complete, configure the audit trail in the web console.

Navigate to target and select audit data collection and add database settings, such as trail type, agent host, etc.

oracle audit vault 8

Adding database settings to the web console.

oracle audit vault 9

After completing the adding step verify using the window below:

oracle audit vault 10