Thursday, 25 October 2012

How to Protect Access Database by User Level Security?


MS Access is one of the most popular DBMS systems in the market. It provides very simple and flexible features to manage and manipulate the data of the database. In parallel to these features, MS Access also provides a very important user-level security feature which is very helpful to manage database security. User-level security controls the access to database depending upon the role of user. For example, it can be used to prevent sales person from accessing the accounting data. 

You can follow the below given steps to Enable User-Level Security:

1. Open MS Access Database you want to secure.

2. Go to Tools menu-> Select Security -> Click User-Level Security

3. You will see "Security Wizard" window having two options: "Create a new workgroup information file" and "Modify a current workgroup information file". Since you are doing it first time so select "Create a new workgroup information file" and click "Next".

4. In next window you need to specify File Name, WID (workgroup identity), your name and company name. Here you can also specify that whether you want security settings to apply on the currently editing database or you want to make it default permission for all database files. Select appropriate option according to your need and click "Next".

5. Next you need to select which table, query, forms, reports and Macros should be covered by user-level security settings. If you want to secure complete database then simply click "Next".

6. Next you need to select the predefined groups which should be enabled in the database. Each group has specific set of permissions granted to them (click the group to see permissions). Depending upon these permissions you can include the groups and click “Next”.

7. In the next screen you need to specify different permissions which can be granted to default user group (in addition with the predefined groups MS Access also creates two other groups User and Admin). There are two options: "Yes, I would like to grant some permission to the user group" and "No, the user group should not have any permission". If you don’t want to allow any permission then select second option and click "Next".

8. Next screen will provide you option to add user to workgroup information and assign each user a unique personal id and password. Click “Next”.

9. In the next window you can assign users to groups in workgroup information file (a single user to more than one group). Click “Next”.

10. In the Next (and last) screen you are provided with the option to make a backup of unencrypted database. This backup database can allow you to recover data if you forget user password. Click "Finish".

Now you have encrypted your database using user-level security.

Here it should be noted that User-Level security can be implemented to MS Access files having old file format (such as .mdb or .ade). It can not be used with files having accdb, accde, accdc and accdr. Data contained in these file formats can be protected by enabling password on mdb file (which is much different approach then user-level security). 

You should also keep in mind that in user-level security if a user forget password then database administrator can grant him permission again but if mdb file password is lost then administrator is also blocked from accessing the database. In such situation you can use Access Password Recovery Software to recover mdb file password and regain access to database.

No comments:

Post a Comment