News
Abstract
This document discusses options on limiting SQL function on IBM i.
Content
Admins often want to limit what SQL statements a user is allowed to run because how powerful SQL can be. SQL is just another interface to the data and runtime SQL is part of the base operating system. SQL uses the standard IBM i object authority when checking whether a user is authorized to run the SQL statement. If the user has the correct authority to the file and data, they can process the request by using SQL, RPG, or any other function. You cannot limit what type of SQL a user is authorized to run. It is important authority to files are correct. For more information on setting authority to your database file, refer to the following links.
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/dm/rbal3scurty.htm
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzahf/rzahfrcactitle.htm
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzahf/rzahfrcactitle.htm
However, you do have the following options to limit the SQL usage on the system:
1. If users have access to a command line you can remove authority to commands that provide SQL interface (STRSQL / RUNSQL / RUNSQLSTM / STRQM, ect.).
NOTE: DB2 for IBM i Query Manager is the ONLY product that allows you to set what type of SQL statements are allowed per user. Use option 10 from STRQM menu to edit the profiles and Select allowed SQL statements to limit the type of SQL statement each user is allowed to run.
2. If running SQL from an application using IBM ODBC/JDBC driver to connect, an exit points are available. The exit point program can capture the SQL request and either allow the request to be processed or reject the request. The exit point program could also log what SQL is being executed. Refer to the following link for more information:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzaik/rzaikodbcexitprog.htm
3. If the user is using ACS and Run SQL Scripts, you can edit the installation package of ACS to prevent the installation of Run SQL Scripts. You need to exclude comps: rss. You can do that by adding the rss to the acsconfig.properties exclude comps section. This function is documented in the getting started folder as part of the product bundle. Refer to section - 9.1.26 RESTRICT.
Or you could limit Run SQL Script access by removing access to all 4 of the following functional areas b using CL command WRKFCNUSG:
QIBM_XD1_OPNAV_DBLIBS
QIBM_XE1_OPNAV_DBSQLPCS
QIBM_XE1_OPNAV_DBSQLPM
QIBM_XE1_OPNAV_DBXACT
QIBM_XE1_OPNAV_DBSQLPCS
QIBM_XE1_OPNAV_DBSQLPM
QIBM_XE1_OPNAV_DBXACT
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]
Was this topic helpful?
Document Information
Modified date:
11 December 2023
UID
ibm10883108