Troubleshooting
Problem
This document discusses IBM i security considerations for ODBC, OLE DB, .NET, and JDBC applications.
Resolving The Problem
Overview
ODBC, JDBC, OLEDB, and .NET do not introduce any new security exposures. What these providers do introduce is the middleware necessary for a plethora of web and desktop applications that offer easy access to data on the IBM iSeries family of servers system. In the older, green-screen IBM i environment, system administrators typically had complete control over the applications that users could run. Many system administrators took the approach of securing the applications users could access rather than securing the data itself. In a client/server environment, the application can be anything from a web browser to a PC productivity application such as IBM Lotus SmartSuite or even a malicious PC program obtained from a hacker's website. In this type of environment, administrators need to consider other approaches.
The following information is not intended to be a comprehensive guide to security issues on the IBM i. The intent is to provide an overview of security issues that can impact users of IBM i data access components. It also discusses some of the security and system management features of IBM i that can be used to address security concerns.
Contents
Security concepts
The following is an overview of some of the security and system administration features offered by IBM i.
Object level security
You can define who can use an object and how it can be used by using object level security. Object level security cannot be circumvented. IBM i checks authority every time the object is accessed. Group profiles and authorization lists can be used to enhance administration.
Here is an example of the object security on a file.
Object secured by authorization list . . . . . . . ORDENTRY
In this example, an authorization list named ORDENTRY is used to ease administration. User profile ADMIN1 has full access to the file, while *PUBLIC can read but not update the file. In this example, user profiles running the order entry application would be entered into the ORDENTRY authorization list with *CHANGE authority. For more information about security on IBM i, see IBM i Security Reference.
Program adopted authority
The system administrator might want to limit the methods by which a user profile can access data. In the prior example, they might decide that the order entry user profile must have no access to the files except when the user runs the order entry application. IBM i supports this concept by using program adopted authority. With the program adopted authority, the user profile running the application program adopts an authorization level attached to the program itself. This technique is commonly used by green screen applications to ensure the integrity of data.
Using the order entry example, it might be changed such that the order entry PROGRAM has *CHANGE authority to the files but the user profiles have *NONE access to the files. The order entry user profiles have *USE authority to the PROGRAM. When the user runs the program, the order entry profiles adopt the authority of the program, gaining access to the file. For more information, see IBM i Documentation.
DB2 on i features and referential integrity
In V3R1M0, DB2/400 introduced database support for referential integrity. Before V3R1M0, many customers used program-adopted authority to protect the integrity of their database. Using our order entry example, we might want to ensure that no order detail entry could be made without matching an existing order header. Using program adopted authority, every program that updates the order entry files must contain this check. Referential Integrity allows the database rules to be coded directly in the database. Administrators no longer have to restrict file updates to certain programs to ensure integrity of the data.
V4R2M0 further enhanced referential integrity with support of column level security and check constraints. Check constraints allow some of the validation and business rule processing to be moved from the applications to the database. Column level security allows update and references authority at the column level. For further information, reference the iSeries Information Center.
Sign-on programs and the QWTSETP API
Occasionally the system administrator might have to deal with a legacy application that does not use program adopted authority and that requires full access to the application files. One way to handle this situation is to use a signon program that the user must run before the user can execute the legacy app. This program calls the QWTSETP OS/400 API to switch the current profile of the 5250 emulation job to an internal profile that has full authority to the application and data. The normal user profile is not given authority to the data. See the System API reference for further details on using the QWTSETP API.
Techniques that secure data access methods rather than the data
Some system administrators attempt to secure access to the data rather than the data itself. This practice is risky as they have to know all the methods that data can be accessed by. Common techniques are:
Security considerations for ODBC
Restricting ODBC tracing
The Microsoft ODBC runtime includes an excellent diagnostic utility that allows tracing of the ODBC API calls. In ODBC 3.0, this function is controlled by the ODBC administrator and implemented in ODBCTRAC.DLL file. In a typical installation, all users have authority to enable the logging. There are two major concerns with this tool:
There are two common methods to prevent the use of the ODBC trace. First, the application can call the ODBC set connection API and set SQL_OPT_TRACE to SQL_OPT_TRACE_OFF. This function call can be easily done by using any ODBC programming model that exposes a connection handle. Another method is to delete the trace DLL (ODBCTRAC.DLL) and secure the PC so that the file cannot be replaced and no other trace tool can be installed.
IBM i Access ODBC always encrypts the password before it sends the password to IBM i. If no user ID or password is passed on the ODBC connect string, the ODBC driver uses the current user of the IBM i Access connection. Passwords are stored and handled in a safe encrypted format. However, if your application needs to pass a hidden user profile and password that has greater authority than the user's normal profile, the profile and password can be traced by the ODBC trace tool. Consider the use of either stored procedure program adopted authority or consider passing an encrypted user ID or password on a stored procedure call in combination with the IBM i Set Profile API.
Restricting database access to specific ODBC programs
System administrators often need to limit access to particular files to a certain program or set of programs. A 'Green Screen' programmer would implement this using program adopted authority. A similar method can be used with ODBC.
Stored procedures allow ODBC programmers to implement program adopted authority. The programmer might not want users to be able to manipulate database files when they use desktop applications such as Microsoft Access or IBM Lotus 1-2-3. Instead, the programmer might want to limit database updates to only their application. To implement this change, user access to the database must be restricted with object level security or user exit programs. The application must then be written to send data requests to the stored procedure and have the stored procedure update the database.
Another advantage to this approach is that it 'hides' the details of your code from others. Anyone can use an ODBC trace to monitor SQL calls and reverse engineer an application or database. Stored procedures hide many of the details from a trace.
For further information on stored procedures, reference the iSeries Information Center.
ODBC data source restrictions
Some ODBC drivers support a 'read only' setting on the data source or mandatory compliance with the SQL_ACCESS_MODE connection attribute. Although not secure, this setting can help in preventing accidental delete or update operations. IBM i Access Windows Application Package supports this function, which can be set in the ODBC data source settings. This setting could be used with iSeries Access policies to enforce the setting.
IBM i Access ODBC policies
IBM i Access includes support for certain IBM i Access ODBC policies. The policies are intended as an administration feature. The IBM i Access ODBC policies include: Prevent usage of non-named data sources and prevent usage of named data sources. When combined with the proper windows policies, you can restrict a user to running only predefined (and configured) IBM i Access ODBC data sources. For example, this policy might be used in combination with a DSN connection type property of read-only to limit a user to read only connections. To be effective, you need to:
Security tools
When you install IBM i, a set of Security Tools is installed to help in analyzing system security. These tools can be accessed from the GO SECTOOLS menu with a user profile that has all object authority. For more information, see IBM Documentation.
More assistance
In-depth security reviews and assistance implementing the strategies described is available through a consulting agreement at 1-800-IBM-SERV. For more information about securing IBM i, see IBM Documentation - IBM i Security
ODBC, JDBC, OLEDB, and .NET do not introduce any new security exposures. What these providers do introduce is the middleware necessary for a plethora of web and desktop applications that offer easy access to data on the IBM iSeries family of servers system. In the older, green-screen IBM i environment, system administrators typically had complete control over the applications that users could run. Many system administrators took the approach of securing the applications users could access rather than securing the data itself. In a client/server environment, the application can be anything from a web browser to a PC productivity application such as IBM Lotus SmartSuite or even a malicious PC program obtained from a hacker's website. In this type of environment, administrators need to consider other approaches.
The following information is not intended to be a comprehensive guide to security issues on the IBM i. The intent is to provide an overview of security issues that can impact users of IBM i data access components. It also discusses some of the security and system management features of IBM i that can be used to address security concerns.
Contents
- Security Concepts (overview of basic iSeries security concepts and tools)
- Object Level Security
- Program Adopted Authority
- IBM DB2 on i Features and Referential Integrity
- Sign-on Programs and the QWTSETP API
- Techniques That Secure Data Access Methods Rather than Data
- IBM i Access Client Solutions (security issues specific to IBM i Access Client Solutions)
- Security Considerations for ODBC
- Security Considerations for the iSeries OLE DB and .NET Providers
- Restricting CPU Use by User
- Database Host Server User Exit Points
- Security Tools
- More Assistance
Security concepts
The following is an overview of some of the security and system administration features offered by IBM i.
Object level security
You can define who can use an object and how it can be used by using object level security. Object level security cannot be circumvented. IBM i checks authority every time the object is accessed. Group profiles and authorization lists can be used to enhance administration.
Here is an example of the object security on a file.
Object secured by authorization list . . . . . . . ORDENTRY
Object ----------Object---------- User Group Authority Opr Mgt Exist Alter Ref ADMIN1 *ALL X X X X X *PUBLIC *USE X Object ---------------Data--------------- User Authority Read Add Update Delete Execute ADMIN1 *ALL X X X X X *PUBLIC *USE X X
In this example, an authorization list named ORDENTRY is used to ease administration. User profile ADMIN1 has full access to the file, while *PUBLIC can read but not update the file. In this example, user profiles running the order entry application would be entered into the ORDENTRY authorization list with *CHANGE authority. For more information about security on IBM i, see IBM i Security Reference.
Program adopted authority
The system administrator might want to limit the methods by which a user profile can access data. In the prior example, they might decide that the order entry user profile must have no access to the files except when the user runs the order entry application. IBM i supports this concept by using program adopted authority. With the program adopted authority, the user profile running the application program adopts an authorization level attached to the program itself. This technique is commonly used by green screen applications to ensure the integrity of data.
Using the order entry example, it might be changed such that the order entry PROGRAM has *CHANGE authority to the files but the user profiles have *NONE access to the files. The order entry user profiles have *USE authority to the PROGRAM. When the user runs the program, the order entry profiles adopt the authority of the program, gaining access to the file. For more information, see IBM i Documentation.
DB2 on i features and referential integrity
In V3R1M0, DB2/400 introduced database support for referential integrity. Before V3R1M0, many customers used program-adopted authority to protect the integrity of their database. Using our order entry example, we might want to ensure that no order detail entry could be made without matching an existing order header. Using program adopted authority, every program that updates the order entry files must contain this check. Referential Integrity allows the database rules to be coded directly in the database. Administrators no longer have to restrict file updates to certain programs to ensure integrity of the data.
V4R2M0 further enhanced referential integrity with support of column level security and check constraints. Check constraints allow some of the validation and business rule processing to be moved from the applications to the database. Column level security allows update and references authority at the column level. For further information, reference the iSeries Information Center.
Sign-on programs and the QWTSETP API
Occasionally the system administrator might have to deal with a legacy application that does not use program adopted authority and that requires full access to the application files. One way to handle this situation is to use a signon program that the user must run before the user can execute the legacy app. This program calls the QWTSETP OS/400 API to switch the current profile of the 5250 emulation job to an internal profile that has full authority to the application and data. The normal user profile is not given authority to the data. See the System API reference for further details on using the QWTSETP API.
Techniques that secure data access methods rather than the data
Some system administrators attempt to secure access to the data rather than the data itself. This practice is risky as they have to know all the methods that data can be accessed by. Common techniques are:
- Limit Capabilities User Profile Parameter
- Useful for green screen or 5250 emulation-based applications. This method assumes that if you prevent users from typing in commands on a 5250 screen, they can access data only through the programs and menus that the system administrator provides them. This method is only secure if all back doors are accounted for. A back door would be virtually any IBM i program, API, or command (IBM or user-written) that can be executed remotely.
- User Exit Programs
- A user exit program allows the system administrator to secure a host server program that implemented support for user exits. System administrators often overlook the DDM user exit, which is documented in IBM i Communications. The DDM server is used by some IBM i Access functions such as the OLE DB provider, the 16-bit remote command program as well as many APPC programs and Rational Developer for i.
- Audit Logs (Monitoring Security)
- IBM i has several logs that can be used to monitor security. QHST, the history log, contains messages that relate to security changes made to the system. For detailed monitoring of security-related functions, QAUDJRN can be enabled.
- Journals
- Journaling is often used with client/server applications to provide commitment control. The journals contain detailed information on every update made to a file being journaled. The journal information can be formatted and queried to sift for specific information such as what user profiles updated the file, what records were updated, type of update. IBM i also allows user-defined journal entries. Used with a user exit program or trigger, journaling offers a relatively low-cost method of maintaining user-defined audits.
- Application Administration
- Application Administration allows system administrators to control the functions or applications available to users and groups on a specific IBM i system. Starting with V4R4M0, Application Administration can control many IBM i Access functions such as ODBC, OLE DB, and Data transfer.
For more information about all of the options described, see IBM i Documentation.
IBM i Access concepts
IBM i Access policies
Several IBM i Access functions support Microsoft Windows policies. The intent of this support is to enhance administration of IBM i Access by working with the "Zero Administration for Windows Initiative" from Microsoft. IBM i Access Client Solutions Windows Application Package ships with a policy template generation utility CWBADGEN.EXE. This utility, when called with the correct parameters, creates the policy template files that can be imported into a policy editor. For more information about this utility, see IBM i Documentation and Client Access Express for Windows IBM Redbooks SG24-5191, Section 12.6.4.
Host servers
Each client application on a PC is a program that communicates with a corresponding server on IBM i. This server processes a request and returns data. IBM i has several servers included in the base operating system, which are documented in IBM Documentation including the database, remote command and program call, data queue, and file servers. A system administrator must watch for changes or additions to these servers.
Any communications program can act as a server. The system administrator must also be aware of any OEM server or user-written server that might reside on their system. Any of these programs can be used to access IBM i data.
Server program security
IBM server programs authenticate the user ID and password sent by the client on the program 'allocate' or connect. By default this ID is the user ID and password of the IBM i Access "current user". However, many servers, including ODBC, OLE DB, .NET, and JDBC, provide an option to override the credential with a different user ID and password. After the server program verifies the security information, it uses the authority of this user ID to access data.
User Exit Programs
Each IBM i host server program has an associated user exit point. A user exit program allows an administrator to control what activities a client is allowed to do for each of the specific servers. Whenever a request is sent to the server, the server first checks whether a user exit program is registered. If so, it passes a block of data with the request information to the user exit. The user exit program can process this data and return a flag allowing or preventing the server from processing the request. User exit programs can have a significant impact on performance.
Common back doors
Several servers offer methods to submit IBM i commands from the client. Restricting command-line usage does not block remote commands. Compliance of Navigator for i and the 32-bit Distributed Program Call API is voluntary and implemented on the client, not enforced by the host. Remote Command uses either the DDM server or the Remote Command and Distributed Program Call Server depending on the client. ODBC, .NET, toolbox JDBC, and OLE DB all support a method of running IBM i commands and programs by a call to QCMDEXC.
Removing data access components
IBM i Access ODBC, OLE DB, and .NET are Selective Setup options of IBM i Access Client Solutions Windows Application Package. Toolbox JDBC is not a function of the Windows Application Package but can be installed through its Selective Setup options or downloaded from the web and it is used by the IBM i Access Client Solutions Base Package. Keep in mind that removing any of these providers does not prevent access to the IBM i database server programs. Other APIs and applications can still directly access the database server through other DLLs included in the base installation. If you want to disable the database server program, we recommend you do so by using a user exit program that rejects all requests. Unlike disabling the TCP/IP port or server program, the exit program returns a descriptive error message to the application. This functionality simplifies administration and debugging.
IBM i Access concepts
IBM i Access policies
Several IBM i Access functions support Microsoft Windows policies. The intent of this support is to enhance administration of IBM i Access by working with the "Zero Administration for Windows Initiative" from Microsoft. IBM i Access Client Solutions Windows Application Package ships with a policy template generation utility CWBADGEN.EXE. This utility, when called with the correct parameters, creates the policy template files that can be imported into a policy editor. For more information about this utility, see IBM i Documentation and Client Access Express for Windows IBM Redbooks SG24-5191, Section 12.6.4.
Host servers
Each client application on a PC is a program that communicates with a corresponding server on IBM i. This server processes a request and returns data. IBM i has several servers included in the base operating system, which are documented in IBM Documentation including the database, remote command and program call, data queue, and file servers. A system administrator must watch for changes or additions to these servers.
Any communications program can act as a server. The system administrator must also be aware of any OEM server or user-written server that might reside on their system. Any of these programs can be used to access IBM i data.
Server program security
IBM server programs authenticate the user ID and password sent by the client on the program 'allocate' or connect. By default this ID is the user ID and password of the IBM i Access "current user". However, many servers, including ODBC, OLE DB, .NET, and JDBC, provide an option to override the credential with a different user ID and password. After the server program verifies the security information, it uses the authority of this user ID to access data.
User Exit Programs
Each IBM i host server program has an associated user exit point. A user exit program allows an administrator to control what activities a client is allowed to do for each of the specific servers. Whenever a request is sent to the server, the server first checks whether a user exit program is registered. If so, it passes a block of data with the request information to the user exit. The user exit program can process this data and return a flag allowing or preventing the server from processing the request. User exit programs can have a significant impact on performance.
Common back doors
Several servers offer methods to submit IBM i commands from the client. Restricting command-line usage does not block remote commands. Compliance of Navigator for i and the 32-bit Distributed Program Call API is voluntary and implemented on the client, not enforced by the host. Remote Command uses either the DDM server or the Remote Command and Distributed Program Call Server depending on the client. ODBC, .NET, toolbox JDBC, and OLE DB all support a method of running IBM i commands and programs by a call to QCMDEXC.
Removing data access components
IBM i Access ODBC, OLE DB, and .NET are Selective Setup options of IBM i Access Client Solutions Windows Application Package. Toolbox JDBC is not a function of the Windows Application Package but can be installed through its Selective Setup options or downloaded from the web and it is used by the IBM i Access Client Solutions Base Package. Keep in mind that removing any of these providers does not prevent access to the IBM i database server programs. Other APIs and applications can still directly access the database server through other DLLs included in the base installation. If you want to disable the database server program, we recommend you do so by using a user exit program that rejects all requests. Unlike disabling the TCP/IP port or server program, the exit program returns a descriptive error message to the application. This functionality simplifies administration and debugging.
Security considerations for ODBC
Restricting ODBC tracing
The Microsoft ODBC runtime includes an excellent diagnostic utility that allows tracing of the ODBC API calls. In ODBC 3.0, this function is controlled by the ODBC administrator and implemented in ODBCTRAC.DLL file. In a typical installation, all users have authority to enable the logging. There are two major concerns with this tool:
- Users can record and view most of the data passed between the application and ODBC driver.
- Early versions of the administrator also recorded the full connect string - including any user ID and password supplied by the application. This issue is not specific to any particular ODBC driver but to the Microsoft ODBC specification in general.
There are two common methods to prevent the use of the ODBC trace. First, the application can call the ODBC set connection API and set SQL_OPT_TRACE to SQL_OPT_TRACE_OFF. This function call can be easily done by using any ODBC programming model that exposes a connection handle. Another method is to delete the trace DLL (ODBCTRAC.DLL) and secure the PC so that the file cannot be replaced and no other trace tool can be installed.
IBM i Access ODBC always encrypts the password before it sends the password to IBM i. If no user ID or password is passed on the ODBC connect string, the ODBC driver uses the current user of the IBM i Access connection. Passwords are stored and handled in a safe encrypted format. However, if your application needs to pass a hidden user profile and password that has greater authority than the user's normal profile, the profile and password can be traced by the ODBC trace tool. Consider the use of either stored procedure program adopted authority or consider passing an encrypted user ID or password on a stored procedure call in combination with the IBM i Set Profile API.
Restricting database access to specific ODBC programs
System administrators often need to limit access to particular files to a certain program or set of programs. A 'Green Screen' programmer would implement this using program adopted authority. A similar method can be used with ODBC.
Stored procedures allow ODBC programmers to implement program adopted authority. The programmer might not want users to be able to manipulate database files when they use desktop applications such as Microsoft Access or IBM Lotus 1-2-3. Instead, the programmer might want to limit database updates to only their application. To implement this change, user access to the database must be restricted with object level security or user exit programs. The application must then be written to send data requests to the stored procedure and have the stored procedure update the database.
Another advantage to this approach is that it 'hides' the details of your code from others. Anyone can use an ODBC trace to monitor SQL calls and reverse engineer an application or database. Stored procedures hide many of the details from a trace.
For further information on stored procedures, reference the iSeries Information Center.
ODBC data source restrictions
Some ODBC drivers support a 'read only' setting on the data source or mandatory compliance with the SQL_ACCESS_MODE connection attribute. Although not secure, this setting can help in preventing accidental delete or update operations. IBM i Access Windows Application Package supports this function, which can be set in the ODBC data source settings. This setting could be used with iSeries Access policies to enforce the setting.
IBM i Access ODBC policies
IBM i Access includes support for certain IBM i Access ODBC policies. The policies are intended as an administration feature. The IBM i Access ODBC policies include: Prevent usage of non-named data sources and prevent usage of named data sources. When combined with the proper windows policies, you can restrict a user to running only predefined (and configured) IBM i Access ODBC data sources. For example, this policy might be used in combination with a DSN connection type property of read-only to limit a user to read only connections. To be effective, you need to:
- Prevent connections of non-managed PCs to the network.
- Prevent user changes to the registry.
- Prevent installation of any other program.
- Prevent access to any program that can access an IBM i server program.
The ODBC policy has no effect on the IBM i Access SQL APIs, OLE DB provider, .NET, or any other programs that use the database server program.
Security Considerations for the IBM i Access OLE DB and .NET Providers
IBM i Access OLE DB and .NET Providers
Most of the techniques discussed previously for ODBC also apply to OLE DB and .NET. However, keep in mind that unlike ODBC, the IBM i Access OLE DB provider can use four different host server programs: Database server (SQL and stored procedure calls); DDM (record level access); Optimized Data Queue Server (Data Queue Access); and the "DPC" or Distributed Program Call and Remote Command Server (CL command and Program calls). If you attempt to implement an access-based security model, you must be aware of which server an application is using. Further tips for securing IBM i Access OLE DB can be found in the Redbooks "A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support" (SG24-5183).
Restricting Tracing
Unlike ODBC, OLE DB and .NET do not include a simple trace tool. The built-in IBM i Access OLE DB trace tools record little information that would be of use to a hacker. If the user is allowed to install debuggers and other spy software, then any API or application can be hacked into. This concern is not unique to IBM i Access OLE DB and .NET.
Restricting Database Access to Users of Specific Programs
The same techniques used for ODBC apply to OLE DB and .NET; however, keep in mind that OLE DB can use any one of four host server programs. Only Distributed Program Call and Stored procedures can take advantage of program adopted authority.
User Exits Used by the OLE DB Provider
The Optimized Data Queue server does not allow direct access to files on IBM i but it does support a user exit program. The DDM exit point is not as robust as the IBM i Access host servers. Further information on monitoring this exit point can be found in document OLE DB - DDM Exit Point Considerations. Further tips for securing IBM i Access OLE DB can be found in the Redbooks "A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support", and the AS/400 iSeries Access Host Servers manual.
IBM i Access policies
IBM i Access OLE DB includes a new policy: Prevent OLE DB provider usage.
Restricting CPU use by user
ODBC, OLE DB, .NET, and JDBC all made it easier to access IBM i data. One negative impact is that users can accidentally create CPU-intensive queries without realizing it. Database host server jobs run at an interactive job priority, which can severely impact system performance. IBM i supports a Query Governor. The Query Governor can be invoked willingly (for example, by the PC application) in a stored procedure call or with ODBC APIs by using the query timeout parameter. It can also be forced on the database server job by a user exit program. The time limit is specified on the QRYTIMLMT parameter of the CHGQRYA CL command.
When you use an ADO application, ADO defines a CommandTimeout property on the command and connection objects. This property corresponds to the ODBC query timeout property. Caution must also be used when you attempt to set the query attributes by using a stored procedure call. Keep in mind that each OLE DB function is supplied by a different program and the stored procedure object runs in its own job.
Job priority and other job runtime attributes can be controlled in a similar manner through a simple user exit program or stored procedure call. For more information, see IBM i Documentation.
Database host server user exit points
Database server user exit points
IBM i Access ODBC, OLE DB, and .NET, and toolbox JDBC requests use the IBM-supplied database server's four user exit points. To allow read only access to data, QIBM_QZDA_NB1, QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2 must be monitored.
QIBM_QZDA_INIT
This exit program is called once at server initialization; for example, on an ODBC connect. This exit point is ideal to use for a query governor or to completely reject incoming requests.
QIBM_QZDA_NB1
This exit program is called for 'native' database requests. An exit program can monitor this exit point to block or restrict database file functions such as delete file, clear file (erase all data), and certain create file commands.
QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2
These exit points process the data related to SQL requests with the difference being that QIBM_QZDA_SQL1 passes the first 512 bytes of the SQL statement to the exit program while QIBM_QZDA_SQL2 passes the entire SQL statement. No parsing or pre-processing of the statement is done. The programmer writing the exit program must parse the statement and interpret its content. For example, to limit certain users to read only access, the exit program would have to scan each string for any SQL function that can update, delete, or insert data into a file. There is significant cost associated with using this exit point since it is called for each SQL request. Object level security must be considered first.
QIBM_QZDA_ROI1
This exit point is called for requests that retrieve information about objects on the database server (catalog requests). Information can be retrieved only.
Security Considerations for the IBM i Access OLE DB and .NET Providers
IBM i Access OLE DB and .NET Providers
Most of the techniques discussed previously for ODBC also apply to OLE DB and .NET. However, keep in mind that unlike ODBC, the IBM i Access OLE DB provider can use four different host server programs: Database server (SQL and stored procedure calls); DDM (record level access); Optimized Data Queue Server (Data Queue Access); and the "DPC" or Distributed Program Call and Remote Command Server (CL command and Program calls). If you attempt to implement an access-based security model, you must be aware of which server an application is using. Further tips for securing IBM i Access OLE DB can be found in the Redbooks "A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support" (SG24-5183).
Restricting Tracing
Unlike ODBC, OLE DB and .NET do not include a simple trace tool. The built-in IBM i Access OLE DB trace tools record little information that would be of use to a hacker. If the user is allowed to install debuggers and other spy software, then any API or application can be hacked into. This concern is not unique to IBM i Access OLE DB and .NET.
Restricting Database Access to Users of Specific Programs
The same techniques used for ODBC apply to OLE DB and .NET; however, keep in mind that OLE DB can use any one of four host server programs. Only Distributed Program Call and Stored procedures can take advantage of program adopted authority.
User Exits Used by the OLE DB Provider
The Optimized Data Queue server does not allow direct access to files on IBM i but it does support a user exit program. The DDM exit point is not as robust as the IBM i Access host servers. Further information on monitoring this exit point can be found in document OLE DB - DDM Exit Point Considerations. Further tips for securing IBM i Access OLE DB can be found in the Redbooks "A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support", and the AS/400 iSeries Access Host Servers manual.
IBM i Access policies
IBM i Access OLE DB includes a new policy: Prevent OLE DB provider usage.
Restricting CPU use by user
ODBC, OLE DB, .NET, and JDBC all made it easier to access IBM i data. One negative impact is that users can accidentally create CPU-intensive queries without realizing it. Database host server jobs run at an interactive job priority, which can severely impact system performance. IBM i supports a Query Governor. The Query Governor can be invoked willingly (for example, by the PC application) in a stored procedure call or with ODBC APIs by using the query timeout parameter. It can also be forced on the database server job by a user exit program. The time limit is specified on the QRYTIMLMT parameter of the CHGQRYA CL command.
When you use an ADO application, ADO defines a CommandTimeout property on the command and connection objects. This property corresponds to the ODBC query timeout property. Caution must also be used when you attempt to set the query attributes by using a stored procedure call. Keep in mind that each OLE DB function is supplied by a different program and the stored procedure object runs in its own job.
Job priority and other job runtime attributes can be controlled in a similar manner through a simple user exit program or stored procedure call. For more information, see IBM i Documentation.
Database host server user exit points
Database server user exit points
IBM i Access ODBC, OLE DB, and .NET, and toolbox JDBC requests use the IBM-supplied database server's four user exit points. To allow read only access to data, QIBM_QZDA_NB1, QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2 must be monitored.
QIBM_QZDA_INIT
This exit program is called once at server initialization; for example, on an ODBC connect. This exit point is ideal to use for a query governor or to completely reject incoming requests.
QIBM_QZDA_NB1
This exit program is called for 'native' database requests. An exit program can monitor this exit point to block or restrict database file functions such as delete file, clear file (erase all data), and certain create file commands.
QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2
These exit points process the data related to SQL requests with the difference being that QIBM_QZDA_SQL1 passes the first 512 bytes of the SQL statement to the exit program while QIBM_QZDA_SQL2 passes the entire SQL statement. No parsing or pre-processing of the statement is done. The programmer writing the exit program must parse the statement and interpret its content. For example, to limit certain users to read only access, the exit program would have to scan each string for any SQL function that can update, delete, or insert data into a file. There is significant cost associated with using this exit point since it is called for each SQL request. Object level security must be considered first.
QIBM_QZDA_ROI1
This exit point is called for requests that retrieve information about objects on the database server (catalog requests). Information can be retrieved only.
| Caution: Different ODBC drivers use different servers. You can secure the IBM Database Server by using a user exit program but other ODBC drivers might not use this server. Many OEM ODBC drivers supply their own server program on IBM i or use IBM® DRDA® or DDM. DRDA (a type of DDM request) uses the SQL Client Integration (SQL CLI) exit point. For more information about these exit points, see IBM i Documentation. |
Security tools
When you install IBM i, a set of Security Tools is installed to help in analyzing system security. These tools can be accessed from the GO SECTOOLS menu with a user profile that has all object authority. For more information, see IBM Documentation.
More assistance
In-depth security reviews and assistance implementing the strategies described is available through a consulting agreement at 1-800-IBM-SERV. For more information about securing IBM i, see IBM Documentation - IBM i Security
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]
Historical Number
13432954
Was this topic helpful?
Document Information
Modified date:
11 August 2025
UID
nas8N1018280