Using the Microsoft Access RunPermissions Property with User-Level Security

For Microsoft Access to display a table or query, it needs to read the design of that table or query. For example, it needs to read field names and other field properties, such as the Format and InputMask properties. As a result, for a user to read and display the data in a table or query, that user must also have permissions to read the design of the table or query. (This is why selecting the Read Data permission check box in the User And Group Permissions dialog box automatically selects the Read Design check box as well.) 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 Microsoft 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, then 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 will have the same level of permissions to view data in the underlying tables as the query’s owner.

Using the RunPermissions property, you can create queries to display data to users who don’t have permission to access the underlying tables. You can build different views of your data, which will provide record-level and field-level security for a table. For example, suppose you have a secure database with an Employees table and a Salary table. Using the RunPermissions property, you can build several views of the two tables: one that allows a user or group to view but not update the Salary field; a second that allows a different user or group to view and update the Salary field; and a third that allows another user or group to view the Salary field only for 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. In the query’s property sheet, set the RunPermissions property of the new query to Owner’s.

   Note You can also set the RunPermissions property for a query in SQL view by using the WITH OWNERACCESS OPTION declaration in the SQL statement.

4. Grant appropriate data permissions for the new query to the users and groups that you want to be able to update data, but not view the design of the underlying table or query. This would typically be Read Design, Read Data, Update Data, Delete Data, and Insert Data permissions.

Tip You can base forms and reports on the new query.

Users can update data in the underlying tables or queries by using the new query or forms based on it. However, they won’t be able to view the design of the underlying tables or queries. If they try to view the design of the new query, they receive a message that they don’t have permissions to view the source tables or queries.

Important 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.

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, the 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 of 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 will be able to edit the query and save changes.

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

See Also For information about using the WITH OWNERACCESS OPTION declaration in an SQL statement, see “Owner Access Queries” later in this chapter.