Backend Security

[ErikFM via AccessMonster.com]

Hi everyone,

I have a question regarding securing a backend Access file.

Currently, the backend is residing on a shared network folder, so that the
users who have the front-end on their PCs can have access to the data.

What I am wondering is if there is someway to password protect the backend
file, or the folder it's sitting in, so that no one can go into the tables
and make changes (or accidently delete) the tables in it.

But, I also want to ensure that the front-end users won't be blocked from
access to the data.

Any thoughts? I've searched the forums here, but am still a little confused
as to what will be the best course of action.

Thanks!

 

[Arvin Meyer [MVP]]

Prior to Office 2007, Microsoft had User Level Security which will do what
you desire. Essentially you would lock users out of the back-end and give
them permissions to data using:

WITH OWNERACCESS OPTION

queries. Here are the steps to properly secure your database in version 2003
and earlier:

Security FAQ
http://support.microsoft.com/support/access/content/secfaq.asp

Lynn Trapp's summarization:
http://www.ltcomputerdesigns.com/The10Steps.htm

KB articles:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q165009
http://download.microsoft.com/download/access97/faq1/1/win98/en-us/secfaq.exe
http://support.microsoft.com/default.aspx?kbid=325261

Joan Wild's articles:
http://www.jmwild.com/security02.htm
http://www.jmwild.com/security97.htm
http://www.jmwild.com/SecureNoLogin.htm
http://www.jmwild.com/Unsecure.htm

 

[Joan Wild]

There are a few things you can do to throw up road blocks, but the
determined can get past them:

1. Put the backend in a hidden share i.e. \\share\folder$ rather than
\\share\folder

2. Put a database password on the backend (you'll then need to
delete/recreate the links in the frontend and then redistribute it)

3. Add an autoexec macro to the backend which
a - msgbox to tell the user to use the frontend
b - quit action to close the mdb

4. Disable the shiftkey bypass in the backend so they can't bypass the
autoexec.

Joan Wild

 

[Tony Toews [MVP]]

Prior to Office 2007, Microsoft had User Level Security which will do what
you desire.

Note that A2007 MDBs still have User Level Security.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[David W. Fenton]

Prior to Office 2007, Microsoft had User Level Security which will
do what you desire.

Access 2007 *still* supports ULS. Neither Access nor the ACE removes
support for ULS -- it is only the ACCDB format that lacks it. MDB
format (which is a fully native format for A2007) still fully
supports ULS.

However, keep in mind that the handwriting is on the wall.
Replication is another thing that ACCDB format lacks, and it's been
announced that while A2010 will still support replicated MDBs, the
replication conflict viewer will no longer be shipped with A2010 --
MS's recommendation is "write your own conflict viewer/resolver",
which is a ludicrously inadequate alternative.

My guess is that the version of Access after 2010 will not support
replication in the UI at all (though it will likely still support
replication in code as long as it supports the MDB format at all).

By analogy, my surmise is that Jet ULS is likely to lose UI support
in some future version of Access as well, and be in the same boat
(supported in code as long as MDB format is supported).

And overall that means that I would guess that MDB format in general
is out the door completely 2 to 3 Access versions from now.

What is the alternative?

Well, database passwords certainly aren't.

A SQL Server back end provides you back-end security (and much
better security than Jet ULS), but at a higher level of complexity.

It looks to me like Sharepoint is the middle-ground data store for
security, and I hear rumors that it has good security (it's SQL
Server behind the scenes), but information on the details of
implementing that is rather hard to find.

 

[David W. Fenton]

4. Disable the shiftkey bypass in the backend so they can't bypass
the autoexec.

....unless, of course, they know how to re-enable it in code.

 

[Joan Wild]

....unless, of course, they know how to re-enable it in code.

One part you snipped:
"There are a few things you can do to throw up road blocks, but the
determined can get past them"

so yes, of course, a road block is only just that

Joan Wild

 

[ErikFM via AccessMonster.com]

Thank you all for your excellent advice. This truly is the best Access forum
on the web.

A fellow analyst uses this solution for his databases:

1. An Excel file which requests a password. The correct password turns on the
Enable ByPass setting the MDB file.

2. Creating the AutoExec close database macro in the backend.


One thing though... with each of these solutions, how can I be sure that my
front-end users will still have access to the back-end data?

Also, if I have 100+ users (Account Managers across several branches in a
VPN/WAN), is User-Level security a pain to set up?

And finally... has anyone ever figured out how to prevent Access users from
just importing everything into another database? Although, that might not be
very desireable, in case the database gets corrupted.

Thanks again! You guys are great.

I think I may search for some low-level questions to answer, just to 'pay it
forward'. =)

 

[Arvin Meyer [MVP]]

For a hundred users, it may take a day, or slightly longer, to set up
security. I'd create a spreadsheet with all the users and groups and use
that to create a set of users and groups in a database. Access 2003 has a
security manager that makes the job much easier, so you may want to build
the database on that. If you want to keep everyone out of the back end, you
have only 1 choice, and that is to use: WITH OWNERACCESS OPTION queries, you
can do that by turning on Owner in Options, and copying and pasting the SQL
in the new query formed. Then remove all permission for everyone except the
owner on the backend.

Download the references that I mentioned in my earlier post. They are
invaluable.

If you follow the instruction in the Security FAQ precisely, you won't have
any significant problems.

If the back end is secure, they can import the file, it's still secure and
they don't have permissions except through the front-end. If you really
think that users will attempt to steal or destroy data, you need to rethink
whether or not you want to keep those users. Access, like anything else
isn't 100% secure. Other databases, like SQL-Server do have more robust
security, and you may want to investigate them. There are free versions of
SQL-Server.

Back up the secure files, the same way you backup and restore any other file
on your computer. Do restore every now and then to make sure your backup
plan works the way you expect.

One last thing. The only really safe ways to use Access over a VPN are to
either create an ASP(.NET) front-end, or to use terminal services. There is
too great a chance of dropping a connection and corrupting a file, and other
way.

 

[David W. Fenton]

A fellow analyst uses this solution for his databases:

1. An Excel file which requests a password. The correct password
turns on the Enable ByPass setting the MDB file.

2. Creating the AutoExec close database macro in the backend.

One thing though... with each of these solutions, how can I be
sure that my front-end users will still have access to the
back-end data?

The bypass keys are relevant only to opening a file in the Access
user interface. Your back end should be used by your users only via
your front end, so if you turn of the bypass keys in the back end,
users won't be able to view the data by opening the back end in
Access.

But it has no effect on retrieving data from the back end via
Jet/ACE for use in your front end (or in any other application,
e.g., Excel).

 

[David W. Fenton]

One part you snipped:
"There are a few things you can do to throw up road blocks, but
the determined can get past them"

so yes, of course, a road block is only just that

I don't consider it a security measure, just something you do to
keep out the well-intentioned and incurious.

 

[Joan Wild]

I don't consider it a security measure, just something you do to
keep out the well-intentioned and incurious.

Exactly

Joan Wild