Question Audittrail and securing a DB

[Dazzy]

Hi All

I had posted in another topic regarding this and was asked to post a new topic so here goes.

I will outline my problem again, I designed a DB for my work a few years back and they had to have a technician secure it using the Set Password option in Access to satisfy the requirements set out by our BSI auditor.

The DB has now become the hub of the business and staff are being judged now as part of SMART targets and objectives and contract appraisals will now be based on these.

I have suspected foul play for some time, so I was in a meeting today with my manager and the chairperson on our board of directors and I proposed adding accountability to the DB as one of my targets for the next year. To my surprise my manager actually admitted he suspects sabotage with regards to the DB and he thinks the system I proposed will be perfect.

The single login to the DB is now causing me issues with the Audittrail aspect, as everyuser is shown as Admin. Due to the way the office is set up I dont believe Access Security will work for me. Nor will tracking the environment user as they dont want to set up individual user accounts on PC's.

The DB is currently on an external harddrive locked in managers office and is accessiable by all on the office network, this drive is backed up everyday onto two different sources, one stays onsite in the safe and other be's taken by manager at the end of each day.

But anyway enough about that.

What I want to acheive is a multi user login system which Audittrail will be able to pick up the username from, I have been using Ghudsons' Audit trail example from this post http://www.access-programmers.co.uk/forums/showthread.php?t=44231 and was hoping to use the security demo posted by Hayley Baxter @ http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47750

I am not an expert by any means in Access and dont really know VBA, so I dont consider the DB I designed to be brilliant but it fulfils the requirements they needed.

Attached is a screenshot of one programme in the DB, there are 5 or 6 other programmes with similar sections. As you can see it isnt the best designed DB but it was the best I could do at the time.

Can anyone help me getting Audittrail to pick up the username from that security example or suggest an alternative?

I really appreciate your help in this matter

Best regards

Gary

 

[KathyL]

I design my own security screens and control access to updates/adds/etc. I'd suggest getting a good Access/VB programmer who can do the same for you.

 

[SOS]

Actually, as far as audit trails go, this one is fantastic and is very simple to implement. And it already gathers the network login of the user. We are actually implementing this one (with a little revision as we are using SQL Server as a backend).

 

[Dazzy]

Hi

@KathyL, as a last resort I may go down this route but I always like to try things for myself first.

@SOS, grabbing the network user, brings me back to the problem where staff all use the default account in Windows XP, their currently is no user accounts set up as some staff move between computers on a daily basis. The setup really is a joke but its what I have to work with.

Staff are barely computer literate so top grade security isn't really a concern, I have trouble getting them to enter data accurately as it stands and if it is indeed being sabotaged by someone this only increases my workload in the end and I am already over stretched as it is.

Adding the Audittrail and getting it working I can already do, just it's useless currently as it takes the default Admin account from Access. I am a teacher and don't have time to be babysitting Administrative staff and the fact there is currently no 'accountability' built into it means they can deny etc and theres no way to prove different.

Many thanks for your replies.

 

[SOS]

@SOS, grabbing the network user, brings me back to the problem where staff all use the default account in Windows XP, their currently is no user accounts set up as some staff move between computers on a daily basis. The setup really is a joke but its what I have to work with.

So they do not have individual network logins? If that is the case, you really are not going to be able to tie anything to anyone - at all. Seriously, you might as well scrap the audit as well. You can capture what was changed and when, and on what computer, but you cannot identify who did it.

 

[Dazzy]

No they dont, we are a small firm with 8 staff, manager, 4 admin, 2 full time tutors, 1 part time tutor. Resources arent unlimited by any means so I am stuck with this.

I will need to figure how to get pass the username from a custom security system to Audittrail, nothing is ever impossible just a matter of working around the obstacles wer are presented with.

 

[SOS]

You can get the username of the computer by using

Environ("username")

instead of CurrentUser

but again, it will only give you the login that was used on the computer and if it is generic, it won't mean much.

 

[Dazzy]

Hi

I already know this and I presented this as a solution today, but it wont work for our office.

There must be a way to pass the username from a custom login like the example I posted to the audittrail module. I am gonna check with a few developers who have helped me with my websites to see if they know anyway aswell.

 

[gemma-the-husky]

i would fight shy of access security, unless you want to restrict the ability to do certain things to certain users. it often adds a lot of unnecessary complexity.

i would definitely use the environ(username) to identify the login

if you really want to get to the bottom of who is doing what - then you NEED to issue proper logins with proper passwords, and stress to your staff that they MUST log in correctly, not share passwords etc etc.

its a management thing

 

[SOS]

Hi

I already know this and I presented this as a solution today, but it wont work for our office.

There must be a way to pass the username from a custom login like the example I posted to the audittrail module. I am gonna check with a few developers who have helped me with my websites to see if they know anyway aswell.

Actually, if you have a custom login, just have a hidden form with an unbound text box on it. Then from your login form set the value

Forms!YourHiddenFormName = Me.MyTextBoxOnMyLoginForm

and there is a sample login on Bob Larson's site, which might help (don't know as I haven't seen it).

 

[Dazzy]

Actually, if you have a custom login, just have a hidden form with an unbound text box on it. Then from your login form set the value

