Securing and Splitting Databases

J

jerryb123

I am working on splitting a simple information lookup database so that a
number of sales users will have a front end on their machine that queries
data contained in a back-end on the server. I am trying to set up the
security and permissions so that "sales" can use the backend data without
looking at it. These are the main issues that are troubling me:

1) I have set the front end to hide the navigation pane, etc., and only show
the lookup form I want the people to use, but I can't figure a way to prevent
anyone from holding down "Shift" on startup and seeing everything I didn't
want them to see.

2) When I set the permissions so that people in "sales" can't open up the
linked tables and take a look at them, it also prevents the lookup form from
working, because it draws from the data I want to hide when it generates the
results I want to show.
 
C

Chris O''Neill

First, I would strongly encourage you to read the MS Access Security FAQ.
It's a bit long and can be a little hard to understand if you're new to all
this, but it will answer just about any question you can think of about
Access security. Here's the link:

http://support.microsoft.com/?id=207793

Now....

jerryb123 said:
1) I have set the front end to hide the navigation pane, etc., and only show
the lookup form I want the people to use, but I can't figure a way to prevent
anyone from holding down "Shift" on startup and seeing everything I didn't
want them to see.

Here's a function from the FAQ that will disable holding down the SHIFT key
to bypass the autoexec macro and startup settings. It only needs to be run
once.

Function faq_DisableShiftKeyBypass(strDBName as String, fAllow as Boolean)
As Boolean

On Error GoTo errDisableShift

Dim ws As Workspace
Dim db As DATABASE
Dim prop As Property
Const conPropNotFound = 3270

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)

db.Properties("AllowByPassKey") = Not fAllow
faq_DisableShiftKeyBypass = fAllow
exitDisableShift:
Exit Function

errDisableShift:
'The AllowBypassKey property is a user-defined
' property of the database that must be created
' before it can be set. This error code will execute
' the first time this function is run in a database.

If Err = conPropNotFound Then
' You must set the fourth DDL parameter to True
' to ensure that only administrators
' can modify it later. If it was created wrongly, then
' delete it and re-create it correctly.
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, False, True)
db.Properties.Append prop
Resume
Else
MsgBox "Function DisableShiftKeyBypass did not complete successfully."
Faq_DisableShiftKeyBypass = False
GoTo exitDisableShift
End If
End Function
2) When I set the permissions so that people in "sales" can't open up the
linked tables and take a look at them, it also prevents the lookup form from
working, because it draws from the data I want to hide when it generates the
results I want to show.

Right. But, you can get around this by coding all of the lookups on your
forms using RWOP queries. "RWOP" means "Run With Owner's Permissions" so,
essentially, the sales people will have no access to the data themselves, but
the queries will work because they'll run with your privileges. There's more
info on RWOP queries in the FAQ.

Hope that helps. DO go and read the FAQ. It's worth the time and effort.

Regards, Chris
(Who's still figuring out MS Access Security himself!) :D
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top