| This chapter has already discussed dealing with user IDs and passwords in non-Jet, ODBC Direct data sources. This is usually managed in a connection string, whether that string is supplied as the value of the Connection object's ConnectString property or as an argument to its Open method. As mentioned in the prior section, "Opening a Database Using DAO," DAO does not offer a Connection object that is distinct from a Database object. Furthermore, Jet databases are secured in a fashion that's very different from, say, a SQL Server database. This section provides an overview of the ways that Jet databases specifically, Access databases are secured, as well as code that enables you to deal with them. An Access database has two distinct methods by which you can deter a user from taking unauthorized actions: a database password and user-level security. Using Access Database Passwords Setting a database password is by far the easier of the two methods, and also the most easily defeated. To secure an Access database by means of a password, you take these steps: - At the outset, the database that you want to secure should be closed.
- Begin by starting Access. Do not begin by double-clicking the icon of the database you want to open. Instead, start Access from the Start menu or from some other shortcut that targets the Access application.
- If Access displays a window that enables you to open a blank database, or to create a new database, or to open an existing file, dismiss the window.
- Choose File, Open. In Access's Open dialog box, navigate to the location of the database you want to secure. When you find the database, select it.
- In the lower-right corner of the Open dialog box, click the arrow on the right edge of the Open button to display the drop-down list shown in Figure 8.11.
Figure 8.11. It's also necessary to open a secured database in exclusive mode if you want to unset a database password. 
- Click Open Exclusive to open the database in exclusive mode.
- From the Tools menu, select Security. Then select Set Database Password from the cascading menu.
- Enter and verify a password in the Set Database Password dialog box, as shown in Figure 8.12. Then click OK.
Figure 8.12. As usual, it's best to choose a password consisting of a mix of letters, numbers, and special characters. 
If you need to use DAO to open a database that's been secured in this way, you have to use syntax similar to the following: Dim dbGLDatabase As DAO.Database Dim strDBPath As String strDBPath = "C:\Documents and Settings\Owner\My Documents\GL.mdb" Set dbGLDatabase = OpenDatabase (strDBPath, False, False, _ "MS Access;PWD=Dismal")
To accomplish the same thing with ADO, you would use code like this: Dim cnGeneralLedger As New ADODB.Connection Dim rsGL As New ADODB.Recordset Dim strDBPath As String strDBPath = "C:\Documents and Settings\Owner\My Documents\GL.mdb" With cnGeneralLedger .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Data Source") = strDBPath .Properties("Jet OLEDB:Database Password") = "Dismal" .Open End With
This illustrates a convenient aspect of ADO connections. After you've established the connection's provider, the connection's Properties collection is populated with properties that the Provider supplies. Here, the Jet engine provides a Data Source property (so does SQLOLEDB.1) and a Jet OLEDB:Database Password property (SQLOLEDB.1 does not supply a database password property that is qualified by Jet OLEDB). Although these properties are not members of the VBA or ADO libraries, they're automatically supplied by the provider. Using Databases Secured with User-Level Security A method of securing an Access database that's much stronger (although hardly bulletproof) than a database password is by means of user-level security. Before you're in a position to understand DAO or ADO code that opens Access databases that have user-level security in place, you need to understand how that security is arranged. When you install Microsoft Access on a computer, one of the files that's installed is named System.mdw. Its location depends on the version of Office that you're using, so you might need to search for it. System.mdw is termed a workgroup information file, and its purpose is to store information about The names of users who can open a Jet database, and the passwords that authenticate them The names of groups of users who can open a Jet database Which users belong to which groups
If no one has modified the System.mdw file (and you would know if you had done so), there is only one user in the workgroup information file, and that user is named Admin. Admin is the default user of an Access database. Any time you open an Access database, Access assumes that you are Admin, unless you say differently. NOTE Don't be misled by the term Admin. Although the name connotes the possession of overarching privileges, the Admin user in a secured Access database usually has relatively few privileges. When an actual user opens a secured database, that user is deemed to be the default Admin user. Because a secured database has been protected for one reason or another, it's typical to prevent the random user from deleting tables or renaming queries or taking other actions that would harm the database's functionality. Therefore, Admin is often restricted from doing much more than reading data.
After you've secured a database with user-level security, the process of opening it depends on whether the default Admin user has been assigned a password. If Admin has a password, Access displays the dialog box shown in Figure 8.13 when anyone tries to open the secured database. Figure 8.13. Access shows the name of the most recent user to supply one. Type over the default name if necessary. 
Unless you're armed with a profound sense of determination, some experience, and some tools, you're not going to get past the dialog box shown in Figure 8.13 without a recognized name and password. That means that if you want to open the database by means of DAO (or even ADO), you'll need to supply that name and password in your code. This chapter will discuss doing so presently, but first some more information about setting up user-level security. Implementing User-Level Security in an Access Database As you'll see when you begin using SQL Server if you haven't already security in SQL Server can be both tighter and easier to administer than is the case in Access. Nevertheless, you're likely to encounter Access databases that require maintenance for some years yet, and even to have need for new, secured Access databases. NOTE Much of the information in this and subsequent sections can also be found in a white paper in the Microsoft Knowledge Base. It is titled SECFAQ.doc, is authored by Mary Chipman, Andy Baron, et al., and includes a considerable amount of good information and advice about securing Access databases.
Establishing a Workgroup Information File Before you can initiate user-level security, you'll need a workgroup information file. If, as is probably the case, you're using Microsoft Office in a networked context, you'll need to establish that file in a share folder that users of the database can open. To establish a workgroup information file, you need to run either Wrkgadm.exe or the Workgroup Administrator. Which you use depends on the version of Office installed on your computer. In Office 97 and Office 2000, you run Wrkgadm.exe, an executable file usually found in the System subfolder of the Windows folder appropriate to the operating system you're using. Locate that file and double-click it to start. In Office 2002 and Office 2003, the workgroup administrator is part of the Access menu itself. You'll find it by selecting Security from the Tools menu, and then clicking Workgroup Administrator in the cascading menu. Whether you use Wrkgadm.exe or the Workgroup Administrator menu item, when you start it you'll see the dialog box shown in Figure 8.14. Click Create. Figure 8.14. The dialog box shows the path and name of the workgroup information file you're currently using. 
TIP One workgroup information file can manage information about multiple groups and users that use different databases. Even if you're managing several databases with user-level security, it's a good idea to keep all that data in one workgroup information file. So doing helps to minimize the administrative headaches.
When you've clicked Create, the Workgroup Owner Information dialog box shown in Figure 8.15 appears. Figure 8.15. You should specify a workgroup ID. This ID is not a password, but helps re-create the file if it is damaged. 
Be sure to make a separate note of the name, organization, and workgroup ID that you specify, and put it away where you and you alone can get at it later. Although many networks now perform comprehensive backups daily, it can take some time to restore a file from backup. You might want to re-create the file quickly from scratch, and to do that you'll need the information shown on Figure 8.15. When you click OK, the Workgroup Information File dialog box appears (see Figure 8.16). Figure 8.16. If you type the path and name yourself, don't forget to give the file an extension of .mdw. 
After you click OK on the Workgroup Information File dialog box, you'll see a Confirm Workgroup Information dialog box, summarizing the data you've just entered. If it's correct, click OK to return to the Workgroup Administrator, and then click OK again to return to the Access interface. If anything is incorrect, click the Change button to return to the Workgroup Owner Information dialog box, where you can correct any errors. Making Access Request a Password If it was running, quit Access, restart it, and open any database (including a new one). Because the default Admin user does not have a password, you won't be prompted to supply one. Choose Security from the Tools menu, and then click User and Group Accounts. This is the route to managing users and groups: their names, passwords, and membership. The User and Group Accounts dialog box appears (see Figure 8.17). Figure 8.17. The new workgroup file starts out with three default accounts: the Admin user and the Admins and Users groups. 
Verify that the default Admin user appears in the Name dropdown. Your next step is to temporarily assign a password for the Admin user, so click the Change Logon Password tab, which is shown in Figure 8.18. Figure 8.18. The Admin account generally has no password, so you usually skip right to the New Password box. 
Enter and verify a password for the Admin account, and click OK. TIP I set and remove the Admin account's password so frequently that I give it just a single character, and I use the same one every time. I am unrepentant: I do this solely to get Access to prompt me for my name and password. I can get through this process more quickly if I never have to think up a new password for Admin, and if it's only one character. There's no real exposure involved because Admin has a minimum of privileges anyway, and they are the same ones that a random user would have when he opens a database secured by this workgroup file, and after I'm in, I immediately clear the Admin user's password.
Establishing Yourself as a User Once again, choose Tools, Security, User and Group Accounts. On the Users tab (refer to Figure 8.17), click the New button to establish yourself as a user. The dialog box shown in Figure 8.19 appears. Figure 8.19. The personal ID that you supply is not the password. It must contain between 4 and 20 characters. 
After entering your name and a personal ID, click OK to return to the Users tab. With your name in the Name box, click on the Admins group in the Available Groups box, and click the Add button. This makes you a member of the Admins group. Put your name and personal ID in the same location where earlier you stored the workgroup name, organization, and workgroup ID information. Again: If you need to restore the workgroup information file, you might be able to do so from a tape backup, but it might be quicker to do so from scratch. Also, if at a later time you delete a user and subsequently want to restore him to membership in the workgroup information file, you'll need his personal ID (not his password). Quit Access again and restart it. Because you've given the Admin account a password, you'll be prompted for a name and password. Supply your name, not the Admin name. Your account does not yet have a password, so leave the Password box blank. But do give yourself a password at some point, just as you gave the Admin account a password. Making sure that you've started Access using your account, choose Tools, Security, User and Group Accounts, and click the Change Logon Password tab. Your username should appear near the top. (If it does not, because you did not log in on your account, quit Access and restart it, using your name to log in.) Supply a password, verify it, and then click OK. Restricting Admin's Permissions It's now time to restrict the permissions that belong to the Admin account. That's easy: just remove the Admin user from the Admins group. Choose Tools, Security, User and Group Accounts. If necessary, use the dropdown on the Users tab to select the Admin user. In the Member Of list, click Admins. Then click the Remove button. The Admins group disappears from the Member Of list, and the Users tab should appear as in Figure 8.20. Figure 8.20. You can't remove Admin (or any user) from the Users group. To do so, you have to delete the user's account entirely. 
Reviewing and Summarizing the Process This probably seems like a lengthy, complicated process, and it is and you're not nearly through yet because you haven't yet applied the security you've been arranging to any database. This is a good point to review what you've done so far. You've done the following: - Created a new workgroup information file to contain the names, IDs, and passwords of database users, the name and ID of the workgroup itself, names of groups, and user membership in groups.
- Assigned a password to the default Admin user. This forces Access to ask who you are when next you start Access.
- Established yourself as a user and joined the Admins group.
- Quit and restarted Access to get Access to ask who you are, and you identified yourself. As a member of the Admins group, you're able to remove the Admin user from the Admins group, and you did so.
The net effect of all this is to establish a workgroup information file that has you and only you as a member of the Admins group, and that has both you and the default Admin user as the members of the Users group. A note in a prior section suggested that you not read too much into the default username Admin. The same is not true of the group named Admins. It starts out with extensive privileges. For example, it's only as a member of Admins that you are able to remove the user Admin from the Admins group. This highlights a fundamental aspect of users and groups. The idea (and it's one used by SQL Server's Enterprise Manager and other database managers) is that the database is likely to have many users. Each user might have slightly different requirements, but most users share many requirements. For example, most users need to be able to see information in the database. Fewer users need to be able to add records and edit information in fields. Even fewer need to add, delete, and modify fields, tables, and queries. And so on. Because there are so many shared requirements, it's a good idea to set up groups: one that can read data but do nothing else, one that can both read and modify data, another that can modify both data and the structures that contain it, and so on. Given that approach, it's much easier to administer permissions. Instead of assigning permissions individually to each and every user, you assign permissions to groups and then arrange to have individual users enter the groups that give them the permissions they need. (A user can belong to more than one group.) If a user needs a slightly different permission structure than your groups supply, you can add or remove specific permissions for that user. So, by this point, you've laid the foundation: You've established yourself as the sole administrator, and now it's time to apply the foundation to a database. Securing the Database In this part of the process, you need to make sure that you are the user of record. You've done that if you're still running an instance of Access in which you identified yourself as the user at startup. If not, start Access and identify yourself as the user in response to Access's Logon prompt, supplying a password if you've already given yourself one. Now open a database that you want to make secure. (At the end of this process, you'll wind up with two databases: the original, unsecured version and a new, secured version.) Choose Tools, Security, User-Level Security Wizard. You'll see the wizard's first step, shown in Figure 8.21. Figure 8.21. The current user must be a member of the Admins group in order to use the Security Wizard. 
NOTE You might see some differences between the wizard on your computer and the figures in this section. Different Access versions have minor differences in the wizard's appearance.
You've already established a workgroup information file, with your user account as a member of Admins and with the Admin account a member only of Users. Therefore, choose Modify My Current Workgroup Information File and click Next. (You might not actually modify the workgroup information file, but you want to avoid creating yet another one.) Figure 8.22 shows the wizard's second step. Figure 8.22. The Select All and Deselect All buttons refer only to objects on the active tab. 
By default, all the check boxes for all the objects in the database are filled, meaning that they'll be secured. Only unusual circumstances would make you decide not to secure all the objects in the database, so you'll usually leave things as they are and click the Next button. TIP If you want the wizard to skip one or more objects, just click the appropriate tab and clear the appropriate check box.
After you click Next, the wizard appears as shown in Figure 8.23. Figure 8.23. You can accept the proposed group IDs or modify them to other values. 
In this step, you reject (the default) or accept the creation of new groups in your workgroup information file. These groups have pre-set permissions and are handy to have in place. For example, if your workgroup information file has the wizard-supplied group named New Data Users, a member of that group will be able to view data and insert new records. Unless a member also belongs to a group with broader permissions, though, he won't be able to modify or delete existing records, or change the design of any table, query, form, or other database object. It's useful to have such a group already defined because you're likely to want to give various users (Admin, for example) precisely those permissions. You can always delete one or more of these groups at a later time, so it doesn't hurt to have them. Fill their check boxes and click the Next button to view the next step in the wizard, shown in Figure 8.24. Figure 8.24. Any permissions you assign to the Users group will be available to anyone who opens the database. 
NOTE The proposed group IDs are regenerated each time you start the Security Wizard, so you needn't worry overmuch about another user obtaining and using them by rerunning the wizard.
You can assign some permissions to the Users group in this step, but it's not recommended. Anyone who opens the database as the default Admin user is automatically a member of the Users group and so will have any permission you assign to Users. For example, you might assign a Read permission for tables. This may seem benign enough, but so doing also assigns a Read Design permission, which you might not regard as quite so harmless. When you click Next, the wizard's next step appears (see Figure 8.25). Figure 8.25. If you prefer not to do it, a user can set his own password by logging on with a username and going to User and Group Accounts. 
The User and Group Accounts dialog box is a little more powerful than is this step of the wizard, but if you want to add other users here you can do so, and assign them to one or more default groups as shown in Figure 8.26. Figure 8.26. Because this user was already a member of Admins, you cannot remove him from that group in this step. 
Your basic choices in this step concern ease of assignment, not functionality. If as yet you've added just a few users, perhaps only one or two (Admin is not counted), it's easier to choose Select a User and assign the user to groups. If you've added several users and have only one or two groups, it's easier to choose Select a Group and assign users to the group. In any other case it doesn't much matter, and in fact you don't need to make any assignments at this point: You can do that later by choosing Tools, Security, User and Group Accounts. After you're finished making assignments, click Next to reach the wizard's final step (see Figure 8.27). Figure 8.27. By default, the unsecured version of the file has an extension of .bak. 
It's a good idea to store the unsecured version of the database in a path where users cannot get at it. A folder to which only you and your network's system administrators have access would be a good choice. Browse to that location to get its path in the box, edit its name if you want, and click Finish. When you do so, the wizard creates a summary report of the information you have entered, as shown in Figure 8.28. You can print it if you want. If you simply close the report, Access offers to save it as a snapshot (.snp) file that you can open and print later. You normally should do one or the other, so that you'll have a paper trail of the changes you've made. Figure 8.28. If you save the report as a snapshot file, you can find it later in the same folder as the secured database. 
Your database has now been secured. See the next section for information about how to open it using ADO. | 