Setting Up Database Security with Access 97
The most flexible and extensive method of securing a database is called user-level security. This form of security is similar to methods used in most network systems. Users are required to identify themselves and type a password when they startMicrosoft Access. Within the workgroup information file, they are identified as members of a group. Microsoft Access provides two default groups: administrators (named the Admins group) and users (named the Users group), but additional groups can be
defined. Permissions are granted to groups and users to regulate how they are allowed to work with each object in a database.
Creating a Secure Workgroup Information File
When you install Microsoft Access, the Setup program automatically creates a Microsoft Access workgroup information file that is identified by the name and organization
information you specify. With this existing workgroup, anyone can administer any database. To prevent this, create a new workgroup information file and specify a workgroup ID (WID). All information regarding user names, passwords and groups will be stored in this file.
1) Exit Microsoft Access, if necessary.
2) To start the Workgroup Administrator, do one of the following, depending on which operating system you are using:
? If you are using Windows 95, use My Computer or Windows Explorer to open the System subfolder in the Windows folder, and then double-click Wrkgadm.exe. ? If you are using Windows NT Workstation 4.0, use My Computer or Windows
Explorer to open the System32 subfolder in the WinNT folder, and then double-click Wrkgadm.exe.
3) In the Workgroup Administrator dialog box, click Create, and then type your name and organization.
4) In the Workgroup Owner Information dialog box, type any combination of up to 20 numbers and letters, and then click OK.
Caution: Be sure to write down your exact name, organization, and workgroup ID,
including whether letters are uppercase or lowercase (for all three entries), and keep them in a secure place. If you have to re-create the workgroup information file, you must supply exactly the same name, organization, and workgroup ID. If you forget or lose these entries, you can't recover them and might lose access to your databases.
Joining a Microsoft Access workgroup
1) Exit Microsoft Access.2) To start the Workgroup Administrator, do one of the following, depending on which operating system you are using:
? If you are using Windows 95, use My Computer or Windows Explorer to open the System subfolder in the Windows folder, and then double-click Wrkgadm.exe. ? If you are using Windows NT Workstation 4.0, use My Computer or Windows
Explorer to open the System32 subfolder in the WinNT folder, and then double-click Wrkgadm.exe.
3) In the Workgroup Administrator dialog box, click Join.
4) Type the path and name of the workgroup information file that defines the Microsoft Access workgroup you want to join, and then click OK, or click Browse and then use the Select Workgroup Information File dialog box to locate the workgroup
information file.
The next time you start Microsoft Access, it uses the user and group accounts and passwords stored in the workgroup information file for the workgroup you joined.
Activate the Logon dialog box
Until you activate the logon procedure for a workgroup, Microsoft Access automatically logs on all users at startup using the predefined Admin user account. You require users in a workgroup to log on by adding a password to the Admin user account.
1) Join the workgroup whose logon procedure you want to activate. 2) Start Microsoft Access and then open a database.
3) On the Tools menu, point to Security, and then click User And Group Accounts. 4) Click the Users tab, and make sure that the predefined Admin user account is
highlighted in the Name box.
5) Click the Change Logon Password tab, click the New Password box, and type the new password. Don't type anything in the Old Password box.
To maintain the security of your password, Microsoft Access displays asterisks (*) as you type. Passwords can be 1 to 14 characters and can include any characters except ASCII character 0 (null). Passwords are case-sensitive.
6) Verify the password by typing it again in the Verify box, and then click OK.
Create the Administrator's User Account.
To complete this procedure, you must be logged on as a member of the Admins group. 1) Start Microsoft Access using a secure workgroup.
2) Open a database.
3) On the Tools menu, point to Security, and then click User And Group Accounts. 4) On the Users tab, click New.
5) In the New User/Group dialog box, type the name of the administrator account and a personal ID (PID), and then click OK to create the new account.
Note The PID is not a password. Microsoft Access uses the PID and the user
name as seeds for an encryption algorithm to generate a secure identifier for the user account.
User names can be 1 to 20 characters long and can include alphabetic characters, accented characters, numbers, spaces, and symbols, with the following exceptions:
? The characters " / \ [ ] : | < > + = ; , ? * ? Leading spaces
? Control characters (ASCII 00 through ASCII 31)
Caution Be sure to write down the exact account name and PID entries, including
whether letters are uppercase or lowercase, and keep them in a secure place. If you ever have to re-create the account, you must supply the same name and PID entries. If you forget or lose these entries, you can't recover them.
6) In the Available Groups box, click Admins, and then click Add. Microsoft Access adds the new administrator account to the Admins group and displays Admins in the
Member Of box.
7) Click OK to create the new administrator account. 8) Exit Microsoft Access and log on as the administrator.
Remove the Admin user from the Admins group.
To complete this procedure, you must be logged on as a member of the Admins group. 1) Start Microsoft Access and open a database.
2) On the Tools menu, point to Security, and then click User And Group Accounts. 3) On the Users tab, display the Admin user in the Name box.
4) In the Member Of box, click the Admins group you want to remove the user from, and then click Remove.
5) Click OK when you are finished.
Using the User-Level Security Wizard
1) Open the database you want to secure.2) On the Tools menu, point to Security, and then click User-Level Security Wizard. 3) Follow the directions in the wizard dialog boxes.
The User-Level Security Wizard creates a new database, exports copies of all of the objects from the original database, secures the object types selected in the first dialog box of the wizard by revoking all permissions of the Users group for those objects in the new database, and then encrypts the new database. The original database is not
changed in any way. Table relationships and any linked tables are also re-created in the new database.
At this point, only members of the Admins group in the workgroup you joined in step 1 will have access to the secured objects in the new database. The Users group has no permissions for the secured objects. You need to grant permissions to users and/or groups in order to regulate access to the secured objects. For information on granting permissions and creating user and group accounts, click .
How permissions work and who can assign them
There are two types of permissions: explicit and implicit. Explicit permissions are those permissions granted directly to a user account; no other users are affected. Implicit permissions are those permissions granted to a group account. Adding a user to that group grants the group's permissions to that user; removing a user from the group takes away the group's permissions from that user.
Permissions can be changed for a database object by:
? Members of the Admins group of the workgroup information file in use when the database was created.
? The owner of the object.
? Any user who has Administer permission for the object.
Even though users might not currently be able to perform an action, they might be able to grant themselves permissions to perform the action. This is true if a user is a member of the Admins group, or if a user is the owner of an object.
Create user or group accounts and grant permissions
If you only need an administrators group and users group for your security purposes, you don't need to create additional groups; you can use the default Admins and Users groups. In this case, you only need to assign the appropriate permissions to the default Users group and add any additional administrators to the default Admins group. Any new users you add are automatically added to the Users group. Typical permissions for the Users group might include Read Data and Update Data for tables and queries, and Open/Run for forms and reports.
Creating a User Account
To complete this procedure, you must be logged on as a member of the Admins group. 1) Start Microsoft Access using the workgroup in which you want to use the account.
Important The accounts you create for users must be stored in the workgroup
information file that those users will use. If you're using a different workgroup to create the database, change your workgroup before creating the accounts.
2) Open a database.
3) On the Tools menu, point to Security, and then click User And Group Accounts. 4) On the Users tab, click New.
5) In the New User/Group dialog box, type the name of the new account and a personal ID (PID), and then click OK to create the new account. It is automatically added to the Users group
User names can be 1 to 20 characters long and can include alphabetic characters, accented characters, numbers, spaces, and symbols, with the following exceptions: ? The characters " / \ [ ] : | < > + = ; , ? *
? Leading spaces
? Control characters (ASCII 00 through ASCII 31)
Caution Be sure to write down the exact account name and PID, including whether
letters are uppercase or lowercase, and keep them in a secure place. If you ever have to re-create an account that has been deleted or created in a different workgroup, you must supply the same name and PID entries. If you forget or lose these entries, you can't recover them.
Note It is usually easier to manage security if you organize users into groups and then
Assign or remove permissions for a database and existing database objects
1) Open the database that contains the objects you want to secure.
The workgroup information file in use when you log on must contain the user or group accounts you want to assign permissions for at this time; however, you can assign permissions to groups and add users to those groups later.
2) On the Tools menu, point to Security, and then click User And Group
Permissions.
3) On the Permissions tab, click Users or Groups, and then click the user or group whose permissions you want to assign in the User/Group Name box.
4) Click the type of object in the Object Type box, and then click the name of the object to assign permissions for in the Object Name box.
Tip You can select multiple objects in the Object Name box by dragging through the
objects you want to select or by holding down CTRL and clicking the objects you want. 5) Under Permissions, select the permissions you want to assign, or clear the
permissions you want to remove for the group or user, and then click Apply. Repeat steps 4 and 5 to assign or remove permissions for additional objects for the current user or group.
6) Repeat steps 3 through 5 for any additional users or groups, and then click OK when you have finished.
Notes
? Some permissions automatically imply the selection of others. For example, the Modify Data permission for a table automatically implies the Read Data and Read Design permissions because you need these to modify the data in a table. Modify Design and Read Data imply Read Design. For macros, Read Design implies Open/Run.
? When you edit an object and save it, it retains its assigned permissions. However, if an object is saved with a new name using the Save As command on the File menu or by cutting and pasting, importing, or exporting the object, the associated
