3 World JDBC Driver Administration

This chapter contains these topics:

Section 3.1, "World JDBC Driver and JD Edwards World Security Features,"
Section 3.2, "World JDBC Driver and JD Edwards World Data-Specific Features."

3.1 World JDBC Driver and JD Edwards World Security Features

The JDBC driver utilizes the following JD Edwards World security features:

User access security
Business unit security
Field security for select
File/field level security for insert/update/delete

3.1.1 Action Code User Access Security

The JDBC driver restricts access to JD Edwards World software using many of the same features JD Edwards World software uses. The JDBC driver user access security also includes the following:

It does not cache or maintain user logins and passwords in the setup it uses during every database connection. The JDBC driver requires the client application to provide the appropriate login and password when requesting a connection. All existing IBM user security setup in place at your installation continues. Every user must log into the IBM i with their existing IBM i user account.
The JDBC driver restricts access tables in the user library list in the JD Edwards World environment tables F0092, F0093, F0094, and F00944. The System Administrator controls which tables that the JDBC driver can access. See Section 3.2.2, "Environments and Specifications" for more information.
The connection library list is the concatenation of the IBM i QSYSLIBL libraries and the libraries in the environment the user chooses.
The JDBC driver validates every table in an SQL statement against the connection library list. See JDEWEnvironment in Section 2.3.4, "JDBC Driver and Data Source Properties."
The JDBC driver retrieves table and column metadata as well as related control data from the current connection library list.
If the JDBC driver references an object that does not exist in the user library list, the JDBC driver causes an error. This includes system files.

3.1.2 Business Unit Security

The JDBC driver performs Business Unit Security validation according to the business unit security rules for the user in the Business Unit Security table (F0001) and conforms to the logic in the Business Unit Security (C0000) standard RPG subroutine. See Work with Business Unit Security in the JD Edwards World Technical Foundation Guide for information.

The F0001 is the primary repository for business unit security rules and the user and table maintain these rules. See Section 3.2.2, "Environments and Specifications" for more information about identifying the specific instance of the F0001 file that the JDBC driver uses.

The JDBC driver extracts the business unit security rules for a given user and table and caches them for future reference. The JDBC driver converts all of the rules for a given user and table and formulates an SQL relational expression of the data values. The JDBC driver appends the SQL expression to the user's SQL statement before it is sent to the IBM i to execute. The JDBC driver always extracts the user ID from the current connection object.

The JDBC driver applies security to all database columns whose associated data dictionary item is class COSTCTRSEC. Some tables use more than one COSTCTRSEC related column. The JDBC driver examines all of these columns to ensure that all values meet the business unit security rules. The JDBC driver might disregard specific business unit security rules for specific columns. The JDBC driver uses UDC 98/UN to identify columns that the driver does not consider for Business Unit Security. The JDBC driver disregards Business Unit Security for a particular column if you enter the table column in UDC 98/UN. The value in the Code field is an arbitrary next number value. The table and column values you enter in the Description field, as shown in the following example, must include one space between them. The value in the Description 2 field indicates whether the exclusion rule is active or not. If the exclusion rule is active then the JDBC driver does not apply Business Unit Security to the database column you indicate. If the exclusion rule is not active, or does not exist in this UDC, then the JDBC driver applies Business Unit Security to the table column. You enter 1 to exclude column from Business Unit Security or another value to use Business Unit Security for the column. Following is an example of the pattern the UDC entries must use:

Code	Description	Description 2
0001	F55XX ABMCU	1
0002	F55XX ABHMCU	0

Business Unit Security differentiates numeric business unit security ranges from alphanumeric business unit security ranges. If you enter numeric values in the From and To fields in the F0001, then the JDBC driver considers only numeric business unit values when it searches for records that are within the range of the rule.

Using the SQL database function, JDEisNumber, enforces the rule that every row it identifies within the numeric business unit range is a numeric value. The JDEisNumber is a separate standalone program SQL function. The first user that attempts to use the JDBC driver automatically creates the JDEisNumber function. The JDBC driver creates this database function only once and all users of the same JD Edwards World instance use this function. The JDBC driver creates the JDEisNumber function in the QGPL library. Users can indicate a different library location for JDEisNumber using the JDEWFunctionSchema driver property. See Section 2.3.4, "JDBC Driver and Data Source Properties" for more information.

