Question Suggestions on Implementing an Audit Log (1 Viewer)

[ytene]

I think I should probably begin this post with a brief apology – whilst what follows will inevitably be influenced by the functionality of VBA, this is more of a generic challenge rather than being platform specific.

I am currently writing some modular functionality that I plan to use as a skeletal structure for a couple of applications that I’ve been asked to write. The skeleton essentially consists of a small number of core features:-

1. A dynamic menu-management system
2. A role-based access control system [geared around activating/deactivating menu options]
3. An integrated user authentication mechanism

I would also like to add a generic, general-purpose application event log capability, such that it will be trivially easy to add a log record to a centralised archive – and of course to develop a simple front-end that would allow an administrator or auditor to trace activities on the system.

Having brain-stormed this as an idea, I’ve come up with two general approaches – but I am not sure whether either technique would be more [or less] appropriate to attempt in VBA.

1. To have a general-purpose table with a scant handful of fields, but then a “BLOB” field in to which I could load a serialized copy of a data object (i.e. before and/or after it is changed).
2. To have a more structured table with a few more fields, then create an “event type” table and require each logged event to have a dedicated record in this event type table, which I can refer to via it’s key field.

I’ve had a brief look around to see what VBA offers in the way of serialization features and found this (un-tested) reference,

http://www.vb-helper.com/howto_net_serialize.html

which strongly suggests that this approach is technical possibly. But being technically possible and being a good idea are two very different things!

This question/decision struck me as something both a little more subtle and a little more profound that asking, “Is there a cool way to serialize objects in VBA?”

In terms of requirements, I would suggest that [in decreasing order of importance] my goals would be: -

1. Extensible framework – easily expanded to include new event types
2. Efficient in performance terms [write time, disc storage]
3. Easy to invoke – in a perfect world it would be a single line of code
4. Easy to code the core functionality
5. Perhaps able to provide an “undo” feature [if I code in “before” images]

I did perform a search in these forums, looking through many of the “audit” related posts, but I didn’t find anything that discussed this particular question. Apologies if I’ve missed anything relevant.

I would be very grateful for any feedback, suggestions or thoughts on the relative merits of these or other approaches. If anyone is aware of best practices or sample solutions I should consider, that would be very welcome too.

Thank you, in advance, for your consideration.

 

[CJ_London]

there are plenty of examples about - take a look in the code repository/sample databases of this forum for example. or take a look at this link which is specific to access

https://nortonsafe.search.ask.com/w...95-2E5E-4A36-A86A-77F8C83F2436&doi=2016-09-01

Also consider history files.

not clear what you mean by 'serialize objects in VBA' so can't help with that

 

[isladogs]

there is a lot in this post & it is likely to get a wide variety of responses

I think I should probably begin this post with a brief apology – whilst what follows will inevitably be influenced by the functionality of VBA, this is more of a generic challenge rather than being platform specific.

I am currently writing some modular functionality that I plan to use as a skeletal structure for a couple of applications that I’ve been asked to write. The skeleton essentially consists of a small number of core features:-

1. A dynamic menu-management system
2. A role-based access control system [geared around activating/deactivating menu options]
3. An integrated user authentication mechanism

I would also like to add a generic, general-purpose application event log capability, such that it will be trivially easy to add a log record to a centralised archive – and of course to develop a simple front-end that would allow an administrator or auditor to trace activities on the system.

Having brain-stormed this as an idea, I’ve come up with two general approaches – but I am not sure whether either technique would be more [or less] appropriate to attempt in VBA.

1. To have a general-purpose table with a scant handful of fields, but then a “BLOB” field in to which I could load a serialized copy of a data object (i.e. before and/or after it is changed).
2. To have a more structured table with a few more fields, then create an “event type” table and require each logged event to have a dedicated record in this event type table, which I can refer to via it’s key field.

I’ve had a brief look around to see what VBA offers in the way of serialization features and found this (un-tested) reference,

http://www.vb-helper.com/howto_net_serialize.html

which strongly suggests that this approach is technical possibly. But being technically possible and being a good idea are two very different things!

This question/decision struck me as something both a little more subtle and a little more profound that asking, “Is there a cool way to serialize objects in VBA?”

