STRSQL is not the only way to use SQL!

SQL is a story in two parts — (1) IBM SQL is an interface to the data and (2) runtime SQL is part of the base operating system.

IBM I Administrators often want to limit what SQL statements a user is allowed to run because how powerful SQL can be. SQL uses the standard IBM i object authority when determining whether a user is authorized to execute the SQL statement, and if the user has the correct authority to the file and data, they can proceed with the request by using SQL, RPG, or any other function.

You cannot limit the type of SQL that a user is authorized to run, so it is essential to ensure that file authorities are correct.

As well as ensuring your file and library (think table and schema) authorities are correct then you still need to lock down access from the command line.

I always recommend locking down the interactive commands to cover the front end (for most basic users) but we also need to lock down SQL access — this is the critical exposure for anyone with malicious intents and basic IBM i knowledge.

The best technique to lock SQL command access down and to also use SQL exit programs.

Restrict Command Line Access to STRSQL, RUNSQL, etc

If users have access to the IBM i command line, you must remove authority to the interactive commands that provide SQL interface:

• STRSQL
• RUNSQL
• RUNSQLSTM
• STRQM

NOTE: Don’t forget to restrict access to every version of these commands in all libraries QSYS, QSQL and so.

If executing 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 permit the request to be processed or reject the request.

The exit point program could also provide information on what SQL is being executed.

Restrict with Exit programs (preferred)

Use the Work with Registration Information (WRKREGINF) command to add your exit programs to the database exit points. The database server has five different exit points defined:

• QIBM_QZDA_INIT called at server initiation
• QIBM_QZDA_NDB1 called for native database requests
• QIBM_QZDA_SQL1 called for SQL requests
• QIBM_QZDA_SQL2 called for SQL requests
• QIBM_QZDA_ROI1 called for retrieving object information requests and SQL catalog functions

Learn more about exists here

Limit access from tools like IBM i ACS

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.

Finally, you could limit Run SQL Script access by removing access to all 4 of the following functional areas by using CL command WRKFCNUSG:

•  QIBM_XD1_OPNAV_DBLIBS                  
•  QIBM_XE1_OPNAV_DBSQLPCS    
•  QIBM_XE1_OPNAV_DBSQLPM   
•  QIBM_XE1_OPNAV_DBXACT 

Any other neat techniques out there?