3.1.3 Field Security for Select

The JDBC driver performs field (column) level security based on information in the File/Field Level Security File (F9401) table.

When you set up Field Security, you should be aware that it uses the same mechanism as World Writer for defining secured data fields in the JD Edwards World software. You can access the File/Field Level Security - Inquiry program (P94011) by choosing Field Level Security on the World Writer Advanced Operations menu (G8231). The system maintains data values in the File/Field Level Security File (F9401) table. The JDBC driver queries this table for data related to a given user and files the SQL uses. See Work with Security in the JD Edwards World World Writer Guide for more information.

When you use an excluded column in an SQL statement the JDBC driver responds differently depending on the context. When you use the excluded column:

As a column in the SQL statement Select List, the ResultSet always returns NULL for the ResultSet column value.
In any other form, such as a Where Clause or Order By Clause, the World JDBC driver issues a parsing error and disallows the SQL statement.

3.1.4 File/Field Security for Insert/Update/Delete

The JDBC driver performs File/Field Security based on information in the File/Field Level Security File (F9401) and the Software Versions Repository (F9801). File/Field Level Security is required to allow the JDBC driver to perform insert, update and delete actions to files other than Batch Input Files (those files with a Function Use Code between 231 and 239 in the Software Versions Repository - F9801). You are allowed to insert/update/delete records in Batch Input Files by default.

To set up File/Field level security for a particular file

Navigate to the File/Field level Security maintenance program, Option 15 on the World Writer Advanced Operations Menu (G8231).
Add security records by user and file to allow insert/update/delete for any file which that user requires updating through the JDBC driver. To allow insert or delete, all fields in the file must be marked Y for Update. If the file will be updated only, only those fields which you will be updating need to be marked Y for Update.

Figure 3-1 Field Level Security screen

Description of "Figure 3-1 Field Level Security screen"

Figure 3-2 File/Field Level Security screen

Description of "Figure 3-2 File/Field Level Security screen"

3.1.5 Action Security

The JDBC driver performs Action Security based on information in the Software Version \Repository File (F9801), Action Code Security File (F0003) and the 98/JA User Defined Code. Action Security is required to allow the JDBC driver to perform insert, update and delete actions.

To set up Action Security for a particular Java application

Create UDC code 98/JA which defines the Java Program map UDC table. The purpose is to provide a cross-reference between a World SVR object name and the Java class names. The Code length must be 10, Second Line must be "Y" and Code Num must be "N".

Figure 3-3 User Defined Code Types screen

Description of "Figure 3-3 User Defined Code Types screen"
Create an SVR Entry to define an object name (Member ID) that represents the Java class name. This name may be anything you want, but Oracle recommends that you make it something that will relate to the Java application.

Figure 3-4 Software Versions Repository screen

Description of "Figure 3-4 Software Versions Repository screen"
Create a UDC Table entry in UDC 98/JA to map a Java class to its corresponding SVR entry. This entry will map the SVR object name to a specific Java class name. Enter the name you used for the SVR Member ID in the 10 Character Code. The Description and Description-2 fields are concatenated together to make up the Java class name. Remember that Java class names are case sensitive.

Figure 3-5 General User Defined Codes screen

Description of "Figure 3-5 General User Defined Codes screen"
Add an Action Code security record for the User and Java application in the Action Code Security file: Only the A, C and D Action Codes are used by the JDBC driver, for Insert, Update, and Delete respectively.

Figure 3-6 Action Code screen

Description of "Figure 3-6 Action Code screen"

3.2 World JDBC Driver and JD Edwards World Data-Specific Features

The JDBC driver includes the following data-specific features.

3.2.1 Metadata Files

These features are operational with all releases from A7.3 forward that JD Edwards World supports. These features affect any metadata that the JDBC driver extracts from the system.

The JDBC driver maintains release independence by using SQL to extract data from the following tables. This eliminates 99% of any compatibility issues with the data files. Each of these tables must exist in the user's environment for the driver to function properly.

