Hello, I have an IBM i 7.4 partition, and I want to limit SQL connection activities to read-only. Could you help me? Thanks.

------------------------------
Miguel Peralta
------------------------------

Let me see if I have this right.  If your software vendor sends you a program and instead of doing an RPG read they do an SQL UPDATE, you want to put a stop to this?

Or more interactive stuff like, if a software developer starts up STRSQL and does an UPDATE you want to put a stop to this?

Or are you solely concerned about client/server or web based SQL access?

"Best practices" is that you limit users access to your data directly.  You use "application only" access as outlined at https://www.linkedin.com/pulse/piecemeal-security-rob-berendt/?trackingId=mnD3iawUTkm%2FYvsj1W5dww%3D%3D

This basically says the authorization list assigned to your data has *ALL for the adopted authority set to the programs either from your vendor or your custom applications.  Then *USE for those users who are your Query mongers or data scientists.  All others are *EXCLUDE.

Now, if you love to play whack-a-mole you can look at exit point software and shovel out some serious cash and tie up time setting that up.

------------------------------
Robert Berendt IBMChampion
Business Systems Analyst, Lead
Dekko
Fort Wayne
------------------------------

Yes, that's correct. If the data is accessed via SQL connections, they can only read it; if it's via RPG, they can execute everything.
Thanks for the information.

------------------------------
Miguel Peralta
------------------------------

Original Message:
Sent: Wed June 17, 2026 03:51 PM
From: Robert Berendt
Subject: SQL limits in 7.4

Let me see if I have this right.  If your software vendor sends you a program and instead of doing an RPG read they do an SQL UPDATE, you want to put a stop to this?

Or more interactive stuff like, if a software developer starts up STRSQL and does an UPDATE you want to put a stop to this?

Or are you solely concerned about client/server or web based SQL access?

"Best practices" is that you limit users access to your data directly.  You use "application only" access as outlined at https://www.linkedin.com/pulse/piecemeal-security-rob-berendt/?trackingId=mnD3iawUTkm%2FYvsj1W5dww%3D%3D

This basically says the authorization list assigned to your data has *ALL for the adopted authority set to the programs either from your vendor or your custom applications.  Then *USE for those users who are your Query mongers or data scientists.  All others are *EXCLUDE.

Now, if you love to play whack-a-mole you can look at exit point software and shovel out some serious cash and tie up time setting that up.

------------------------------
Robert Berendt IBMChampion
Business Systems Analyst, Lead
Dekko
Fort Wayne
------------------------------

Hi Miguel, i am not sure if i understood the question, but, i think you should need an exit program to control the exit point of SQL remote access function.

------------------------------
ULISES JIMENEZ
------------------------------

Hi Miguel,

To restrict SQL connections to read-only on IBM i 7.4, the most secure approach combines database object authority management with Exit Point programs for remote access.

1. Database Authorities (Server-Side Security) This is the most robust method because it applies regardless of how the user connects.

• Native IBM i Authorities: Grant *USE (read-only) authority instead of *CHANGE or *ALL on the target tables or libraries using: GRTOBJAUT OBJ(LIBRARY/TABLE) OBJTYPE(*FILE) USER(USERNAME) AUT(*USE)

• SQL Statements: Execute standard GRANT/REVOKE commands to enforce explicit permissions- REVOKE INSERT, UPDATE, DELETE ON TABLE FROM USERNAME;
  - GRANT SELECT ON TABLE TO USERNAME;

2. Exit Point Programs If connections originate from external connectors (ODBC, JDBC, OLE DB, .NET), network traffic can be controlled via database exit points.

• Attach custom logic to exit points QIBM_QZDA_SQL2 (SQL execution) or QIBM_QZDA_NDB1 (native DB access).

• The Exit Program inspects incoming SQL statements prior to execution and rejects requests containing INSERT, UPDATE, DELETE, ETC.

3. Client-Side Configuration (ODBC/JDBC)

• Set the connection string or Data Source Name to use read-only mode (AccessMode=1 or ReadOnly=True).

• Note: This prevents accidental edits from the client app, but it must be backed by server-side security to prevent users from overriding local settings.

4. User Profile Hardening

• Set the user profile to LMTCPB(*YES) to block execution of system command lines through emulation tools or interactive SQL utilities.
  
  

Regards.

------------------------------
[Guido] [Martinez]
------------------------------

------------------------------
Tony Crabtree
------------------------------

Original Message:
Sent: 08/25/26 09:01 AM
From: Guido Martinez
Subject: SQL limits in 7.4

Hi Miguel,

To restrict SQL connections to read-only on IBM i 7.4, the most secure approach combines database object authority management with Exit Point programs for remote access.

1. Database Authorities (Server-Side Security) This is the most robust method because it applies regardless of how the user connects.

• Native IBM i Authorities: Grant *USE (read-only) authority instead of *CHANGE or *ALL on the target tables or libraries using: GRTOBJAUT OBJ(LIBRARY/TABLE) OBJTYPE(*FILE) USER(USERNAME) AUT(*USE)

• SQL Statements: Execute standard GRANT/REVOKE commands to enforce explicit permissions- REVOKE INSERT, UPDATE, DELETE ON TABLE FROM USERNAME;
  - GRANT SELECT ON TABLE TO USERNAME;

