Question Audit trail table

[matchew]

Hi developers,

I enable the audit trail of progress to my database now I want to have a House Keeping Window for this table I want to delete some records within a given date range of the user.

Now I tried to delete one record this my code

Find First _aud-audit-data No-Lock No-error.
if available _aud-audit-data then
DO:
delete _aud-audit-data.
END.

and error pops up "Insufficient access privilege for table _aud-audit-data 239"

Anyone still awake?

thanks in advance

 

[Rob Fitzpatrick]

If you could delete data programmatically, it wouldn't be a very good audit table. The OE audit tables are protected such that you can't use ordinary ABL logic to do CRUD operations against them (except reads; see below).

I suggest you have a read through of the Auditing documentation, in both the Database Administration manual and the Core Business Services manual. There's a fair bit of material to absorb, and you really need to understand it if you want to avoid making some bad design decisions.

In short, if you want to purge audit data you use the proutil -C auditarchive command. This dumps data from the audit tables to a dump file (.abd) and purges the data. Typically what you would do is keep audit data in the production database(s) only for a short time, and then dump it and load it into an audit archive DB for storage and reporting. Otherwise your audit data areas will grow to an unmanageable size.

The .adb file can be loaded into a DB with the proutil -C auditload command. The audit archive DB can hold audit data from several different production DBs, if required.

In order to run these proutil commands your user ID (_User or OS user) requires the Audit Data Archiver privilege. You can read the data in the audit tables with the ABL, e.g. to do audit reporting; in this case your user ID requires the Audit Data Reporter privilege.

 

[matchew]

Yes that is what I am scared of the table/area of audit trail that will grow in unimaginable size but I am wondering if can I archive programmatically? I just wanted to minimize the space of the audit as much as possible because the program that I am auditing is teller program where there is a transaction of database every minute thanks ^_^

 

[Cringer]

There's a good discussion about Auditing here: http://www.progresstalk.com/threads/progress-auditing.122722/
Loads of links to really useful stuff.
Essentially though, you should be archiving your audit data regularly (dependent on the amount of audit you are generating) to an audit archive db.

 

[matchew]

Cringer some links to the thread are 404 not found zzZzzZzz

 

[Rob Fitzpatrick]

Yes that is what I am scared of the table/area of audit trail that will grow in unimaginable size but I am wondering if can I archive programmatically? I just wanted to minimize the space of the audit as much as possible because the program that I am auditing is teller program where there is a transaction of database every minute thanks ^_^

You can create a program to run these archiving commands and monitor the results. It doesn't have to be a manual process but you do have to use proutil. Just ensure you understand the settings required to do this securely. You will still have the intermediate step of dumping audit data to an .abd file and then loading it into the archive DB. Protect that file appropriately, as at that point it contains the only copy of your audit data from that period (aside from DB backups of course). Protect the file and its directory with appropriate file system permissions to prevent unauthorized access.

Remember that archiving doesn't solve the space utilization issue; it just moves it to a DB other than production. You still need to decide how long you want to keep archived audit data and devise a process to implement your data retention policy.

 

[matchew]

Rob can you give me some link reference for the archiving the audit table?
so that I can have a kick start for studying the intermediate dumping of record form audit trail

 

[Rob Fitzpatrick]

Download the documentation set for your version of OpenEdge (I don't think you have mentioned the version). Go to the Reference section at the end of the Database Administration manual and find the section on the proutil command. The command qualifiers to look at are auditarchive and auditload.

Also, in the Protecting Your Data section there is a chapter on OpenEdge Auditing. Apart from that there are auditing chapters in the Core Business Services manual (this might have been renamed in the 11.x docs). Reading all of that should get you started.

 

[matchew]

I am using 10.1C sorry about not mentioning earlier

 

[Cringer]

Cringer some links to the thread are 404 not found zzZzzZzz

That sucks. Sorry about that. They worked when they were linked to, and in fact the main doc I used to set up our proof of concept of auditing is no longer there. Which is a bit of a problem really because I didn't keep a copy.

 

[Cringer]

You should definitely look at upgrading to 10.2B08 at the very least.

 

[matchew]

grrrrrr I think its impossible for me to upgrade client using 10.1C already

 

[matchew]

they just want to add auditing in their system

 

[Rob Fitzpatrick]

Who are "they"?

 

[Rob Fitzpatrick]

Which links are broken?

 

[Cringer]

Which links are broken?

Strange - the getting started link was broken yesterday. Working fine now.

 

[Rob Fitzpatrick]

I just fixed a few links, although not that one. Maybe it's a good idea to download it now.

 

[Cringer]

Done