Using Security Features in Access

Establishing Security with the User-Level Security Wizard

After you understand how the Microsoft Access user-level security model works, you can proceed with the steps to secure a database. Although you can perform individual steps yourself, the User-Level Security Wizard is the easiest and most reliable way to secure a database.

In Access 2000, the User-Level Security Wizard performs all the necessary steps for you and can even be run again on a secured database to modify previous settings. The User-Level Security Wizard performs the following actions to secure your Access database:

• Creates a new secure workgroup information file by using a secure workgroup ID (WID).

  You can also modify a workgroup information file previously created by running the User-Level Security Wizard or the Workgroup Administrator program. You can make the workgroup information file the default for the current installation of Access, or the wizard can create a Windows shortcut that uses the /wrkgrp command-line option to open your secured database by using the workgroup information file.

• Secures all selected database objects, and sets the permissions that are assigned to any new objects that users create after running the wizard.
• Secures the Visual Basic for Applications (VBA) project to protect access to all code modules (stand-alone modules, the modules behind forms and reports, and class modules) by setting a password.

  If the VBA project has been password-protected, you must unlock the project before running the wizard.

• Creates as many as seven predefined group accounts for the typical user roles.

  These groups include Backup Operators, Full Data Users, Full Permissions, New Data Users, Project Designers, Read-Only Users, and Update Data Users.

• Removes permissions on all objects for the default Users group.

  You can also grant permissions to the Users group for some objects. This option is useful for developers who don’t want to require users to log on to the database and only want to secure certain objects.

• Creates and adds new user accounts to the workgroup information file, and allows you to assign passwords for each new user.

  If you choose to create a new workgroup information file, a new user account is created for you.

• Assigns users to the default Admins group account or to the selected predefined group accounts.

  At least one new user account must be assigned to the Admins group account to serve as the database administrator account. By default, the new user account created in the wizard is assigned to the default Admins group as the new database administrator account.

• Creates a backup copy of the current database, and secures the current database in place.

  The objects that you selected are secured by revoking all permissions on those objects for the default Users group. Ownership of the database and its objects is transferred to the new database administrator account. Finally, the secured database is encrypted.

• Formats a report that documents the values used to create the new workgroup information file and user accounts.

  Keep this report in a secure location in the event that you need to use these values to re-create the workgroup information file. This report also documents which objects have been secured.

To secure a database with the Access User-Level Security Wizard

1. Open the database that you want to secure.
2. On the Tools menu, point to Security, and then click User-Level Security Wizard.
3. Follow the instructions in the wizard.

If you log on as a member of the Admins group, you can run the User-Level Security Wizard again on the new, secured database to create new users, modify permissions, and to assign users to groups.

Top

Securing a front-end/back-end application

You can establish user-level security for a database that has been split into a back-end database that contains only tables and for a front-end database that contains the remaining objects as well as links to the tables in the back-end database.

To establish user-level security for a front-end/back-end database

1. Assign users to the appropriate groups so that they have permissions to read, update, insert, or delete data in tables in the back-end database.

   – or –

   Remove all permissions for the tables and create queries in the front-end database that have the RunPermissions property set to Owner’s and that use the tables in the back-end database. All users must be assigned to groups that have Open/Run permission for the Database object in the back-end database.

2. In the front-end database, grant users Modify Design permission for the table links.
3. When the users first install your database, have them run the Linked Table Manager (Tools menu, Add-ins submenu) from the front-end database to refresh the links to the tables in the back-end database in its new location.

Because users have Modify Design permission for the linked tables in the front-end database, they can reset the links to the back-end tables if the location of the back-end database changes. However, they can’t make any modifications to the design of the tables in the back-end database.

Top

Securing a database without requiring users to log on

If you want to secure some objects in a database, such as the code modules and the design of objects, but you do not care about establishing different levels of access for different groups of users, you might want to consider securing an application without requiring users to log on. You can do this by running the User-Level Security Wizard and granting the permissions you want available for all users to the Users group.

To secure your database without requiring users to log on

1. Open the database that you want to secure.
2. On the Tools menu, point to Security, and then click User-Level Security Wizard.
3. When prompted by the wizard, click Yes, I would like to grant some permissions to the Users group.
4. Assign permissions to the Users group for objects that you want to make available to all users.

   Typical permissions include Read Data and Update Data permissions for tables and queries, and Open/Run permission for forms and reports. Don’t give the Users group the permission to modify the design of tables and queries, and don’t give users Administer permission for the database.

5. Distribute the database to users who are using the default workgroup information file that is created when Access is installed or to users who are using another file that allows users to start Access without logging on.

Important   Do not distribute copies of the workgroup information file that was in use when you secured the database in this procedure. If you need to allow a user to administer your database, give a copy of the workgroup information file to that user only.

To perform administrative functions, you must use the workgroup information file that was in use when you secured the database, and you must log on as one of the members of that workgroup information file’s Admins group in one of two ways:

• Temporarily define a password for the Admin user to reactivate the logon procedure, and then log on as a member of the Admins group.

  – or –

• Use the /pwd and /user command-line options to specify your password and user name when starting Access.