Applying User-Level Security to Access Front-Ends

Descriptions of Access's user- and group-based security design range from labyrinthine to inscrutable. In reality, the Jet-based security model closely resembles that of Windows .NET/2000/NT and early versions of SQL Server. You create groups having particular sets of permissions for Access objects, and then add individual users to the groups. Unlike SQL Server and Windows versions of other client/server RDBMSs, Jet-based security doesn't integrate with Windows authentication.

Establishing Your Own Admins Name, Password, and PID

Access has two levels of security: user level and file level. The user-level security system requires each user of Access to enter a username and a password to start Access. You establish file-level security for back-end .mdbs through the network operating system, and grant users permissions to access shared folders. If the server's file system is NTFS, you can grant permissions for individual files, as described in the earlier "Establishing Network Share and File Security for the Back End" topic. Network administrators usually manage server folder- and file-level security.

User-level security for multiuser Jet front ends requires a shared System.mdw workgroup file that contains user and group names, user passwords, and security identifiers (SIDs). You can use a shared System.mdw file to hold user and group accounts for multiple secured databases.

To review System.mdw basics, see "Jet Workgroup Information Files," p. 153.


Establishing user-level security for Jet front ends involves the following basic steps, which are covered later in this section in more detail:

  1. Add a copy of the current System.mdw file to a network share for which you have Read and Change permissions. You can change the file name, if you want. If you're using linked Jet tables, place the .mdw file in the same folder as the backend .mdb file. If you want the .mdw file to control permissions for multiple front and back ends, create a back-end database folder hierarchy, and store the .mdw file at the top level. All Windows groups or users must have Read permission for the .mdw file.

    Tip

    To find the location of the current System.mdw file, choose Tools, Security, Workgroup Administrator to open the Workgroup Administrator dialog. The Workgroup label contains the well-formed path to the current (default, in this case) workgroup file.

  2. Connect to the shared .mdw file, and activate the logon procedure for Access. This action requires that you add a password for the Admin user. To remain Admin, you need not complete the remaining steps, but your only security is your password.

  3. Create a new account for yourself as a member of the Admins group.

  4. Log on to Access using your new Admins user account.

  5. Remove the default Admin user account from the Admins group. The Admins group should include entries for active database administrators only. You can't remove the Admin user from the Users group.

  6. Run the User-Level Security Wizard to create a secured version of the front-end .mdb. If you're using a Jet back end, you have the option to create a secured copy of the data .mdb file. The Wizard changes the ownership of all Access objects from the Admin user to the Admins account you use when starting Access.

If you forget the username or password you assigned to yourself after deleting the Admin user, you can't log on to Access. So before you begin the following procedure, make a floppy disk backup copy of the System.mdw file in use and any database files that you created or modified while using the default System.mdw. Most back-end .mdbs won't fit on a floppy disk, so use a higher-capacity removable disk drive or burn and test a CD-R or CD-RW disc. In this case, you must restore the original version of the System.mdw file. Then you might not be able to open the database files with which the original version of the System.mdw file is associated unless you restore the backed-up versions. For this reason, it's not a common practice to apply user-level security to back-end .mdb files. You can however, apply user-level security to the links.

Caution

Don't use the Northwind.mdb database in your ...\Office10\Samples folder for the examples that follow. You should preserve Northwind.mdb and the System.mdw file in the original state. Use the NWClient.mdb file created earlier in this chapter with the Database Splitter, or make a copy of Northwind.mdb for a user-level security trial run. If you've password-protected the front-end .mdb file, open it in Exclusive mode and remove the password.


Tip

You don't need to open a database to add or modify user accounts. All user account information is stored in System.mdw, which Access automatically opens when launched. Only members of the Admins group can open System.mdw in Access's database window.


Creating a Shared System.mdw File and Activating Logon for the Admin User

