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

Last updated on JULY 05, 2017

Applies to:

Oracle Rdb Server on OpenVMS - Version 7.2.1 and later
HP OpenVMS VAX
HP OpenVMS Itanium
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/AUDIT. However, the actual data selected is not available as this functionality does not exist in RMU/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/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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms