Frequently Asked Questions About Microsoft®Access Security for Microsoft Access versions 2.0, 7.0 and 97 |
|
Version 2.3 April, 1997
Copyright 1997 Andy Baron, Chris Bell, Mary Chipman, and Paul Litwin. All rights reserved. If you prefer, you can download this document in a Microsoft Word format: Secfaq.exe Table of Contents
1. What are the steps to secure a database?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: Securing a database is the same, no matter which version you use. The only difference is that in Microsoft Access 95 and Microsoft Access 97, the Security Wizard is built into the product. In Microsoft Access 2.0, you need to order it from Microsoft or download it from Microsoft's Web site.
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 on the users and groups of users that can open databases during that session. In Microsoft Access 2.0, this workgroup file is given a default name of System.mda, and in Microsoft Access 95 and 97 it is named System.mdw, but any valid file name can be used.The workgroup file contains the names and security ID's 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 safely be ignored. You can add new groups and new user accounts using Microsoft Access menus or through programming 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 again. 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 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 a nightmare 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 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. Microsoft Access 95 and Microsoft Access 97: A new feature in Microsoft Access 95 and Microsoft Access 97 is the database password. This is a simple password that allows only users who know the password to open the file. You cannot assign permissions to users or groups with this feature. See item 4, "How can I set a single password on my database?" for more information on the database password feature.
3. What has changed in Microsoft Access security between Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97?
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 secure your application by following the steps in item 1. After you are finished, assign the Users group permissions for the objects 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 item 5, "How can I clear a user's forgotten password?", for the necessary code.Microsoft Access 95 and Microsoft Access 97: Microsoft Access 95 and Microsoft Access 97 support 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 and have the database open exclusively. The database password is also 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. You cannot use the NewPassword method to clear a database password.
4.1 Database Password BugsMicrosoft 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 you attempt 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 on this problem can be found in the Microsoft Knowledge Base article Q152760, "DB Password with Space Becomes Invalid After Compact". 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. That way you can at least open the database to get in and change the password to something less troublesome.
5. How can I clear a user's forgotten password?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: In Microsoft Access, there is one method of user objects for both setting and clearing passwords, NewPassword. It takes two arguments, the old password, and the new password. If you are a member of the Admins group, 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 in the Admins group, you can only run NewPassword to change your old password, and you must supply the old password argument for it to take effect. The following code will clear 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 faq_ChangePassword(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
6. What's the best way to convert my secured Microsoft Access 2.0 application to Microsoft Access 95 or Microsoft Access 97?The best way to convert your secured Microsoft Access 2.0 application is possible only if you remembered to write down your WIDs and PIDs when you created your original secured workgroup information file in Microsoft Access 2.0. You can create a new workgroup file in Microsoft Access 95 or Microsoft Access 97 by inputting those exact strings. Then, when you convert your database, you are ready to go. If you can't recreate your workgroup information file, you can run a Microsoft Access 95 or Microsoft Access 97 database against a Microsoft Access 2.0 workgroup file, but you'd be missing out on the new functionality in Microsoft Access 95 and Microsoft Access 97. Your best bet is to "de-secure" the application in Microsoft Access 2.0 before converting it (grant full permissions to the Users group and put the Admin user back in Admins, clearing its password), as mentioned in item 36, "How can I 'de-secure' a database?" Convert the de-secured application, and then re-secure it in Microsoft Access 95 or Microsoft Access 97, using the steps mentioned in item 1, "What are the steps to secure a database?"
7. What's all this about a security hole in Microsoft Access 2.0?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 item 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 ID 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 in Microsoft Access 95. Microsoft Access 95 and Microsoft Access 97: The security hole that allowed the Microsoft Access user interface to be used to de-secure database objects has been fixed. However, other, more difficult techniques can still be used to penetrate Microsoft Access security. Note: It is not humanly possible to adequately secure a desktop database against all comers, should they possess the determination, knowledge, and tools. If your security needs are such that you require impregnable security, you would be better off looking at a server database such as SQL Server on a locked-up installation of Windows NT®.
8. How can I secure just my code without users having to log on?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: 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.
9. How do I delete the Admin user?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: 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 securing 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 on my tables (RWOP or queries with Run Permissions set to Owner's)?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: 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 item 11, "Why can't I get my RWOP Append Query to Run?", item 13, "How do I manage attached tables using Microsoft Access security?", and item 14.1, "Using RefreshLink to reattach tables" for more information.
11. Why can't I get my RWOP Append Query to Run?Microsoft Access 2.0: 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 insert, update, or delete operation are governed by those provided by the query owner and there should be no reference to the implicit permissions of the current user. However, Microsoft Access 2.0 append queries are an exception. They also require implicit Read Data and Insert Data permissions on the table, in addition to the Read Data permissions on the append query. In the case of an attached table insert, you need these implicit permissions on both table objects. Without those permissions, you are likely to get the error message: Operation must use an updateable queryMicrosoft Access 95 and Microsoft Access 97: This problem has been corrected.
12. What is the difference between an "attached" table and a "linked" table?Terminology Change Alert: In Microsoft Access 95 and Microsoft Access 97 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 and Microsoft Access 97 linked tables: The following changes in behavior have occurred.
13. How do I manage attached tables using Microsoft Access security?The main issues involved here are:
It is important to be aware of the fact 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 item 16.3, "Updating or Refreshing Existing Links/Attachments" for more information.
14. What permissions are necessary to update table attachments?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: You can refresh existing table attachments using the 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 attach tables using the Attachment Manager or the RefreshLink method. Alongside each permission description is the corresponding Access Basic or Visual Basic for Applications constant equivalent.
Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: 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, and Microsoft Access 97. 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 = ws.databases(0)
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 reattach tablesNow 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 reattach tables. The following code uses Microsoft Access 2.0 conventions but will run in Microsoft Access 95 and Microsoft Access 97.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 = ws.databases(0)
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 reattach tablesYour 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 linedoc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATAto read doc.permissions = DB_SEC_READDEFwhich 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 = dbengine(0)(0)
' 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 reattach tablesIf 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 linedoc.permissions = doc.permissions Or DB_SEC_RETRIEVEDATAto doc.permissions = DB_SEC_NOACCESSIf 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 error returned is 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 ws As WorkSpace
Dim db As Database
Dim tdf As TableDef
Set ws = dbengine.workspaces(0)
Set db = ws.databases(0)
' 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 messagesRegardless of the technique selected, Microsoft Access verifies that you have the necessary permissions. 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:
15. Why do users require permission to create new tables in order to update a table attachment?Even if you are only refreshing an existing attachment, 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 item 18, "How can I 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 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 attachments. Microsoft Access 95 and Microsoft Access 97: 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 item 24, "How can I 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 2.0, Microsoft Access 95, and Microsoft Access 97: 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 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 reattach tables. If they don't need to reattach tables, then removing ALL permissions and using RWOP queries will give you the highest level of data security possible.
16.1 Creating New LinksMicrosoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: The process of adding an attached 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 an attached table is refreshed, it will receive full rights, but any access to the attached table will follow the "most restrictive" rule and access to the data will be downgraded to rights assigned in the remote database. To provide the highest level of data security, 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, have full permissions on them.
16.3 Updating or Refreshing Existing Links/AttachmentsMicrosoft Access 2.0: Updating an existing link 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> in item 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 and Microsoft Access 97: This problem has been fixed. The act of updating an existing link has no effect on the existing permissions previously set on attached tables.
16.4 Synchronizing PermissionsIf you need the base table permissions to always be identical to the attached table permissions after refreshing a link, you will need to copy the permissions 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, it is necessary to log on as a member of the Admins group in order to execute 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 need to retrieve the explicit permissions for the specific user of the table. Then you need to add all group membership or implicit permissions. This step needs to 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 item 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 and Microsoft Access 97: 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
Next
faq_Implicit = lngPerms
End Function
18. Can I prevent users from attaching tables?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: Users may attach tables, as long as they have Open/Run permissions on the source database itself. They need Read Data permissions to attach through the user interface, but not in code. See item 13, "How do I manage attached tables using Microsoft Access security?" and item 14, "What permissions are necessary to update table attachments?" for more information.There is nothing to stop users from simply opening the remote database directly if they have Open/Run permissions. You can prevent users from opening the remote database in Datasheet view and viewing the data by removing Read Data permissions from the tables in the back-end database.
19. How do I work with a secured application and an unsecured application at the same time?Create separate icons on your desktop for your secured application and the default, unsecured installation of Microsoft Access. You can only log on to one workgroup information file (System.mda/mdw) at a time, so your users must understand that they have to quit and restart Microsoft Access when they want to switch back and forth between secured and unsecured applications. A second option is to train your users to use the Workgroup Administrator, but this is usually confusing to end users. Creating separate icons is not that difficult, and you only have to do it once. The techniques are different for each version of Microsoft Access.Microsoft Access 2.0: You need to use two different .INI files: one for the default installation and one for the secured application. The easiest way to do this is to make a copy of MSACC20.INI and name it something else, like SECACC20.INI and leave it in your \Windows directory. Change one line in the Options section in your SECACC20.INI to point to your secured workgroup information file (SECAPP.MDA): [Options] SystemDB=C:\ACCESS\SECAPP.MDAThe next step is to create the icon for your secured application by using the /INI switch on the command line options to point to the .INI file specifying the secured workgroup information file. It should look something like the following: c:\msoffice\access\msaccess.exe /ini c:\windows\secacc20.iniUsing this icon will start the secured version of Microsoft Access. Your original icon is unchanged, and will start the unsecured, default installation of Microsoft Access. Microsoft Access 95 and Microsoft Access 97: You don't need .INI files any more; you can use the /WRKGRP switch directly on the command line of the icon: c:\msoffice\access\msaccess.exe /wrkgrp c:\myapp\secacc20.mdw 20. How do I keep users from viewing Code Behind Forms?Microsoft Access 2.0: You can't prevent users from viewing any code that you place directly in a form or report module (this code is typically referred to as Code Behind Forms, or CBF). Unfortunately, there is a bug in Microsoft Access 2.0 that exposes all CBF if the user includes DebugLibraries=True in the [Options] section of the MSACC20.INI file, which is impossible to secure. There is no workaround for this in Microsoft Access 2.0, but it was fixed in Microsoft Access 95. You can protect your code by moving sensitive procedures to global modules, and calling the global procedures from your CBF. Make sure to remove Read Design permissions from your global modules.Microsoft Access 95 and Microsoft Access 97: Remove Read Design permissions from your forms.
21. How can I tell who is logged on to my shared, networked application?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: Microsoft Access has a function, CurrentUser(), which returns the name of the logged-on user of the current database, but that won't help you if you're trying to determine all of the users of a shared database centrally located on a network. There is no built-in way to do this, unfortunately. In order to implement this functionality, you would have to include your own special table that adds a user's name whenever someone starts your application, and then deletes it when that person logs off.The problem to this method is that if a user does not exit gracefully (for instance, if that user's computer GPFs or that user turns the computer off without first quitting Microsoft Access) then that user will still be listed as being "logged on" even though that user is not. To guard against this, you should delete all names from the table at a time when you are certain no one is in the database (such as when you are doing a backup/repair/compact). You may also want to provide a way for your administrative users to browse and adjust the table to remove such a phantom logon. There is a DLL available from Microsoft that will let you see which people are using a specific database (it will list all the names their computers used on the network), but although it can check Jet database engine 2.0/2.5/3.5 applications, it cannot be run from a 16-bit application such as Microsoft Access 2.0. Check the Microsoft Web site for the most recent version, which should be compatible with Jet database engine 2.5 through Jet database engine 3.5. The JETLOCK.EXE file contains the "Understanding Jet Locking" white paper, as well as the LDBView utility and the new MSLDBUsr.DLL.
22. How can I obtain group and user membership information programmatically?Microsoft Access 2.0: The following functions assume that you are a member of the Admins group. This is the only way to ensure that you have sufficient permissions to run the code without error. In Microsoft Access 2.0, only members of the Admins group can view the group membership and permissions of other users. Microsoft Access 95 and Microsoft Access 97 allow all users to retrieve group membership information, provided that they are using a Microsoft Access 95 or 97 workgroup file rather than a Microsoft Access 2.0 workgroup file. The code will also work in Microsoft Access 95 and Microsoft Access 97.List Users in the system: Function faq_ListUsersInSystem ()
Dim ws As WorkSpace
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
For i = 0 To ws.Users.count - 1
Debug.Print ws.Users(i).Name
Next i
End Function
List Groups in System:
Function faq_ListGroupsInSystem ()
Dim ws As WorkSpace
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
For i = 0 To ws.Groups.count - 1
Debug.Print ws.Groups(i).Name
Next i
End Function
List Members of Group:
Function faq_ListUsersOfGroup (strGroupName As String)
Dim ws As WorkSpace
Dim grp As Group
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroupName)
For i = 0 To grp.Users.count - 1
Debug.Print grp.Users(i).Name
Next i
End Function
List Groups User is a member of:
Function faq_ListGroupsOfUser (strUserName As String)
Dim ws As WorkSpace
Dim usr As User
Dim i As Integer
Set ws = DBEngine.Workspaces(0)
Set usr = ws.Users(strUserName)
For i = 0 To usr.Groups.count - 1
Debug.Print usr.Groups(i).Name
Next i
End Function
Determine if a User is in a given Group:
Function faq_IsUserInGroup (strGroup As String, _
strUser as String) As Integer
' Returns True if user is in group, False otherwise
' This only works if you're a member of the Admins group.
Dim ws As WorkSpace
Dim grp As Group
Dim strUserName as string
Set ws = DBEngine.Workspaces(0)
Set grp = ws.Groups(strGroup)
On Error Resume Next
strUserName = ws.groups(strGroup).users(strUser).Name
faq_IsUserInGroup = (Err = 0)
End Function
Microsoft Access 95 and Microsoft Access 97: In Microsoft Access 95 and Microsoft Access 97, you can now view User and Group information without being a member of the Admins group if you are using a Microsoft Access 95 or 97 workgroup database (System.mdw).
23. How can I obtain the groups that the current user belongs to without hard-coding an Admins ID and password in the code?Microsoft Access 2.0: There is a way to do this; however, Microsoft does not support it. It will allow all users to view all groups and users membership. You do not need to use this technique in Microsoft Access 95 or Microsoft Access 97, because you can now view group and user membership without being a member of the Admins group.Note: Make sure you have made a backup copy of your workgroup information file (System.mda or System.mdw) before starting this process. If something goes terribly wrong, you stand a chance of locking yourself out of your application. The method relies on granting permissions to the Users group for two tables that reside in the workgroup file (which store user and group information):
MSysAccounts You typically get an error if you try to read the information in these tables and you are not a member of Admins, because these tables are set up with no permissions for any other users or groups. Because the workgroup information file is just a Microsoft Access database, you can open it directly while logged on as a member of the Admins group. Assign Read Data permissions to these two tables for selected users or groups (assigning permissions to the Users group will grant it for all users). These are the steps required to provide group membership information to selected users and groups. It assumes the name of the workgroup file is System.mda.
Microsoft Access 95 and Microsoft Access 97: Not needed. All users can see group and user membership from the security menus or from code.
24. How can I prevent users from creating new objects in my database?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: The following function will remove permissions to create new tables or queries in the current database from the specified user. The argument, strUser, can be either a User or a Group. There is no way to remove permission to create forms, reports, macros, or modules in Microsoft Access. You need to have Administer permissions in order to run this code (shown in Microsoft Access 2.0 format). Function faq_NoNew (strUser as String)
Dim db As Database
Dim con As Container
Set db = DBEngine(0)(0)
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions And Not DB_SEC_CREATE
End Function
If you want to set the permission to create new tables and queries back again, use:
Function faq_OKNew (strUser as String)
Dim db As Database
Dim con As Container
Set db = DBEngine(0)(0)
Set con = db.Containers("Tables")
con.UserName = strUser
con.Permissions = con.Permissions Or DB_SEC_CREATE
End Function
Microsoft Access 95 and Microsoft Access 97: The intrinsic constants are the difference here. The correct spelling can be found in the Object Browser or listed in the table in item 3, "What has changed in Microsoft Access security between Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97?".
con.Permissions = con.Permissions And Not dbSecCreate 25. How can I prevent users from updating any tables from any means other than through forms?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: The following technique requires logging on to a "secured workspace" in code as shown in the following code fragment:
Set ws = dbengine.CreateWorkspace("NewWS","DevUser","ValidPwd")
Members of the Admins group may only set permissions, so DevUser must be a valid user account in the Admins group, and ValidPwd must be a valid password.
Warning: Using this technique in Microsoft Access 2.0 could compromise the security of your application (see item 7, "What's all this about a security hole in Microsoft Access 2.0?"). If you're not using Microsoft Access 2.0, or you don't think your users will stumble on the security hole, these are the steps to follow after removing ALL rights to underlying tables for all users and groups:
When the form opens, it will trigger the Activate event, which will provide appropriate permissions to the form's record source, and then, on the first time through, assign the record source itself. The Deactivate event removes permissions. When the window is brought back into focus, the Activate event sets them back again. When the form closes, the Deactivate event is triggered before the Close event. Pop-up forms present a different problem because they do not trigger the Activate and Deactivate events. The code for these events would need to be transferred to the Open and Close events, respectively. They would either need to have their Modal and PopUp properties set to Yes, or to be invoked via the A_DIALOG argument of the OpenForm action, which has the same effect of forcing the form to maintain focus while it is visible. OnActivate : =faq_TblQueryGivePermissions ("MyQuery", Form)
OnDeactivate : =faq_TblQueryRemovePermissions (Form)
Function faq_TblQueryGivePermissions (pstrRecordSource As String, _
pfrm As Form)
'----------------------------------------------------
' Use OR operator to assign permissions additively
' Pass permission settings to secured workspace routine
' If recordsource is null, set it up (first time only)
' ---------------------------------------------------
Dim lngPermissions As Long
lngPermissions = lngPermissions Or DB_SEC_RETRIEVEDATA
lngPermissions = lngPermissions Or DB_SEC_INSERTDATA
lngPermissions = lngPermissions Or DB_SEC_REPLACEDATA
lngPermissions = lngPermissions Or DB_SEC_DELETEDATA
faq_SetPermissions (pstrRecordSource), lngPermissions
If IsNull(pfrm.RecordSource) Then
pfrm.RecordSource = pstrRecordSource
End If
End Function
Function faq_TblQueryRemovePermissions ( pfrm as Form )
' ----------------------------------------------------
' Reset permissions by calling secured workspace routine
' ----------------------------------------------------
faq_SetPermissions (pfrm.RecordSource , DB_SEC_NOACCESS
' faq_SetPermissions (pfrm.RecordSource , DB_SEC_RETRIEVEDATA )
End Function
Sub faq_SetPermissions ( pstrTblQry as string , _
plngPermissions as Long )
' ----------------------------------
' log in to secured workspace
' establish tables container (also contains queries)
' establish table / query document and user
' set permissions to passed value
' ----------------------------------
Dim ws As WorkSpace, db As Database, con As Container
Dim doc As Document
Set db = dbengine(0)(0)
Set ws = dbengine.CreateWorkspace("Temp","System User", _
"Tricky Pswd")
Set db = ws.OpenDatabase(db.Name)
Set con = db.Containers("Tables")
Set doc = con.Documents( pstrTblQry )
doc.UserName = CurrentUser()
doc.permissions = plngPermissions
con.Documents.Refresh
db.Close
ws.Close
End Sub
26. How can I secure some parts of my application (an add-in), yet make others totally open to any Microsoft Access user?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: You will need two workgroup information files: one that you will use to develop and secure your application, and one that you use to distribute it. The distribution workgroup file can be the default that is installed with Microsoft Access, if you prefer. When you secure your application using your workgroup file, you will want to make sure that the default Users group has only the permissions you want all users to have, while full permissions are held only by a member of your own System.mda/mdw. Granting the Users group permissions on "public" objects means that the Users group in other workgroup files (including the default System.mda/mdw) will inherit them. You will need to log on to your development workgroup file in order to modify your entire application.
This technique is, unfortunately, not bulletproof in Microsoft Access 2.0 (see item 7, "What's all this about a security hole in Microsoft Access 2.0?").
27. How do I prevent users from holding down the SHIFT key to bypass the AutoExec macro?Microsoft Access 2.0: There is no way to keep them from doing this, unless you distribute your application using the run-time version of Microsoft Access. In the full retail version of Microsoft Access, there are only a few methods you can use to discourage people from doing this:
Microsoft Access 95 and Microsoft Access 97: You can set the AllowBypassKey property to False to prevent a user from bypassing the startup properties and the AutoExec macro by holding down the SHIFT key. This option is only available through code. The following function could be run from the Debug window by typing:
?DisableShiftKeyBypass()This function only needs to be run once for each database. (Of course, a sophisticated user with adequate permissions could easily create and run a similar function that would reset the property back to False, allowing the AutoExec macro to be bypassed.) Function faq_DisableShiftKeyBypass() As Boolean
'The next time the database is opened
' after this function has been run,
' the autoexec macro will not be bypassed,
' even if the shift key is pressed.
On Error GoTo errDisableShift
Dim db As DATABASE
Dim prop As Property
Const conPropNotFound = 3270
Set db = CurrentDb()
db.Properties("AllowByPassKey") = False
faq_DisableShiftKeyBypass = True
exitDisableShift:
Exit Function
errDisableShift:
'The AllowBypassKey property is a user-defined
' property of the database that must be created
' before it can be set. This error code will execute
' the first time this function is run in a database.
If Err = conPropNotFound Then
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, False)
db.Properties.Append prop
Resume Next
Else
MsgBox "Function DisableShiftKeyBypass did" & _
" not complete successfully."
Faq_DisableShiftKeyBypass = False
GoTo exitDisableShift
End If
End Function
28. How do I prevent a run-time application from being opened in full retail Microsoft Access?Microsoft Access 2.0: You’ll need to create a function that uses SysCmd() to check to see whether the database is currently opened under the run-time version: Function KillIt() As Integer
If Syscmd(SYSCMD_RUNTIME) = 0 Then
' use the following intrinsic constant
' for Microsoft Access 95 or 97
' If Syscmd(acSysCmdRuntime) = 0 Then
Application.Quit
End If
End Function
Call this function from your AutoExec macro, as the very first action. This is the first line of defense. Unfortunately, anyone who knows to hold down the SHIFT key will not be kept out by this function. You may also want to put additional calls to KillIt() in your database – for example, in the Open events of your forms, or as calculated columns in queries. You may not be able to keep people out, but you can certainly make it inconvenient for them to be in.
Microsoft Access 95 and Microsoft Access 97: You can set the AllowBypassKey property as outlined in item 27, "How do I prevent users from holding down the SHIFT key to bypass the AutoExec macro?". This will cause the AutoExec macro to always execute, so you wouldn't need to place your kill function in all your major forms or queries.
29. Does Microsoft Access security still work if I use OLE automation or Microsoft Query to manipulate Microsoft Access tables?Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: Yes. Microsoft Access security is set at the Microsoft Jet database engine level, which cannot be overridden by any other application. The Jet database engine verifies that another user or application has sufficient permissions before it hands over any data. Permissions you set for each object will be honored.DDE Alert: One special thing must be kept in mind when accessing data via DDE: because you can connect to a currently running session of Microsoft Access, all permissions are decided by the permissions of the user who logged on to that session, not the user who connects via DDE.
30. How can I use the Security Wizard without creating an encrypted database?Microsoft Access 2.0: After the Security Wizard has run, you can decrypt the database by choosing File/Encrypt/Decrypt Database, and selecting your database with the Decrypt option. In order for this to appear on the File menu, no database must be currently open.Microsoft Access 95 and Microsoft Access 97: Same as above, except the menu option is found under Tools/Security/Encrypt/Decrypt Database.
31. When I use the Security Wizard in Microsoft Access 2.0, it runs to 99%, and then freezesMicrosoft Access 2.0: This is a bug in the Security Wizard for Microsoft Access 2.0, which is triggered by not having any objects, other than tables, in your database. Create another object in your database (it can be an empty form, report, macro, or module), and then re-run the wizard.
32. I thought I secured my database, but someone opened it with his or her own workgroup file. Is Microsoft Access security broken?There are certain things that all workgroup (System.mda) files have in common–the default Admin user and the default Users group. If you give either of these permissions, you are in effect giving those permissions to the entire population of Microsoft Access users. (See item 26, "How can I secure some parts of my application (an add-in), yet make others totally open to any Microsoft Access user?")In order to keep this from happening, you need to follow the steps in item 1, "What are the steps to secure a database?", using the Security Wizard to keep the Users group from having any permissions. Microsoft Access is a PC desktop database, so by definition, it can't be secured because the file can always be copied. The tools and hackers exist out there to break into any physical file. Most so-called security holes in Microsoft Access arise because the security that does exist has been misunderstood and, as a consequence, has not been implemented correctly. As you can see from item 1, there are many steps, and it is easy to forget one, and therefore leave your database incompletely secured. In addition, the security hole in Microsoft Access 2.0 makes non-data objects especially vulnerable to hackers (see item 7 for more information). So, the answer is that Microsoft Access security can be counted on, to a point. If your security needs are truly stringent, you may want to consider a server product, such as SQL Server implemented on a locked-up installation of Microsoft Windows NT, for data security and a true compiled language, such as C++, for application security.
33. I want users in other groups besides the Admins group to be able to administer the database and add accountsMicrosoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: You can set this up by using two different workgroup information files: one for development, and one for distribution. You secure your application using the development file, assigning permissions to custom groups (implicit permissions) so that all permissions are inherited through group membership. You use a second workgroup information file to distribute your application. By entering identical group names and PIDs in both workgroup information files, permissions to objects in the database are inherited from the developer workgroup information file for those specific groups. You can then create a site administrator account in the distribution workgroup file and add it to the Admins group. The site administrator account will be able to create users and move them in and out of groups, and will be able to create and delete users and groups, but will not be able to modify permissions to database objects or even to open objects unless the site administrator inherits those permissions by being a member of other groups. The reason this works is that the remote site administrator is not present in the Admins group of the workgroup file used to secure the database; therefore, this account cannot administer permissions on objects in the database. Unlike the Users group, which is the same across all workgroup files, the Admins group is unique, its SID being encrypted from the strings used to create the workgroup file. The Admins group has Administer permissions that cannot be revoked, but only the Admins group can be used to secure the database. Therefore, the remote site administrator can manipulate user and group information (stored in the workgroup file), but not permissions on database objects (which are stored in the database, which "sees" only the correct Admins group as having the necessary permissions).This technique works equally well in Microsoft Access 2.0, Microsoft Access 95 or Microsoft Access 97. In Microsoft Access 2.0, work group databases have the extension, MDA; in Microsoft Access 95 and 97, the extension is MDW. Here are the steps to follow:
34. How can I "de-secure" a database?You need to have Administer permissions and/or be a member of the Admins group. Grant full permissions to the Users group and the Admin user account. Put the Admin user back in the Admins group and remove the password from the Admin user. You will not be prompted for a logon ID and password once the password is removed from the Admin user. Optionally, you can also run the Security Wizard again to transfer ownership of all objects back to the Admin user, but the wizard will also remove permissions from the Users group, which you may not want.
35. I lost/forgot my password and can't get into my databaseThis can be a real problem if you lost your password after you have downloaded this FAQ, because you probably have fully implemented security, and done so correctly. There are two approaches you can take:
If none of the above methods works, posting a message on one of the public forums on CompuServe or the Internet newsgroups will probably yield a third-party vendor who will be willing to help you work around this problem for a small fee.
36. Do I need a separate workgroup file for every database I develop for my department?Having a separate workgroup database for each database when you have essentially the same group of people using all of your applications can present administrative nightmares. It's much more efficient to create one secured workgroup database with many groups so that when user accounts are added or removed, it only has to be done in one place. Bear in mind that user profiles and group membership are stored in the workgroup database (System.mda/mdw) while actual permissions to various objects are stored with individual databases. To ease the pain of trying to figure out which group belongs to which database, you could tag group names with the database name to make decipherment easier. An example would be "acctUsers" for your accounting database, "mktUsers" for your marketing database, and so on. You simply remove all permissions from the Users group and all groups that don't apply to a particular database, and only grant permission to those groups that do belong.If you do end up with separate workgroup databases, you can easily create icons for each one. See item 19, "How do I work with a secured application and an unsecured application at the same time?" for more information on how to set this up.
37. How do I use DAO to manipulate permissions?Permissions can be set on the Container and Document objects and the Containers and Documents collections. Permissions are properties of Document objects that are assigned to specific Users and Groups. They are actually bit masks which are represented by security constants (see item 3.1, "Table 1: DAO Security Constants" for a complete listing).Setting permissions is done by combining the constants using the Visual Basic for Applications operators AND and OR, and by assigning the desired permission to the appropriate user or group. For example, the following code explicitly gives permission to the Users group to open the database in shared mode. All of the code in this section is shown with Microsoft Access 95 and Microsoft Access 97 intrinsic constants but will work just as well in Microsoft Access 2.0 by substituting the Microsoft Access 2.0 constants (see item 3, "What has changed in Microsoft Access security between Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97?" for a mapping of Microsoft Access 95/97 and Microsoft Access 2.0 constants). Dim db as Database
Dim con as Container
Set db = DBEngine(0)(0)
Set con = db.Containers("Databases")
con.UserName = "Users"
con.Permissions = dbSecDBOpen
You can also combine permissions with existing permissions. For example, the following lines of code add the permission to open the database exclusively, in addition to other permissions the Users group might have, by using the OR operator to add the permission:
con.UserName = "Users" con.Permissions = con.Permissions Or dbSecDBExclusiveIf you wanted to remove just that one permission, you would use the AND NOT operator to strip the OpenExclusive option while leaving other permissions intact: con.UserName = "Users" con.Permissions = con.Permissions And Not dbSecDBExclusiveIf you just want to check to see what permissions a user has, you use the AND operator and look for the specific permission. The following example checks to see if the Users group can open the Employees form in the sample Northwind database: Dim db as Database
Dim con as Container
Dim doc as Document
Set db = DBEngine(0)(0)
Set con = db.Containers("Forms")
' refresh to make sure the collection is current
con.Documents.Refresh
Set doc = con.Documents("Employees")
con.UserName = "Users"
If (doc.permissions And acSecFrmRptExecute) > 0 Then
Debug.Print "Users group can open Employees form"
Else
Debug.Print "Users group can’t open Employees form"
End If
38. I created a user in code but the user isn't in the Users group and can't start Microsoft AccessMicrosoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: The most likely cause of this is that you need to add the user to the Users group as a separate step in your code. When you create a user through the user interface, this step is taken care of for you automatically. The following function will create a new user account and add it to the Users group. The syntax uses several features of Microsoft Access 95 and Microsoft Access 97 that do not exist in Microsoft Access 2.0. The function can be modified by removing the line continuation characters (_), the optional varPwd argument to the function itself, and the IsMissing line where the optional argument is evaluated. The inline error handling line, "If Err.Number = 0 Then" can be re-written "If Err = 0 Then". The mapping of intrinsic constants is shown in the table in item 3. Public Function sCreateUser(ByVal strUser As String, ByVal _
strPID As String, Optional varPwd As Variant) As Integer
'-----------------------------------------------------------
' Create a new user and add them to the Users group
' Returns True on success, False if user already exists
'===========================================================
Dim db As DATABASE
Dim ws As Workspace
Dim usr As User
Dim grpUsers As GROUP
Dim strSQL As String
' if the password isn't supplied, make sure you
' pass an empty string for the password argument
If IsMissing(varPwd) Then varPwd = ""
Set ws = DBEngine.Workspaces(0)
ws.Users.Refresh
On Error Resume Next
' check to see if user already exists by using inline
' error handling to trap any errors caused by setting
' a reference to a possibly non-existent user
strUser = ws.Users(strUser).Name
If Err.Number = 0 Then
MsgBox "The user you are trying to add already exists.", _
vbInformation, "Can't Add User"
sCreateUser = False
Else
' go ahead and create the user account
Set usr = ws.CreateUser(strUser, strPID, varPwd)
ws.Users.Append usr
ws.Users.Refresh
' now add the user to the Users group
Set grpUsers = ws.Groups("Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
sCreateUser = True
End If
End Function
39. I created a user and I can't log on as that userMicrosoft Access 2.0, Microsoft Access 95, and Microsoft Access 97: When you create a user using the Microsoft Access security menus, you are prompted to fill in a dialog box with the name of the new user and a Personal ID, or PID. The PID is not a password, and typing it in the Password dialog box will cause the logon attempt to fail. After you create the user, log on as the new user and don't type anything in the Password dialog box. You can then set a password for this new user account using the Microsoft Access menus. If you create your users through code, you can set both the PID and the password at the same time, as shown in the code in item 38, "I created a user in code but the user isn't in the Users group and can't start Microsoft Access".
40. I ran the Security Wizard but users from another workgroup can still open the databaseMicrosoft Access 2.0, Microsoft Access 95: The reason for this is that the Security Wizard in both versions of Microsoft Access does not remove the Open/Run permissions to the database itself from the Users group. If you have followed all of the steps to secure your database and are sure you haven't left anything out, remove the Open/Run permission from the Users group. If you still have a problem, then possibly you left out a step in securing your application (most commonly neglecting to remove the Admin user from the Admins group or having the Admin user retain explicit permissions on objects).Microsoft Access 97: The Security Wizard removes Open/Run permission from the Users group.
41.How do I implement security when I am using Visual Basic 3.0 as a front-end?Visual Basic 3.0/4.0: You must own a copy of Microsoft Access (Microsoft Access 2.0 for Visual Basic 3.0 or Visual Basic 4.0-16; Microsoft Access 95 for Visual Basic 4.0-32) to secure a database because Visual Basic doesn't include the Workgroup Administrator program that ships with Microsoft Access. (See item 1 for information on securing a database).Visual Basic 3.0: Visual Basic 3.0 does not let you directly set permissions or owners for objects. You'll need to use Microsoft Access to set up your security scheme. For further information, see the KnowledgeBase article Q105990, "How Visual Basic Handles Security Set by Microsoft Access". Visual Basic 4.0: You can implement security in Visual Basic 4.0 using DAO, just as you can in Microsoft Access, assuming that you are using 32-bit Visual Basic 4.0 with a Jet 3.0 database. In general, you want to manipulate security using Microsoft Access because it's a lot easier than writing Visual Basic code to do so. You can always modify permissions later through Visual Basic.
42. Do I need to use a System.mda when I'm using Visual Basic to control secured objects?Visual Basic 3.0/4.0: Yes. See below.Visual Basic 3.0: You can use the SetDataAccessOption statement within Visual Basic to point to the .INI file that points to the appropriate system database, and then use the SetDefaultWorkspace statement before any data access takes place to log on as a particular user. See the Office Developer's Kit for more information, or the KnowledgeBase article Q105646, "ACC1x: How to Use a Microsoft Access Database in Visual Basic". The .INI file must include the SystemDB topic of the Options section as follows: [Options] SystemDB=c:\msoffice\access\system.mdaVisual Basic 4.0-16: If you're using Visual Basic 4.0-16, then you'll need to create a .INI file as with Visual Basic 3.0. In addition, use the following code before accessing the Jet 2.5 database: DBEngine.IniPath = c:\myapp\myapp.ini DBEngine.DefaultUser = "LogonUserName" DBEngine.DefaultPassword = "LogonPassword"The .INI file must include the SystemDB topic of the Options section like this: [Options] SystemDB=c:\msoffice\access\system.mdwThe user name and password can be variables or references to controls. Note that you must use the DefaultUser and DefaultPassword methods; the CreateWorkspace method of DBEngine will not work properly if you try to pass along the username and password without first using the DefaultUser and DefaultPassword methods. Visual Basic 4.0-32: If you're using Visual Basic 4.0-32, you must use code similar to the following code before accessing the Jet 3.0 database: DBEngine.SystemDB = c:\myapp\system.mdw DBEngine.DefaultUser = "LogonUserName" DBEngine.DefaultPassword = "LogonPassword"Notice that with Visual Basic 4.0-32 you can use the SystemDB method to directly point to the workgroup file, bypassing the need for a .INI file reference. Again, the user name and password can be variables or references to controls. Note that you must use the DefaultUser and DefaultPassword methods; the CreateWorkspace method of DBEngine will not work properly if you try to pass along the username and password without first using the DefaultUser and DefaultPassword methods.
43. How do I open a password-protected database from Visual Basic?If you have protected a database using the simplified password-based security, you can open the database using Visual Basic 4.0 using code similar to the following (assuming the database is located at "c:\path\database.mdb" and the password is "curry": Dim wrk as Workspace
Dim dbProtected as Database
Set wrk = DBEngine.Workspaces(0)
Set dbProtected = wrk.OpenDatabase("c:\path\database.mdb", _
False, False, ";PWD=curry")
This same code will also work with Microsoft Access 95 or Microsoft Access 97 when attempting to open a password-protected database programmatically. (The above code won't work with Microsoft Access 2.0 or Visual Basic 3.0, because the Jet 1.x and Jet 2.x database engines don't support database passwords.)
44. Additional Sources of Information:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||