Openedge Database Auditing

Auditing goal can be achieved through below steps:

1. Enabling Audit in a DB.

2. Implementing policies.

3. Generating reports.

ENABLING AUDIT  :

1. Create  a  storage area for auditing tables . May create an area for Index too. Area should be of Type 2 storage essentially . eg.

Add.st :

d "AUDIT_AREA_DATA":70,64;512 ./testdb_70.d1 

d "AUDIT_AREA_IDX":71,64;512 ./testdb_71.d1

2. After creating Area enable auditing for database :

$ proutil testdb -C enableauditing area "AUDIT_AREA_DATA" - indexarea "AUDIT_AREA_IDX" deactivateidx

“OpenEdge Release 10.2B04 as of Thu Mar  3 19:17:16 EST 2011

The 95 audit event records for default system events were loaded successfully. (14812)

The 36 audit event records for encryption audit events were loaded successfully. (14812)

Auditing has been enabled for database testdb. (12479)”

IMPLEMENTING AUDIT POLICY  :

1. Import Policy in local Database.

2. Export implemented Policy in Character Database. 

There are various audit policies predefined for OE database. However we can create our customized policies too. Here in particular to our environment our goal is to enable auditing for DBA activities like 

Dump/load , database object creation, idxbuild etc.

For this purpose we two pre defined policies as below.

App-Schema 

PSC-Db-Admin

A little description is as below :

App-Schema:

Application schema changes. This includes when any of the following is created, updated, or deleted: table, table trigger, table field, table index; or sequence. This also includes when a database is created or updated.

 PSC-Db-Admin:

Database administration tools. This includes when the database is started or stopped; or when any of the following operations are performed on the database: backup, restore, binary dump, binary load, copy, table move, index move, index check, index rebuild, index fix, area truncate, SQL dump, text dump, text load.

Now comes the tool to Activate a policy called “Audit Policy Maintenance”  .

Above tools is for Windows GUI only not for Character Interface. So we need to Enable a policy in our local system ,then export it in a XML format and load it in Character interface through Admin tool.

We will see this further.  

After enabling Audit in our local DB ( Local DB should be a similar copy of Character DB in terms of schema) , we need to import policy from Audit policy maintenance tool . Read  below .

1. Goto “Audit Policy Maintenance “option in “Tools” in Data Dictionary.

2.You will get below window . Go to File and then “Import Policy” .

3. Auditing policies are stored  in $DLC/auditing drive in XML format . Browse there and select   “policies.xml” . 

4. After importing you will get below message box.

5. You can see Different policies being imported as below .

6. You need to select a policy to activate (however when you import policies.xml , all policies in this xml are activated by default . we need  to activate App-Schema and PSC-DB-Admin Policy and deactivate others as below) .

Select one policy ( here  PSC-DB-Admin) .

 If you uncheck “Policy active” check box , it will get deactivated ( after saving changes.).

7. You need to uncheck and rest of the policies and check(if not) two policies in concern .

Now you need to save changes as below from  ”Save record” . 

  

8. Once you save it you can see active policies as below.

Export Policy to character Database:

Goto “Audit Policy Maintenance” tool and select File > Export Policy.

  

           
Browse to directory where you want to generate XML file.

                 


Give a name like “db-policy.xml”  and export by clicking on Open .

The Xml generated needs to be loaded in target Database .

GENERATING REPORTS :

Once Auditing is enabled we need to query audit related tables. Below figure may help us.

Details of audit tables can be found in documents as below .

Table name Description Archived

_aud-audit-data    >>>     This table contains the audit data records. All

events are stored in this table.

_aud-audit-data-value >>>  This table is a child table of the _aud-audit-data

table, and contains records for each field

change data event.

_aud-audit-policy  >>>  This table contains named audit policies. If

multiple policies are active, the aggregation of

the policies is applied, and the highest level of

auditing will be applied if a conflict exists

between policies.

_aud-event  >>>  This table contains the definitions for all

supported OpenEdge and user-defined audit

events and their event ids. All event ids up to

32,000 are reserved. You can create custom

application events with ids greater than 32,000.

_aud-event-policy  >>>  This table contains policy settings for events

associated with policies.

_aud-field-policy >>> This table contains field level auditing settings

associated with a named policy.

_aud-file-policy  >>>  This table contains table level auditing settings

associated with a named policy.