Question Progress Auditing

[Cringer]

Morning all. I've been asked by the powers that be to start a preliminary investigation into the possibilities of using Progress Auditing. We currently have our own self-rolled solution (that we won't be removing at this time), but have new requirements for Auditing on certain tables that wasn't there before. Ideally we'd like to do all our auditing through Progress rather than our own tools, so this seems a good point to look into it.
We are currently on Progress 11.2.1, on a Win Server 2008 box. We use our own user security. So essentially, my first question, as we don't use the _user table, are we precluded from using Progress' auditing capabilities, or can we still use it in some fashion?

 

[RealHeavyDude]

As far as I know most of the auditing needs a user authenticated either against _User or against a security domain ( with the client principal object ). You might want to have a look into the SET-CLIENT and SET-DB-CLIENT methods. Otherwise, how would the database know who the user was?

The auditing also includes AUDIT-CONTROL system handle to allow you to log anything - but that might not be sufficient when it comes to autiding CRUD operations ( which, out of my experience, most auditors are interested in ).

If you don't use the _user ( neither do we ) then I would the client principal a go. I guess you have a central authentication logic which you would need to extend to build the client principal and use it to authenticate against the database.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Thanks RHD. Time to get reading.

 

[Cringer]

Is there a step-by-step guide to using the Client Principal Object somewhere? From setting it up to using it. This is a good starting point - http://pugchallenge.org/slides/CPObjectPUG1.pdf but I'm now tasked with seeing how easy it is to use on a copy of Sports.

 

[RealHeavyDude]

In a nutshell - this is what you need to do:

1. Data Administration: You need to setup authentication systems and domains in the security / domain maintenance menu. The most important part is the access code of the domain. This is the key with which you will seal the client principal so that it is ready to be used to authenticate against the database.
2. ABL: You create the client principal and set its attributes - most prominently the user id and domain name.
3. ABL: You seal the client principal with the seal method provinding the access code for the domain.
4. ABL: You use either the set-client or set-db-client function providing the sealed client principal object to authenticate against the database.
5. ABL: You can now check the userid function to see who is authenticated against the database.

Now, you just need to make sure that you know the access code of the domain you want to authenticate against when you build the client principal:

• You can hard code it but I wouldn't advice this. On the other hand since your db was not secured ( no use of _user ) before, therefore, consequently trusting any ABL code that can connect to it, it might not be a big deal ( audit issue so to speak ... ).
• I build the client principal on the AppServer. The AppServer has access to a password file that contains that secret. That password file is generated during handover of the system to the operator how owns the machine ...

Heavy Regards, RealHeavy Dude.

 

[Cringer]

Thanks RHD.

 

[Rob Fitzpatrick]

Just curious: if you don't use _User and you don't use CP, how do you handle user authentication?

 

[Cringer]

We have a separate db for users and have our self-rolled security stuff.

 

[Rob Fitzpatrick]

I see. I suspect an auditor would say that if your authentication is "probably not very secure" then any audit trail for such an application should be considered compromised from the start. So adding auditing now is probably a waste of effort.

I suggest changing your authentication implementation to use CP. It has been enhanced a fair bit since it was first introduced, though I'm not sure if there were any enhancements after 11.2.1. Once you have good authentication then it makes sense to add OE Auditing.

Note that if you use it you will be wanting to add another DB for audit data archiving. But I suspect your "user" DB will go away once you've moved to CP authentication so it's more or less a wash from a DB admin perspective.

 

[TomBascom]

Rob is being subtle and understated with "you will be wanting to add another DB for audit data archiving". I'll be blunt.

Plan your audit archiving strategy from day 1.

Just letting the audit data flow into the default audit area without ever archiving it and with no plan for its life-cycle will result in an auditing fuster-cluck. You will have a gigantic, unwieldly storage area sitting there becoming a bigger and bigger problem every day. Your backups will become huge and any sort of activity that involves potentially backing up and restoring will quickly become impractical. And if you leave it for too long doing anything about it becomes a nightmare of nibbling away at the audit data trying to archive a little bit here and a little bit there without impacting the users too hard -- nights and weekends will become a distant memory.

Do not even consider going live with auditing unless you have an iron-clad audit archive strategy and process agreed to by the business.

 

[Rob Fitzpatrick]

Yes, in retrospect I was being "understated". Well said, Tom, as usual.

 

[RealHeavyDude]

Tom, Rob: +1

Sometimes I can't see the wood because of all these trees ...

Of course, not only do you need to have an archive strategy in place from day one, you should also carefully design your audit policy: Usually when you ask auditors what it is that needs to be audited, they tell you everything. But everything means that you will generate tons of data and I am pretty sure that there is data in your database that don't needs to be audited. For example, in our case, it turned out that we only need to audit data that is relevant for the financial statement of a client.

Furthermore you should also think about what additional information you would like to have in the audit trail: CRUD is the information everybody comes up with. But when lots of data is changed by batch processes then some information about the type of business transaction that caused all these changes might be very helpful too - maybe the origin of such a business transaction does also make sense too.

