Using Security Features in Access

Using the RunPermissions Property with User-Level Security

In order for Microsoft Access to display a table or query, it must read the design of that table or query. As a result, in order for a user to read and display the data in a table or query, that user must also have permission to read the design of the table or query.

If you don’t want your users to see the design of your table or query, you can create a query and set its RunPermissions property to restrict their access to this information. The RunPermissions property determines whether Access uses the query user’s permissions or the query owner’s permissions when checking the user-level security permissions for each of the underlying tables in a query.

If the RunPermissions property is set to User’s, the users of a query have only their own permissions to view data in underlying tables. However, if the owner of a query sets the RunPermissions property to Owner’s, anyone who uses that query has the same level of permissions to view data in the underlying tables as the query’s owner.

Top

Displaying data from secured tables and queries

By using the RunPermissions property, you can create queries to display data to users who don’t have access to the underlying tables. You can build different views of your data, which provides record-level and field-level security for a table.

For example, suppose that you have a secure database with an Employees table and a Salary table. By using the RunPermissions property, you can build several views of the two tables:

• One view that allows a user or group to view but not update the Salary field.
• A second view that allows a different user or group to view and update the Salary field.
• A third view that allows another user or group to view the Salary field for only a certain category of employees.

To prevent users from viewing the design of underlying tables or queries

1. For the users or groups whose access you want to restrict, remove all permissions for the tables or queries whose design you want to secure.
2. Build a new query that includes all the fields you want to include from those tables or queries.

   You can exclude access to a field by omitting that field. You can also limit access to a certain range of values by defining criteria for your query.

3. On the query’s property sheet, set the RunPermissions property of the new query to Owner’s.
4. Grant appropriate data permissions for the new query to the users and groups that you want to be able to update data, but do not want viewing the design of the underlying table or query.

   Such permissions typically include Read Design, Read Data, Update Data, Delete Data, and Insert Data.

Top

Modifying queries

By default, the user who creates a query is its owner, and only the owner of a query can save changes to it if the RunPermissions property is set to Owner’s. Even members of the Admins group or users with Administer permission are prevented from saving changes to a query created by another user if the RunPermissions property is set to Owner’s. However, anyone with Modify Design permission for the query can set the RunPermissions property to User’s and then successfully save changes to the query.

Similarly, if a user is otherwise prevented from creating or adding to a table, you can create either a make-table or append query and set its RunPermissions property to Owner’s.

Because the creator of a query owns it by default, having the RunPermissions property set to Owner’s can create problems if you need to allow more than one user to work with the design of a query. To correct this, ownership of the query can be transferred to a group. To do this, create a group, change the owner of the query to this group on the Change Owner tab in the User And Group Permissions dialog box, and then add the users who need to modify the query to the new group. Any member of the new group can edit the query and save changes.