Forms!YourHiddenFormName = Me.MyTextBoxOnMyLoginForm

and there is a sample login on Bob Larson's site, which might help (don't know as I haven't seen it).

So let me see if I understand this right if I create a hidden form to store the username of the person who logged in I can then pass this value to audittrail by means of the module file?

So instead of checking the environment or curruser it can be set so it checks the value on hidden form?

Does anyone have a simple example of this?

 

[Dazzy]

Big thanks to SOS i now have this picking up the username from the custom login solution, now just to decide should I use auditrail on form are as a seperate table. Based on the screenshot above guys what would yous recommend?

 

[SOS]

Big thanks to SOS i now have this picking up the username from the custom login solution, now just to decide should I use auditrail on form are as a seperate table. Based on the screenshot above guys what would yous recommend?

The actual trail info is stored in a table. The audit trail needs to be enabled for each form (Before Update and Delete events - at least for the trail that I posted a link to).

 

[Dazzy]

Hi

I havent really looked at that example yet as I havent had the time to try and figure how to intregrate it yet. I do like how that example also tracks deletions however.

All the best

 

[Dazzy]

Want to say a big thanks to all in this thread I have pretty much the prototype done now to show the board of directers next week.

 

[SOS]

Want to say a big thanks to all in this thread I have pretty much the prototype done now to show the board of directers next week.

Cool - hope it goes well

 

[irish634]

So they do not have individual network logins? If that is the case, you really are not going to be able to tie anything to anyone - at all. Seriously, you might as well scrap the audit as well. You can capture what was changed and when, and on what computer, but you cannot identify who did it.

I tend to disagree with this. Just because there are no network logins, does not mean you can't figure out which pc it came from and when. Creating your own log-ins, disabling the shift key, menus, etc is a step in the right direction. It won't be perfect, but he can surely make it extremely difficult for anyone looking to poke around and cause problems.

 

[SOS]

I tend to disagree with this. Just because there are no network logins, does not mean you can't figure out which pc it came from and when..

I never said that you can't figure out which pc it came from and when. You can't figure out WHO (the big WHO) did it. And that CAN be VERY important. Just knowing which computer and when it came in is not very informative if you do not keep detailed records as to who was using which computer when. So, you can disagree all you want but the fact of the matter is without secure INDIVIDUAL logins you CAN NOT prove who did what and therefore an audit trail is SEVERELY lacking.

 

[SOS]

I tend to disagree with this. Just because there are no network logins, does not mean you can't figure out which pc it came from and when. Creating your own log-ins, disabling the shift key, menus, etc is a step in the right direction. It won't be perfect, but he can surely make it extremely difficult for anyone looking to poke around and cause problems.

Actually, I'm adding to my last statement. The OP has stated:

The DB has now become the hub of the business and staff are being judged now as part of SMART targets and objectives and contract appraisals will now be based on these.

I have suspected foul play for some time, so I was in a meeting today with my manager and the chairperson on our board of directors and I proposed adding accountability to the DB as one of my targets for the next year.

And if staff are being judged on this, it is EXTREMELY IMPORTANT that they be able to identify WHO has made changes. It is less important as to the computer it came from, but it the WHO is important and currently they CANNOT get this information. Just so you know - it would not be legally enforceable to have the capture of the computer information with the date/time to say Sally was working on that computer yesterday and it looks like the changes came from that computer so Sally did it. You CAN NOT PROVE THAT legally. If users had their own logins and are, by policy, not allowed to share their login information with others, then she COULD be legally held liable if the information showed that it changed from her login. But with just the computer and the fact that others use the computers from time to time, NOBODY could be held legally liable or even fired over doing something because it could NOT BE PROVEN. And an Audit Trail that does not have the ability to identify WHO did the change is basically as useful as a swim suit in a flow of lava.

 

[Dazzy]

Hi Guys

Dont fall out over this, the solution I came up to with the help of this forum will more than cover me, it means some radical changes to how the DB operates but staff wont notice much difference.

For example for simplisity when I set it up I used the switchboard manager to handle the menu screens, but these will be changing to forms to facilate private menus for manager with private reports etc.

I am fortunate here where the staff wont be even able to figure out how to show hidden objects and the audittrail will hopefuly put stop to any willful sabotage.

The audittrail will be done via memos and will likely be visible to all staff via a History button on the form for all to see.

Thanks to SOS I am utilising the delete aspect of the audittrail example he pointed out to me to catch deleted items, this report will only be for the manager, I have also created simple reports for the manager to show all edited records etc, these will be added to over time.

I fully expect this deterent will be enough to stop any stupid actions by staff, and they can no longer claim I deleted it by accident either as not only does Access ask you to confirm but now they have to enter a reason for deleting. General staff wont be told deleted records are being recorded too. By using the audittrail in memos, it also shows that element in the Deleted Records report without any intervention from me (which is always nice lol).

The security system is perfect for this as it asks staff members to change password on first login, manager wont even have access to the User Management screen, he will only get this if I decide to leave the organisation and this was agreed with the chairperson of the board of directors today. She fully understands when i said I have no reason to sabotage this DB as in the end it only leads to increasing my work fixing it. I already teach for 32 out of the 37.5 hours I work each week and getting anything done is very difficult.

All the best and thanks again