Date Last Reviewed:
03/29/2007
Occasionally a database needs to be secured. What this means is assigning some privileges for one or more users to modify the data and the structure of objects within a database. For example, some users may only view data in tables and queries and view and print reports while other users may view and modify data in some tables. Sometimes one or more database administrators can be identified who would have the privilege of modifying the structure of objects. For example, if a new field needs to be added to a table only certain users should have the privilege of modifying the table’s structure since this mandates opening a database exclusively. Opening a database exclusively prevents other users from opening the database until the database is closed.
Fortunately Access provides a wizard to assist in creating the file that contains all the defined users, groups, and associated privileges. This file is called the workgroup information file and contains a .MDW file extension. To start this wizard you must have the database you wish to secure already opened.
Figure 1 shows the first screen of this wizard. As explained in this screen a new workgroup information file will be created and an unsecured copy of the database will created before securing the currently opened database.
Figure 1
The next screen asks for the name and location for the workgroup information file. There is also an option to make this workgroup information file the default for new databases created or to create a shortcut on your desktop that opens the secured database using the workgroup information file.
The default location of the workgroup information file is the My Documents folder and the default name is “Secured.mdw”. It is considered good practice to set the location to the same folder where the secured database is stored. You can also rename the workgroup information file to the same name as the database file since it will use a different file extension.
The box labeled WID is the workgroup ID and is used to uniquely identify the workgroup information file. You can enter a new ID or accept the default. Most of the time the default is selected as this ID is never referenced within the secured database.
The boxes labeled Your name and Company are optional and thus can be left blank.
We want to use this workgroup information file we are creating only when we open the secured database. If we select the other option to make this workgroup information file the default then every new and existing database will inherit the privileges of this file. Different databases will have different privileges so we always want to use a workgroup information file for opening only one database.
Figure 2 shows the wizard screen with some sample input.
Figure 2
The next screen of the wizard allows us to choose the objects within the database we wish to secure. By default every object is already selected and usually we would leave every object selected.
The next screen displays the sample security groups that can be selected for the database. Generally the groups are identified and the users are assigned to one of the selected groups. The user then inherits the privileges assigned to the group.
Each group has a Group ID that contains a default value and is displayed in the box labeled Group ID. Generally the default is accepted for the group because this ID is never referenced by users in the database.
Figure 3 shows some selected groups.
Figure 3
The next screen asks if the Users group should be assigned any privileges. The Users group is a default group created by Access and cannot be removed. Since every user is assigned to this group in addition to other groups it is wise to leave this group without any privileges. When a user is assigned to more than one group it inherits the combination of privileges from all groups. If a user has the privilege to delete records in a table in one group but doesn’t have the same privilege in another group, the user will have the privilege to delete records in that table.
The next screen is where we identify the users. The topmost entry in the list box labeled <Add New User> starts the process of identifying the users. The boxes labeled User name, Password, and PID are filled in. Generally the AccountID of the user is used as the name if the database resides on a shared network drive but you can use any spelling. Just remember each user name must be unique. For the password you can set up a default password that is the same for every user and then let each user create their own password after logging into the database initially. This will be explained in another article. Just like the Group ID just leave the default PID for each user created since this will not be referenced by the users.
The next screen allows us to assign each created user to the selected groups to be used in the database. There is an option to assign users to groups or assign groups to users. This is a matter of preference but generally users are assigned to groups since the group privileges are inherited by users as they are assigned.
The Admins group is a default workgroup created by Access that identifies the administrators of the database. By default the creator of a database is the administrator. Administrators have every privilege for the database so you only want to assign designated users to the Admins group.
Although you can assign users to more than one group other than the Admins group this is not a desired practice because the user will inherit every privilege combination from all the assigned groups. Figure 4 shows a sample user assigned to a group.
Figure 4
The last screen of the wizard asks us for the name and location of the backup copy of the database. This backup copy is the unsecured version of the database. That is, this is the original database before we applied any security to it. Generally the unsecured database is saved in the same folder where the secured version is saved.
A report will be displayed showing all the users and groups created along with the group membership. You can print this report for future reference.
A shortcut will be created on your desktop that will enable you to open the secured database. You will be prompted for your user name and password. After supplying this the database will open.
In the subsequent articles I will explain how to modify the security of a database. This includes creating new users, removing users, modifying the privileges of users, and assigning passwords to users. This is all done from the pull-down menu.