With the increasing risk of being hacked, suffering from ransomware attacks, the deployment of and activation of Unified Auditing on databases is highly recommended.
But, when being responsible for a lot of databases, all the Unified Audit data needs to be checked, maintained and where possible archived.
In this blog we will describe the setup of a central repository to be used for unified auditing. This setup is only valid for databases using the multitenant setup, and therefore are running on 12.1 and higher.
Oracle has the DataVault solution available that is also able to collect this kind of information in a central location. With many databases however, a DataVault license can become a costly challenge.
That is why we investigated a manual solution.
The idea of this solution is as in the picture.
All databases have Unified Auditing enabled and send on regular intervals their Audit data to this Central Table in a central database.
What is needed to get this correctly setup:
1) A central database with sufficient free space
2) A user in this database to hold the central repository table
3) A common user in all other databases
4) A database link for this common user, connecting to the central database using the repository user.
5) A procedure to regularly send Audit data to the central repository table.
1) The central database is preferably a Enterprise Edition database with the partitioning option enabled. Collecting Unified Auditing data can make a table grow very fast, especially in a average used environment. Preferably create a seperate PDB for this repository, to prevent other users from being able to read this data.
2) The user in this database needs to have a strong password but no special rights to be able to hold the repository table. CREATE TABLE rights and a tablespace quota are the only rights necessary.
It is recommended to create a partitioned table with the same structure as the CDB_UNIFIED_AUDIT_TRAIL table that resides in the CDB of each database and contains Auditing data for every open PDB. Preferably add two extra columns to this table, for the CDB name and PDB names of the remote databases.
3) In the remote databases create a common user in the root-container. This user is going to collect the Unified Audit data for all PDB's and write this to the central table. It is recommended to send data over per PDB, instead of the whole table at once. Remember that whenever a PDB is closed, the data for that PDB can not be read from this table, so when looping thtough the PDB's you will not miss any data when e.g. a PDB is temporarily down.
What to write to this central repository is depending on what is already there. We use the max(event_timestamp) value found in the destination table to determine what data has to be copied.
The first run will therefore take the most time, as there is no max(event_timestamp) found yet at the destination. A creation script would look like this:
CREATE USER C##AUDIT_AP
IDENTIFIED BY "<some complex password>"
HTTP DIGEST DISABLE
DEFAULT TABLESPACE UNIAUDITD
TEMPORARY TABLESPACE TEMP
PROFILE C##KRG_DB_MGMT
ACCOUNT UNLOCK
;
GRANT CREATE SESSION TO C##AUDIT_AP;
GRANT AUDIT_VIEWER TO C##AUDIT_AP;
ALTER USER C##AUDIT_AP DEFAULT ROLE ALL;
GRANT CREATE DATABASE LINK TO C##AUDIT_AP;
GRANT CREATE PROCEDURE TO C##AUDIT_AP;
GRANT CREATE TABLE TO C##AUDIT_AP;
GRANT SELECT ON CDB_PDBS TO C##AUDIT_AP CONTAINER=ALL;
GRANT SELECT ON CDB_UNIFIED_AUDIT_TRAIL TO C##AUDIT_AP CONTAINER=ALL;
ALTER USER C##AUDIT_AP SET CONTAINER_DATA = ALL FOR CDB_UNIFIED_AUDIT_TRAIL CONTAINER=CURRENT;
ALTER USER C##AUDIT_AP SET CONTAINER_DATA = ALL FOR CDB_PDBS CONTAINER=CURRENT;
ALTER USER C##AUDIT_AP QUOTA UNLIMITED ON UNIAUDITD;
Please note the two lines in bold, that give this user special rights to read these two container tables
4) The common user has its own private database link connecting to the repository database. This makes sure that the repository data is secure from unwanted access by other users.
5) Finally a procedure is needed to regularly copy the audit data over to the repository.
Some remarks on where to pay attention:
- The cdb_unified_audit_trail table differs per database version and even per patchlevel when on 19c. This means that all inserts have to have a complete specication of which colums to take into account.
- Running an inline query in a procedure to get the max(event_timestamp) from the repository for a certain PDB always resulted in a NULL value. We solved this by using an execute immediate, but we were unable to explain the why.
Feel free to send me a message on this topic, if you would like to have a copy of the procedure we have written.