Just some thoughts ...

 

[Cringer]

Thanks all for your great input as ever. The archiving strategy is a good point. As ever, the requirement at the moment is to audit a few fields on a small handful of tables. In itself it probably won't be much of a hit on resources. But as is always the case, if it works then others will want to add other things, and so the problem grows. Like I say, we have our own auditing code in the triggers which works reasonably well, and we're not looking to replace that at this time. The time may come though!
I'm using Sports2000 to try and set up a demo of CP and Auditing to prove it's a viable option. It's certainly good practise!

 

[Rob Fitzpatrick]

A couple of other points occur to me, in no particular order.

• Make sure you understand the function of the various database security roles, e.g. audit data archiver, audit data reporter, audit data administrator, security administrator, etc.
• Follow the principle of least privilege: don't configure a user as an audit administrator if, for example, they only need to read audit data. Don't give a user any access to audit data if their role doesn't require it.
  
• Once you decide to pursue OE Auditing, make sure your application designers and developers clearly understand why they are auditing. Who is the intended viewer of the audit data, and what is their role? If it is because of an internal or external regulatory requirement, be sure everyone understands the difference between business data and audit data. Don't mix them. Once some well-intentioned people understand the capabilities of the OE Auditing platform with its before-and-after data snapshots, they may be tempted to use it to track and report on changes in application state: orders, applications, widget production; whatever your application does. That's business data.
  Data captured by OE Auditing resides in protected audit tables and you need special privilege to read them. So if everyone needs to be an audit data reporter just so they can read the new business meta-data you're creating via OEA then they can also see the rest of the audit trail as well, like the information you track about the actions of users. If you audit the actions of users, that information isn't supposed to be visible to those same users; it should be confidential. It's supposed to be visible to auditors, and application auditors are separate from application users. (Or if they aren't, your auditing is a sham.) Remember also that a user who can see what you're tracking about his movements also knows everything that you *aren't* tracking.
• To reiterate RHD's point, you (or someone) will be tempted to audit everything. Don't.
  
• To reiterate Tom's point, don't let your production audit data and index areas grow large. You should transfer data from your production database(s) to your audit archive database on a regular basis; daily, or whatever is appropriate for your application. You do this with proutil auditarchive/proutil auditload. These are also privileged commands and you have to think about how to protect them accordingly (remember that by default, auditarchive *deletes* audit data as it dumps). In our implementation we have an OS user that is configured in the DBs with the audit data archiver role (it is not in _User). It is the only user with this role. A shell script runs from that user's cron and runs auditarchive from the source, runs auditload to the target, then deletes the .abd file (the dump file). If the load fails it archives the file in a directory with appropriate permissions and sends an alert. The power of this role underscores why it is important to protect the audit administrator role as well, as it can grant new roles to users (e.g. create another audit data archiver, which might lead to loss of audit data).
• In terms of performance, auditing isn't free. By definition, you're doing some amount of extra writes. For this reason it is best practice to deactivate your audit indexes in your production DB(s) to minimize the hit. This in turn means you shouldn't be reading the audit data in production. If you need to report on audit data, do it against your audit archive database where the audit indexes are enabled. Remember that if you later do an index rebuild in production, say after a D&L, it re-enables all indexes so you will have to go back and disable them again.
• Auditing will increase your volume of BI/AI notes. Check your BI/AI configuration beforehand, and keep an eye on the relevant performance metrics after you enable OEA.
  
• Some idiot control freak well-intentioned person may say "Hey, there's sensitive data in table X. Now that we use OEA, can we audit *reads* on that table?" (Don't ask me how I know this.) Just say no and walk away. Turning reads into reads/writes, especially when most applications are read-heavy, is a bad bad idea. There are other ways to control access to tables and fields.
  
• Using the application (i.e. creating audit data) and viewing audit data are really two separate activities. Depending on how stringent your requirements are, you may want to consider creating a separate audit data viewer application for the latter activity.

Some OEA resources you may find helpful:

Gus Björklund at BravePoint Virtual Interchange 2009 (a great overview)
slides
video

A Progress web paper on getting started with OEA.

An OEA conversation with Mike Jacobs and Anthony Swindells on "PSDN Radio" (2006).

Mike Jacobs again on OEA for DBAs (2008).

A four-part web paper series (can't find part 2) on integrating auditing and security into your application (includes sample code) (from the 10.1A era):
1: installing using sample code
3: registering trusted domains
4: managing user identities
sample code

Exchange 2009 featured a presentation by JP Mathieu of Lumec Inc. It's a good "from the trenches" overview of implementing OEA. Also the source of the idea for a separate viewer app; his looks pretty slick.
slide deck

"Tales from the Audit Trails", a presentation from PUG Challenge Americas 2013 by John Harlow and Mike Furgal of BravePoint.
slides
audio
audio (version 2)
_________________________

Edit: fixed broken links

 

[Cringer]

That's one awesome post. Thanks Rob. Much to digest!