The Microsoft Access Security Apparatus: Other References

Last edit: 08-08-15 Graham Wideman

Access

The Microsoft Access Security Apparatus: Other References
Article created: 2005-09-01

Overview

There are a number of other userful and/or prominent references available for the Access security apparatus. I provide a list here, and a detailed critique of some of them below.

Num	Link	Preview Quote
1	MSDN Library: Exploring Microsoft Access Security (July 2002)	Introduction In earlier versions of Microsoft® Access (prior to Microsoft Access 2000), security was sometimes looked at as too difficult for anyone to understand and use. There were a number of steps that the user had to navigate, in order, and to forget a step or to get the sequence wrong could have disastrous effects. With the advent of the Security Wizard in Microsoft Access for Microsoft Windows® 95 and the improvements made to the Security Wizard in Microsoft Access 2000, implementing security in Access has become much easier. However, even with this additional assistance, it is extremely important that you understand your options and are aware of the process used to protect the data and objects in your database. Failure to do so could, at a minimum, result in unsecured data or, in an extreme case, lock you out of your database. There are a number of ways that you can protect an Access database and the objects it contains. In this article, we will examine some of the ways that you can protect the objects that comprise your database, to include the data, the objects that it contains such as forms and reports, and the code, which can constitute a considerable investment of intellectual property in your database. The techniques discussed in this article apply only to Microsoft Access database (.mdb) files. (GW Note: Code examples use ADO, which I think is less clear than using DAO)
2	MSDN Library: Microsoft Access User-Level Security or... Microsoft Access User-Level Security	The following sections discuss the significance of a workgroup-based user-level security model; how you can create user and group accounts to create a workgroup and establish permissions; and how you can use the tools that Microsoft Access provides, such as the User-Level Security Wizard, to help secure your databases. You can also establish security and perform security actions that arent available using the Microsoft Access user interface by using Data Access Objects (DAO) in Visual Basic. GW Comment: As do many articles, this one includes an error in the method for reading permissions. Incorrect: (docTemp.AllPermissions And dbSecRetrieveData) > 0 Correct: (docTemp.AllPermissions And dbSecRetrieveData) = dbSecRetrieveData
3	FAQ by Access MVPs: 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.
4	Real World Microsoft Access Protection And Security	Article: Introduction to Microsoft Access Security And Passwords
5	Lit Window: Howto: Create a secure database for MS-Jet (Access 2000) from a program. Date: 15-th, January, 2005	This article shows you how to create a new MS-Access compatible database file (.mdb) from a C++ or VBA or Java program using ODBC drivers and set up detailed access rights based on users and roles using a workgroup file (.mdw). After reading this article you should be able to:

Critiques

1. MSDN Library: Exploring Microsoft Access Security July 2002

As of Sept 2005, this is the most recent MSDN article on the subject, and one would hope it to be authoritative. While it does contain much useful info, there are some pretty murky sections and some errors that need addressing.

One general comment on the code sections: This article provides examples using the ADO/ADOX library. I find this a considerable distraction for the reasons given here. (On the other hand, if you want to write code that might upsize from Access to SQL Server or Oracle, perhaps the additional obfuscation at this stage is worth it.)

Section	Article Says...	My Comments
Introduction	In earlier versions of Microsoft® Access (prior to Microsoft Access 2000), security was sometimes looked at as too difficult for anyone to understand and use.	The security apparatus changed little for 2002, it's just that the wizard was added.
Encrypt or Decrypt the Database	The simplest (and least secure) method of protection is to encrypt the database.	This suggests that other topics, such as User-Level Security, are perhaps more-secure ways to protect the database, which is nonsense. What is probably meant here is that MS Access's encryption is weak, so if you want to prevent bad guys from using tools to crack it you have to prevent them from getting their hands on the mdb files. This may or may not fit conveniently with your scenario.
	Encryption can be used to keep casual users from accessing the information in your database when you transmit a database electronically, or when you store it on floppy disk, tape, or compact disc.	This is merely an incomplete list of scenarios where the bad guys could grab a copy of your mdb and get to work on it with the power tools. The fact that a floppy or CD is involve is irrelevant.
