Protecting Your Query Design

The more common ways to protect and secure your query design (from being changed) are done through the Hidden property (see Chapter 3) and by using workgroup security (see Chapter 8 and this chapter). If you want to hide the design of the queries, consider trying the different techniques outlined in the following sections.

Protect Your Query by using RWOP Queries

In Chapter 10, I described how a certain type of query, called a Read with Owner Permission (RWOP), would allow a workgroup User account that had no permissions on a table or query access to any fields included in the RWOP query. What I didn't explain, though, was that RWOP queries provide very good protection for the design of a query itself. To demonstrate this protection, we need to remove permissions granted to the Full Data Users group from a query in the Northwind database that we secured earlier in the chapter. Then we will set up a RWOP query that actually returns all the information that the Full Data Users group can no longer view directly, which means that the Editor user, as a member of the Full Data Users group , will never find out the design of this query. Here are the detailed steps:

1. Use the Developer account to log on to the secured Northwind database.

2. Remove all permissions for the ubiquitous Users group for the Invoices query (as shown in Figure 11-6). If any permissions exist for the query, then you need to review permissions for all objects, as something may have gone astray with the User-Level Security wizard.

   
   Figure 11-6: Removing all permissions for the Invoices query for the Users group.

3. Select the Full Data Users group for the Invoices query and clear all permissions.

4. Select the anonymous Admin user for the Invoices query and clear all permissions.

5. Create a new query that returns all information from the Invoices query by using the following SQL code:

    select * from invoices 

6. While you have the query in design mode, choose View ˜ Properties.

7. Select the Run Permissions property and choose Owners, as shown in Figure 11-7.

   
   Figure 11-7: Choosing Owners as the query permission property.

8. Save the query with a name of Invoices RWOP .

Now close Access, log on as the Editor account, and try to open the Invoices query. You should get a No Read Definitions error, whether you want to open the query or view it in design mode. When you open the Invoices RWOP query, however, the Editor account will be able to see all the data. Voila ”you have protected the design of your query from your users.