Fix Readme
Abstract
To better protect the authentication information stored in the SYSIBM.USERNAMES table, a new DB2-supplied stored procedure, SYSPROC.DSNLEUSR is introduced in Version 8. This stored procedure stores the authorization ID and password in encrypted form in the SYSIBM.USERNAMES table. After this information is inserted, no external way to decrypt the encrypted authentication information exists. Encrypting the authorization ID and password protects against unauthorized access to them and to the backup copies outside of DB2®.
Content
To better protect the authentication information stored in the SYSIBM.USERNAMES table, a new DB2-supplied stored procedure, SYSPROC.DSNLEUSR is introduced in Version 8. This stored procedure stores the authorization ID and password in encrypted form in the SYSIBM.USERNAMES table. After this information is inserted, no external way to decrypt the encrypted authentication information exists. Encrypting the authorization ID and password protects against unauthorized access to them and to the backup copies outside of DB2.
The values to be inserted in the SYSIBM.USERNAMES table are passed as parameters to the stored procedure, SYSPROC.DSNLEUSR. The stored procedure encrypts the NEWAUTHID and PASSWORD values and inserts the row in the SYSIBM.USERNAMES table. During the remote connection authentication process, DB2 decrypts the NEWAUTHID and PASSWORD values if they are encrypted in the SYSIBM.USERNAMES table before sending them to the remote server.
This function is available only in new-function mode. This function is supported with SNA and TCP/IP connections. z/OS® Integrated Cryptographic Services Facility (ICSF) is used for the encryption and decryption functions. ICSF needs to be installed and configured properly to use this function.
This stored procedure encrypts the authorization ID and password only in the DB2 catalog. For sending encrypted authorization ID and password across the network, encryption security options should be specified in the SYSIBM.IPNAMES table.
The row inserted in SYSIBM.USERNAMES table by calling DSNLEUSR stored procedure is deleted by issuing a DELETE statement on the row.
DSNLEUSR runs in a WLM-established stored procedure address space.
You do not need to recycle DDF if DSNLEUSR is called to insert a row in SYSIBM.USERNAMES table.
Syntax diagram that shows the SQL CALL statement for invoking DSNLEUSR
DSNLEUSR option description:
The input parameters to the stored procedure represent the columns in SYSIBM.USERNAMES table. Refer to the SYSIBM.USERNAMES table for description of the input parameters.
| Type | Specifies the 'Type' column of SYSIBM.USERNAMES table. Type is an input parameter of type CHAR(1). |
| Authid | Specifies the 'Authid' column of SYSIBM.USERNAMES table. Authid is an input parameter of type VARCHAR(128). |
| Linkname | Specifies the 'Linkname' column of SYSIBM.USERNAMES table. Linkname is an input parameter of type CHAR(8). |
| Newauthid | Specifies the 'Newauthid' column of SYSIBM.USERNAMES table. Newauthid is an input parameter of type VARCHAR(54). Newauthid is restricted to VARCHAR(54) to allow for expansion during encryption. |
| Password | Specifies the 'Password' column of SYSIBM.USERNAMES table. Password is an input parameter of type CHAR(8). |
| ReturnCode | Return code from the stored procedure. Possible values are: 0 The call completed successfully. 8 The request to encrypt the newauthid, password columns failed. MsgArea contains the SQLCA that describes the error, followed by DSNL045I message that contains the ICSF return code, ICSF reason code, and ICSF function that failed. 12 The request to insert into SYSIBM.USERNAMES table failed. MsgArea contains the SQLCA that describes the error. 16 The system is not in New Function Mode. The request to call the stored procedure failed. MsgArea contains the SQLCA that describes the error. ReturnCode is an output parameter of type INTEGER. |
| MsgArea | Contains SQLCA area and messages if an error occurs during stored procedure execution. The first message in this area is the SQLCA area. If encryption fails, DSNL045I message will follow the first message. MsgArea is an output parameter of type VARCHAR(500). |
DSNLEUSR output:
DSNLEUSR places the return code from DSNLEUSR execution in the ReturnCode parameter. If the value of the return code is non-zero, DSNLEUSR puts the SQLCA area, followed by DSNL045I message for encryption failures in the MsgArea parameter.
DSNLEUSR CREATE:
CREATE PROCEDURE SYSPROC.DSNLEUSR ( IN TYPE CHAR(1) , IN AUTHID VARCHAR(128) , IN LINKNAME CHAR(8) , IN NEWAUTHID VARCHAR(54) , IN PASSWORD CHAR(8) , OUT RETURNCODE INTEGER , OUT MSGAREA VARCHAR(500)) PARAMETER CCSID EBCDIC RESULT SETS 0 LANGUAGE C EXTERNAL NAME DSNLEUSR MODIFIES SQL DATA COLLID DSNLEUSR WLM ENVIRONMENT WLMENV PARAMETER STYLE GENERAL STAY RESIDENT YES PROGRAM TYPE SUB;
Example of DSNLEUSR invocation in PL/I:
/*****************************************/ /* DSNLEUSR PARAMETERS */ /*****************************************/ DECLARE TYPE CHAR(1); DECLARE AUTHID CHAR(128) VARYING; DECLARE LINK_NAME CHAR(8); DECLARE NEW_AUTHID CHAR(54) VARYING; DECLARE PASSWORD CHAR(8); DECLARE RETURN_CODE BIN FIXED(31); DECLARE MSG_AREA CHAR(500) VARYING; TYPE = 'O'; AUTHID = ' '; LINK_NAME = 'SYEC1B'; NEW_AUTHID = 'SYSADM'; PASSWORD = 'SYSADM'; /** CALL DSNLEUSR **/ EXEC SQL CALL SYSPROC.DSNLEUSR( :TYPE, :AUTHID, :LINK_NAME, :NEW_AUTHID, :PASSWORD, :RETURN_CODE, :MSG_AREA);
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21208122