Frequently Asked Questions About Microsoft Access Security for Microsoft Access versions 2.0 through 2000

Version 2.42 October 2000

By Mary Chipman, Andy Baron, Chris Bell, Michael Kaplan, Paul Litwin, and Rudy Torrico

Introduction

This FAQ was written originally to cover Microsoft Access versions 2.0 through 97. However, the basic concepts remain unchanged for Access 2000. Almost all of the FAQ items that apply to Access 97 also apply to Access 2000. If you have any questions or comments after reading the FAQ, please post them in the access.security section of the Microsoft public newsgroups (msnews.microsoft.com) or contact Microsoft Access Technical Support.

Table of Contents

1. What are the steps to help protect a database?
2. In a nutshell, how does Microsoft Access security work?
3. What has changed in Microsoft Access security between Microsoft Access 2.0, 95, 97, and 2000?
   3.1  Table 1: DAO Security Constants
4. How can I set a single password on my database?
   4.1  Database Password Bugs
5. How can I clear a user's forgotten password?
6. What's the best way to convert my security-enhanced Microsoft Access application to the latest version of Microsoft Access?
7. What's all this about a security hole in Microsoft Access 2.0?
8. How can I help protect just my code without users having to log on
9. How do I delete the Admin user?
10. How do I implement field-level or row-level security on my tables (RWOP or queries with Run Permissions set to Owner's)?
11. Why can't I get my RWOP Append Query to Run?
12. What is the difference between an "attached" table and a "linked" table?
13. How do I manage linked tables using Microsoft Access security?
14. What permissions are necessary to update table links?
    14.1  Using RefreshLink to relink tables
    14.2  Using TransferDatabase to relink tables
    14.3  No Permissions necessary - Using the .Connect property to relink tables
    14.4  Error messages
15. Why do users require permission to create new tables in the destination database in order to update a table attachment?
16. What happens when the front-end database permissions on an attached table differ from those in the back-end database?
    16.1  Creating New Links
    16.2  What permissions should you set for <New Table/Queries>?
    16.3  Updating or Refreshing Existing Links/Attachments
    16.4  Synchronizing Permissions
17. How can I retrieve the "most restrictive" permissions for attached tables?
18. Can I prevent users from linking tables?
19. How do I work with a security-enhanced application and an unsecured application at the same time?
20. How do I keep users from viewing Code Behind Forms?
21. How can I tell who is logged on to my shared, networked application?
22. How can I obtain group and user membership information programmatically?
23. How can I obtain the groups that the current user belongs to without hard-coding an Admins ID and password in the code?
24. How can I help prevent users from creating new objects in my database?
25. How can I help prevent users from updating any tables by any means other than through forms?
26. How can I help protect some parts of my application (an add-in), yet make others totally open to any Microsoft Access user?
27. How do I prevent users from holding down the SHIFT key to bypass the AutoExec macro?
28. How do I prevent a run-time application from being opened in full retail Microsoft Access?
29. Does Microsoft Access security still work if I use OLE automation or Microsoft Query to manipulate Microsoft Access tables?
30. How can I use the Security Wizard without creating an encrypted database?
31. When I use the Security Wizard in Microsoft Access 2.0, it runs to 99%, and then freezes
32. I thought I helped protect the security of my database, but someone opened it with his or her own workgroup file. Is Microsoft Access security broken?
33. I want to create a remote site administrator able to administer the database and add user accounts but not alter permissions on database object
34. How can I "de-secure" a database?
35. I lost/forgot my password and can't get into my database
36. Do I need a separate workgroup file for every database I develop for my department?
37. How do I use DAO to manipulate permissions?
38. I created a user in code but the user isn't in the Users group and can't start Microsoft Access.
39. I created a user and I can't log on as that user
40. I ran the Security Wizard but users from another workgroup can still open the database
41. How do I help implement security when I am using Visual Basic as a front-end?
42. Do I need to use a System.mda when I'm using Visual Basic to control security-enhanced objects?
43. How do I open a password-protected database from Visual Basic?
44. How do I open a report in a security-enhanced Access database from Visual Basic?
45. What about using ADOX or ADO to programmatically manage security?
46. How can I open a database in code that was encrypted using another workgroup file?
47. Additional Sources of Information

1. What are the steps to help protect a database?

The process to help protect a database is the same, no matter which version you use. The only differences are: beginning with Microsoft Access 95, the Security Wizard is built into the product and in Access 2000 the Security Wizard can perform all of the steps for you, including creating a new workgroup information file. You may elect not to use the Security Wizard and to help protect the database manually by following these steps.

1. Use the Workgroup Administrator program (Wrkgadm.exe) to create a new workgroup information file. Write down the Name, Organization, and WorkGroup ID strings that you will be prompted for when you create your new workgroup information file and store them in a safe place. If your workgroup information file ever becomes lost or corrupted, you can reconstruct it by using these identical strings, which are then encrypted to create a unique token. Without a valid workgroup information file, you could conceivably be locked out of your database forever. Another reason to save this information is for upgrading an encrypted Access database to a newer version of Access. The recommended path for upgrading databases is to re-create the workgroup file in the new version of Access before upgrading the database itself.
2. The Workgroup Administrator automatically switches you to the new workgroup information file. Start Access, and open any database.
3. You will be logged on as a user named Admin. Use the Security menu options to add a password for the Admin user. The Admin user is the default account, and setting its password is what causes Access to prompt for a logon Name and Password the next time that you start Access.
4. Create a new user, which is the account you will use to help protect the database. Add this new user to the Admins group. Write down the strings that you use for the name and PID in case you ever need to re-create your workgroup information file. The PID is not the password-the string used for PID is encrypted, along with the string used for the Name, to create a unique token (SID, or system identifier) identifying the user.
5. Quit Microsoft Access and log back on as the new user account that you created in step 4. You will not have a password for this account yet, (the PID you typed with the name in step 4 is not the password), so now is a good time to set one.
6. Remove the Admin user from the Admins group so that Admin is a member only of the Users group. The Admin user account has no administrative powers built into it; they are derived from membership in the Admins group, which does. Although you cannot delete any of the built-in users or groups (Admin, Admins, and Users), you can move users to and from the Admins group and restrict permissions to the Users group.
7. Open the database that you want to help protect and run the Security Wizard. Select the objects that you want to help protect (it makes sense to select them all). The wizard will then create a new database owned by your new user, and will import all of the objects and relationships into it. It will also remove all permissions from the Admin user and the Users group and encrypt the new database. The original database will not be altered. Note that the Access 2000 security wizard does not create a new database-it simply creates a backup copy of the original. One flaw with this arrangement is that not all permissions to open the database are removed from the Admin user and Users group to open the database, even though they appear to have been removed.
8. Open the new database. Because the Security Wizard removed all permissions from the Users group for the security-enhanced objects, you need to create your own custom groups and assign the level of permissions needed to these groups. Every user is required to be a member of the Users group (otherwise, a user would not be able to start Microsoft Access), so only grant permissions to Users that you want everyone to have. Members of the Admins group have irrevocable power to administer database objects, so make sure to limit membership in the Admins group to only those users who are administrators.
9. Create your own users and assign them to the groups that reflect the level of permissions that you want them to have. Do not assign permissions directly to users because that is extremely hard to administer. Users inherit permissions from the groups they are members of, and keeping track of the permissions assigned to a group is much easier than keeping track of the separate permissions of individuals. If a user is a member of multiple groups, then that user will have all of the permissions granted to any of those groups plus any permissions assigned specifically to the user (this is known as the "least restrictive" rule). There is no way to deny permissions to a user if that user is a member of a group that has been granted those permissions. If you need to create specific permissions for only a single user, create a group for that user and assign the permissions to the group; then, add the user to the group. The reason for this becomes clear when you consider that the user may quit, and you may have to set up permissions for the replacement on short notice.
10. Additionally, you may need manually to remove the Open/Run permission from the database container for the Users group through the security menus or through code. This will prevent someone from opening the database by using another workgroup information file or the default System.mda/mdw. In Microsoft Access 97, the User Level Security Wizard is supposed to remove the Open/Run database permissions for the Users group, but fails to do so. The Access 2000 Security Wizard removes permissions to the point where they are not visible on the security menus, but testing has revealed that in Access 2000 it is possible to open a database by using the default workgroup information file regardless of the menu settings. The cure for both versions of Access is to create a new, empty database while logged on as a member of the Admins group and import all of the objects from the security-enhanced database. You should take this step before spending too much time helping protect objects because Access considers imported objects to be "new" and loses the permission information that was stored in the source database.

    The following table lists the default names and locations of the workgroup file and the Workgroup Administrator program.

    Version	Workgroup File Name (default)	Workgroup File Location	Wrkgadm.Exe Location
    2.0	System.mda	C:\Access	C:\Access
    95	System.mdw	C:\MSOffice\Access	C:\MSOffice\Access
    97	System.mdw	C:\Windows\System	C:\Windows\System
    2000	System.mdw	\Program Files\Common Files\System	\Program Files\Microsoft Office\Office\1033 Note: 1033 is the default folder for the English version of Access

     

2. In a nutshell, how does Microsoft Access security work?

The Microsoft Jet database engine, which Microsoft Access uses to store and retrieve its objects and data, employs a workgroup-based security model. Every time the Jet database engine runs, it looks for a workgroup file, which holds information about the users and groups of users who can open databases during that session. Any valid file name can be used, such as Wrkgrp_Sec.mdw.

The workgroup file contains the names and security IDs of all the groups and users in that workgroup, including passwords. There are built-in groups (Admins and Users) and a generic user account (Admin) that every workgroup contains by default. The built-in group Guests and user account Guest, which are included in Microsoft Access 2.0 only, can be ignored. You can add new groups and new user accounts using Microsoft Access menus or through code.

The Admins group is always present and its users have Administer rights that cannot be revoked. You can remove rights from the Admins group through the menus or through code, but any member of Admins can assign them right back. There must always be at least one member in the Admins group to administer the database. The default user account, Admin, always starts out as a member of the Admins group and is the account that everyone logs on as by default in an unsecured database. The other built-in group, Users, is a generic group to which all users must belong, no matter which other groups they belong to. It is possible to create a user through code, but that user is not automatically added to the Users group. If you do not take the extra step to add the person to the Users group, the person will not be able to start Microsoft Access because many of the tables that Microsoft Access uses internally are mapped to the permissions of the Users group. Neither the Admin user account nor the Users group has any built-in permissions (as the Admins group does).

Securing a database involves adding a new member to the Admins group and removing the Admin user from that group, removing permissions from the Admin user and from the Users group, and assigning permissions to the custom groups that you define.

Permissions to various objects in Microsoft Access can be assigned directly to users (explicit permissions) or to groups. Users inherit permissions from the groups they belong to (implicit permissions). Microsoft Access employs the "least restrictive" rule: users have the sum total of their explicit and implicit permissions. In other words, if a user belongs to a group that has full permissions and you make that user a member of a group that has restricted permissions, the user will still have full permissions because he is still a member of the unrestricted group. Although Microsoft Access allows you to assign permissions directly to users, this is not recommended. Administering your database can become very difficult if you do.

User and group information, including passwords, is saved in the workgroup file, or System.mda/mdw, which validates user logons at startup. Permissions to individual objects are saved in the database itself. You can give the groups and users within a workgroup various levels of permission to view, modify, create, and delete the objects and data in a database. For example, the users of a particular group might be permitted to read only certain tables in a database and not others, or you could permit a group to use certain forms but not to modify the design of those forms.

Setting a password for the default Admin user account activates the logon dialog box so that users will be prompted for a valid user ID and password each time that they start Microsoft Access. If you never set a password, all users will be logged on as the Admin user (with no password) and you will never see the logon dialog box when starting Microsoft Access. So even though it may appear that there is no security present, it is just transparent until you set a password on the Admin user account.

The database password was introduced in Microsoft Access 95. This is a simple password on the database itself that allows only users who know the password to open the file. You cannot assign permissions to users or groups with this feature. In addition, the database password feature is not considered to be very secure. See Section 4, "How can I set a single password on my database?" for more information about the database password feature.

3. What has changed in Microsoft Access security between Microsoft Access 2.0, 95, 97, and 2000?

1. Since Microsoft Access 95, the Security Wizard is included as part of the Microsoft Access product. The Security Wizard assists you in creating a security-enhanced application out of an unsecured one.
2. Users can retrieve group membership information even if they are not members of the Admins group. This was very difficult to do in Microsoft Access 2.0 and required some workarounds. See Section 23, "How can I obtain group and user membership information programmatically?"
3. In Access 95, the dbSecReadSec constant was added to permit users to retrieve security information for an object. Without this setting, attempts to retrieve permissions are rejected. The Access 2.0, equivalent of this constant is DB_SEC_READSEC.
4. Since Microsoft Access 95, permissions are no longer reset to <New Tables/Queries> when a RefreshLink is performed against linked (attached) tables.
5. The minimum permissions for a linked table require Modify Design permission on the front-end table (not the back-end base table). In Microsoft Access 2.0, Read Design Permissions were sufficient.
6. Since Microsoft Access 95, there is a new database password feature, which allows you to set a single password on a database file. Anyone in the Admins group or the database owner, who has opened the database exclusively, can set or reset this password.
7. Beginning with Microsoft Access 95, the default workgroup information file is given the name, "System.mdw", not "System.mda". This helps distinguish it from wizard and library databases, which still have the .mda extension.
8. A bug with RWOP append queries in 2.0, which required implicit Insert Data permissions for the current user, has been corrected.
9. The intrinsic constants that you use in security have changed, although they are similar enough once you get used to the format. Table 1 shows Microsoft Access 2.0 security constants and Microsoft Access 95 through 2000 security constants.
10. In Access 2000, modules can be secured by using the VBA (Visual Basic&#reg; for Applications) password, which is set from within the Visual Basic Editor. On the Tools menu, click <project name> Properties, and then click the Protection tab. User-level security in Access 2000 cannot prevent someone from viewing and editing your modules and code behind forms in the VBE. With a VBA password you can help prevent users from viewing the project's properties and the code in any standard/class module as well as code behind forms. However, your best protection against someone viewing or editing your code is to convert your MDB to the MDE file format.

    Access 2000 has added a new permission to Tables: the ability to edit AutoNumber columns. Although no constant is defined for this permission, you can define your own in code:

       Private Const dbSecModifyAutonumber = &HFF00
    

When a table is created in Access 2000 or an earlier-version database is converted, this permission will not be set, by default. The Access 2000 security user interface does not expose it, and neither does ADOX; the only way to set it is through DAO security code using the above constant definition. The Access 2000 Security Wizard does set this permission for several of the custom groups it creates.

Note This permission does not allow you to ignore referential integrity; if you do not have cascading updates enabled in an enforced relationship, then the update will fail. You should also be careful about when you use it because it can affect what value Access will use for the next record that is inserted into the table. To change this yourself, you can use ADOX to modify the Seed property of the AutoNumber column. (For more information see the white paper, "Migrating from DAO to ADO," listed in Section 47.)

3.1 Table 1: DAO Security Constants

* In Access 2000, this permission has some effect in the Access user interface (you cannot use the OutputTo command, for example) but is not supported by VBE. Therefore modules cannot be considered to be secured by Access in any real sense. To help bolster Access 2000 code security, you will need to look into the VBA password, discussed in Section 3.10 earlier in this paper.

4. How can I set a single password on my database?

Microsoft Access 2.0: There is no database password feature in Microsoft Access 2.0, so you need to help protect your application by following the steps discussed in Section 1 earlier in this paper. After you are finished, assign the Users group permissions for the objects that you want your users to be able to access. Distribute the password for the Admin user that you have defined. Make sure that the Admin user is not in the Admins group. In Microsoft Access, users can reset their own passwords, and if someone resets the password for the Admin account and then forgets it, you won't have any way of clearing the password unless you can log on to the database as a member of Admins and run code to clear it. See Section 5, "How can I clear a user's forgotten password?", for the necessary code.

Since Microsoft Access 95, Microsoft Access has supported share-level security with a database password. You can find this feature under Tools|Security|Set Database Password. In order to set the database password, you must be a member of the Admins group or the database owner, and have the database open exclusively. The database password is not supported in a replicated database. One danger is that if you set a password and then forget it, you (and everyone else) can be locked out of the database.

4.1 Database Password Bugs

Microsoft Access 95: There is a bug in the database password feature, in Microsoft Access 95 only. If you set a password, such as "My Password", that contains a space in it, and then you compact the database, the next time that you try to open the database, it will fail. The process of compacting the database will truncate the database password at the space. Your new password will be "My". More information about this problem can be found in the Microsoft Knowledge Base article, ACC95: DB Password with Space Becomes Invalid After Compact (Q152760). This bug has been fixed in Microsoft Access 97.

Microsoft Access 95 and Microsoft Access 97: A second bug has recently emerged that affects both Microsoft Access 95 and Microsoft Access 97 databases. If you use a backslash (\) character in the password, you will be unable to open the database using the password. The workaround is to type two backslashes (\\) instead of one (\). In other words, if your password is "My\Password", typing "My\\Password" will open your database.

5. How can I clear a user's forgotten password?

TheDAO method, NewPassword, can be used in a VBA procedure to set and clear passwords. It takes two arguments: the old password, and the new password. If you are a member of the Admins group or the owner of the database, you can run NewPassword on any user and pass in empty quotation marks ("") for either the old password or the new password arguments. If you are not one of these accounts, you can only run NewPassword to change or clear your old password, and you must supply the old password argument for it to take effect. The following code will clear or change a user's password. There is no error trapping; you will need to make sure that you pass in a valid user name and that the string for the password does not exceed 14 characters.

     Function faqChangePassword (ByVal strUser As String, _
         ByVal strPwd As String) As Integer

         Dim ws As Workspace
         Dim usr As User

         Set ws = DBEngine.Workspaces(0)
         Set usr = ws.Users(strUser)
      usr.NewPassword "", strPwd
     End Function

You can change the database password by using the NewPassword method on the Database object instead of a User object, but you have to know the old password.

6. What's the best way to convert my security-enhanced Microsoft Access application to the latest version of Microsoft Access?

The best way to convert your security-enhanced Microsoft Access application is possible only if you remembered to write down your Workgroup ID (WID) and Personal ID's (PID) when you created your original security-enhanced workgroup information file. You can create a new workgroup file in the latest version of Microsoft Access by inputting those exact strings. Then, when you convert your database, you are ready to go. If you can't re-create your workgroup information file, you can run a Microsoft Access database against an earlier version's workgroup file. However, you will be unable to take advantage of any new functionality that may have been added to user-level security in the latest version of Microsoft Access. Your best bet is to "de-secure" the application in the older version of Microsoft Access before converting it (grant full permissions to the Users group and put the Admin user back in Admins, clearing its password), as mentioned in Section 36, "How can I 'de-secure' a database?" Convert the de-secured application, and then re-secure it in the current version of Microsoft Access, using the steps discussed in Section 1, "What are the steps to help protect a database?"

7. What's all this about a security hole in Microsoft Access 2.0?

The security hole in Microsoft Access 2.0 has been widely discussed on the Internet. It exists in the user interface and does not involve complicated tools or esoteric knowledge - many people have stumbled upon it accidentally. Someone who knows about it can very easily get into your forms, reports, macros, and modules. They can't use the security hole to get at your tables and queries if you have removed all permissions from them and use queries that have their RunPermissions property set to Owner's (the default is User's) in order to access your data (see Section 10, "How do I implement field-level or row-level security on my tables (RWOP or queries with Run Permissions set to Owner's)?"). Unfortunately, your code is completely vulnerable, so if you have used the technique of embedding an Admins account name and password in your modules in order to perform certain operations, you may want to consider another approach.

It is important to note that this particular security hole is only available to those users who have a valid logon ID and password and permission to open the database. It has been plugged since Microsoft Access 95.

Note It is not humanly possible to adequately secure a desktop database against everyone who has the determination, knowledge, and tools. There are security holes in Access 95 - 2000 as well, so simply upgrading to a newer version is no guarantee against hackers. If your security needs are such that you require stronger security-enhancements, you are better off looking at a server database such as SQL Server on a locked-up installation of Windows NT&#reg; or Windows 2000&#reg;.

8. How can I help protect just my code without users having to log on?

This works by using two separate workgroup information files: one for development and securing your database, and one for distribution. You can even use the default System.mda/mdw for distribution.

1. Help protect your database completely by following the steps discussed in Section 1, "What are the steps to help protect a database?"
2. Make sure that all permissions to modules are revoked for the Users group and the Admin user. (If you have used the Security Wizard, this is already taken care of.)
3. Grant full permissions to the Admin user and the Users group for all the objects that you want everyone to be able to use.
4. Distribute your application using the default workgroup information file. Because there is no password assigned to the Admin user in the default System.mda/mdw, everyone logs on as Admin and everyone has only those permissions you have assigned the Admin user and the Users group.

When you need to make modifications to your application, you need to switch to your development workgroup database and log on as the owner of the database.

Beginning with Microsoft Access 97, a database can be converted to an .mde file. This removes all editable code from the database and makes the design of forms, reports, and modules inaccessible to users. It has no effect whatsoever on data or on linked tables. The account creating the .mde file must own or have Administer permission to the source .mdb file.

9. How do I delete the Admin user?

The answer is, you can't. This is a common bit of misinformation that has been perpetuated in several books, starting with the release of Microsoft Access 2.0. In Microsoft Access 1.x, you could delete the Admin user, but not since. Because the Admin account is a default account, which is identical across all Microsoft Access installations, you do need to remove it from the Admins group as part of the process of helping protect the database. You want Admin to be merely another member of the Users group. You then remove all permissions from the Users group, so Admin inherits nothing (you assign permissions only to your custom groups). If you neglect this step, your database will never be secure.

10. How do I implement field-level or row-level security-enhancements on my tables (RWOP or queries with Run Permissions set to Owner's)?

To understand how the Run permissions work you must understand the concept of ownership in Microsoft Access. The user who creates or imports an object in Microsoft Access becomes the owner of that object and has full permissions to administer that object. For example, if I create a query that draws on certain tables, then I must have rights to the data in those tables or I would not be able to create, run, and save the query. The RunPermissions property in the query is by default set to User's, which means that the query will run with whatever permissions the logged-on user has on the base tables that make up the query. If I change the RunPermission property to Owner's, I allow users to run the query as though they were logged on as me, the owner of the query. The query will run with the permissions of the query owner, rather than with the permissions of the logged-on user. Such queries are often referred to as RWOP queries (Run With Owner's Permissions).

The RunPermissions property applies only to saved queries. You cannot use it with SQL strings defined in your module code by the simple expedient of typing WITH OWNERACCESS OPTION in the string. The reason for this is that security is defined on saved objects only by setting certain bits on them. Your module code has no way of knowing who you want the owner to be in this situation because there is no saved object to read those bits from. You can use RWOP queries to grant partial access to tables by restricting either the columns or the rows returned by the query. Therefore, if you have a Salary column in your payroll table, you can design a query for your users to run that returns all the columns except Salary. If you wanted your users to see all salaries except managers salaries, you could design a query that restricted the rows returned by using a WHERE clause that excluded those with a managerial job classification. The best way to implement this is to remove ALL permissions from the underlying table(s) and use only queries to get at the data you want your users to have. You then grant the appropriate level of permissions to the groups or users only on the query itself, allowing them only to view data or to be able to modify it through your query.

Note There are other issues to be considered when using RWOP queries with attached tables. See Section 11, "Why can't I get my RWOP Append Query to Run?", Section 13, "How do I manage linked tables using Microsoft Access security?", and Section 14.1, "Using RefreshLink to relink tables" for more information.

11. Why can't I get my RWOP Append Query to Run?

Action (append, update, or delete) queries usually only require Read Data permissions for the query object itself. The type of action query conveys the permission intention. For example, it would have just been a nuisance to demand that the Update Data permissions be set separately for an update query.

For RWOP queries, table permissions for the actual append, update, or delete operation are governed by those assigned to the query owner (Insert Data, Update Data and Delete Data). The permissions of the RWOP query's user on the target tables are not considered at all. In fact, the query's user will normally have no permissions on the RWOP query's source or target table(s). However, Microsoft Access 2.0 append queries are an exception. They also require Read Data and Insert Data permissions on the table, in addition to Read Data permission on the append query. In the case of a linked table insert, the user needs these permissions on both source database- and destination-database table objects.

Without those permissions, you are likely to get the error message:

Operation must use an updateable query

12. What is the difference between an "attached" table and a "linked" table?

Terminology Change Alert: In Microsoft Access 95 and later the term "attached" table was replaced with the synonymous term "linked" table. This new term can be misleading because essentially the behavior has not changed, and a linked table is not a linked object in OLE terminology. It is still just a string reference of the disk address to the database that contains the base table (if you look in the table properties you will see the hard-wired connection string). Linked tables in Microsoft Access bear no relation to OLE links found in Microsoft Word or Microsoft Excel; any similarity is in terminology only-they do not update automatically. If you move the location of the table or database, you will break the link.

For the purposes of this document the terms are interchangeable, and the term, "base table," is used to refer to the table that is being linked to or attached.

Microsoft Access 95/97/2000 linked tables: The following changes in behavior have occurred.

1. Permissions are no longer reset to <New Tables/Queries> when a RefreshLink is performed against an existing link. This was a huge pain in Microsoft Access 2.0, and has fortunately been fixed.
2. The minimum permissions for a linked table require Modify Design permission on the linked table (not the base table). In Microsoft Access 2.0, Read Design permission was sufficient.

13. How do I manage linked tables using Microsoft Access security?

The main issues involved here are:

• Providing users with sufficient permissions for them to connect or reconnect their application to the back-end database.
• Resolving the differences in permission settings between tables in the front- and back-end databases.

Four techniques are available to change a back-end connection:

1. Use the RefreshLink method after altering the connection string for a TableDef.
2. Use the TransferDatabase command to link the back-end tables.
3. First delete the existing link and then re-add it with the new connection string by using the Connect property of a TableDef object.
4. Use the Linked Table Manager (in Access 2.0 this is called the Attachment Manager)

Although the use of RefreshLink probably seems the more "elegant" solution, it has the drawback of requiring Read Data permissions on the back-end base table in order for the user to be able to execute the method. For instances where the only access is to be provided by RWOP queries, the re-creation of the connection (preceded by a delete) only requires Read Design permissions on the back-end base table. See Section 10, "How do I implement field-level or row-level security on my tables (RWOP or queries with Run Permissions set to Owner's)?". The creation of a new attachment can be implemented either via DAO by setting the Connect property of a TableDef object or by use of the TransferDatabase macro action. If you connect to a table by using the menus, you need Read Design permissions, but if you connect through code to reset the Connect property, you don't need any permissions - the error returned in your module code is ignored and the table is linked anyway. If you use the TransferDatabase macro action, you need Read Designpermissions on the back-end tables. When using the Linked Table/Attachment Manager add-in, you must have Modify Design permission on the front-end table and Read Data on the back-end table.

It is important to realize that you are working with two distinct objects as far as security is concerned. In other words, you have the base table in the back-end database to deal with, and the link in the front-end database. The table and the link are separate objects and have permissions set independently of each other through the Container and Document objects in DAO. Granting permissions on the link does not affect any permissions set on the back-end or base, and vice-versa.

Regardless of the setting in the link, a user cannot inherit higher permissions than those set on the base table. In other words, granting full permissions on an attached or linked table in the front-end, including Administer, will have absolutely no effect on the actual table in the back-end- if you have removed all permissions in the back-end, users will not be able to open the table in either Design or Datasheet view in the front-end where you have granted full permissions. The important thing is to restrict permissions as necessary in the back-end database (where the tables actually reside). This will prevent users from opening the back-end tables directly.

When you attach, or link, a table, it automatically inherits whatever permissions you have defined in your database for <New Tables> for each Group or User in the front-end database. It is recommended to grant full permissions on <New Tables> because this has no effect on the source tables.

Microsoft Access 2.0: In Microsoft Access 2.0, the act of simply refreshing attached tables also resets the permissions of the table in the current database to those assigned to <New Tables>. See Section 16.3, "Updating or Refreshing Existing Links/Attachments" for more information.

14. What permissions are necessary to update table links?

You can refresh existing table attachments by using the Linked Table Manager or Attachment Manager provided by Microsoft Access or via code. You need to have Read Data permissions on the TableDef object in the source database.

The following minimum permissions are necessary before a user can link tables using the Linked Table/Attachment Manager or the RefreshLink method. Alongside each permission description is the corresponding Access Basic or Visual Basic for Applications constant equivalent.

The following code is an example of how you can programmatically manipulate these permission settings. The constants used are Microsoft Access 2.0 constants, but the code will also run in Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000. It assumes that a member of the Admins group will be executing the code.

   Function faq_SetPermissions (strTable As String, strSourceDB As String, strUsrName As String)
   ' This function will set permissions on the source table so that
   ' you can use the RefreshLink method to reattach tables. It grants
   ' Read Data permissions on the source table, Open/Run permissions
   ' on the source database and full permissions in the destination
   ' database on all tables and queries. You need to be a member of the
   ' Admins group to run this code.
   ' 
   ' Parameters:
   '       strTable
   '           Name of the table for permissions to be set. Assumes the
   '           table named the same in both source and destination db.
   '       strSourceDB
   '           Fully-qualified name of the source database
   '       strUsrName
   '           Name of group or user you want to be able to
   '           use RefreshLink
   '
       Dim db As Database
          Dim con As Container
          Dim doc  As Document
          Dim ws As WorkSpace
       Set ws = dbengine.workspaces(0)
    
       ' set default full permissions in destination
       ' (current) database for new tables
       Set db = CurrentDB()
       Set con = db.Containers("Tables")
       con.username = strUsrName
       con.permissions = DB_SEC_FULLACCESS
    
       ' set full permissions for the linked table
       ' in the destination database
          Set doc = con.Documents(strTable)
          doc.username = strUsrName
       doc.permissions = DB_SEC_FULLACCESS
    
       ' Set open database permissions for the source database
       Set db = ws.OpenDatabase(strSourceDB)
       Set con = db.Containers("Databases")
       Set doc = con.Documents("MSysdb")
       doc.username = "Users"
       doc.permissions = doc.permissions Or DB_SEC_DBOPEN
    
       ' Set read data permissions for the base table
       Set con = db.Containers("Tables")
       Set doc = con.Documents(strTable)
       doc.username = strUsrName
       doc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATA
   End Function

14.1 Using RefreshLink to relink tables

Now that you have assigned Read Data permissions to your source tables and full permissions to the destination tables for a given user, the user will be able to use the RefreshLink method to relink tables. The following code uses Microsoft Access 2.0 conventions but will run in Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000.

   Function faq_RefreshLink (strTable As String, strSourceDB As String)
   ' This function can be run by any user who has Read Data permission
   ' on the source table and Open/Run permissions on the source database
   '   Parameters:
   '       strTable
   '           Name of the table to be refreshed
   '       strSourceDB
   '           Fully-qualified path and filename of the source db
   '
       Dim ws As WorkSpace
       Dim db As Database
       Dim tdf As TableDef
    
       Set ws = dbengine.workspaces(0)
       Set db = CurrentDB()
       Set tdf = db.TableDefs(strTable)
       ' specify new location of source table
       tdf.Connect = ";DATABASE=" & strSourceDB
       ' refresh the link
       tdf.RefreshLink
   End Function

14.2 Using TransferDatabase to relink tables

Your users only need Read Design permissions on your source tables if you elect to use the TransferDatabase command to refresh your links. You could modify the faq_SetPermissions function listed above to grant only Read Design by modifying the following line

       doc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATA

to read

       doc.permissions = DB_SEC_READDEF

which will restrict permissions on the source table to Read Design only. Users will not be able to see the data in the table in Datasheet view or modify the design of the table. The following function will allow users to use the TransferDatabase method to relink tables:

   Function faq_TransferDatabase (strTable As String, strSourceDB As String)
   ' TransferDatabase is actually a macro command. This function
   ' acts as a wrapper, passing the name of the table and the fully-
   ' qualified path and filename of the new location of the source
   ' database. Users need Read Design on the source table in order
   ' to run this code. It is assumed they have full permissions on
   ' the destination database.
   '
       Dim db As Database
       Dim tdf As TableDef
    
       Set db = CurrentDB()
    
       ' Ignore any error that occurs if the table isn't already
       ' present in the destination database
       On Error Resume Next
       ' remove the old link
       db.tabledefs.Delete strTable
    
       ' Relink using TransferDatabase
       DoCmd.TransferDatabase A_ATTACH, "Microsoft Access", strSourceDB, A_TABLE, strTable, strTable
    
   End Function

14.3 No Permissions necessary - Using the .Connect property to relink tables

If you don't want to grant any permissions at all on your source tables and you'd still like users to be able to link to the tables, you can employ this method. To remove all permissions for the source table, change the faq_SetPermissions line

doc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATA

to

doc.permissions = DB_SEC_NOACCESS

If a user tries to link or attach tables through the user interface, the user will be told that he or she doesn't have sufficient permissions to do so. However, when using this technique in code, the On Error Resume Next statement causes the error returned to be ignored, and the table is linked anyway. The user cannot open the table in either Datasheet or Design view, so you need to have previously created RWOP queries for the user to be able to access the data. A user should be able to run the following code, as long as the user has full permissions in the destination database and Open/Run permissions on the source database-no permissions at all are necessary on the source tables.

   Function faq_ConnectLink (strTable As String, strSourceDB As String)
   ' This function can be run by any user who has OpenRun permission
   ' on the source database. It works equally well to link tables
   ' from scratch or to relink previously attached tables. In-line
   ' error handling is used to ignore any errors
   '   Parameters:
   '       strTable
   '           Name of the table to be linked
   '       strSourceDB
   '           Fully-qualified path and filename of the source db
   '
   On Error Resume Next
       
       Dim db As Database
       Dim tdf As TableDef
    
       Set db = CurrentDB()
    
       ' Delete the link if it already exists
       db.tabledefs.Delete strTable
    
       ' Create new link
       Set tdf = db.CreateTableDef(strTable)
    
       ' Set the properties of the new link
       ' and append to the tabledefs collection
       tdf.SourceTableName = strTable
       tdf.Connect = ";DATABASE=" & strSourceDB
       db.tabledefs.Append tdf
   End Function

14.4 Error messages

Regardless of the technique selected, Microsoft Access verifies that you have the necessary permissions. Remember that you do not need to grant any permissions on the source tables if you relink by using the Connect property and that granting full permissions on the destination database will not, in and of itself, jeopardize data security. If you don't, you may receive the following error messages:

Microsoft Access 2.0.

Microsoft Access 95: Regardless of which setting is missing, only one error message and number is returned. This is the generic "Application-defined or object-defined error" message.

Microsoft Access 97 and 2000.

15. Why do users require permission to create new tables in the destination database in order to update a table attachment?

Even if you are only refreshing an existing link, Microsoft Access still treats the attachment as if you were creating a new table. When you set permissions for tables, the setting <New Tables> refers to permissions a specific group or user will inherit when a new table is created and has nothing to do with revoking the ability to create tables in the first place. You can't remove permission to create new tables in the user interface-you have to do it from code as shown in Section 18, "How can I help prevent users from creating new objects in my database?".

Microsoft Access 2.0: The Security Wizard presents a check box that will exclude this ability from the existing users and groups. If you select this option at the time that you run the Security Wizard, then only members of the Admins group will be able to create new tables and queries. This makes it impossible for any user not in the Admins group to create new links.

Microsoft Access 95 Microsoft Access 97 and Microsoft Access 2000: The removal of this permission setting can now only be accomplished via DAO code. The check box available in the Microsoft Access 2.0 Security Wizard is not present in the new built-in wizard. See Section 24, "How can I help prevent users from creating new objects in my database?" for the code listing to set or remove this permission.

16. What happens when the front-end database permissions on an attached table differ from those in the back-end database?

Microsoft Access uses a combination of security settings in both the current database and remote database when ascertaining rights to attached/linked tables. Permissions set on the back-end tables cannot be over-ridden by granting more liberal permissions on the front-end tables. In this context, it is helpful to think of the attachments in the front-end database as merely the connection strings, or the information needed to locate the actual tables in the back-end database. Permissions set on connection strings should not, and do not, override permissions set on actual tables.

Microsoft Access does not treat the combination of permissions between the base tables and the connection strings by using the "least restrictive" rule, but rather it determines the most restrictive rights. Microsoft Access first determines the least restrictive rights for the table in both the current and remote databases. Then Microsoft Access compares these permissions and combines them so that, unless a permission is available in both databases, it will restrict rights to the attached table.

The following table highlights this.

The simplest thing to do is to grant users full permissions on tables in the front-end and restrict the back-end to the barest minimum needed if users need to relink tables. If they don't need to relink tables, then removing ALL permissions and using RWOP queries will help provide strong protection for your data.

16.1 Creating New Links

The process of adding a linked table will set the default permissions to those assigned for <New Tables/Queries>.

16.2 What permissions should you set for <New Table/Queries>?

The best solution is to provide full permissions for all of your custom groups on <New Tables/Queries> in the destination database. When a linked table is refreshed, it will receive full rights, but any access to the linked table will follow the "most restrictive" rule and access to the data will be downgraded to rights assigned in the remote database. To provide strong data security measures, remove all permissions for all groups and users for <New Tables/Queries> and all tables in the source database. To access the data, you can use queries with the RunPermission set to Owner's instead of User's (also known as RWOP queries). Any tables a user creates in the front-end database will not be affected by the default permission settings because the user will own the objects and, by default, will have full permissions on them.

16.3 Updating or Refreshing Existing Links/Attachments

Microsoft Access 2.0: Updating an existing attachment causes the existing permissions for the attached table to be reset to the default settings for <New Tables/Queries> for all users and groups other than Admins and the user actually performing the attachment. Microsoft Access 2.0 treats the refresh of an attached table as the creation of a new table, and the user performing the attachment becomes the owner of the object and receives full permissions, regardless of the settings in <New Tables/Queries>. If you have followed the suggestions of having full or zero permissions for <New Tables/Queries> that were discussed in Section 16.2, then this will not be a problem. Any access to the attached table (including access via a RWOP query) will follow the "most restrictive" rules and be downgraded to rights of the base table in the remote database.

Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000: This problem has been fixed. The act of updating an existing link has no effect on the existing permissions previously set on linked tables.

16.4 Synchronizing Permissions

If you need the base table permissions to always be identical to the linked table permissions after refreshing a link, you must copy the permissions by using DAO.

The following code sample opens the remote database and retrieves the permissions set on the base tables, and then copies those permissions to the front-end linked tables. It assumes that the names of the tables and the names of the links in both databases are identical. Because only members of the Admins group can set permissions, you must log on as a member of the Admins group in order to run the code.

Calling example:

      ysnOK = faq_CopyTablePermissions ( "C:\DATA.MDB" )
       
   Function faq_CopyTablePermissions(pstrRemoteDB As String) As Integer
     ' ------------------------------------------------
     ' copies permissions from the RemoteDB to the CurrentDB
     ' the full path to the remote db is passed in the pstrRemoteDB
     ' string parameter
     ' -------------------------------------------------
     Dim ws As Workspace, db As DATABASE
     Dim dbTo As DATABASE, dbFrom  As DATABASE
     Dim conFrom As Container, docFrom As Document
     Dim conTo As Container, docTo  As Document
     Dim strDBName As String, strTblName  As String
     Dim i As Integer, k  As Integer, m  As Integer
    
     On Error GoTo faq_Err_CopyTablePermissions
     '--------------------------
     ' get current database
     '--------------------------
     Set ws = DBEngine.Workspaces(0)
     Set db = CurrentDB()
     '----------------------------------------------
     ' open From/To database under secured workspace
     ' establish From/To Containers
     '----------------------------------------------
     Set dbTo = ws.OpenDatabase(db.Name)
     Set dbFrom = ws.OpenDatabase(pstrRemoteDB)
     Set conFrom = dbFrom.Containers("Tables")
     Set conTo = dbTo.Containers("Tables")
     '-----------------------------------
     ' copy permissions for all tables
     ' - ignore unmatched & system tables
     '------------------------------------
     For i = 0 To dbFrom.TableDefs.Count - 1
       strTblName = dbFrom.TableDefs(i).Name
       If Left$(strTblName, 4) <> "MSYS" Then
         Set docFrom = conFrom.Documents(strTblName)
         strTblName = ""
         On Error Resume Next
         strTblName = dbTo.TableDefs(i).Name
         On Error GoTo faq_Err_CopyTablePermissions
         If strTblName <> "" Then
             Set docTo = conTo.Documents(strTblName)
             For k = 0 To ws.Groups.Count - 1
               '-----------------------
               ' synchronise group names
               ' copy group permissions
               ' -----------------------
               docFrom.UserName = ws.Groups(k).Name
               docTo.UserName = docFrom.UserName
               If docTo.Permissions <> docFrom.Permissions Then
                 docTo.Permissions = docFrom.Permissions
               End If
               ' ------------------------------------------
               ' following For/Next loop is only necessary
               ' if User Level permissions are applicable
               ' ------------------------------------------
               For m = 0 To ws.Groups(k).Users.Count - 1
                 docFrom.UserName = ws.Groups(k).Users(m).Name
                    docTo.UserName = docFrom.UserName
                    docTo.Permissions = docFrom.Permissions
                  Next m
             Next k
           End If
       End If
     Next i
     faq_CopyTablePermissions = True
    
      faq_Exit_CopyTablePermissions:
        If Not dbFrom Is Nothing Then dbFrom.Close
     If Not dbTo Is Nothing Then dbTo.Close
     If Not ws Is Nothing Then ws.Close
   Exit Function
    
   faq_Err_CopyTablePermissions:
     MsgBox "Table: [" & strTblName & "]" & Chr(13) & Error
     faq_CopyTablePermissions = False
     Resume faq_Exit_CopyTablePermissions
   End Function

17. How can I retrieve the "most restrictive" permissions for attached tables?

This is a multi-step process but can be useful in setting the Enabled property of command buttons that might rely on a recordset being updateable.

First, you must retrieve the explicit permissions for the specific user of the table. Then you must add all group membership or implicit permissions. This step must be done separately for both the current database and the remote database.

Once both sets of implicit rights are computed, the two are merged using a binary AND operator. This will leave only the bits that are set in both sets and forms the "most restrictive" set of permissions.

Microsoft Access 2.0: If the current user is not a member of the Admins group, this information can be tricky to obtain. See Section 23, "How can I obtain the groups that the current user belongs to without hard-coding an Admins ID and password in the code?" for a workaround.

Microsoft Access 95, Microsoft Access 97, and Microsoft Access 2000: A new security setting was added that permits users to retrieve security information for an object. Without this setting, attempts to retrieve permissions are rejected. This setting can be addressed via the dbSecReadSec constant.

The following function will return the "most restrictive" permissions for the specified table. Optionally, a query name can be provided as an argument. This would be useful in conditions where RWOP queries controlled permissions to the data. To obtain the correct permissions, the query is examined to see if it is a RWOP query by searching for the "WITH OWNERACCESS OPTION" string in the SQL. If it is, the permissions for the owner of the query rather than the current user are retrieved and used in the calculations. As a final step, the current user permissions for the query are applied to account for any further permission restrictions.

Example Usage:

   If (faq_MostRestrictive("Categories","MyQuery") And dbSecReplaceData) = _
   dbSecReplaceData then
      Msgbox "I can update this table"
   End if
    
   Function faq_MostRestrictive(pstrTable As String, pstrQuery As String) As Long
   ' ------------------------------------------------
   ' computes "most restrictive" permissions for the
   ' current user and specified table
   ' optional query may filter permissions
   ' -------------------------------------------------
     Dim db As DATABASE, tdef As TableDef
        Dim con As Container, qdef As QueryDef
        Dim doc As Document, docQry As Document
        Dim lngImpCurrent As Long, lngImpRemote As Long
     Dim lngImpQuery As Long, strUser As String
     '--------------------------------------
     ' open currentdb - set document to table
        ' compute implicit permissions
        '--------------------------------------
     Set db = CurrentDB()
     Set con = db.Containers!tables
     Set doc = con.Documents(pstrTable)
     '------------------------------------------------
     ' if query filter
     ' ... check for owner or user permissions
     ' ... adjust for currentuser permissions on query
     '-------------------------------------------------
     strUser = CurrentUser()
     lngImpQuery = DB_SEC_FULLACCESS
     If pstrQuery <> "" Then
        Set qdef = db.QueryDefs(pstrQuery)
        If InStr(qdef.SQL, "WITH OWNERACCESS OPTION") > 0 Then
              Set docQry = con.Documents(pstrQuery)
              strUser = docQry.Owner
           lngImpQuery = faq_Implicit(docQry, CurrentUser())
        End If
     End If
     lngImpCurrent = lngImpQuery And faq_Implicit(doc, strUser)
     '--------------------------------------
     ' get source table name & connect string
     ' open remote db - set document to table
        ' compute implicit permissions
        '---------------------------------------
     Set tdef = db.TableDefs(pstrTable)
     pstrTable = tdef.SourceTableName
     Set db = OpenDatabase(Mid$(tdef.Connect, 11))
     Set con = db.Containers!tables
     Set doc = con.Documents(pstrTable)
     lngImpRemote = faq_Implicit(doc, strUser)
     '--------------------------------
     ' compute most restrictive rights
     '--------------------------------
     faq_MostRestrictive = lngImpCurrent And lngImpRemote
   End Function
    
   Function faq_Implicit(pdoc As Document, pstrUser As String) As Long
   '------------------------------------------------
   ' computes "implicit" permissions for the
   ' specified user and document ( table or query )
   '------------------------------------------------
   Dim ws As Workspace
   Dim usr As User
   Dim lngPerms As Long
   Dim i As Integer
         '-------------------------
         ' get explicit permissions
         '-------------------------
      pdoc.UserName = pstrUser
      lngPerms = pdoc.Permissions
      '-------------------------------------
      ' add in each group where current user
      '   is a member
      '-------------------------------------
      Set ws = DBEngine(0)
      Set usr = ws.Users(pstrUser)
      For i = 0 To usr.Groups.Count - 1
          pdoc.UserName = usr.Groups(i).Name
          lngPerms = lngperms Or pdoc.Permissions