In terms of requirements, I would suggest that [in decreasing order of importance] my goals would be: -

1. Extensible framework – easily expanded to include new event types
2. Efficient in performance terms [write time, disc storage]
3. Easy to invoke – in a perfect world it would be a single line of code
4. Easy to code the core functionality
5. Perhaps able to provide an “undo” feature [if I code in “before” images]

I did perform a search in these forums, looking through many of the “audit” related posts, but I didn’t find anything that discussed this particular question. Apologies if I’ve missed anything relevant.

I would be very grateful for any feedback, suggestions or thoughts on the relative merits of these or other approaches. If anyone is aware of best practices or sample solutions I should consider, that would be very welcome too.

Thank you, in advance, for your consideration.

First thoughts based on things I've already done in my own projects

1. A menu system which varies according to users role or permission levels is easy to do assuming users need to login to the db.

2. Event logging is also easy enough to do in VBA
I assume you mean to log which menu item is used when and by whom
I have no idea about using BLOBS but have used a dedicated table for this purpose for many years.
The actual code isn't short but its implementation only needs a function call.

We use this to track activity but also to allow users quick access to their 'favourite' menu items based on previous usage

3. I also do something similar for error logging

The numbered list is achievable though I would suggest using regular backups rather undo feature.

All the above is assuming you are proficient in using VBA
Anyway do plan in detail before starting as its a big project

 

[ytene]

Very helpful, thank you...

Obviously I'm not just interested in actual code samples, but actual experiences of people trying to use either of these or other approaches and how they got on.

Just in case I'm using VBA-inappropriate terminology, my interpretation of "serialization" is this...

In VBA I can create a (software) object using,

Set myNewObject = new objectClass()

in which "objectClass" is the name of a pre-defined object that I have already written, and "myNewObject" is the name of an instance that I want to use at run time in my code.

One of the cool things that you can do with a software object is "freeze" it or take a snapshot of it, by "serializing" it. So you would have something along the lines of

myBLOB = serialize(myNewObject)

and this would take the software object, convert it to a single [and long!] string of data and then store it in a pre-defined BLOB object.

There are possibly three reasons why you might want to do this:-

1. It's actually a lot simpler to code the storage of an object if you simply say, "serialize this and then store the result";
2. Depending on how efficient your language handles the "serialize" command, it can actually be quicker than writing a method to assign all the fields in your object to table columns
3. If your language allows polymorphism, you can serialize any variety of a base-class object with a single line of code...

 

[ytene]

there is a lot in this post & it is likely to get a wide variety of responses

First thoughts based on things I've already done in my own projects

1. A menu system which varies according to users role or permission levels is easy to do assuming users need to login to the db.

2. Event logging is also easy enough to do in VBA
I assume you mean to log which menu item is used when and by whom
I have no idea about using BLOBS but have used a dedicated table for this purpose for many years.
The actual code isn't short but its implementation only needs a function call.

We use this to track activity but also to allow users quick access to their 'favourite' menu items based on previous usage

3. I also do something similar for error logging

The numbered list is achievable though I would suggest using regular backups rather undo feature.

All the above is assuming you are proficient in using VBA
Anyway do plan in detail before starting as its a big project

Thanks Colin, I appreciate you taking the time to share your thoughts...

