gw_logo_08.gif (1982 bytes) 
Last edit: 05-09-18 Graham Wideman
Microsoft Access
Microsoft Access Permissions Explorer
Article created: 2005-09-01

Document Status
Date Description
2005-09-17 Update for V 1.1.1
2005-09-15 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.

This apparatus can be very useful in MS Access scenarios ranging from informal to elaborate, allowing expert or privileged users to perform delicate operations on the data while more casual users still gain the basic access they need.

The apparatus is reasonably sensible and comprehensible, except that few good tools exist for viewing and manipulating security settings, and documentation is rather sparse. Consequently its operation has remained mysterious, and in books, articles and across the web, it has accumulated more than its share of myths and outright mistaken understandings.

Some years ago, I wrote the tool described here to get to the bottom of all this.  It's been pretty useful for me, so here it is for a wider audience.

Related Articles

Microsoft Access Security Apparatus Extended description of the Access system of users, groups and permissions
Other References ....with some commentary

What PermExpl does

Simply put, PermExpl's function is to allow you to view and set two main kinds of information:

Users and Groups Shows all Users and Groups in a workgroup file, with a grid that displays and allows adding or removing users and groups, and setting which users are members of which groups.
Permissions Displays all objects in a database (mdb file), and displays and allows setting what permissions each user and group has on each object.


This tool is primarily intended to help understand how Access security behaves, rather than for frequent use on large production database.  It can be used to inspect the current state of a database or workgroup file, for example to assess the action of some Access UI action, a SQL permissions command or DAO function.

Danger! Since you can use PermExpl to make very dangerous changes which can render the database unusable, please use it only on databases for which you have a backup!
Tedium! PermExpl has not been extensively exercised with production-scale databases, which might well contain large numbers of database objects along with large numbers of users. The Permissions grid in particular is likely to grow quite large  [rowcount ~= objects x  (users + groups) ], which might slow it down considerably.

How to Use

First, download and install, as described at the bottom of this page. Launch by double-clicking PermExpl in Windows Explorer.

Then follow the screenshots and instructions below.

Database/Login Panel

Step Discussion
Database Type in, paste or browse to an Access database (mdb)
Workgroup Type in, paste or browse to an Access workgroup file (mdw)
UserName Enter an Access user name. To login as the default user for databases that normally require no login, use "Admin"
Password Enter password
DAO/Jet/Access version Choose version appropriate to the database and workgroup file. If in doubt, use DAO 3.6.
Next time... For quick reuse over and over during development and experimentation, PermExpl remembers recent settings in PermExpl.ini in the application's directory. You may want to delete this file to avoid saving passwords in the long term.

Top Panel

Various user actions will prompt PermExpl to connect to the database and resurvey the security info. To explicitly force PermExpl to resurvey (for example after you make changes to the database or workgroup in Access), hit the Refresh button.

Note that PermExpl does not stay connected to the database or workgroup file.


Task Discussion
View This panel allows you to view the workgroup file's users and groups, and memberships.
Add users or groups Use the button
Delete users,
change memberships
You can right-click to get a popup menu that allows you to delete users and change memberships.
Show/Hide system objects Use the Hide checkbox to hide system objects, in this case the system users Creator and Engine

CAUTION: Think carefully before removing users from the group you are using for administrators (possibly Admins, but often not), and especially before removing users from Users. (Or use some disposable database for experimentation!).

Permissions Panel

(Reminder: Use the Refresh button to update this view if you make changes to the database by some other means, like in Access or via your own program.)

Task Discussion
View This panel allows you to view the permissions specified in the database (MDB) for the users specified in the workgroup (MDW). (Subtlety: the database may actually specify more permissions for other users not in this workgroup -- these cannot be inspected and hence do not appear in this table.)
Adjust columns Float the cursor over the boundaries between the left columns to get resize cursor.
Sort Use the radio buttons to sort in order of Container-Object, or Group and User
Reduce clutter Use the checkboxes to hide system objects (system users and system tables etc). Also you can hide rows which have no permissions.
Copy table Use the Copy button to copy the entire table in a format that pastes conveniently into Excel.
Choose a particular cell Float cursor over cell to see hint that clarifies object and user or group. Also shows the hex mask needed to enable or disable this particular permission.


Change single Permissions Right-click on a permissions cell, and select one of the editing choices (as in screenshot).
Change Permissions bitmap Right-click on Perm Exp column, choose "Enter Hex Permissions"
Change Owner Right-click on the Owner column in the row of the new owner, choose "Set New Owner"
Flip Container Inherit Right-click on Cnt Inherit column, select "Flip Inherit Property"

