Microsoft Office 2000/Visual Basic Programmer's Guide   

Using the Access RunPermissions Property with User-Level Security

In order for 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, 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. (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 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 will have the same level of permissions to view data in the underlying tables as the query's owner.

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

  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 to view the design of the underlying table or query. Such permissions typically include Read Design, Read Data, Update Data, Delete Data, and Insert Data.

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

You can also set the RunPermissions property for a query in SQL view or in code by using the WITH OWNERACCESS OPTION declaration in an SQL statement. For example, suppose that a user doesn't have permission to view the payroll information in your application. You can create a query like the following example that uses WITH OWNERACCESS OPTION to allow the user to view this information:

SELECT LastName, FirstName, Salary
FROM Employees
ORDER BY LastName
WITH OWNERACCESS OPTION;

Just as when you use the Access RunPermissions property, you can't change the owner of a query created by using the WITH OWNERACCESS OPTION declaration. If you need to do this, you must remove the WITH OWNERACCESS OPTION declaration, change the owner, and then change it back to a query created by using the WITH OWNERACCESS OPTION declaration. Note that only the owner of such a query can save design changes to that query. If you have several developers who need to modify this type of query, assign those developers to a group, and then make the group the owner of the query, by using the steps previously described.