There are not many restrictions or requirements for Audit Archive Databases apart from:
- The implicit auditing related aspects (audit enabled, security, audit privilege).
- The audit archive database can only store data from databases that have the same codepage (-cpinternal) and collation (-cpcol)
Other than these, general “large database” maintenance rules are recommended for the audit archive database.
Creating the audit archive database:
A. PROREST or PROCOPY
The simplest method employed.
- Backup and restore or procopy the production auditenabled database.
- Remove all Application Storage Areas, as application schema tables are not needed: PROUTIL -C truncate area
- While the audit data areas could just expand dynamically (with largefiles enabled), about a year’s worth of fixed extents and one variable for some unexpected spike in the amount of audit data collected ensures that this diskspace is reserved for the audit archive database and does not run out of space.
- From this point on all auditarchive (.abd) files can be applied with auditload routines and audit related queries can be run.
B. Create a new audit archive database:
1. Create the Audit Archive database:
$ prostrct create audarc audarc.st –blocksize 8192
$ procopy DLC/empty audarc
$ proutil audarc –C truncate bi –bi 16384 –biblocksize 16
$ proutil audarc –C enablelargefiles
Where:
The structure file describes the Audit Areas considering extent expansion:
b . f 256000
b .
d "Schema Area":6,64;1 .
d "Audit Indexes":13,1;64 .
d "Audit Data":14,128;512 .
2. Enable Auditing
- Enable the Auditing feature the same as the production database auditing was enabled in order to be able to AUDITLOAD.
- In order to properly query audit data, Indexes need to all be active which is not necessarily a requirement on the production copy where audit data are created.
$ proutil audarc -C enableauditing area "Audit Data" indexarea "Audit Indexes"
3. Configure Audit Security
Audit Security should be configured so that only those granted Audit Privileges can access the database, the separation of Database and Auditing Security should already have been considered when production was auditenabled. The exact Security information that needs loading to the audit archive database is entirely dependent on the Security design at each site. These can all be dumped from the production audit database through the Database Administration, Admin, Dump utility. Amongst other, these would be:
- Security Domains: _sec-authentication-system, _sec-authentication-domain
- Security Permissions: _sec-role, _sec-granted-role, _sec-granted-role-condition
- Database Options, User Table Contents.
4. There is no need to load Application Schema tables (.df). There are no references between the audit data tables and any customer tables/records.
5. Load Audit Policies.
Keep only Audit Policies running that keep a complete history to prove that the recorded audit data has not been compromised in the auditdump/load sequence. iow only policies that apply to the 'long term storage' database (the ‘audit’ audit policies that record audit-load, security configuration). These can all be loaded through the Database Administration, Admin, Load, Audit Policies utility after being dumped from production. Amongst other, these would be:
- PSC-Audit
- PSC-Security
- PSC-User
6. The audit archive database is ready for loading archived data from the production audit database(s) and running Audit related queries.
General recommendations, design and planning considerations:
- Setup the Audit Security and Database Security with separation-of-duty design from the beginning.
- Allow limited access to the audit trail through the Audit Security permissions.
- Design the archive plan where after x amount of time auditarchive from the online audit archive database to date-indexed backup offsite storage (again) with the PROUTIL -C auditarchive method using the “date-range” qualifier.
- Design a recovery plan whereby offsite archived audit data can be re-instated in the event audit data that is “years” old needs to be available should an auditor require information that is not in the online audit archive database.
- Have all _aud indexes enabled for better query performance (as opposed to the production database with minimal enabled indexes for performant reasons)
- Structure for a very large size as audit trials can grow rapidly. For example the "Audit Data" and "Audit Indexes" Type II structure could employ larger cluster-sizes than the production database.
- Reconfigure audit data areas and recover the physical disk space associated with audit trails with the new feature introduced in OpenEdge 11.3: "PROUTIL AUDITRECONFIG". Apart from being able to purge all data from existing audit areas, it can also be used to split or combine index and data areas and set new records per block and blocks per cluster values by moving existing audit data and indexes to new Storage Areas. For further information refer to the documentation: OpenEdge Data Management: Database Administration, Auditing, Reconfiguring Audit Areas
- Audit queries can be very large, _aud indexes should be periodically optimised with the usual Database Administration routines eg: IDXBUILD, IDXCOMPACT How to reduce / reclaim index space used by OpenEdge Audit indices after auditarchiver run.
- Assign the auditing indexes area to a lower area number than auditing data area so that IDXBUILD will have to do less work in this configuration.
- Trim the audit policies in the audit archive database to just that which is required to maintain an audit trail over maintaining the audit trail data.
- If the audit trail derives from a multi-tenant database, any auditor can see audit data from any tenant.
- Auditing trails for records changes do not support multi-tenant at the time of writing (OpenEdge 11.6). In other words, those audit records don’t have tenant information.
- The audit archive database should be encrypted if the audit trail comes from a TDE enabled database.
- The audit archive database can be enabled for OpenEdge Replication, but being mostly an audit-trail reporting tool, replication would be for DR considerations. Similarly, PROBKUP and AI strategies could be designed.
- Since OpenEdge Auditing was first introduced in OpenEdge 10.1A, auditing archive files (.abd) are compatible between OpenEdge releases, in other words adb files can be auditarchived from and earlier release and auditload to a later OpenEdge release long term storage database as long as they have the same codepage and collation.
