Last edit: 05-09-18 Graham Wideman
|
Last edit: 05-09-18 Graham Wideman |
Microsoft Access |
| Microsoft Access Permissions Explorer Article created: 2005-09-01 |
| 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.
| Microsoft Access Security Apparatus | Extended description of the Access system of users, groups and permissions |
| Other References | ....with some commentary |
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. |
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.
| 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. |
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!).
(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 | |||||||||||||||||||||||||||||
| Permissions Create..DBAdmin |
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:
In general, the Permissions columns are labelled as follows:
But Some Permissions Involve Multiple Bits!
See Access, Jet or DAO documentation for Permissions property for details on what each
Permission does. |
Mouse-over to see details. Right-click menu to set. |
|||||||||||||||||||||||||||||
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 |
| MSAccess_PermExpl_1.1.1.zip | 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:
