Limiting what SQL statements a user is allowed to run

Content

SQL is just another interface that can be used to access database files. SQL uses the standard IBM i authority the user has to the file. For example, if they have delete data authority to the file, they can delete rows from any interface (RPG / SQL). Because SQL is powerful, many administrators would like to limit the SQL statements a user is allowed to run (only select statement). There is no OS function that would achieve this.  You have the following options to limit certain SQL activity.

1. Limit SQL activity to the Query Manager product (STRQM). Each profile has a Query Manager profile and you can limit what SQL statements a user is allowed to use in the Query Manager product. You can do this by doing the following:

STRQM and take option 10

Put a 2 next to the profile you want to limit.

Page down and change Select allowed SQL statements to a Y.

Now you have the ability to only select the SQL statements you want this user to use.

NOTE: This does not set this for all SQL interfaces for this user - only when the user is using STRQM.

2. Use exit programs or create your own SQL program to capture the SQL statement the user is wanting to run and determine if the SQL should be allowed to run or not.  You would need to look at each application o connection to determine what exit programs are available.

3. Limit the authority to to the database file to read only. Any application that the user needs to edit the data would need to use adopt authority to allow the necessary authority.