2. Exit Point Programs If connections originate from external connectors (ODBC, JDBC, OLE DB, .NET), network traffic can be controlled via database exit points.

• Attach custom logic to exit points QIBM_QZDA_SQL2 (SQL execution) or QIBM_QZDA_NDB1 (native DB access).

• The Exit Program inspects incoming SQL statements prior to execution and rejects requests containing INSERT, UPDATE, DELETE, ETC.

3. Client-Side Configuration (ODBC/JDBC)

• Set the connection string or Data Source Name to use read-only mode (AccessMode=1 or ReadOnly=True).

• Note: This prevents accidental edits from the client app, but it must be backed by server-side security to prevent users from overriding local settings.

4. User Profile Hardening

• Set the user profile to LMTCPB(*YES) to block execution of system command lines through emulation tools or interactive SQL utilities.
  
  

Regards.

------------------------------
[Guido] [Martinez]
------------------------------

1. The Exit Point: You need to register your custom program under the native system exit point QIBM_QZDA_SQL2 using the ZDAQ0200 parameter format.
2. The Code Logic: The operating system passes a structured data structure to your program (usually written in RPGLE or C). This structure contains the user profile, the request type, and the exact raw SQL text.
3. The Verdict: Your program inspects the text string (e.g., verifying it starts with SELECT and does not contain unauthorized keywords like DELETE, UPDATE, or DROP). If authorized, it returns a parameter value of X'F1' (allow); if unauthorized, it returns X'F0' (reject), which immediately kills the execution and returns a database error to the user.

• Official Parameter Structure: Review this official IBM documentation to see the exact byte layout, lengths, and positions of the SQL text and user fields within the structure: Parameter fields for exit point QIBM_QZDA_SQL2 format ZDAQ0200.
• Step-by-Step Implementation Guide: This article by IT Jungle explains how to structure the program logic and register it in the OS using system commands: Get Creative Using the SQL Database Exit Point.
• Practical Code Example: If you want to see a functional code skeleton to intercept these remote database requests, check out this community guide: Secure DB2 for i Database Server Access.

------------------------------
Cristian Ballestero
Software Arquitect
BAC
------------------------------

If you want to parse the SQL statement, do NOT rely on your own parsing capabilities - there is too much, that you will get wrong. 

But there is the SQL function PARSE_STATEMENT - this will do most of the heavy lifting. 

https://www.ibm.com/docs/en/i/7.4.0?topic=services-parse-statement-table-function

It should give you enough information - but of course it will add some overhead to SQL execution times, but you can limit this easily to "remote SQL sessions" only, and don't parse statements, that are issued from programs.

HTH

Daniel

------------------------------
Daniel Gross
#IBMChampion
Senior Core Developer, Geis Group
Pegnitz, Germany
https://blog.qpgmr.de/
------------------------------

Original Message:
Sent: 08/29/26 01:37 PM
From: Cristian Ballestero
Subject: SQL limits in 7.4

Hi,

Regarding your question about whether it is possible to validate SQL statements sent by a user in IBM i (for example, to restrict access so only SELECT statements are allowed), yes, it is completely feasible.

This is achieved by intercepting external database connections (such as ODBC, JDBC, or .NET) using an exit program. Here is a breakdown of how it works technically, along with the official documentation and community code examples to help you get started:

Technical Overview

1. The Exit Point: You need to register your custom program under the native system exit point QIBM_QZDA_SQL2 using the ZDAQ0200 parameter format.
2. The Code Logic: The operating system passes a structured data structure to your program (usually written in RPGLE or C). This structure contains the user profile, the request type, and the exact raw SQL text.
3. The Verdict: Your program inspects the text string (e.g., verifying it starts with SELECT and does not contain unauthorized keywords like DELETE, UPDATE, or DROP). If authorized, it returns a parameter value of X'F1' (allow); if unauthorized, it returns X'F0' (reject), which immediately kills the execution and returns a database error to the user.

Documentation and Code Examples:

• Official Parameter Structure: Review this official IBM documentation to see the exact byte layout, lengths, and positions of the SQL text and user fields within the structure: Parameter fields for exit point QIBM_QZDA_SQL2 format ZDAQ0200.
• Step-by-Step Implementation Guide: This article by IT Jungle explains how to structure the program logic and register it in the OS using system commands: Get Creative Using the SQL Database Exit Point.
• Practical Code Example: If you want to see a functional code skeleton to intercept these remote database requests, check out this community guide: Secure DB2 for i Database Server Access.

Important Considerations:

While this approach is excellent for auditing or perimeter blocking, it does introduce a minor performance overhead because the program triggers for every single SQL statement sent to the server. Furthermore, parsing raw text can sometimes be bypassed using SQL evasion tricks (such as embedding inline comments like UPD/*comment*/ATE).

Because of this, IBM always recommends pairing or prioritizing native object-level security. If a user is restricted to read-only (*READ) authority at the database level, the Db2 for i engine will automatically block any write attempts natively, which is inherently more secure.

------------------------------
Cristian Ballestero
Software Arquitect
BAC
------------------------------