MS Access Multiuser - preventing direct access to the back end file on a network

Author

preventing direct access to the back end file on a network

Paul Ponzelli

2005-09-27, 8:25 pm

We have a number of split databases (Access 2002) on our network, where the
users have a copy of the front end on their local drives, linked to tables
in the back end on a network server. The front ends are *.mde files, and
the F11 key is disabled so they can't open the database window and view the
tables directly. However, they can open the tables directly by opening the
back end mdb file on the network drive.

We're running the Windows 2003 operating system on our network and Windows
XP on our desktops. I've experimented with preventing direct access to the
back end database files by restricting user access at the OS level.
However, Access 2002 reads network permissions, so if someone doesn't have
permissions into the back end at OS level, they can't open a linked
connection to the table from the front end, which is of course necessary.

I would like to avoid working with user and group security in Access, as I
have read and been told that it's very complex. One source described it as
ranging from labyrinthine to inscrutable. Is there a relatively simple way
to restrict direct access to the back end for all (but administrative)
users, and still retain full permissions through the linked connections in
the front end?

Thank in advance,

Paul

david epsom dot com dot au

2005-09-28, 3:25 am

> XP on our desktops. I've experimented with preventing direct access
> back end database files by restricting user access at the OS level.

You should of course use a hidden share, probably with no browse
permission, and no browse permission on parent folders. And

> the F11 key is disabled so they can't open the database window
> and view the tables directly.

--- also in the back end.

Logically, the only way to do what you want is to assign
permission to your application database. That is supported
by Windows security for signed applications (like a signed
Access database), but not supported by the current version
of Access. Perhaps in the next version. I'm not holding my
breath.

(david)

PS:
Access security is only difficult to learn and teach, not
to use. Setting up, using, changing, removing, re-setting
Access security is all easy and trivial: only explaining
it to someone is difficult.

"Labyrinthine to inscrutable" is a description of
explanations of Access security - and the excuse
used by people who, when they tried to write about
Access security, found their own text to be no better.
(Even good authors are limited by the language
available to describe the system).

I myself have a good explanation of Access security
but the margin is to small to show it ...

"Paul Ponzelli" <begone@spam.forever> wrote in message
news:%23gl2Xl8wFHA.3740@TK2MSFTNGP14.phx.gbl...
> We have a number of split databases (Access 2002) on our network, where
> the
> users have a copy of the front end on their local drives, linked to tables
> in the back end on a network server. The front ends are *.mde files, and
> the F11 key is disabled so they can't open the database window and view
> the
> tables directly. However, they can open the tables directly by opening
> the
> back end mdb file on the network drive.
>
> We're running the Windows 2003 operating system on our network and Windows
> XP on our desktops. I've experimented with preventing direct access to
> the
> back end database files by restricting user access at the OS level.
> However, Access 2002 reads network permissions, so if someone doesn't have
> permissions into the back end at OS level, they can't open a linked
> connection to the table from the front end, which is of course necessary.
>
> I would like to avoid working with user and group security in Access, as I
> have read and been told that it's very complex. One source described it
> as
> ranging from labyrinthine to inscrutable. Is there a relatively simple
> way
> to restrict direct access to the back end for all (but administrative)
> users, and still retain full permissions through the linked connections in
> the front end?
>
> Thank in advance,
>
> Paul
>
>

Albert D.Kallal

2005-09-28, 7:24 am

Yes, what I do is put a autoexec macro on the back end, and when you open,
or run the mdb back end, I give a user a "fake" message that they don't have
permissions to open the database. When they click ok, they are exited. Works
for most people. If you disable the shift key bypass, this tends to keep
people out.

They can create a "blank" database and "import" the data, but at least they
will not be messing around with the back end database.

The time to implement the above is less time then it takes to write my
response here.

So, I make a macro called AutoExec. In the macro, I put the following code:

Action Parms
Msgbox message:You do not have permissions to run this file
Beep: Yes
Type: Critical
Title: Can not run

Quit: Options:Exit

As an alterative, you an make a nice little form in the back end, and set
the startup options to run that form that displays a message, and then when
they click ok...you do a application.quit.

And, to disable the "shift key", simply grab my shift key utility here to
turn off the shift key by-pass

http://www.members.shaw.ca/AlbertKa...s/msaccess.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@
msn.com
http://www.members.shaw.ca/AlbertKallal

(PeteCresswell)

2005-09-28, 9:24 am

Per Albert D. Kallal:
> When they click ok, they are exited. Works
>for most people. If you disable the shift key bypass, this tends to keep
>people out.

How do you let yourself or another developer in? LAN UserID check?
--
PeteCresswell

Albert D.Kallal

2005-09-28, 8:26 pm

I hold down the shift key.....

And, if I disabled the shift key, then I use my nice little utility to
"browse" to the back end, and turn off the shift key.

You can find my utility here:
http://www.members.shaw.ca/AlbertKa...s/msaccess.html

I mean, you might ask what about those users grabbing my shift key utility?

a - we hope they don't
b - then implement security on the back end file (means you now have to use
a workgroup file). If security is set, then the shift key by-pass can't
be-undone unless the user has admin privileges (means my utility will not
work)

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@
msn.com
http://www.members.shaw.ca/AlbertKallal

Paul Ponzelli

2005-09-28, 8:26 pm

Thanks for the suggestions, David.

I do have a few questions about them:

1. If I set up a hidden share, will the tables still be visible to the front
end through the table links?

2. When you say "Logically, the only way to do what you want is to assign
permission to your application database,", are you referring to the front
end, the back end, or both?

3. In that same statement, are you talking about assigning permission at
windows level or in Access itself? And when you say this isn't currently
supported in Access, does this mean it can't be done unless and until it's
available in Access?

Thanks again.

Paul

Paul Ponzelli

2005-09-28, 8:26 pm

Albert, I've been using your ShiftKey2000 that I downloaded from your
website (linked in your preceding message) for some time now, and it works
great. When I said in my opening question in this thread that I disable the
shift key in our front end dbs, I'm using ShiftKey2000 to do it. So I'll
thank you again for making this available to the community.

Thanks also for the suggestions about "hiding" the back end db. I'm going
to experiment with it.

Paul

"Albert D.Kallal" < PleaseNOOOsPAMmkalla
l@msn.com> wrote in message
news:ugB9ABHxFHA.3180@TK2MSFTNGP14.phx.gbl...
>I hold down the shift key.....
>
> And, if I disabled the shift key, then I use my nice little utility to
> "browse" to the back end, and turn off the shift key.
>
> You can find my utility here:
> http://www.members.shaw.ca/AlbertKa...s/msaccess.html
>
> I mean, you might ask what about those users grabbing my shift key
> utility?
>
> a - we hope they don't
> b - then implement security on the back end file (means you now have to
> use a workgroup file). If security is set, then the shift key by-pass
> can't be-undone unless the user has admin privileges (means my utility
> will not work)
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@
msn.com
> http://www.members.shaw.ca/AlbertKallal
>

2005-09-28, 8:26 pm

> 1. If I set up a hidden share, will the tables still be visible to the
front

> end through the table links?

1) A hidden share is a share with a name that ends with "$".
When you browse the network, hidden shares are not shown.
However, you can type the name into Windows Explorer
and browse the share, and Windows Explorer will remember
the share name for it's auto-completion suggestions. A hidden
share can be used exactly the same as any other share. This is
not really security now, it is just a way to manage the very
long list of shares you might get on a large network. It was
a bit more obscure when we were using DOS and Novel
networks.

Browse permission is the same only better. On Windows XP,
non-admin users can't use Windows Explorer to see a list of
files in their Windows folder. They get a blank screen message
instead. This is the Browse permission. Note that those non-admin
users still do have permission to use all the files in their Windows
folder: Windows wouldn't work otherwise.