User-Level Security	In addition to share-level security, you can also use user-level security, which provides the most restrictive access and gives you the most control over the database and objects it contains	No you can't. Share Level (password only) security is mutually exclusive with User-Level security. The author means to say that User-Level security is an alternative to Share Level.
	The only way to prevent users from modifying the queries, macros, or data access pages in your database is to put the database file (or data access pages) on a protected file share.	... where the database would not be usable at all by users without access to those files... so not a very helpful statement. The subsequent sentences put it straight: There is no way to completely prevent theft of data by users when users have to have read access to the data files, and there's no way to completely prevent malicious data alteration when users have to have write access to contribute to the data. Instead use a database server.
About Access User-Level Security	The workgroup information file holds group and user information, including passwords, which determines who can open the database and the privileges they have on the objects in the database.	The workgroup file contains only the user and group info, NOT the permissions info, which is instead in the database containing the objects upon which the permissions are granted.
	The workgroup information file contains built-in groups (Admins and Users) and a generic user account (Admin) with administrative (unlimited) privileges on the database and the objects it contains.	This is the case in the default workgroup system.mdw. Implementing security involves creating one or more other workgroup files with alternative arrangements.
	When you install Access, the setup program automatically creates a workgroup information file that is identified by the name and organization information you specify.	This repeats a common confusion. The workgroup file is not "identified by" name and organization. Instead, the name and organization are used as inputs to the calculation of SIDs, and workgroups with the same inputs thus produce the same SIDs for the same users. For full details see here.
	You can create user accounts in VBA without adding the new user account to the Users group. However, if you don't also add that user account to the Admins group, the user won't be able to successfully start Access because many of the tables that Access uses internally to administer the database are mapped to the permissions of the Users group.	Yikes! This should be "Users" group. Ie: When you add users from code, you should also add them to the Users group for the reasons stated in the last part of the sentence. (Obviously adding all users to the Admins group will potentially give them permissions they shouldn't have).
Secure an Access Database with the Security Wizard	4. In the second screen of the Security Wizard [..] Type a workgroup ID (WID). The WID uniquely identifies the Admins group for this workgroup file.	Again, the WID does not "uniquely identify the Admins group". It allows Jet to calculate an SID for Admins group that differs from the Admins SID produced by workgroup files using a different WID.
Programmatically Add and Remove Users and Groups	' Instantiate the Catalog object. Set catDB = New ADOX.Catalog With catDB ' Open Catalog object by using connection to the current ' database. .ActiveConnection = CurrentProject.Connection ' Create new user account. .Users.Append strUser, strPwd, strPID ' Append new user account to default Users group. .Groups("Users").Users.Append strUser End With The procedure then opens a connection to the current database and appends the new user to the Users collection of the Catalog object	Because ADO/ADOX is generalized for a variety of databases, its model revolves around database servers, where the same server is responsible for managing users and interfacing to data. In that context the code needs to "connect to the database" before manipulating users. But in the Jet context this is miseading. Here the statement .ActiveConnection = CurrentProject.Connection gets ADOX ready for action, but the actual manipulation of the Catalog.Users and Catalog.Groups collection actually causes changes in the MDW workgroup file, NOT in whichever database file happens to be in use (and I suspect no actual database file is needed.) This is just one example why I feel that providing examples using ADO is a distraction.
Set Permissions Programmatically	Private Function SetGroupPermissions(ByVal strGroup As String, ByVal strTable As String, ByVal strObjectType As String, ByVal strAction As String, ByVal strRevokeEnum As String) As Boolean	This example looks problematic. The caller provides Table as a string argument (presumably table name), but the code then somehow knows about tblTable. Furthermore, providing arguments like ObjectType and Action as strings is prone to error -- and this example provides no guidance as to what are legitimate strings one might provide.

Go to: