Access security model

[Charles Ulrich]

Greetings,

Although my day job is typically working with PostgreSQL and MySQL
databases, I've recently been tasked to design a DB in Access. One of the
requirements of the design is that they want real user- and group-based
security. This is my first time using Access, and after skimming through a
few books on the topic, I thought it would be up to the task.

I've read countless articles (including the famous Access Security FAQ) and
I'm now well familiar with the concepts of groups, users, workgroup
information files, WIDs, PIDs, and the like. But there's just one thing
that hasn't yet been directly answered: If I create a DB on one machine and
forbid all but the necessary users from opening it, is there anything
stopping an unauthorized person from merely copying the *.mdb file off the
file server onto their laptop or some other machine and then opening it in
their copy of Access?

If the answer is yes, how? And how does Access link the users, groups,
permissions, etc in a workgroup information file to a particular database?
(Near as my research has shown, the two are nearly completely separate. I'm
still trying to figure out why the workgroup information isn't stored in
the DB itself like every other DB on the planet.)

I have seen people mention that you have to remove all rights from the
built-in Admin user as well as the Admins and Users groups. I have done
that and I can still open the DB, read it, modify it, and all on a machine
with a fresh Office 2003 install and no custom workgroup information file.
This is not exactly what my customers want. :/

If anyone can shine some light on this dreary subject, I would most
appreciate it. Thanks.

Charles

 

[Joan Wild]

Hi Charles,

If I create a
DB on one machine and forbid all but the necessary users from opening
it, is there anything stopping an unauthorized person from merely
copying the *.mdb file off the file server onto their laptop or some
other machine and then opening it in their copy of Access?

If they are able to open it using their copy of Access, they'd be doing so
using the standard system.mdw workgroup file. If you've secured it
properly, they won't be able to even open it.

If the answer is yes, how? And how does Access link the users, groups,
permissions, etc in a workgroup information file to a particular
database?

The workgroup information file is not linked to a database; nor is a
database linked to a single workgroup file. You could have multiple
databases use a single WIF and a single WIF can be used with multiple
databases. Access uses security in every session. It ships with a WIF
called system.mdw, and uses that as the default for every session. You are
silently logged in as a user named 'Admin'. Since every system.mdw is the
same, that's why you are able to give someone else an unsecured mdb and they
are able to open it - the Admin user is the same in everyone's system.mdw.

The WIF comes into play when Access is first opened. It contains the
usernames/passwords/groups/memberships. So a user is authenticated when
they login using the WIF. The actual permissions that a user or group have
on an object is defined with the object (i.e. in the mdb).

I have seen people mention that you have to remove all rights from the
built-in Admin user as well as the Admins and Users groups. I have
done that and I can still open the DB, read it, modify it, and all on
a machine with a fresh Office 2003 install and no custom workgroup
information file. This is not exactly what my customers want. :/

You need to follow every step in the FAQ; if you miss even one step you
could end up with an unsecured mdb. Actually the security wizard in 2003
isn't bad, as long as you're happy with the built-in groups it offers.

Have a look at www.jmwild.com/AccessSecurity.htm

 

[TC]

"Dreary"? No way! Who needs Indiana Jones when you can read the FAQ
instead?

I'll add one thing to Joan's comprehensive reply.

There is indeed a small link between a database, and the workgroup file
which was in effect when that database was first created. The database
does /not/ know the name or location of that workgroup file, but it can
tell (by secret magic behind the scenes) whether the /current/
workgroup file /is or is not/ the workgroup file that was in effect
when the database was first created.

If the current workgroup file /is/ the workgroup file which was in
effect when the database was first created - the members of the Admins
group have administrative permisions to that database. They can do
whatever they want in it. But if the current workgroup file is /not/
the workgroup file which was in effect when the database was first
created - the members of the Admins group do not have those special
permissions.

Otherwise, you could break in to a secured database by the simple
expedient of creating a new workgroup file, then logging on as the
Admin user (who at that point does /not/ have a password and /is/ a
member of the Admins group) of that new workgroup file. This doesn't
work, because Access /knows/, by looking inside the database file, that
the new workgroup file is /not/ the workgroup file which was in effect
when the database was first created.

HTH,
TC