Last edit: 08-08-15 Graham Wideman

Access

Overview

The Microsoft Jet database engine (and hence Microsoft Access) has a quite elaborate apparatus for establishing Users and Groups, and controlling the permissions they have for performing operations (such as read, write, create) for various database objects such as tables and queries. 

However, the modest complexity of this apparatus is not done justice by the user interface provided by Access, nor by official documentation, and in general the apparatus is widely misunderstood and thus unused or misused.  This is a shame because the apparatus is conceptually quite similar to that of many other databases. So if only it was exposed better and described in more coherent fashion, it's a branch of knowledge that's readily transferable to other database scenarios.

It should be said initially that for actual prevention of data theft or meddling, Access's encryption scheme is not very secure, and this perhaps contributes to some lack of determination to clarify its use. (While Access' encryption could be stronger, to a certain extent any database scheme where the actual database file is exposed to the users would be similarly vulnerable).

But that said, as a mechanism which the developer can use for coercing non-hostile users or programs to perform only safe operations, maintain data integrity and the like, it can be quite effective.

This article describes the apparatus used in Access from version 95 through 2003, which has seen only minor changes along the way.

Related Articles and Tools

What Does "Security" Refer To?

"Security" relates to two main goals:

With respect to MS Access the following topics will be of interest:

More elaborate detail can be read in The MSDN article Exploring Microsoft Access Security which I critique here.

Structure of Access and Jet Scenarios

First things first, it's important to understand the players in the scene.

Main points:

	"MS Access Environment" has two pieces	What you initially perceive as the Access environment is actually composed of two parts: the Access application that provides a user interface, and the Jet database engine that handles interpretation of SQL and interaction with database files. This is important because the Jet engine (and hence database files) is available for other applications to use via the DAO library, and also via ODBC. All of these mechanism interact with the users/groups and permissions system in a uniform way, for the most part.
	blue = "out of the box"	The blue items above correspond to what's initially set up when you install Access. (Actually, other apps may install the Jet engine, even if you don't have Access installed).
	"Security" is always active and involves one or another workgroup file.	Jet always employs one or another workgroup file.  This is true even in the initial installation where security appears disabled and a username and password are not required.

Mastery of this apparatus involves the following parts:

	Understanding workgroup  (mdw) files	What do they provide?  It's all about SIDs. Determining which workgroup file is the one in use Control of users and groups
	Understanding permissions in a database (mdb)	Permissions are granted to SIDs, not users or groups per se What permissions are needed to do what. What permissions must be eliminated to prevent what Ownership of database and objects in it Access's default setup involves certain default users and groups, and their permissions Tractable procedure for eliminating default permissions, and setting up desired permissions

Several Alternative Methods For Manipulating Security Features

There are several methods for manipulating security settings. All ultimately adjust the same settings in the workgroup file or database file, but one or another method may be more suited to your development process or expertise.

Basics of SIDs (Security Identifiers)

To understand Jet security, you absolutely have to get your mind around SIDs. The following diagram sketches it out.

The diagram shows a simple scenario involving a database mdb containing a single table and a single query, and a custom workgroup mdw with the default users and groups, plus users fred and mary. Highlighted in blue are the SIDs attributable to mary -- her own explicit SID, plus the SIDs of the groups she belongs to.

Conceptually, when mary logs in to Jet (via Access or some other application), Jet collects up the SIDs for mary. Thenceforth during that session, Jet uses those SIDs to determine what she is allowed to do with each object, according to the ObjectPermissions table (a system table really called MSysACEs) in the database. (E = explicit permission, I = permission Inherited from a group that this SID/user is a member of). 

We can see, for example, that for MyQuery1, mary has:

• Permissions to Read ("retrieve") data, inherited from SID_06
• Permission to Write, explicitly given to her (ie: to SID_05).

Supposing that MyQuery1 SELECTs data from MyTable1 -- is mary allowed to do that? By virtue of her membership in the group Users (SID_02), she is indeed.