The Menu system is coded and working perfectly. It's based on a hierarchy similar to i.e. disk file systems [NTFS-like] and I'm happy with it. Authentication of users is working fine [although I'm still only using SHA1 to hash passwords and not salting yet].

I totally agree with your comments regarding the undo feature and backups. Nothing - but nothing - beats backups. However, one cool possibility from using the audit log might be the ability to reverse a single change - without having to restore an entire database to an earlier state and then perhaps have to re-do a lot of work. I will experiment with that a bit...

I also think your advice concerning about detailed planning is absolutely crucial advice. I began with a data model - I quite like using Access to prototype data models because it's so easy to set up and tweak things. I've then coded a core set of "base classes" for my major data structures, as needed, implementing the core of read/create/update/delete functionality as you would expect. Some beautification required, but the basics are there.

In terms of designing the rest of the application, I have found that my adoption of a menu-based application helps enormously. I split out each activity into a separate menu item which can be access controlled discretely. Each menu item then gets a dedicated form that handles all the underlying logic. As a result this means that I can evolve the code step-by-step, not breaking anything and not disturbing existing functionality.

The worst-case scenarios I have hit with past projects has been when code I've written closer to the presentation layer - i.e. embedded within a Form, becomes more generally useful and needs to be switched to either an object or a code library. In practice this isn't too bad - the moment you realise the need you can write the class or library object, comment out your local logic and test. The changes are always localised and modular. I've never had to migrate the other way [i.e. from library to front end].

The hardest part has been migrating from a background in programming things like Mainframe COBOL and adopting to VBA. Believe it or not, I found that using PHP as a stepping-stone was a big help...

Having said that, I always find something new to learn!

 

[The_Doc_Man]

ytene - let's talk practicality and desire here. What you need, what you want, and what you can do depend on what you want to do with it.

When I was still working for the U.S. Dept. of Defense at a Navy Enterprise Data Center, we had two levels of audit logging. We had "reconstructive" logs that would allow us to, in essence, run a record backwards through time to know how it appeared on a given date at a given time. These included ORACLE-like "ReDo" logs that were like a detailed, field-by-field record including timestamp for every change in every monitored table. The cost of a "ReDo" log is disk-space. You cannot BEGIN to imagine how much space they take up on a busy database. We had enough traffic that we had to do a daily archive of our ReDo logs to tape and if we missed two days of copy/remove archiving, the disks would fill up and shut us down. Then we WOULD have to do some really quick tap-dancing to finish the backup, remove the old logs, and bring things up again.

The other type of logging was that we bit the bullet, did daily snapshot archiving of the database as it appeared at midnight, and then only kept "forensic" logs that showed who was touching what record in what mode. (We eventually stopped tracking some tables because they were so commonly used for lookup that even THEY were prohibitive, space-wise, for "touch" logging.) Then if "stuff happened" we could step back to the previous midnight backup, warn users that we had to recover a day-old copy, and ask them to re-enter transactions. That wasn't popular but we could at least figure out who whacked our tables and could then counsel that person on proper database usage.

The difference, of course, is what you do with the data. Seeing logins, identifying popular tables, and seeing update frequencies helped us to focus on tables that needed optimization or an extra index to make lookups faster. We also found some cases where knowing the timing of some things was useful so we included ultra-precise timestamps for some internal events. This can be done with forensic logging.

In the cases where we did "Reconstructive" logging (ReDo), one of the characteristics was availability. The ReDo logs could be archived to a given time of day while the database was still active. Then we had a time of day that was really low usage so that we could have the night crew run a quick procedure to Compact & Repair the DB to reclaim the space. That was daily for the ReDo setup, but the lower level of logging implied in a forensic log allowed us to miss a day or two without worry. We were able to do those monthly and still get by.

It's all a matter of what you want and what you are willing to pay in terms of procedures and processes to support your database assets.

 

[ytene]

ytene - let's talk practicality and desire here. What you need, what you want, and what you can do depend on what you want to do with it.

When I was still working for the U.S. Dept. of Defense at a Navy Enterprise Data Center, we had two levels of audit logging. We had "reconstructive" logs that would allow us to, in essence, run a record backwards through time to know how it appeared on a given date at a given time. These included ORACLE-like "ReDo" logs that were like a detailed, field-by-field record including timestamp for every change in every monitored table. The cost of a "ReDo" log is disk-space. You cannot BEGIN to imagine how much space they take up on a busy database. We had enough traffic that we had to do a daily archive of our ReDo logs to tape and if we missed two days of copy/remove archiving, the disks would fill up and shut us down. Then we WOULD have to do some really quick tap-dancing to finish the backup, remove the old logs, and bring things up again.

The other type of logging was that we bit the bullet, did daily snapshot archiving of the database as it appeared at midnight, and then only kept "forensic" logs that showed who was touching what record in what mode. (We eventually stopped tracking some tables because they were so commonly used for lookup that even THEY were prohibitive, space-wise, for "touch" logging.) Then if "stuff happened" we could step back to the previous midnight backup, warn users that we had to recover a day-old copy, and ask them to re-enter transactions. That wasn't popular but we could at least figure out who whacked our tables and could then counsel that person on proper database usage.

The difference, of course, is what you do with the data. Seeing logins, identifying popular tables, and seeing update frequencies helped us to focus on tables that needed optimization or an extra index to make lookups faster. We also found some cases where knowing the timing of some things was useful so we included ultra-precise timestamps for some internal events. This can be done with forensic logging.

In the cases where we did "Reconstructive" logging (ReDo), one of the characteristics was availability. The ReDo logs could be archived to a given time of day while the database was still active. Then we had a time of day that was really low usage so that we could have the night crew run a quick procedure to Compact & Repair the DB to reclaim the space. That was daily for the ReDo setup, but the lower level of logging implied in a forensic log allowed us to miss a day or two without worry. We were able to do those monthly and still get by.

It's all a matter of what you want and what you are willing to pay in terms of procedures and processes to support your database assets.

Thank you for the detailed response - it is much appreciated.
You make lots of valid points.

I understand heavy use applications; I won't tell you who I work for [since I'm still employed and would like to stay that way] but our SWIFT gateway processes 5 million+ transactions a day; we have another application that runs the largest single Oracle DB application anywhere in the world [accordingly to Oracle]...

