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.

How to Manage the Security of MS Access Database?

MS Access Database is one of the most popular Database Management System used to manage and maintain large quantity of data. It is considered as most flexible and the simplest database management system as compared with other Database management system. It is preferred by most of the Microsoft User because of its 'Windows look and feel' and also for its compatibility with other Microsoft products.

Every database stores all type of data including sensitive and confidential data. So it is necessary to manage the proper security of your database to prevent from unauthorized access. MS Access also provides some security features so that data can be accessed by authorized user only. One of such security feature is setting user level permission. User permissions allow database administrator to assign different role to different user according to their requirement. In MS access there are nine types of permission which can be assigned to a user (or a group of user). These nine types of permission and associated roles are listed below.

1. Open - Allows user to open a database, report or form.

2. Open Exclusive - It allows a user to open a database exclusively (other user can not open the database simultaneously).

3. Read Design - It allows user to read/view object design (user can not change the design).

4. Modify Design - It allows user to view, change (or delete) objects.

5. Administer - This type of access have full access to the database and objects. With this permission user can set password to database and assign permission to other user.

6. Data Read - User can view data but cannot edit it (user cannot view design of tables and queries).

7. Update Data - User is allowed to view and edit existing data but cannot insert or delete data.

8. Insert Data - User can view and insert data but not allowed to delete or change data.

9. Delete Data - User can view or delete data but cannot change or insert data.

It should be noted that before using above mentioned user permissions you should have a clear view about the task of every employee who interacts with the database. For example ‘Data Read’ permission can be allowed to ‘Data Analyst’ user (because he needs to view data only), ‘Insert Data’ can be assigned to user who is responsible for adding new data to the database.

If you are the only user of database then it is not a good idea to use any of the user permission to protect database. In such case you can set password to the main database file (MDB or ACCDB file) so that other user can not access database without your permission. Setting password to the MDB or ACCDB file is considered as the easiest method but you can fall in problem if you forget (which is common human nature) the password of your access database. In case you forget your MDB file password then you can use Access Password Recovery Software to recover password of access database. Such software provides easiest way to recover password of MS Access database file.