Whenever I help a client re-work their application security scheme and other work to improve their systems security, one of the areas of focus is usually queries.  Queries seem to cause a few headaches so I thought it might be helpful to have a discussion on why they are problematic as well as a discussion on securing them.   

A Mixed Bag

Why is securing queries so problematic?  All queries do is read database files, apply the criteria of the query and produce a printed report, right?  What's so difficult about that?   If producing a report was the only thing a query ever did, it wouldn't be that difficult.  But one of the options of a query is to produce a file rather than a printed report.  If the same person always runs the query, things are fine.  But if a different person runs the query on Tuesday and another person on Thursday then that's typically where the problems start.  Why is that?  The query is often written to delete and re-create the file produced by the query.  Ms. Tuesday owns the file after running the query and so if the *PUBLIC authority of the file isn't *ALL, when Mr. Thursday goes to run the query, he doesn't have sufficient authority to delete and re-create the file.   Compounding the problem is that the query file is often being created into the same file as all production data.

Recommendations

I highly recommend moving the query files into a separate library from your production data.  This provides options for securing the files that you don't have if you leave them in the same library.  I also recommend that you don't lump all of the system's query files into the same library.  The reason is because the query file is usually downloaded to a PC or sent to a server.  When downloading to a PC, the same user running a query to gather inventory information is most likely not the same person running a query relating to your organization's financial data.  And for that reason, they shouldn't have the ability to see - or download - the other person's data.  If all query files are in the same library, it becomes much more very difficult to secure files to ensure only the appropriate people see the appropriate data. 

Another reason to separate the queries out of the production data file is because of the authority required to the library.  If the file is being deleted and re-created, the users need *CHANGE authority to the library.  You typically don't want to have your production data library set to *CHANGE.  For query libraries, you can set the *PUBLIC authority to *EXCLUDE (to prevent unauthorized access of the data) and authorize the user or their group with *CHANGE authority. 

Options

Option 1:  Have the query file owned by the application owner, call the query from a program that is adopting the application owner's authority and specify on the query to replace the member rather than replacing the file.  This technique doesn't change the ownership of the file itself and since the query is running with adopted authority of the owner of the file, there will be no problems

Option 2:  If the users running the same query are in the same group profile, consider configuring the users' profile so that any new objects created are owned by the users' group profile.  In other words, set the Owner parameter to *GRPPRF.  Note:  This option will not work if the query is run by users in different group profiles.

Option 3:  Once you've separated the queries into different libraries, you have the option of specifying the Create authority parameter on the library.  In this case, you can specify an authorization list for that parameter so that any new object created into the library is secured with the authorization list.  You typically authorize the users or groups running the query with *ALL authority and set *PUBLIC authority to *EXCLUDE.  This option works well when users in different groups need access to the query files.