A password for the Admin user is necessary to activate Access's logon procedure. To activate the logon procedure for Access, complete the following steps:

  1. Copy System.mdw to the shared folder on the server, which can be a machine running Windows NT/2000/.NET Server or a peer server running Windows XP or 2000 Professional. For this example, the server share is \\OAKLEAF-MS10\Northwind on a Windows 2000 member server with Read and Modify permissions.

    Tip

    Alternatively, you can create a new workgroup file with the Workgroup Administrator tool.

  2. If you're running Windows NT/2000+/XP, set permissions on the System.mdw file which give users read-only access and database owners (you and other Access developers) at least Read and Write permissions.

  3. Choose Tools, Security, Workgroup Administrator to open the dialog, which displays the path to your current System.mdw file.

  4. Click Join to open the Workgroup Information File dialog, and type the UNC path to and the name of the shared System.mdw file you copied in step 1 (see Figure 19.36). For this example, the shared file is \\OAKLEAF-MS10\Northwind\Northwind.mdw.

    Figure 19.36. Click Join in the Workgroup Administrator, and then type in the Workgroup Information File dialog the full UNC path to the new System.mdw in the shared folder (Nortwind.mdw for this example).

    graphics/19fig36.jpg

  5. Click OK three times to make the change, acknowledge the confirmation message, and close the Workgroup Administrator dialog.

  6. Choose Tools, Security, User and Group Accounts to open the User and Group Accounts properties dialog (see Figure 19.37). You are logged on as Admin, a member of the Admins and Users group, by default.

    Figure 19.37. The User and Group Accounts dialog displays Access's default user (Admin) and groups (Admins and Users).

    graphics/19fig37.gif

  7. Click the Change Logon Password tab, press the Tab key to bypass the Old Password text box (this enters the equivalent of an empty password), and type a difficult-to-guess password, such as Xy8zW3ab, in the New Password text box. Passwords are case sensitive, so Xy8zW3ab is a different password from xy8zw3ab.

  8. Type the password in the Verify text box to test your entry, as shown in Figure 19.38. Access 2003's verification test is case sensitive, unlike some of its predecessors. Click OK to close the dialog and save the new password in the .mdw file.

    Figure 19.38. With the Old Password text box empty, type your new password twice and click OK to assign a password to the Admin account. Workgroup passwords have the same character limitations as database passwords.

    graphics/19fig38.gif

  9. Exit Access and launch it again, and then open the front-end database. The Admin account is password-protected, so the Logon dialog appears.

    Note

    Versions prior to Access 2000 required you to log on to start Access after applying user-level security. After you log onto a database, you can open without a logon step other databases for which your username and password are valid.

  10. The initial default logon ID is your computer's network or Windows logon ID, if you've assigned it. Type Admin in the Logon dialog's Name text box, press Tab, and type the password exactly as you typed it in step 3 (see Figure 19.39). Press Enter or click OK. If you type the password correctly, Access continues the startup procedure.

    Figure 19.39. In Access 2000 and later, the Logon dialog appears when you open a database with user-level security applied.

    graphics/19fig39.jpg

Creating Your New Account in the Admins Group

To add your new user account in the Admins group, do the following:

  1. Choose Tools, Security, User and Group Accounts to display the User and Group Accounts properties dialog.

  2. Click the New button to open the New User/Group dialog.

  3. Type the name you want to use to identify yourself to Access in the Name text box and enter a PID having four or more numbers and letters in the Personal ID text box (see Figure 19.40). Click OK to close the New User/Group dialog and return to the User and Group Accounts page.

    Figure 19.40. A new user account requires a logon Name and Personal ID code.

    graphics/19fig40.gif

    Note

    The PID, with the Name entry, uniquely identifies your account. This precaution is necessary because two people might use the same logon name; the Name and PID values are combined to create a no-duplicates index on the Users table in the shared System.mdw file.

  4. This is a critical step. Select Admins in the Available Groups list and click the Add button to add the Admins group to your new username (see Figure 19.41). If you fail to do this, you can't later remove the Admin account from the Admins group. (Access requires that there be at least one member of the Admins group in each system database file.)

    Figure 19.41. Be sure to add your new administrative user account to the Admins group. Adding a new Admins user also adds the account to the Users group.

    graphics/19fig41.gif

    Note

    When you log on with your new username, you can't see the names of the last databases you opened as Admin when you choose File, Open. Prior database selections are specific to each user.

  5. You don't enter a password for the new user at this time because you still are logged onto Access as Admin. Click OK to close the User and Group Accounts properties sheet, and then exit Access.

  6. Open the front end, type your new username in the Logon dialog, and press Enter or click OK. Don't enter a password because you have an empty password at this point. Usernames aren't case sensitive; Access considers NewAdmin and newadmin to be the same user.

  7. Choose Tools, Security, User and Group Accounts, select your new username from the Name drop-down list, and click the Change Logon Password tab. Press Tab to bypass the Old Password text box and type the password you plan to use until it's time to change your password (to maintain system security). Verify your password, and then press Enter or click OK to close the Password sheet.

  8. Close Access, reopen the front end, and log on with your new username and password. This step verifies that your new Admins username and password are valid.

  9. Choose Tools, Security, User and Group Accounts. Open the Users list of the User and Group Accounts page and select your new username from the list. Verify for the last time that you're a member of the Admins and Users group.

  10. Open the Users list again and select the Admin user. Select Admins in the Member Of list; then click Remove. Admin remains a member of the Users group, as shown in Figure 19.42. Click OK to close the properties dialog.

    Figure 19.42. Delete the default Admin user's membership in the Admins group in preparation to secure a database previously created with this account.

    graphics/19fig42.gif