Specification Type	Metadata Source Table	Release Dependencies
JD Edwards World User and Environment	User Profile F0092/F0093/F0094/F00944	None
Business Unit Security	F0001	None
Column Security	F9401 (prior to A93) F8202 (A93 and later)	None
Company Constants	F0010	None
Currency Codes	F0013	None
Data Item Master	F9200 F9201 or F9210 F9202 F9203	The JDBC driver determines which table to use by attempting to locate the F9210 table. If it locates this table in the library list then the JDBC driver assumes that the environment release is A8.1 or later and uses this table. If it cannot locate the F9210, then it assumes that F9201 is the active Data Item Master. Expect all dictionary tables to be in the same data library.
Payment Terms	F0014	None
Table Definitions	SYSTABLES and F9801	None
User Defined Codes	F0005	None
User Display Preference	F00921	None
Table Column Definitions	SYSCOLUMNS	None
Server	N/A	The JDBC driver extracts the server name from the connection data.

3.2.2 Environments and Specifications

The JDBC driver must be able to identify the user's current JD Edwards World environment. Any user login that can log into the JD Edwards World software system using their normal user interface, can also log using the JDBC driver. As long as the JD Edwards World environment contains the minimum required files from the metadata source table, a user should be able to connect using the JDBC driver. The JDBC driver's ability to identify a JD Edwards World environment follows the same logic that the JD Edwards World J98INIT and J98INITA programs use. The user can choose only one environment for a given database connection.

The JDBC driver determines the user from the connection object which is available after a login and creates the connection object. A jt400.jar API extracts the IBM user profile (USRPRF) from the IBM i. This allows access to all information for the current user. You can identify the JD Edwards World software instance by reviewing the user profile Initial Program Library of the connection. The JDBC driver examines the QJDF data area in this library to determine:

The library in positions 131-140 of the QJDF data area is the JD Edwards World Object library for the instance.
The library in positions 181-190 of the QJDF data area is the JD Edwards World Security library for the instance.
The JD Edwards World Environment data is in the JD Edwards World Security library.
The JDBC driver creates new objects in the he JD Edwards World Object library.

Within the JD Edwards World Security library tables are the valid environments the user has authority to use. The JDBC driver assumes that the following tables are in the library: F0092, F0093, F0094, and F00944. If any of these tables are missing from the library found at position 181 of the QJDF data area then the user cannot connect.

If the user's initial program is J98INIT, then the correct environment name is the same as the user login name. JD Edwards World software permits only one environment in this scenario.
If the user's initial program is J98INITA, then the JDBC driver can use any environment name for the user from the F0093 table.

The JDBC driver makes an environment choice for a connection via the driver property JDEWEnvironment. The JDBC driver identifies and constructs the environment choice. The connection library list is the concatenation of the system library list found in the IBM i QSYSLIBL system value and the libraries in the environment you choose. If the JD Edwards World Object library in the QJDF data area is not currently in the user library list, then the JDBC driver adds it at the bottom of the list. The JDBC driver applies the Library list to the current user connection by disconnecting the initial database connection and reconnecting it using the original URL with the properties in the original connection. It also changes the IBM JTOpen libraries property to that of the JD Edwards World environment. The JDBC driver extracts table specifications for all tables for all libraries in the library list. The JDBC driver performs this only once per library. If the JDBC driver extracts specifications for a library with the first user, then when the next user logs in, the JDBC driver already contains the table specification in the cache.

Note:

The SYSTABLES and SYSCOLUMNS database engine files support the driver. These files exist in the QSYS2 library and QSYS2 must exist in either the QSYSLIBL system value or in the JDEWEnvironment library list definition.

Table specification definitions for tables in the environment:

The JDBC driver restricts all client SQL queries to the IBM i to libraries and files in the JDEWEnvironment library list. The JDBC driver processes each SQL statement and verifies the tables you request against the user's environment library list.
The JDBC driver extracts table specifications for tables in the library list. The driver performs this only once per library.
- If you indicate a specific schema (library) for the table in the SQL statement, for example select * from JDFDTA.F0101, then the JDBC driver validates the schema against the environment library list. If the library does not exist in the user library list then the JDBC driver delivers an SQL Exception and rejects the SQL statement.
- If you do not provide a schema, for example select * from F0101, then the JDBC driver uses the first occurrence of the table in the library list.
- If the table does not exist in the user library list then the JDBC driver delivers an SQL Exception and rejects the SQL statement.

The JDBC driver identifies the exact instance of the table, extracts table and column definitions, and specifications from the SYSTABLES and SYSCOLUMNS database tables.