Workgroup Files and SIDs -- Main Points

	MDW Workgroup File; Users and Groups	Jet (and hence Access) always employs a "Workgroup" file, in which are defined Users and Groups. In addition to the lists of Users and Groups, there is also a table telling which users belong to which groups. Users may belong to multiple groups, but there is no concept of groups containing subgroups.)    The groups "Admins" and "Users" and the user "Admin" are predefined, and you can add more groups and users. You can log in as a user, and user accounts may optionally require passwords. You cannot log in as a group directly, and thus group accounts don't have use for a password. When Access is installed, the installation sets up a default Workgroup file (system.mdw), but a user or solution developer can set up another (here shown as MyWorkgroup.mdw), and use it either permanently or temporarily. (more on that later)
	Access/Jet users unrelated to Windows users	Just to be clear -- the users and groups defined in a workgroup file are unrelated to Windows user accounts. Unlike, for example, SQL Server, there is no scheme for integrating management of Windows and Access users. (This is reasonable, since you might well want to copy a database and its workgroup file to another machine where completely different Windows user accounts are in effect.)
	SIDs	Jet assigns to each user and group an Id called an "SID". Users are generally unaware of SIDs, as they are hidden and intended to be secure (but more on that later).
	Permissions associate SIDs and database objects in MDB	Permissions are stored in the database file Permissions are granted to SIDs The database file knows about SIDs, not about users or groups
	No MDW - MDB association	Nowhere is it remembered that a particular workgroup MDW is "the preferred one" to be used with a particular database MDB. Neither the database nor any other mechanism (such as the registry)  remembers which MDW(s) were used to create objects or set permissions.  (Though of course there may only be one mdw that provides the necessary SIDs -- but that's up to you or your application to remember).

Implications -- Why we need to know how SIDs are calculated.

You (or someone else) are free to use any workgroup file (mdw) in combination with any database (mdb).  You can use that to advantage to control which users get to do what. But as you grant special permissions to particular SIDs, you have to make sure that someone else can't come along with a workgroup file that might supply those same SIDs, and hence gain access in undesired ways.  And the flip side is that if you want to grant some permissions to absolutely anyone who might receive your mdb, even in the absence of a custom mdw, then you need to know which SIDs are generated the same in all workgroup files.

You might have hoped that each time you create a user or group, Access or Jet would provide a unique SID that someone else can't duplicate. This is true in general, but there are special rules for the SIDs of the default user and groups. These special rules make it feasible to share "no-security" databases freely, and there are ways to use this advantageously even if you do have some security refinement. However they also make it important that you understand the pitfalls of the default user and groups.

Most conspicuously, you can't limit access simply by eliminating the admin user from the workgroup that you happen to be using.

More on this in sections below

Potential Confusions!

If it's not already apparent, certain features discussed so far are named in a manner that's ripe for confusion. In particular:

These are discussed in sections below.

Controlling and Viewing which MDW is in use

Controlling MS Access Login Behavior

Sensible Use of Workgroup files

Let's consolidate some ideas about workgroup files by considering a couple of prominent scenarios.

In many situations, developers decide to implement user/group/permissions security in order to control how or by whom data gets entered into the database, while affording more relaxed read-only access to a wider audience of users.  Examples:

	Expert data entry, "casual" read-only users	A database into which multiple expert users, using Access, input data over a network, while a larger number of assistant users use Access or other tools to read data from the database for summary or reporting.
	Auto data entry, human read-only users	A custom instrumentation application that takes readings and automatically records them into a database file. To ensure integrity and auditability of the data, no human users are allowed to add, edit or delete data, but many users are permitted to read the data.

In addition to the arrangements of users involved, situations vary in terms of how the application is deployed, for example:

	Single shared database on file server	A single database on a file server accessed by many users via a network connection to a file server.
	Multiple independent installations	Multiple copies (perhaps thousands) of an application and its databases, sold to multiple independent customers who each install it on their own individual machines.

In all scenarios that combine these features, the developer will want to establish more-privileged users (typically one or more privileged groups), and less privileged users.  How should the workgroup files be arranged?

For Privileged Users:

First, it is generally undesirable and usually impossible to set up the privileged users using the default system.mdw that each user's Access installation would be employing. To do so would be at the least a great hassle, and would be impossible if each user's Access installation didn't use the same values that feed into the SID calculation (upcoming section details this).

So, as a starting point, scenarios like the above would employ their own workgroup file, which can be as simple as just establishing an mdw file to accompany the mdb file. For the "shared database on server" situation, this mdw could reside in the same directory as the mdb file. For the "multi independent installations" the mdw could again be installed in the same directory as the database file(s), or possibly in the application directory (if user has file-system write permission for that).

To actually run Access  with the custom mdw, see the topic above "Start Access with Different Workgroup file". For a custom application, the programmer can specify in code where to find the relevant mdw file, basing it perhaps on the installation location of the application, or of the database, or an INI file or registry entry configured during installation.

For Less-Privileged Users:

You may want to set up very specific permissions on a per-user or specialized-group basis, in which case use the custom workgroup just described. 

In other situations, you may want to permit anybody to have, for example, read access on some tables, or to have very selective access to data via queries refined using the "Run With Owner Permissions" feature (see help for details). Here you might be quite happy to provide these permissions to user admin. User admin, as we will see, gets the same SID in all workgroup files, and hence all persons using Access, regardless of which workgroup file they are using, will be able to gain those permissions (so long as they logon as the default user admin of course!).  (And at the same time, you don't have to handle giving all those users individual accounts).

How Access (Jet) Calculates SIDs

...and Which SIDs Are or Are Not Unique

In general, Jet calculates SIDs for users and groups using a formula based on some or all of the following details:

Two points immediately jump out:

	Uniqueness	In general,  SIDs are pretty unique, especially if you provide long and obscure WID or PID "seeds".
	To recreate a workgroup file	... is possible, but requires that you know all the above info that was used to create the original workgroup file and its users and groups.

But there's a major additional part of the story concerning the default system.mdw, and concerning the default user admin, and default groups Users and Admins. Indeed, this additional part is what enables your no-security databases to be used by anyone with Access.

The key is that certain accounts have SIDs that are not unique, because their SID formula does not use all of the information described above. Here are the details:

Here we can see marked in pink the basis for Access's default "anything goes" security:

Group "Users", and user "admin" have SIDs that are the same in all workgroup files. Even though you could delete one or the other from a specific mdw, you could not prevent someone from using a different mdw equipped with these default users and groups, and thus having available their SIDs.

So in the process of securing a database, you will need to remove permissions from user admin and group Users, or at least be careful to restrict their permissions to those you want.

Controversial "Admins" Group?

There is some controversy over whether you should avoid using the Admins group. It is certainly the case that you should avoid using the Admins group in a default mdw, as that has the same SID in all default mdws.  However, the Admins group in a custom workgroup mdw provides a unique SID, so should be fine.  Using it may mean less "swimming upstream", but on the other hand, it doesn't so conspicuously reassure that you have avoided accidentally using your default system.mdw's Admins account.

A Brief Look At Permissions

After all this talk of how to set up users and groups, what kinds of permissions do you get to set?  The following table is copied from the DAO Help, relating to the Permissions property on database objects, corresponding to constants dbSecBlahBlah. You can find documentation on these in Access help, or online http://msdn.microsoft.com

There are further permissions for the Databases container:

Jet itself doesn't know about Access forms and reports, but is happy to store them along with permissions. These Access-specific permissions are:

Docs for these are harder to find, but just pick one of the constants (like "acSecFrmRptReadDef ") and search MSDN library:

http://msdn.microsoft.com

Ownership

"Ownership" is a concept that's intimately related to permissions, but slightly separate. Each object in a database, and the database itself, have an "owner".

	Original Owner	... of an object is the user logged in to Access (or Jet) who created the object.
	Changing Ownership	... can be done using Access (Tools > Security > User and Group Permissions > Change Owner.  You cannot, however, change the ownership of a database itself (which has significant implications, discussed below).
	Ownership permissions	Although it's possible and often useful to revoke various permissions of the user who happens to be the owner of an object, the one permission that the owner always retains is ability to reinstate permissions on the objects they own.

All of this implies that regardless of how careful you are about setting permissions, you must also ensure that it's a responsible user (often the developer) who is accorded ownership of objects (presumably not default user admin!).  This is especially tricky in the case of the database itself. A project that started in ad hoc fashion might well have had the database created by user admin. This can't be changed -- which is why advice about securing Access databases usually has a step for copying all the objects to another database owned by a user you set deliberately.

Finally - Setting up a Secure Workgroup and Database

Yes, I too will weigh in on the series of steps I think are needed -- not because other authoritative sources are wrong, but rather because it seems to me that other sources sometimes treat this as a rote exercise, obscuring what you are really trying to achieve. So here is a list of steps, if you are starting from scratch:

And of course, as you proceed, you may want to perform some reality checks using PermExpl.

Go to: