Securing Files Without User-Level Security | Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)

Securing Files Without User -Level Security

Access offers a rich array of security features to support the needs of different types of Access applications. Many multiuser Access applications can benefit from user-level security, which lets developers assign database permissions to individual users and groups of users. Other applications have more specialized needs. This section offers a brief overview of security techniques other than user-level security.

One of the strengths of Access is its ability to serve different audiences. Some applications are code intensive , so you need to secure your investment in source code. Other applications serve small workgroups with limited technical capabilities but still require minimum levels of security to restrict access to data. Still other applications benefit from a custom user interface (UI) that restricts functionality simply by exposing a restricted set of commands.

Using a Custom Interface

Sometimes you can adequately secure an application by simply replacing the standard Access interface with a custom one. Choose Startup from the Tools menu to open a dialog box that lets you specify a custom application title and icon, a custom startup form, and custom menus to replace the standard Access ones. This dialog box also lets you suppress the Database window and the status bar. You can also manipulate the features of the Startup dialog box programmatically. If this type of manipulation is suitable for your security needs, consider augmenting it with the Show and Hide methods , the Visible property, and the Hidden property for objects in the Database window.

Note

See earlier chapters for creating custom features. Chapter 5 introduces building a custom startup form, and Chapter 8 demonstrates how to eliminate access to the Database window when your application has a custom startup form. Chapter 8 also illustrates programming techniques for building custom menus and adapting the built-in menus with the CommandBars object model.

Setting a Database Password Manually

You can require users to enter a password to gain unrestricted access to all Access data and database objects. Passwords are easy to administer compared to user-level security. Password security is appropriate if you have a group whose members need equal access to all elements of a database file but not everyone in the office is a member of that group.

You cannot use a password-protected file as a member in a replica set because Jet database replication cannot synchronize with a password-protected file. You should also be careful about linking to database files with password protection because anyone who can access the file that links to the protected file has unrestricted access to the protected file. Furthermore, Access stores an encrypted version of the password along with other information about the linked file. Finally, if a user changes the password for a linked file, Access prompts for the new password the next time another database file links to it.

To assign and remove a database password, you need exclusive access to the file. You obtain exclusive access when you open a file by choosing Open Exclusive from the Open button's drop-down menu in the Open dialog box.

To assign a database password perform the following steps:

On the Tools menu, point to Security then choose Set Database Password.
In the Set Database Password dialog box, enter your password of choice in the Password and Verify boxes and then click OK. The application will ask for the password the next time a user opens the file.

To remove a database password, do the following:

On the Tools menu, point to Security and choose Unset Database Password.
Type the password in the Unset Database Password dialog box. This removes the initial prompt for a password before a database is made available.

Setting a Database Password Programmatically

You can also programmatically initialize, change, and remove a database password. The next three samples draw on Jet SQL, offering a simple approach to managing the security for Jet databases. Use the ALTER DATABASE PASSWORD statement to set and remove database passwords. The first argument signifies the new password, and the second argument denotes the old password. You represent the passwords as strings delimited by square brackets ([ ]), except in two cases. As with all SQL terms and expressions, you should leave a blank space between SQL statement arguments, such as the delimited old and new passwords. When initializing a database password, use NULL to signify the old password. When removing a database password, designate NULL as the new password. The keyword NULL should not appear within square brackets.

The CreateDBPassword procedure shown next initializes a database password for a database by performing three main steps. First the code constructs the string based on Jet SQL to assign a password to a database for the first time. The password is the word password and is the first argument after the ALTER DATABASE PASSWORD keyword. Notice that NULL appears as the second argument. This is because there is no password to replace. The next block of code creates a connection to the database for which the procedure assigns a password. Notice that the procedure opens the database file exclusively (see the assignment for the Mode property). This is imperative when you manipulate the password for a database. The sample assigns a password to the UserLevel.mdb file. The UserLevel.mdb file serves double duty for user-level security and password-protected file demonstrations . If you haven't copied the file from the book's companion content, you'll need to do it now to run this sample. When you do so, remember to remove the read-only file property setting. You can, of course, use any other file you choose, but you must revise the listing accordingly . The third step executes the SQL string with the ADO Connection object created in the second step. This takes just one line.

 SubCreateDBPassword() Dimcnn1AsADODB.Connection Dimstr1AsString     'SQLstringtoinitializethedatabasepassword 'topassword str1= "ALTERDATABASEPASSWORD[password]NULL;"     'Openunsecureddatabase Setcnn1=NewADODB.Connection Withcnn1 .Mode=adModeShareExclusive .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data " &_  "Source=C:\Access11Files\UserLevel.mdb;"     'ExecuteSQLinstr1tosecuredatabasewithapassword .Execute(str1) EndWith     'Cleanupobjects cnn1.Close Setcnn1=Nothing     EndSub

When your application changes the password for a database, you have to log in with the old password (see the next code sample). To do this, you assign the old password to the Database Password property of the Connection object for the database whose password will change. This is one of the extended properties for the Connection object, so you need to use a special syntax, as the sample demonstrates. The sample also confirms the Jet SQL syntax for replacing one password with another. In this instance, "foo" replaces the password. Aside from these two modifications, the code sample has the same structure as the preceding one; both initialize a database password for an Access database file.

 SubChangeDBPassword() Dimcnn1AsADODB.Connection Dimstr1AsString     'SQLstringtochangethedatabasepassword 'frompasswordtofoo str1= "ALTERDATABASEPASSWORD[foo][password]"     'Opensecureddatabasewithpasswordforits 'databasepassword Setcnn1=NewADODB.Connection Withcnn1 .Mode=adModeShareExclusive .Provider= "Microsoft.Jet.OLEDB.4.0" .Properties("JetOLEDB:DatabasePassword")= "password" .Open "DataSource=C:\Access11Files\UserLevel.mdb;"     'ExecuteSQLinstr1tochangedatabasepasswordfrom 'passwordtofoo .Execute(str1) EndWith     'Cleanupobjects cnn1.Close Setcnn1=Nothing     EndSub

The next code sample demonstrates how to remove a database password from an Access database file. This sample borrows heavily from both of the preceding ones. In fact, the only way that this sample differs from the preceding two is in its definition of the SQL string. When you remove a password from a database, make the first argument for the ALTER DATABASE PASSWORD keyword NULL . The second argument password should be the current password for the database. This is the same password that you use to connect to the database. If you've been following the progression of these code samples, you already know that this password will be "foo."

 SubRemoveDBPassword() Dimcnn1AsADODB.Connection Dimstr1AsString     'SQLstringtoclearthedatabasepassword 'fromadatabasefile str1= "ALTERDATABASEPASSWORDNULL[foo]"     'Opensecureddatabasewithfooforits 'databasepassword Setcnn1=NewADODB.Connection Withcnn1 .Mode=adModeShareExclusive .Provider= "Microsoft.Jet.OLEDB.4.0" .Properties("JetOLEDB:DatabasePassword")= "foo" .Open "DataSource=C:\Access11Files\UserLevel.mdb;"     'ExecuteSQLinstr1tocleardatabasepassword 'fromadatabasefile .Execute(str1) EndWith     'Cleanupobjects cnn1.Close Setcnn1=Nothing     EndSub

Setting a Module Password

Since its 2000 version, the Access UI has enabled password security for modules instead of offering user-level security. This new approach makes Access consistent with the other Office 2000 components . It applies to all standard and standalone class modules as well as the modules behind forms and reports .

Note	In the "User-Level Security via Jet SQL and ADO" section that appears later in this chapter, you will read about user-level security for module containers. This feature is available through Jet SQL and the Jet Engine rather than the Access UI.

You set password security once for all the modules in a Microsoft Visual Basic for Applications (VBA) project from the Visual Basic Editor (VBE). Choose the Properties command for the project from the Tools menu to open the Project Properties dialog box. The Protection tab (shown in Figure 10-1) offers the Lock Project For Viewing check box and text boxes for entering and confirming a password for the module. Assigning a password for viewing modules in a project does not prevent your code from running as though it were not protected. If you assign a password but do not select the Lock Project For Viewing check box, anyone can edit the code but the Project Properties dialog box will be protected. You remove password security from the modules in a project by clearing all entries on the Protection tab.

Figure 10.1: You use the Protection tab of the Project Properties dialog box to set password security for the modules in a project.

After securing your modules with a password, you must enter the password once per session before you can view, edit, or add new code. You can secure forms and reports with both user-level security and module password security. User-level security applies to designing and using forms and reports. You can require a user to have Modify Design permission to add controls to forms. That user will also need the password for modules in a project to write event procedures for the control. Conversely, knowing the password for the modules in a project does not enable a user to add controls to or remove controls from a form. Also, Modify Design permission does not allow a user to change the HasModule property of forms and reports to No; the user must first enter the password for the modules in a project.

Using .mde Files

An .mde file totally secures the code for an Access database file. When you convert an .mdb file to an .mde file, Access compiles all your modules, removes editable code, and compacts the destination database while preserving the original .mdb file. The size of your database will shrink because of the removal of editable code. Also, because the conversion optimizes memory usage, your code will run faster.

To convert an .mdb file to an .mde file, you must have exclusive access to the file. (See the "Setting a Database Password Manually" section in this chapter for instructions on how to open a database exclusively.) In addition, the database file must have a 2002 file format; Access 2003 and Access 2002 share this file format, although the Access 2000 file format is the default file format for Access 2000 through Access 2003. On the Tools menu, point to Database Utilities and then choose Make MDE File. After saving the converted file, be sure to save your original file. The only way to edit or add to the code in a database file is to modify the original file and then convert it to an .mde file. After making an .mde file based on an .mdb file, you will have two files with the same file name but with different extensions (the original one with .mdb and the new one with .mde). Save the original .mdb to make the underlying code visible and available for editing.

Note	You can use the Advanced tab of the Options dialog box to select the file format for the next database that you create. Use the drop-down box for the default file format to select the file format.

An .mde file has some restrictions:

You cannot modify or add forms, reports, or modules.
You cannot import or export forms, reports, or modules to a standard .mdb file. You can, however, freely import and export tables, queries, macros, and shortcuts for data access pages with other database files.
You cannot add, delete, or change references to other object libraries or databases.
You cannot dynamically change code because .mde files contain no editable code.
You cannot convert any existing member of a replica set to an .mde file, but an .mde file can participate in a replica set.
An .mde file can reference another database file only if that file is also an .mde file. You must start converting .mdb files (or .mda add-in files) that are referenced before you convert the .mdb file that references them. The new reference must point at the new .mde file.