And yes; when I was coding on mainframe platforms I wouldn't think twice about configuring JCL with "JOURNAL=BOTH" to get both Before Journals [for transaction restart capability] and After Journals, to roll forward from a backup for point-in-time recovery...

But as you say, I'm just coding for me.

So:
Minimum Requirement:-
I need to be able to allow my application to designate an unknown subset of activities as "log-worthy events". I must be able to capture a minimum set of fields, including User, Date, Time, Event Type, plus, perhaps, an additional core set of variables. I won't know what those variables are until I identify each log requirement, so the log may need to be able to support a variety of different record formats, or a generic field that can be adapted to convet multiple fields [i.e. by concatenating several fields with comma-separated values and placing the result in a large text field].

If I can [and there is no reason to think that this is not possible] I like the idea of splitting the structured, non-BLOB approach into two discrete chunks. One would be an administrative feature that allows me to register new Log Events by type - i.e. to mark up and label the various fields... This data would then be used by the parse functionality when an administrator is browsing the log data for real.

The administrative interface to the log record should allow me to do a variety of things:-

1. Purge all records with a timestamp of "before MMDDYYYY:HHMMSS"
2. Identify and display all records generated by User X
3. Identify and display all records by event Type [i.e. all "User Account Deletions"
4. Identify and display all records by event ?Severity?, where each record, when written, will be required to define a severity field with a scalar variable that translations to (Informational/Warning/Error/OhCrap)...
5. Perhaps a generic "browse up and down the event log" feature...

Pragmatically I can do this with the structured, non-BLOB approach. However, I've not attempted this sort of functionality in business logic before, so I was hoping to take insight from anyone who had done so and could make a recommendation... Does that help explain what I'm trying to achieve?

 

[jdraw]

Ytene,

Given the size and activity on the databases you described, why are you looking at vba?
There is no inherent audit logging in Access. Perhaps, you could give readers a picture of how this proposed application would be used (number of simultaneous users; distribution of users....)

Just curious.

Good luck.

 

[The_Doc_Man]

Though I did most of my work in Access, we actually DID have ORACLE databases. One of my long-ago certifications is as an ORACLE administrator (for ORACLE 5.x). You don't need ANYTHING from Access to implement either kind of logging. I'm like jdraw - I understand you want some feedback but don't see its relevance to Access.

Since I know what SWIFT is, let's just hope you don't work for United. Right now they are not in a happy place.

As to browsing audit logs, that can be done but beware that the more complex your logs, the worse your programming problem becomes. Just for starters, your FE has to link to a table that is just about ALWAYS open. You have to wait for a checkpoint or log switch to occur before you can see the most recent event logs.

My forensic logs contained several fields but beyond a certain point there was nothing to be done with them other than review for anomalies when problems came up. If I recall this correctly, my most complex audit log included

* an autonumber PK (because in theory two events COULD occur at the same time because it was two or more FE submitting events for logging)
* the timestamp to the second
* the user ID (a unique numeric code) whose FE was making the entry
* one of about a dozen event type codes (login/out, update/insert to table, delete from table, open form, trap intercept logging, and certain bulk operations)
* a free-form short text field. (Plus a flag used for archiving.)

That was a long, a date/time, a long, a long, and a short-text (limit 255). If I was using the high-precision timer, it was in the body of a message which had its own message type so I could filter for that case.

Here is the issue I have with having the severity ranging from "Ho-hum" to "Oh-crap" conditions. What are you going to do about it when an Oh-Crap error occurs? Isn't it a bit too late unless you have some way to immediately notify an operator? With an all-Access FE/BE split, that is theoretically possible only if you can figure out how to use Windows Messaging - AND only if an identifiable operator is logged in at the moment.

I guess you COULD filter the form or find a way to jump-scroll to the next Oh-Crap message, but just remember that it will get complex fast if you try to do ANYTHING other than find or skip messages.

 

[ytene]

Ytene,

Given the size and activity on the databases you described, why are you looking at vba?
There is no inherent audit logging in Access. Perhaps, you could give readers a picture of how this proposed application would be used (number of simultaneous users; distribution of users....)

Just curious.

Good luck.

JDraw, I think my previous reply has caused confusion. I'm not looking to build anything of that scale in Access or VBA, that was just to illustrate that I have a little experience of database logging and journaling on other platforms.

Let me try and give you a brief synopsis of what I'm trying to do. This will be in two parts. The first will outline a generic framework [the piece I am currently working on]. The second will be a specific implementation of that framework.

1. The Framework
I am trying to write a series of reusable Access functions that I can integrate with other logic to create complete "Applications". I anticipate that in this release, each implementation will result in a stand-alone, single-access-but-multi-user solution. However, it would be nice if the result could be uploaded to say Sharepoint to allow limited multi-user access at some point in the future.

The framework is intended to deliver a few basic features:-

1. A Menu Navigation System [mostly written]
2. User Administration [Create/Amend/Delete/etc][mostly written]
3. Role Administration [Create/Amend/Delete/etc][mostly written]
4. Parameter Management [Create/Amend/Delete][written]
5. User Self Service [Password Change/etc] Half written]
6. Access Administration [Linking Users-to-Roles, Roles-to-Menu-Items] [Not Started]
7. Audit Logging [Not Started]
Other, specific business, logic, will then be able to "plug in" to this structure. [This has been successfully achieved].

2. Example Implementations
My first specific implementation will be to combine three existing data models (a control framework based on industry Standards including NIST CSF, COBIT5 and ISO27002); inventory data from an asset management system; and a vulnerability/defect management system).

By combining these data sets we have shown that it is possible to provide a "risk appetite" model and to provide concrete guidance in terms of prioritising remediation of vulnerabilities and/or application of patches.

As you might imagine, the owners of the respective data sets are quite terratorial. Since we know we can successfully combine the data in Access, I'm hoping to write a front end that wraps the data in a way that makes it relatively simple to control what an individual can see or do within the system, based on their ID. The aim is to make something which is flexible so that other functionality can be added if required - but also so the solution can be re-used, if required. The idea is that each community of users will only be able to see and manipulate their own data - controlled by role-based access and menu functions.

My questions concerning the audit portion were in recognition of one specific aspect of the solution. Based on manually-entered risk appetite data, we can show whether or not a given application or system is "within risk tolerance" or not. Unfortunately, we also came up with a couple of examples where it looks as though a party or parties unknown have manually "tweaked" some of our data to downgrade certain items and thus nudge data back to "within tolerance".

I can't prove anything - and given this is all being developed on personal time with zero budget - options are limited. However, given the rest of the framework, it should be possible to splice in a simple log generation capability and capture key events in this way. I have quite a bit of flexibility in terms of how that logging feature is designed and added, but I have no experience of actually managing a log file once generated.

I anticipate tens or hundreds [max] of log-worthy events per day.

Not sure if this is providing you the perspective you need. Happy to augment or answer specific questions if required.