User level security is used to be set in case one wants to set security permissions for the users of his database. To establish user level security in MS Access follow these steps:-
- Open MS Access
- Open your database
- Point on Tools menu, point on Security sub-menu and click on the User Level Security Wizard. This will open the wizard.
- The wizard will now ask you to create workgroup information file. The workgroup information file is the location where the user right / permissions are saved for a particular database. Click the option and proceed by clicking the next button. The workgroup information file contains the names of users and groups that will develop or use your application.
- Now give a filename to the workgroup information file. Provide a WID (unique ID to workgroup information file). Provide your name and your company’s name.
- Click on the option saying “I want to create a shortcut to open my security-enhanced database” and click on next button.
- In the next screen the wizard shows the list of all the objects of the database and by default the wizard selects all the present objects to secure. You can alter the security settings for objects which you do not want to secure by deselecting the objects in the window. Click on the next button to continue.
- Now the wizard asks to include pre-defined groups within your workgroup. You can click on the workgroups for detailed information about them. Select the groups that you want. You can also change the Group Id to an easier one for convenience next time. Click on the next button to continue.
- By default, all database users are added into the Users group so select the option saying “No, the users groups should not have any permissions”
- In the next window you can add users to the workgroup information file and assign each user a password and a unique PID (Personal ID). The PID is a unique 4-20 character long alphanumeric string. After you have added all the users and assigned them a password and a unique PID. Click on the next button to continue.
- Now you can assign your users the groups which you want. This can be done in two ways i.e. “select a user and assign the user to groups” and “select a group and assign users to the group”. Once you have assigned all the users to the groups you want to click the next button to continue to the next step.
- Specify the location where you want the unsecured backup to be stored. One needs to change the extension of the workgroup information file from .bak to .mdb and click on the finish button.
MS Access will then create the workgroup information file, a secured version of your database, an unsecured version to the location you specified, and a One-Step Security wizard report. You can take a hard copy of the report but do not save for security reasons.
Exit the database and MS Access and reopen it to confirm the changes have been made.