Last edit: 08-08-15 Graham Wideman
|The Microsoft Access Security Apparatus
Article created: 2005-09-01
|2008-08-15||Made the Basic SID diagram and text a little clearer.|
|2006-10-25||Corrected "system.mdb" to "system.mdw" (Thanks to MJ)|
|2005-09-18||Original public release|
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.
|Other References||....with some commentary are here.|
|Permissions Explorer Tool||To get a thorough understanding of Access/Jet users, groups and permissions, you may be interested in my PermExpl permissions explorer tool.|
"Security" relates to two main goals:
|Data Integrity and Quality||Prevent accidental or deliberate changes that introduce incorrect or conflicting data, and maintain confidence in how the data got to its current state. Auditability and traceability.|
|Confidentiality||Protection of proprietary or private personal data from being viewed by unauthorized parties|
With respect to MS Access the following topics will be of interest:
|Users, Groups, Permission||"User-level security" or "Workgroup-file-based security": Governing who is permitted to view, add, edit and delete database objects (tables, queries etc), and to edit those permissions for other users. This is the main topic of this article.|
|"Share Level" (single password) security||As a simpler alternative to Users Groups and Permissions, you can simply set a password on the database. Anybody who needs access to the database has to have that password, and once they are "in" they can do anything they want. This topic is not covered further in this article.|
|Access to database files in the file system||Who can copy, delete and view database files using programs
other than Access. This concerns Windows user management and control of file system
permissions on directories and files. In most cases, for Access or a Jet-based
application to use an Access database, the (Windows) user needs to have the file system
permissions that also allow them to do whatever they want with the files.
This could include investigating database files with a hex editor or with cracking tools, and this sets a not-very-high upper limit on the level of absolute security that can be achieved. Only in scenarios where the user is using a remote UI do they get to read/manipulate data without direct access to the database files. (For example, the user interacts with forms provided by a web server, which uses an Access database on the server; Or perhaps Access forms application accessed through a Windows terminal server session that permits no other activity.).
This topic is not covered further in this article.
|Database encryption||By default, Access mdb files are unencrypted... you can just
drop them into a text editor such as Notepad, or better yet a hex editor, and read interesting
data from them. (They could be edited in the same way, but skill would be needed to make
edits that Access could later digest.) Access (and Jet) provide the option to encrypt the
database, which prevents casual snooping of that sort.
However, the encryption is not very strong, so it's readily crackable. The MSDN article "Exploring Microsoft Access Security" says "Jet uses a very weak method of encryption and should never be used to protect sensitive data." This is basically saying: Do not use the Microsoft Access product to store sensitive data, if bad guys have access to the mdb files. This is not completely damning -- many many useful scenarios can be implemented that don't feature bad guys, or that don't allow the bad guys access to the files.
|VBA code security||This concerns preventing users from scrutinizing your VBA project, or simply preventing them from altering it. This can be achieved using module passwords. This topic is not covered further in this article.|
|MDE files||If you create a complete application in Access (combining say forms, reports. tables and queries), you can convert it into an MDE file, which compiles all the VBA code, and turns the database into more of a standalone application. In the process, this prevents users from browsing the code. This topic is not covered further in this article.|
|Code/database signing||Like other Office applications, Access 2003 debuted increased paranoia about malicious code. To allow running with a "Macro Security" level other than Low, you can digitally sign your VBA projects (maybe the entire database?). My personal view is that the Office approach to code signing is such a shambles that it's only worth hassling with this if there's a serious potential of accidentally running malicious Access databases. This topic is not covered further in this article.|
More elaborate detail can be read in The MSDN article Exploring Microsoft Access Security which I critique here.
First things first, it's important to understand the players in the scene.
|"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:
What do they provide? It's all about SIDs.
Determining which workgroup file is the one in use
Control of users and groups
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
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.
|MS Access User Interface||The Tools > Security menu in Access leads you to dialogs that allow you to manage users and groups, and to manage permissions on all database objects. Unfortunately, for any real-world database, this system of piecemeal poking around makes it hard to determine the current state of all settings, and to methodically set them. But it does provide at least a lowest common denominator approach.|
|User Level Security Wizard||Tools > Security > User-Level Security Wizard This automates much of what needs to be done to apply relatively generic security to a workgroup and database. However, it very much glosses over the details, so if you have particular requirements it may be difficult to know how to achieve them with the wizard.|
|DAO||The Data Access Object (DAO) library provides a VBA (or COM for other languages) interface direct to the Jet engine. The DAO object model (and the DAO Developer Help in file DAO360.CHM in Access installations) provides the clearest view of the structure of the security apparatus. DAO's objects have properties and methods by which to read and set security directly.|
|ADO, ADOX||The Active Data Object library provide functionality for
accessing a variety of databases (not just Jet), and ADO-eXtension provides additional
functions crucial for administration. The combination allows you to manipulate Jet security.
My view on this is that though ADO is "more recent technology", DAO is more directly pertinent to Jet, and thus a clearer way to approach the problem.
Interestingly, Access 2002 VBA defaulted to providing a reference to the ADO library. Access 2003 VBA added (reverted to?) a default reference to the DAO library and places it ahead of the ADO reference, so that DAO takes precedence. This DAO -- ADO thing creates some confusion because some of the object types in the two libraries have the same name but are different types. On the whole, I take the prominence of DAO in Access 2003 as a sign that DAO continues to be a good way to go.
|SQL||Much if not all security can be set up using SQL commands, even via ODBC apparently. (For details see the Lit Window article referred to here.)|
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:
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.
|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).|
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
If it's not already apparent, certain features discussed so far are named in a manner that's ripe for confusion. In particular:
|Users group||AllUsers||Don't get confused between the table of users shown above, and the group called Users. Access apparently grants the group Users (conceptually AllUsers) permissions on certain system objects in the database that are necessary just for each user to use the database at all, so all users should be members of the group Users.|
|admin user||anybody||In a default installation with no further refinement Access
establishes a default user called admin, with no password required. This is the user that
Access logs you in as when no user/password prompt appears.
This is a most unfortunate choice of name, as this is the user least appropriate to be an administrator in a situation where you want to establish some security. In a no-security-restrictions setup, it is indeed true that admin has permissions on everything. However, for implementing more refined permissions, the way SIDs work for this user makes it completely inappropriate for "administration", as we will see.
So when you see mentions of the admin user, think "anybody".
|Admins group||There is a default Admins group, of which indeed default user
admin is a
member in a default setup.
In a tailored-permissions scenario, the Admins group may or may not be suitable for actual administrators, depending on how you like the way the Admins SID is calculated -- more below.
These are discussed in sections below.
|Feature or Task||Description|
|How Access selects a workgroup file||When a user starts Access and provides a username and password
(or accepts default no-password admin user), Access selects a workgroup file for Jet as
1. If a workgroup file is specified on the command line, use that.
2. Refer to the registry to find out the workgroup file most recently selected (using the "join a workgroup" feature in Access: (Tools > Security > Workgroup Admin).
3. If the user has not changed their workgroup as in (2), then the registry will be pointing to the default system.mdw file.
More details below.
|Default Workgroup file: System.mdw||The default workgroup file is called system.mdw. In earlier versions of Access,
it was located in:
or equivalent, and in more recent versions in:
C:\Documents and Settings\[user]\Application Data\Microsoft\Access
If you lose track of it, you can try using Windows Explorer to search for system.mdw.
(Note that this recent setup makes the default workgroup file particular to the user who is logged in.)
|Set Default Workgroup file||In Access, use Tools > Security > Workgroup Admin
(Choosing a different mdw file = "Join a different workgroup").
NOTE: This is laborious for a temporary change, so see the procedures below before doing this. Also, be sure to note the location of the currently-in-use MDW, especially if it's the default, so you can change back later.
|Start Access with Different Workgroup file||Without changing the selection of default MDW, you can start Access with a
command line that specifies to use a different MDW instead. Example (unwrap for actual use):
|Determine current MDW||You can get very confused if you don't know which workgroup mdw Access is currently
a) You can look at the Tools > Security > Workgroup Admin panel to see the current default workgroup. However, this reflects only the current workgroup file setting for this user in the registry, not the actual workgroup file Access is currently using. The actual in-use mdw may be different as a result of the command-line options just mentioned, so the Workgroup Admin mdw cannot to be trusted as definitive.
b) Instead, open the VBA window (Alt-F11), open the "Immediate Window" (View > Immediate Window) and type (or copy/paste) this command:
... which will tell you the path to the mdw currently in use by the Jet engine.
|Check name of user currently logged in||In the VBA Immediate Window, type (or copy/paste) the following
command (assuming one user)
|Feature or Task||Description|
|Forcing the login dialog to appear||Assign a password to user admin|
|Forcing no login dialog||Either:
Remove password from user admin, so Access will automatically login use admin.
Launch Access with user and password on command line
|Provide user and password
on command line
... which you can turn into a desktop shortcut.
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).
In general, Jet calculates SIDs for users and groups using a formula based on some or all of the following details:
|Input to SID calc||Description|
|User or Group name|
|Organization Name||Entered at the time you create a custom workgroup file|
|WID (Workgroup Id) or PID (Personal Id)||Entered at the time you create a custom workgroup file
(WID) or create a new user or group (PID). These are not really "Id"s in the normal
sense. These are simply additional "encryption seeds" to make the resulting SID more unique and
harder for someone else's workgroup file to duplicate.
Recommendation: Use long random strings for these to make them hard to guess, and less vulnerable to brute-force attack.
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:
|Inputs to SID Calculation||Implications for SID
(Access 2K and above)
|User or group||OrgName||"Seed"|
|Group||Admins||||-||SID same in ALL DEFAULT workgroups
(But see note )
|Users||-||-||SID same in ALL workgroups|
|GroupX||||PID||SID is unique |
|Users||admin||-||-||SID is same in all workgroups.|
|userX||||PID||SID is unique |
|Group||Admins||OrgName||WID||SID is unique|
|Users||-||-||SID same in ALL workgroups|
|GroupX||OrgName||PID||SID is unique|
|Users||admin||-||-||SID is same in all workgroups.|
|userX||OrgName||PID||SID is unique|
: The best info I could find says that the OrgName value is not used in SID calculations for default mdws in Access 2K and above, but is used in Access 95 and 97. This mostly implies that in Access 2K and above, you can count on permissions accorded to group Admins (of default mdws only!) to be available to all users logged in as Admin members if they are still using their default system.mdws. This seems like a rather unrobust idea.
: A default workgroup is one created by an initial Access installation. (Or maybe an initial Jet installation, since Jet can be installed without Access.)
: ... but not quite as guess-proof as in a custom mdw, as the OrgName is not included in the calculation -- which seems rather inconsequential all things considered.
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.
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
The user can read the table definition, including column and index information.
The user can modify or delete the table definition, including column and index information.
The user can retrieve data from the object.
The user can add records.
The user can modify records.
The user can delete records.
There are further permissions for the Databases container:
|DBAdmin||The user can replicate the database and change the database password.|
|DBCreate||The user can create new databases. This setting is valid only on the
container in the workgroup information file (System.mdw).
|DBExclusive||The user has exclusive access to the database.|
|DBOpen||The user can open the database.|
Jet itself doesn't know about Access forms and reports, but is happy to store them along with permissions. These Access-specific permissions are:
|User or group can|
|FrmRptReadDef||Open the form or report in Design view but not make any changes.|
|FrmRptWriteDef||Modify or delete the form or report in Design view.|
|FrmRptExecute||Open the form in Form view or Datasheet view; print or open the report in Sample Preview or Print Preview.|
|MacReadDef||Open the Macro window and view a macro without making changes.|
|MacWriteDef||Modify or delete the macro in the Macro window.|
|ModReadDef||Open the module but not make any changes.|
|ModWriteDef||Modify or delete the contents of a module.|
|MacExecute||Run the macro.|
Docs for these are harder to find, but just pick one of the constants (like "acSecFrmRptReadDef ") and search MSDN library:
"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.
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:
|Plan an initial set of users or groups
that make sense for development
|Typically something like:
almighty: A developer, who will own the database and all objects in it, and be a member of group Admins.
PowerUsers group and an example poweruser user. For testing functions as seen by typical users
The default user admin and default group Users so you can test how much or little these defaults get.
|Establish a new custom workgroup
||Using for example Tools > Security > Workgroup Administrator
Note the settings you use, and path of previous workgroup file.
|Establish your groups and users||1. Make sure that Access has now switched to using the new
workgroup file (see how to in "Controlling and Viewing which MDW is in use" above.)
2. Add the groups and users planned above
3. Log out and log in as almighty, set password
|Create database for your application||While using the custom workgroup, and logged in as almighty, create a new database. This establishes that almighty is the owner of the database (and more importantly, that nobody else is :-)|
|Add Tables, queries etc etc||While using the custom workgroup, and logged in as almighty,
add the objects you want (... so these objects are owned by almighty).
|Grant and revoke permissions to other groups or users||While using the custom workgroup, and logged in as almighty,
grant and revoke permissions, using Tools > Security > User and Group Permissions, or using DAO
or SQL (if you want to be more orderly).
Keep an eye on whether unwanted permissions get granted to group Users, and if so revoke them.
|Continuing Development||As you work on the app, you may not want to continue to have Access defaulting to your custom workgroup file. Instead, you can use Workgroup Admin to switch Access back to the default system.mdw, but when working on your application launch Access with command line arguments to temporarily use the custom mdw, possibly using a shortcut. (See "Controlling and Viewing which MDW is in use" above.)|
|Deployment||When deploying, you will also have to deploy a custom workgroup file. This can be a copy of the one you developed with, assuming nobody knows the password for user almighty. Or you can use a copy of your workgroup file in which you delete user almighty.|
And of course, as you proceed, you may want to perform some reality checks using PermExpl.