How can we Lockdown SQL Access for IBM i Users?

Administrators often want to limit what SQL statements a user is allowed to run because how powerful SQL can be.

What’s Different about SQL? SQL is a separate interface to the data and runtime SQL is part of the basic operating system. SQL uses the standard IBM i object authority to determine whether a user is authorized to execute the SQL statement. If the user has the right authority to the file and data, they can process the request using SQL, RPG, or any other function.

Once a user is within SQL — You cannot limit the type of SQL that a user is authorized to run!!

Obviously, it’s essential that the authority to files are accurate. Think of the case of a user accidentally entering a malformed SQL statement when intending to delete one row, and accidentally deleting ALL rows in a table!?

For more information on setting authority to your database file, see the following links.

IBM i Security

The IBM i operating system provides many security features that help you control access to data and files.

These topics describe some of the file security functions. The topics covered include types of object authority including object operational, object existence, object management, object reference, and object alter authorities. Data authority and why you would want to limit user access to data is also described. Other topics that are included are authorities that are required for file operations and how to limit access to files and data when you are creating files.

• Object authority
  There are several types of authority that can be granted to a user for a file. Also, you can use the SQL GRANT and REVOKE statements to assign and remove these IBM i authorities to SQL tables, including individual columns within those tables.
• Data authorities
  You can use data authorities to limit user access to the data in files.
• Authorities required for file operations
  This topic lists the file object authority and data authority required for file functions.
• Limitation of access to files and data when creating files
  Specifying authorities allows you to control access to a file. You use the AUT parameter on the create command to specify public authority when you create a file.

MORE – Security – IBM Documentation

Row and column access control (RCAC)

Row and column access control (RCAC) provide a data-centric alternative to achieve data security.

RCAC places access control at the table level around the data itself. SQL rules that are created on rows and columns are the basis of the implementation of this capability.

RCAC terms

• Base table – The table (physical file) the permission or mask is added to.
• Dependent object – Any object (file, schema, function, or other object) the permission or mask references.
• QIBM_DB_SECADM – The function usage identifier the user must be authorized to in order to manipulate all actions that are related to permissions and masks.
• Row and Column Access Control (RCAC) – Access control is the ability to control the access to data by using permissions and masks.
• Permission – A row permission defines a row access control rule for rows of a table.
• Mask – A column mask defines a column access control rule for a specific column in a table.
• RULETEXT – The expression to be used by the permission or mask.
• 5770-SS1 IBM Advanced Data Security for i (Option 47) – Product that needs to be ordered and installed to be able to:
  • create row permissions.
  • create column masks.
  • execute database access over objects that have active RCAC.

• Overview
  IBM Advanced Data Security for i introduces RCAC as an extra layer of data security.
• Permissions and masks
  RCAC is a model in which a security administrator manages privacy and security policies.
• SQL statements
  The SQL create, alter, and drop statements support the implementation of RCAC with permissions and masks.
• Secure functions
  Functions must be defined as secure before they can be called within RCAC definitions.
• Secure triggers
  Triggers defined on a table with RCAC activated must be secure.
• Administrative authority
  Authorization to the Database Security Administrator function of IBM i can be assigned through Application Administration in IBM Navigator for i.
• Best practices when using permissions and masks
  Permissions and masks can be created for a table in a number of different implementations. This section will explain some of the implementations that can be used to create permissions and masks

MORE – Row and column access control (RCAC) – IBM Documentation

So, how do we limited SQL access?

As with most IBM I Functions, we need to use several solutions for a full lockdown:

Lockdown COMMAND Access

If users have access to a command line, they can remove the authority to commands that provide SQL interface (STRSQL / RUNSQL / RUNSQLSTM / STRQM, ect.).

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 modify the profiles and Select allowed SQL statements to limit the type of SQL statement each user is allowed to execute.

Lockdown Network Connections ODBC and JDBC

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.

Exit programs

There are requirements when calling an IBM® i Access ODBC exit program.

An exit program is a program to which control is passed from a calling program. When you specify an exit program, the servers pass the following two parameters to the exit program before running your request:

• A 1-byte return code value.
• A structure containing information about your request. This structure is different for each of the exit points.

These two parameters allow the exit program to determine whether your request is allowed. If the exit program sets the return code to X’F0′, the server rejects the request. If the return code is set to anything else, the server allows the request.

The same program can be used for multiple exit points. The program can determine what function is being called by looking at the data in the second parameter structure.

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

Note: This exit point is called less often than in V5R1 and earlier Client Access ODBC drivers. If you have an exit program that uses this exit point, verify that it still works as intended.

• Examples: User exit programs
  The following examples do not show all of the programming considerations or techniques. Review the examples before you begin IBM i Access ODBC application design and coding.
• Exit program parameter formats
  The exit points for native database and retrieving object information have two formats that are defined: QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2. Depending on the type of IBM i database function that is requested, one of the formats is used

MORE – Exit programs – IBM Documentation⁤

Lockdown the Client Access SQL Buttons

If the user is utilizing ACS and Run SQL Scripts, you can modify the installation package of ACS to prevent the installation of Run SQL Scripts.

You need to exclude comps: rss.  You can do this by adding the rss to the acsconfig.properties exclude comps.

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

•  QIBM_XD1_OPNAV_DBLIBS                  
•  QIBM_XE1_OPNAV_DBSQLPCS    
•  QIBM_XE1_OPNAV_DBSQLPM   
•  QIBM_XE1_OPNAV_DBXACT