IBM Support

SQL and limiting access on your IBM i

Question & Answer


Question

What are my options to limit query access to my data on the IBM i?

Cause

There are a few ways to limit query access to your data on the IBM i.
Each subsequent option adds complexity.

Answer

On the IBM i the easiest way to prevent users from seeing data that they should not be able to see is using authority:
www.ibm.com/docs/en/i/7.5?topic=authority-types-data 
The benefit of using object level authority is that it does not matter how the user tries to access to the data, any interface is protectee.
If users have access to a command line you can remove authority to commands that provide SQL interface (STRSQL / RUNSQL / RUNSQLSTM / STRQM, etc.)
If running SQL from an application using ODBC or JDBC driver to connect some 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.
QIBM_QZDA_INIT
QIBM_QZDA_NDB1
QIBM_QZDA_SQL1
QIBM_QZDA_SQL2
QIBM_QZDA_ROI1
These exit points are described here:
www.ibm.com/docs/en/i/7.5?topic=performance-exit-programs
The Open Database File exit program is called when a job is opening a database file regardless of the interface used.
This exit is called in the job that is attempting to open the file. 
This developerWorks article describes using QIBM_QDB_OPEN.

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m3p000000hBhtAAE","label":"IBM i Db2-\u003EEncryption"},{"code":"a8m0z0000001i3gAAA","label":"IBM i Db2-\u003EField Procedures (FieldProc)"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
30 November 2024

UID

ibm17073452

Page Feedback