You use the same procedure to add other users as members of the default Admins or Users group or of new security groups you create. You have not fully secured the open database because the Admin user still has full permissions for the objects in the database as a result of being the database owner when creating the objects. The Admin user also is the owner of sample databases and Data Access Pages (DAP) included with Access.

Tip

Write down and save your PID and the PID of every user you add to the workgroup for future reference. Usernames and PIDs aren't secure elements, so you can safely keep a list without compromising system security. This list, however, should be accessible only to database administrators. You need a user's PID so that the user can be recognized as a member of another workgroup when the need arises.


Taking Advantage of the User-Level Security Wizard

You can change the ownership of and permissions for all the objects in a database for which you have Administer permissions by importing all the database objects into a new database you create with a user ID other than Admin. Access 2.0 first made it easy to import all the database objects from one .mdb file into another .mdb file with its Import Database Add-In. Access 2000 introduced a greatly enhanced Security Wizard, which goes the Import Database Add-In one better by letting you choose the database objects to secure in a long series of steps. One of the primary benefits of the Security Wizard is that you can add groups, users, and assign object permission in a series of linked dialogs.

Don't use the Security Wizard with the Northwind.mdb database in your ...\Office11\Samples directory. Use the NWClient.mdb and NWData.mdb files you created earlier in this chapter with the Database Splitter, and use the System.mdw file and new database account you added in the previous two sections.