Column Meanings

Column Meaning Features
Num Row number Simply helps you keep track of where you are in the table
Container Kind of Access object, such as Tables, Queries etc  
Object Name Specific table, query etc name  
User/Group U = User, G = Group that this row provides permissions for  
UGName Name of user or group  
Perm Exp Binary bitmap of permissions explicitly granted to this user/group. Corresponds to the Permissions  property. Right-click menu to set all/none or enter in hexadecimal
Perm All Binary bitmap of all permissions explicit or inherited. Corresponds to the AllPermissions  property.  
Cnt Inh Container Inherit property.  Specifies whether new member objects (eg: tables in Tables container) will be given a default set of Permissions based on the container's Permissions property Right-click menu to flip
Owner Is the user on this row the owner of this object? Right-click menu to change
Perm Exp and Perm All interpreted.  One column for each permission type.

Revised V1.1.1:  These columns tell you whether the permissions bitmap contains the bits associated with the DAO constant whose name corresponds to the column head. Each object has two permissions properties:

object.Permissions Permissions explicitly set for this object
object.AllPermissions object.Permission plus permissions inherited by user
from a group that user is member of

In general, the Permissions columns are labelled as follows:

blank No permission
E Explicit permission
I Permission enabled in AllPermissions but not in Permissions, hence must be inherited

But Some Permissions Involve Multiple Bits!

For example, the RetrieveData column corresponds to DAO constant dbSecRetrieveData, whose value is 0014 hex. This contains two "one" bits, 0010 which controls reading of data specifically, and 0004, which is the value of dbSecReadDef. Why two bits? Because in general an app that needs to RetrieveData often needs to be able to read the table definition, so the constant for "enable RetrieveData" includes the bit to enable reading the table definition as well.

So, when enabling such permissions, you will notice multiple columns affected.

[Revised V1.1.1]  These kinds of columns need special indications when their requirements are partially fulfilled. In general, this is handled in PermExpl by using the lower-case letter ("i" or "e").  There is one final case that's a problem: partly enabled explicitly, and fully enabled by inheritance. In that case, PermExpl uses a "J", to mean "a bit more than "I".

The complete table of possibilities appears here:

None Part Full
None blank i I
Part e e J
Full E E E

See Access, Jet or DAO documentation for Permissions property for details on what each Permission does.

To see the value of the Enable and Disable constants involved, float the cursor over the cell of interest, and view the hint. The "Enbl" constant provides the bits for "full enablement", and the "Dsbl" constant provides the specific bit for this narrow function.

Mouse-over to see details. 
Right-click menu to set.

Database Info Panel

A collection of miscellaneous info...

(Reminder: Use the Refresh button to update this view if you make changes to the database by some other means.)

Information Section Description
In Current Workgroup MDW This summarizes how many permissions there are in the database MDB, for the users/groups in the currently selected workgroup MDW file.
MDB MSysACEs Table This summarizes the permissions as listed in the database MDB. The non-zero permissions in the MDB may be more than those attributable only to the users/groups of the current MDW, as some may concern users defined in other MDW files, as is the case in the screenshot.

Ie: at some previous time, the database MDB was opened in conjunction with a different workgroup MDW, and some permissions granted to those "foreign" users. This is important to know if assessing whether there are permissions you don't know about.

NOTE: You may see a message saying that MSysACEs table is not accessible. This is likely because you lack RetrieveData permissions on the table MSysACEs. If you are logged in as an appropriate user you can use the Permissions Panel to grant yourself permission, and then Refresh.
"Properties" Hit the "Read" button to read the Properties property for DBEngine, Workspace and current Database. (This function often take several seconds to complete -- I don't know why.)


Download Item Version Date Description/Release Notes 1.1.1 2005-09-17 Revised Permission columns display to cover "partly enabled" situations
  1.1.0 2005-09-15 MS Access Permissions Explorer application
First public release


Step Description
Download .... from this page
Unzip Using WinXP Windows Explorer, or WinZip. Copy contents to some convenient folder, perhaps:
c:\Program Files\MSAccess_PermExpl
Note: this directory should be writable, as PermExpl saves recent settings in an INI file.
Shortcuts Optionally create a shortcut on desktop or in Start menu in the usual way, by Alt-Dragging the executable file from Windows Explorer.


Go to:  gw_logo_08.gif (1982 bytes)