My Oracle Support Banner

RDBPROD: Logging all access to a database using RMU/SET AUDIT and Logminer (Doc ID 736865.1)

Last updated on JULY 18, 2018

Applies to:

Oracle Rdb Server on OpenVMS - Version 7.2.1 and later
HP OpenVMS Itanium
HP OpenVMS VAX
HP OpenVMS Alpha

Goal

This article describes a way to log every SELECT, INSERT, UPDATE and DELETE to a database. For auditing the SELECTs, the OpenVMS audit journal file is used via the functionality of RMU/SET AUDIT. However, the actual data selected is not available as this functionality does not exist in RMU/SET AUDIT. For INSERT, UPDATE and DELETE, the Rdb Logminer (static or continuous) is used, where the data inserted, updated or deleted is available. For this procedure,  Oracle Rdb 7.2.1 or higher is required, since the username is not available to Rdb Logminer in earlier releases. For older versions of Oracle Rdb, see the procedure described in RDBPROD: How to Track Ownership of All Changes within a Database using Logminer.

Note that actions performed in a stored procedure or stored function may run using a different username than the caller's username. RMU/SET AUDIT can not report the caller's username for this activity. Similarly, SQL/Services services defined with Database Authorization: SERVICE OWNER would be recorded with the service owner's username rather than the caller.

For this procedure, to ensure there is no performance impact on the original database, all logging data is stored in a separate database, which should be located on disk(s) separate from the original database. 

The procedure demonstrated below uses an example database with 2 simple tables.

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 
1. Set up the example database
 1.1 Defining logicals and creating the example database
 
1.2 Create the AIJ's and enable journaling and Logminer.
 
1.3 Configure and enable the auditing of the selects on tables T and T2 in AUDIT_DEMO_DB for users with identifiers [RDBVMS,*].
 2. Set up the audit database
 2.1 Define the AUDIT_DB, in which the audit information will be stored.
 
2.2 Define functions
 
2.3 Define tables
 2.4 Audited table setup
 3. Collecting the audited data in the Audit database AUDIT_DB.
 3.1 Load Audit journal file into AUDIT_DB
 3.2 Backup of the AIJ('s)
 
3.3 Unload the information then load it into the audit database
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.