In this article, we will discuss how to manage user permissions in Access Database. As this will help you, a lot to specify the level of permission assigned to each individual Access database users.
What Permissions You Can Assign To Any Access Database User?
You have the option to manually change object and database permissions for individual users or any group accounts in the workgroup. It is always recommended to allot permission to groups not to any specific users as each user receives the permission assigned to the workgroup.
You can assign each user with 9 types of permission for handling objects or data in the database. Below given table below contains detailed information on 9 user-permission types, applicable to what kind of objects, and what permission it gives to users.
|Permission||Allows a user to||Objects involved|
|Open/Run||Open a database, form, or report. Run a macro.||Databases, forms, reports, and macros.|
|Open Exclusive||Open a database on a network, while ensuring that others cannot open the database while the first user has it open.||Tables, queries, forms, reports, macros, and modules.|
|Read Design||View the design of objects. No changes to the design are allowed.||Tables, queries, forms, reports, macros, and modules.|
|Modify Design||Change the design of objects and delete objects.||Tables, queries, forms, reports, macros, and modules.|
|Administer||Set database passwords, replicate databases, and change startup properties. Have full access to objects and data, and assign permissions for objects.||Databases, tables, queries, forms, reports, macros, and modules.|
|Read Data||View data, but not table designs or query designs.||Tables and queries.|
|Update Data||View and edit data, but not insert or delete data.||Tables and queries.|
|Insert Data||View and insert data, but not change or delete data.||Tables and queries.|
|Delete Data||View and delete data, but not change or insert data.||Tables and queries.|
Now you must have got enough idea on what type of user permission you should assign for any specific task.
How To Manage User Permission In Access Database?
Follow the below steps to grant or manage user permission in Access database and to its objects.
- Choose Tools>Security> User, And Group Permissions option, This will open the dialog box of User And Group Permissions.
- Switch to the Change Owner tab. Here you can see who is the current owner of the database and all its objects. Go to the Permissions tab and take a look over the name of the database workgroup or users to which permissions are assigned. From the appearing list of user/group names, you have to select the Admin group.
- It’s time to Explore detail about the permissions assigned to various group names and object types.
- Choose order entry from the list of User/Group Name. Suppose you want to assign permission to this group then follow the following steps.
- Now from the appearing drop-down list of object names you have to choose the database. From the Permissions section choose the Open/Run.
- Tap to Apply option. After this, all the users who are in the order entry group will get permission to open and run their current database.
- It’s time to set permissions for the table objects of the order entry group. Similarly from the drop-down list of an object type, you have to choose table. After that from the appearing list of the object name, select entire table names.
- From the Permissions section, select the insert and Update Data option. By default, the option Read Design and Read Data will appear checked to you. You have to uncheck Administer, Modify Design and Delete Data.
- Tap the Apply button.
- To close the opened dialog box click the ok button.
How To Specify Level Of Access To Each Access Database Users?
Using the user-level security wizard you can specify the level of access to each database user.
- To start the wizard, go to the Tools menu, choose the Security option, and then hit the User-Level Security Wizard.
- In the opened wizard screen, you are asked whether you want to edit the existing file or want to start with a new security file.
- Suppose you want to start with a new one then choose the “Create new workgroup information file” after then hit the Next.
- In the next opened screen you have to enter the name and company detail. Well, this step is an optional one. Here you will also see a strange string known as WID. It is a unique identifier that is assigned randomly and it must not be modified.
- On the same screen, you are asked whether you need to apply the security setting only the currently editing database wants to apply the default permission to entire databases. After making your choice hit the next.
- The next screen defines the scope of your security settings. If you wish, you may exclude particular tables, queries, forms, reports, or macros from the security scheme. We’ll assume you want to secure the entire database, so press the Next button to continue.
- In the next opened wizard screen you have to specify the groups for enabling the database.
- By selecting each group you can check out what permission assigned to it.
- Now you need to assign permission also to the default users group. In this default group, you will get all users of the computer listed here.
- In case you are enabling user-level security then most probably you don’t want to allow any rights to the user. So for this, select the “No, the Users group should not have any permissions” After that press the Next button.
- In the next step, you have to create a new database user, well you can create as much as a user you want. Just by clicking the “Add new user” option. you need to set some strong and unique passwords for each of your database users.
Don’t create shared accounts. Allowing each database user with an individual account name will increase security and accountability.
- After then from the drop-down box, you have to select each user and then assign them with one or more groups. This step will offer each user security permissions which are indirectly inherited from group membership.
- At last, you have the option to make a backup of the unencrypted database. This backup helps in easy recovery of your data if you mistakenly forget the password.
- It’s a good habit to make a backup, so save it on some removable storage device like a DVD or flash drive. Store this device in some safe location.
- After the creation of the backup, delete the unencrypted file from the hard disk for protecting it from prying eyes.
In MS Access some permission by default identifies the selection of other objects. Suppose you allow Modify Data Permission in the table, other permissions like read design and read Data will be assigned automatically.
While working with the Access databases you always need to be prepared for incidents like corruption and crashes.
One of the best ways to resolve Access database corruption is to use a powerful recovery like Access database repair.