how to secure database from editing?

[concurrent]

how can i secure forms and queries from editing from other authorized users in Access 2007? also how to protect saved records from deleting?

[rpeare]

The simplest thing to do is to put the database in a network folder and assign permissions to the folder on the NETWORK level, I think that's the only method that's reliably worked for me.

If a person has read/write access to your database, there is no real way to deny them the ability to delete a record, but you can mitigate that a few ways.

1. Hide all tables
2. Create data entry forms as your user interface rather than allowing direct access to the tables.
3. Require a login to the database (either password protect the entire database or use some VBA code to create your own login security).

[Perceptus]

I believe this can be all be bypassed by any user that holds shift while opening the database.

[rpeare]

If he sets the security at the network level (first suggestion) people will not be able to change/add data I've got a couple of databases we set up this way.

For the remaining items (the numbered ones) you are correct, if someone knows what they are doing in access you can circumvent a lot of things but that depends on the audience your database is targetting.

Another method would be to put a password on the database itself, then nobody could get into it without the password but that's a little clumsy.

[Perceptus]

You may be able to promote the database front end to an asp.net dynamic data entities web site. This could give you the protection from the UI being changed by the user and you would have control to lock out records from being deleted. check out <- link. The scaffolding option could get you up and running pretty quick.

[NTC]

Security is like an onion. there are layers - and the right answer for you depends on how sophisticated is your users - vs - how much hassle you want to maintain on an ongoing basis.... stopping hard core crackers is more involved than preventing clueless user errors..... so the answer can be anything from locked fields in forms to encrypted/password protected files. But basically be sure all users are in forms - never directly in tables - and then this is your control point.

[Missinglinq]

Security is like an onion. there are layers - and the right answer for you depends on how sophisticated is your users - vs - how much hassle you want to maintain on an ongoing basis...But basically be sure all users are in forms - never directly in tables - and then this is your control point...

Exactly! If you ride a classic sports car, you might spend $5000 for a state-of-the-art security system; if your ride is a ten year-old banger, probably not! And one of the problems, here, is that the OP hasn't really spelled out what he or she is trying to protect against; accidental changes or intentional ones.

Since it is obviously in a multi-user environment, it needs to be Split into a Front End/Back End configuration, which should pretty much resolve the question of direct user access to Tables.

The Front End should then be converted to an accde file; this will prevent design changes to the Forms. With all access to Records being made through Forms, defeating Deletion attempts is easy:

Private Sub Form_Delete(Cancel As Integer)
 MsgBox "Deletion of Saved Records Is Not Allowed!"
 Cancel = True
End Sub

The real problem is securing the Queries against modification, and I'm not real sure how, in Access, itself, you could do that. Even in an mde/accde file Queries can be changed, the theory being that anyone with enough knowledge to do that should be able to, to suit his individual needs. The important thing to remember is that in a Split database, with each user having their own copy of the Front End on their PC, they will only be modifying their copy of the Query, no one else's.

Linq ;0)>

[NTC]

one can remove the navigation pane availability to protect queries.... in the Options/Current Database - uncheck the Use Special Keys choice.....

bottom line: Access can be as secure as one needs - it just requires more management as one climbs the ladder of higher and higher security....

[jtm013]

I believe this can be all be bypassed by any user that holds shift while opening the database.

This can be taken care of using:

Function ap_DisableShift()
'This function disable the shift at startup. This action causes
'the Autoexec macro and Startup properties to always be executed.


On Error GoTo errDisableShift


Dim db As DAO.Database
Dim prop As DAO.Property
Const conPropNotFound = 3270


Set db = CurrentDb()


'This next line disables the shift key on startup.
db.Properties("AllowByPassKey") = False


'The function is successful.
Exit Function


errDisableShift:
'The first part of this error routine creates the "AllowByPassKey
'property if it does not exist.
If Err = conPropNotFound Then
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, False)
db.Properties.Append prop
Resume Next
Else
MsgBox "Function 'ap_DisableShift' did not complete successfully."
Exit Function
End If


End Function

As others have said it is an onion. My own case simply required hiding access to navigation and menus, requiring users to use form based navigation and using vba to create a login screen which grants users permissions based on stored values in a login information table.