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.
How To Change Already Assigned Default Permissions In MS Access?
You have the option to change the default permission, by making update and allowing new users or group to use database of any specific category.
This will allow other users to view the data that has been returned from the action queries to execute queries. Even if, the permission is not allowed to view table or query in subject.
This option is quiet handy for organization having huge data in their PC.
So any Access database user can use this method for managing and altering default permission set for their resources. All this will ultimately help in easy accessing of the information whenever required.
1: From the tools menu, select the Options tab
2: Tap to the Table/Queries Tab.
3: Hit the Run Permissions which user needs to use.
Precautions to take while assigning default permissions:
If a user selects Owner’s:
Entire users have owner’s query permissions to view or execute query.
- Only the query owner is allowed to save changes done in the query.
- Only the query owner can easily change the query’s ownership rights.
If a user selects User’s:
Permission given within the user’s classification has the permission to opt it. Users having the Administer permission are allowed to save and make modifications in the query. Such types of users also have authority to change the ownership.
Go With The Recommended Option:
Unfortunately, due to any Microsoft Access database problem after Window 1o update if your Access database got corrupted. Then for the recovery of lost data from a corrupt Access database, take the help of Access Repair and Recovery solution.
This software very efficiently repairs corrupt .accdb and .mdb files. With the help of this, you can restore all your database objects like modules, tables, macros, deleted records, etc.
In fact, the software deliberately works on fixing several Access database errors.
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.
I hope the above detail on how to manage user permissions in Access Database is helpful to you. So try to implement all these things before assigning your Access database to anyone else.
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 tool like Access database repair and recovery.