Problem solve Get help with specific problems with your technologies, process and projects.

Securing query definitions

A better way of granting users access to query definitions.

A surprising byproduct of writing security tips for is people actually read them. I know that because...

I get what I call "fan mail" from readers on a fairly regular basis, and it still catches me off guard. This week, I heard from a reader who asked the following question:

"I have a Query/400 user who is not allowed to replace a query. I checked their user profile and I have granted them *ALLOBJ special authority. I know that is not the right way to go. With *ALLOBJ, they can run the query and do their work, but I want to eliminate the use of special authority *ALLOBJ. How do I handle such a situation so I don't have to use the special authority?"
More Information

I took some time and played around with *ALLOBJ special authority to come up with a good solution. I had to assume the reader was running security level 30 or higher. If not, SHAME ON HIM!

The solution I came up with relates to the fact that the public access setting for this query object is probably set to *USE. That means anyone not specifically authorized to alter the query definition object can run only the query and cannot make changes to it.

You can view the security setup for the query definition object using the following command:


How you correct the situation depends on how you want other users to access the query. If you change the public setting to *ALL, then anyone can update the query and run it. If you want everyone to have the ability to change the query definition, then this is all you need to do.

If you want just one additional user to have this ability, but leave the general public out, you will have to edit the authority for the query definition and add that new user to have *ALL authority. You can edit the authority using the following command:


To add a new specific user, use the F6 option and add the user in the list that opens up with *ALL in the Object Authority column.

Whenever any of the authorized users updates the Query Definition, the object ownership and authorization settings will be kept. If you have a lot of Query Definitions where you want to establish this sort of control, consider using an authorization list so you have to maintain only one set of rules.

When you create or update a Query Definition, Query/400 lets you specify an authorization list when you are finished working with the definition. Remember that the default authorization for a Query Definition -- when it is created -- is taken from the authorization setup for the library in which the definition is stored.

If you have any questions about this topic, you can reach me at, I'll give it my best shot. All e-mail messages will be answered.

About the author: Rich Loeber is president of Kisco Information Systems Inc.s in Saranac Lake, N.Y. The company is a provider of various security products for the iSeries market.

Dig Deeper on Oracle on iSeries

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.