To give the Security Wizard a test drive with a front-end .mdb, follow these steps:

  1. Re-launch Access, if necessary, open the database to secure, NWClient.mdb for this example, and log on with your new user ID that includes Admins group membership.

  2. Choose Tools, Security, User-Level Security Wizard to display the Security Wizard's opening dialog. Accept the default Modify My Current Workgroup Information File, unless you have a particular reason for creating a new .mdw file (see Figure 19.43). Click Next to open the second Wizard dialog.

    Figure 19.43. The first User-Level Security Wizard dialog gives you the opportunity to create a new System.mdw file.

    graphics/19fig43.gif

  3. By default, the Wizard secures all objects in the database. Click the appropriate tab that corresponds to the class of database objects that you don't want to make secure, and then clear the check box for the individual object. If you want to secure all database objects, accept the Wizard's default (see Figure 19.44). Click Next to proceed to the third Wizard dialog.

    Figure 19.44. If you don't want to secure all objects in your database, clear the check boxes for those objects to remain unsecured in the second Wizard dialog.

    graphics/19fig44.jpg

    Note

    graphics/2002_icon.gif

    NWClient.mdb contains only links to SQL Server or Jet back-end tables, not the tables themselves. However, permissions you apply to the links to Jet or SQL Server back ends are enforced on the link. For example, if a group has read-only privileges for a link to an SQL Server table, the members of the group don't see the tentative append record in Table or Query Datasheet view, nor can they update data in the table, even if they have sp_datawriter privileges for the database.

    The Access 2002 version of the Wizard lets you password protect the VBA code in your project at this point. To password-protect your code, use the procedure described in the earlier "Password-Protecting VBA Code" section.

  4. The Wizard offers a collection of predefined groups with a specific set of access permissions appropriate to the group. Text within the Group Permissions frame describes the object permissions for each group. The Wizard adds the groups you specify in this dialog to the current .mdw file. Mark the check boxes of the groups to gain permissions for the tables of NWDataSQL or NWData.mdb (see Figure 19.45). Click Next.

    Figure 19.45. Mark the check boxes for the default security groups you want to include in the System.mdw file. The six groups shown here are the most useful.

    graphics/19fig45.jpg

  5. By default, members of the Users group have full permissions for all objects until you secure them. Accept No, the Users Group Should Not Have Any Permissions, unless you have a very specific reason for doing otherwise (see Figure 19.46). Click Next to continue with the Security Wizard's task.

    Figure 19.46. The groups you specified in earlier step 4 eliminate the need for the default Users group, which includes all user accounts you add. To prevent permissions from being inherited from the Users group, remove all the group's permissions.

    graphics/19fig46.jpg

  6. Another feature of the User-Level Security Wizard is the ability to add new users to your .mdw file during the securing process. Type the name of a new user in the User Name text box and provide a password, which appears in the clear in the Password text box (see Figure 19.47). Click the Add This User to the List button. Add a test user account with the name of each group account, such as BackupTest, FullDataTest, and the like, with a common password. (The Password text box in this case doesn't substitute asterisks for password characters.) Add user accounts for users of your production database at this point, such as NWReader1 and NWWriter1. Click Next when you've finished adding users.

    Figure 19.47. Add production database users and a test user account for the groups you selected in step 5. The test user accounts let you verify that the Wizard applies security as advertised in the Group Permissions frame.

    graphics/19fig47.jpg

  7. You can assign or remove (except from Users) group memberships of all users in your workgroup file (see Figure 19.48). Accept the default Select a User and Assign the User to Groups option. Assign each of the test user and the production accounts you added to the appropriate security group you select from the drop-down list. For example, make NWReader1 a member of the Read-Only Users group and NWWriter1 a member of the Full Data Users group. After making group assignments, click Next to open the last Wizard dialog.

    Figure 19.48. Adding test and production users and their passwords in the Wizard dialog is faster than adding them with the User and Group Accounts properties dialog.

    graphics/19fig48.jpg

  8. The Wizard secures the current database, and creates a unsecured backup database copy in the process. The default name of the backup file is DatabaseName.bak (see Figure 19.49).

    Figure 19.49. Accept or change the name of the unsecured backup file the Wizard creates when you click Finish.

    graphics/19fig49.jpg

  9. Click Finish to put the Security Wizard to work. After a few seconds, the "One-step Security Wizard Report" shown in Figure 19.50 opens, indicating successful creation of the new secure database. The report for the NWClient.mdb front end is three pages long. Close the report.

    Figure 19.50. The Wizard's report includes a list of the objects secured and the names, passwords, and group membership of users you added.

    graphics/19fig50.gif

  10. Closing the report opens a message box asking whether you want to save a Report Snapshot (.snp) file that you can view with the Access Snapshot viewer. Take the Wizard's advice to store a copy of the report in a secure place for future reference. (You should also print a copy now.) Click Yes to create and view DatabaseName.snp, which is identical to the report created in the preceding step.

    Tip

    Copy the DatabaseName.snp file to a floppy disk saved in a secure location, and then delete the .snp file from your fixed disk. The printed report and .snp file includes the logon IDs and passwords you assigned to test and production users. Users should change their passwords periodically; unlike Windows NT/2000+, Access doesn't store password expiration intervals.

  11. Close the .snp file window. The Wizard has added new users and assigned object permissions. You can confirm the Wizard's work in the User and Group Accounts and User and Group Permissions dialogs. Object permissions are the subject of the later "Changing Database Group and User Permissions" section.

  12. If you didn't do so in step 3, apply password protection to the VBA code in your front end using the procedure described in the earlier "Password-Protecting VBA Code" section.

  13. Log on with the test user accounts you created in step 6, change the accounts' workgroup file to the shared, secure copy, and verify the privileges of each user. If your version of NWClient.mdb links to the NWDataSQL SQL Server database, notice that the tables no longer have a tentative append record when you log on as the NWReader1 user.

Note

The Access 97 version of the Security Wizard automatically encrypted front-end and back-end .mdb files. It's not a common practice to encrypt front ends, so Access 2003 doesn't encrypt .mdb files that don't contain tables. When you apply user-level security to back-end .mdb databases, encryption is automatic.


The owner of all the objects in the new database is the user ID you entered when you opened the source database. Only members of the Admins, Full Permissions, and Project Designers groups have design access of any kind to the newly secured database. You should provide the VBA code password to other members of the Admins group and to all Project Designers.

Note

This chapter uses the term user ID to identify users of Access. Internally, Access uses a system ID (SID) to identify users. The SID is a value that Access computes from the user ID, password, and PID. The SID is stored in the MSysUsers table of System.mdw as an encrypted binary value in a field of the Binary (varbinary) data type.


Securing Jet Back-End Databases

If you use Jet as the database back end, you might want to secure the back-end tables against exploration by unauthorized users. All users need read/write access to the shared folder, but only groups having the authority to update tables need write permissions for the back-end .mdb. For this example, NWWriter1 has Read and Write access to NWData.mdb and could open it and change its design with Access.

  1. Open the back-end .mdb file with the new account you added with Admins permissions.

  2. Repeat steps 2 12 of the preceding procedure. You must specify the same groups you specified in step 4, but you don't need to add users to the groups in step 7.

  3. Close Access, and reopen the front-end .mdb file with the test user accounts to verify appropriate read-only and read/write access to the encrypted back-end Jet database.

You can run the User-Level Security Wizard multiple times to alter permissions for either front-end or back-end databases.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net