user record security

[martinainscough]

Hi Guys

I am begining to create a IT support database for my company. I have never really needed to use secuirty for the other projects that i have completed but this one is a bit different. i have used the security wizard to create the users and groups but would like to get the basis of the security correct before i go any further.

My question is how do i configure it so that users can only update or change or delete thier own entries apart from me who could alter anything. Do i begin now when i am setting up the tables or is it a bit more complex and would require checks during accessing forms etc.

Any help and advice would be great

Many Thanks

Martin

 

[Len Boorman]

Access security is very good but it can be difficult to achieve what you are seeking to do.

I have used a system whereby I detect the Network logon ID and stamp the record with this ID. I can then set up queries such that only records with a matching logon ID are available to the average user.

I then have a separate set of edit functions that are available to "Master Users" that basically can edit anything.

The overrall set up is
Database opens
Novel ID Form opens (fields visible property set to false (Actually also has PC ID as well)
Opening form Opens that has buttons as usual for add, edit etc. along with a Master User button that is only visible if the logon id matches a master user logon id.

Need to use custom toolbar, disable F11 key and by pass key. so its a little bit of setting up but after that it seems to work quite well. I do not set up Access Security and someone smart enough could import all objects into a new database etc but nobody really atthat sort of level.

Hope this gives dome ideas

Len B

 

[The_Doc_Man]

What Len said ... PLUS a little clarification.

You can never allow users to directly open tables or queries. You must set up a startup and/or switchboard form that never ever in a gazillion years allows anyone but an Admin to see the DB window. Everyone else must go through the forms. Because that is the ONLY way you can be sure that no one touches anything they aren't supposed to touch.

You would need to decide whether users could SEE other data because the path you take somewhat depends on that answer.

If they can't even SEE data they don't own, you need to implement a parameter query and the parameter is their code number, which must be part of the underlying record. They would not be able to query any records they didn't own so changes would never occur. (Can't change what you can't see...)

If they can see but cannot CHANGE data they don't own, you must implement this in some sort of code that blocks the "normal" ways of updating Access - like disabling navigation and exit attempts while the form is "dirty" (look up that property of forms). Then in the code underneath the form, perhaps put a subroutine call in a control_LostFocus routine and have it test (a) whether the person is authorized to change the data and (b) whether the data has been changed from its "oldvalue" (another lookup in the help files). Then based on the owner code and your code, you can decide what to do when (b) is true. To save data changes made by an authorized user, you would probably need to create a SAVE button (and for symmetry, a CANCEL button). Then the corresponding OnClick event code could do the security checks and other operations.

 

[Len Boorman]

Doc Man is quite correct in that when you start down this path there area few bits that always need to be considered.

I normally have an Opening Form which has Add, Edit View Delete command buttons.

On every one except the View and Add the selection of a record is based upon a selection from a combo box. Within the combo box only records that have been stamped with the User ID are available. On the view all records are available but the resulting display is Read Only.

Anybody basically can create a record.

Another command button for Master User, then a password display and then Edit, Delete etc but this time the combo's have no limltations. It does work but you do have to make sure as Doc Man says that nobody gets in the back door

Len B

 

[martinainscough]

Thanks Guys - Much Appreciated!!!