You can make a hidden share, and remove Browse permission.
Users will still be able to link to or delete your file if they know
the file path: they just won't be able to find or list the files.

2) I was talking about assigning permissions to Applications-
like your FE database.

3) Microsoft Windows security allows you to assign permissions
to Users or to Signed Applications.

MSAccess.exe may or may not be a signed application, but you
would not want to assign permissions to it, because then any
database code would be able to abuse those permissions.

Access databases may be signed, but MSAccess.exe does not
pass that information to the OS when requesting access to your
BE files. (A process sometimes called "impersonation")

In order for you to use permissions assigned to your Signed
FE database, msAccess.exe would have to impersonate
your signed FE database. msAccess.exe does not do that.

.... It can't be done unless and until it is supported by Access.

(david)

"Paul Ponzelli" <begone@spam.forever.net> wrote in message
news:OSDXIVHxFHA.2848@TK2MSFTNGP15.phx.gbl...
> Thanks for the suggestions, David.
>
> I do have a few questions about them:
>
> 1. If I set up a hidden share, will the tables still be visible to the
front

> end through the table links?
>
> 2. When you say "Logically, the only way to do what you want is to assign
> permission to your application database,", are you referring to the front
> end, the back end, or both?
>
> 3. In that same statement, are you talking about assigning permission at
> windows level or in Access itself? And when you say this isn't currently
> supported in Access, does this mean it can't be done unless and until it's
> available in Access?
>
> Thanks again.
>
> Paul
>
>

Paul Ponzelli

2005-09-28, 8:26 pm

Thanks for the further clarification, David. Based on what you're saying,
I'm going to ask our network people to set up a hidden share. While not a
100% solution to security, it does seem to go a long way beyond where we are
now.

You also mentioned that you have a good explanation of Access security, but
you said the margin is too small to show it. Is that because it's in html?
In any event, would you be able to share it by some other means, like on a
web site, or by email? If the latter, my email is (after concatenation to
avoid automated spam)

"pponzelli" & "@" & "surewest" & "." & "net"

Thanks again for taking the time to provide these explanations.

Paul

david epsom dot com dot au

2005-09-29, 3:25 am

> You also mentioned that you have a good explanation of Access security,
> but you said the margin is too small to show it. Is that because it's in
> html?

http://mathsforeurope.digibel.be/fermat.htm

I still think it's funny :~)

Here are a couple of url's:

http://www.wideman-one.com/gw/tech/access/accesssec/

www.jmwild.com/accesssecurity.htm

(david)

"Paul Ponzelli" <begone@spam.forever.net> wrote in message
news:%23CQhfMIxFHA.3400@TK2MSFTNGP14.phx.gbl...
> Thanks for the further clarification, David. Based on what you're saying,
> I'm going to ask our network people to set up a hidden share. While not a
> 100% solution to security, it does seem to go a long way beyond where we
> are now.
>
> You also mentioned that you have a good explanation of Access security,
> but you said the margin is too small to show it. Is that because it's in
> html? In any event, would you be able to share it by some other means,
> like on a web site, or by email? If the latter, my email is (after
> concatenation to avoid automated spam)
>
> "pponzelli" & "@" & "surewest" & "." & "net"
>
> Thanks again for taking the time to provide these explanations.
>
> Paul
>

Paul Ponzelli

2005-09-29, 9:24 am

> I still think it's funny :~)

I had never seen that before - I figured the explanation had some diagrams
that couldn't be transmitted in a text-only newsgroup. I agree, it is
funny.

> Here are a couple of url's:
>
> http://www.wideman-one.com/gw/tech/access/accesssec/
>
> www.jmwild.com/accesssecurity.htm
>

These appear to be well written explanations of how to apply security. I'll
try them out.

Thanks so much for your help with this, David.

Sponsored Links