DSNLEUSR stored procedure

The DSNLEUSR stored procedure is a sample stored procedure. Use this stored procedure to store encrypted values in the translated authorization ID (NEWAUTHID) and password fields of the SYSIBM.USERNAMES table.

Begin general-use programming interface information. You provide all the values for a SYSIBM.USERNAMES row as input to DSNLEUSR. DSNLEUSR encrypts the translated authorization ID and password values using 256-bit Advanced Encryption Standard (AES) encryption before it inserts the row into SYSIBM.USERNAMES.

Environment

DSNLEUSR has the following requirements:

DSNLEUSR runs in a WLM-established stored procedure address space.
z/OS® Integrated Cryptographic Service Facility (ICSF) must be installed, configured, and active. The services that ICSF calls that are used by this stored procedure are CSNBCKM and CSNBENC.

Authorization

To execute the CALL DSNLEUSR statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges:

The EXECUTE privilege on the package for DSNLEUSR
Ownership of the package
PACKADM authority for the package collection
SYSADM authority

The owner of the package or plan that contains the CALL statement must also have INSERT authority on SYSIBM.USERNAMES.

Syntax

The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:

Option descriptions

Type

Specifies the value that is to be inserted into the TYPE column of SYSIBM.USERNAMES.

This is an input parameter of type CHAR(1).

AuthID

Specifies the value that is to be inserted into the AUTHID column of SYSIBM.USERNAMES.

This is an input parameter of type VARCHAR(128). If you specify a null value, DSNLEUSR does not insert a value for AuthID .

LinkName

Specifies the value that is to be inserted into the LINKNAME column of SYSIBM.USERNAMES.

This is an input parameter of type CHAR(8). Although the LINKNAME field of SYSIBM.USERNAMES is VARCHAR(24), this value is restricted to a maximum of 8 bytes.

If you specify a null value, DSNLEUSR does not insert a value for LinkName .

NewAuthID

Specifies the value that is to be inserted into the NEWAUTHID column of SYSIBM.USERNAMES.

This is an input parameter of type VARCHAR(54). The NEWAUTHID field is type VARCHAR(54) to allow for expansion during encryption.

If you specify a null value, DSNLEUSR does not insert a value for NewAuthID.

Password

Specifies the value that is to be inserted into the PASSWORD column of SYSIBM.USERNAMES.

If the input value to Password represents a password, the password value is restricted to 100 or fewer bytes. This applies even if the PASSWORD column of SYSIBM.USERNAMES is VARCHAR(255).

If you specify a null value, DSNLEUSR does not insert a value for Password.

ReturnCode

The return code from DSNLEUSR execution. Possible values are:

0

DSNLEUSR executed successfully.

8

The request to encrypt the translated authorization ID or password failed. MsgArea contains the following fields:

An unformatted SQLCA that describes the error.
A string that contains a DSNL045I message with the ICSF return code, the ICSF reason code, and the ICSF function that failed. The string immediately follows the SQLCA field and does not begin with a length field.

12

The insert operation for the SYSIBM.USERNAMES row failed. MsgArea contains an SQLCA that describes the error.

16

DSNLEUSR terminated because the Db2 subsystem is not in new-function mode. MsgArea contains an SQLCA that describes the error.

This is an output parameter of type INTEGER.

MsgArea

Contains information about DSNLEUSR execution. The information that is returned is described in the ReturnCode description.

This is an output parameter of type VARCHAR(500).

Example

The following COBOL example shows variable declarations and an SQL CALL for inserting a row into SYSIBM.USERNAMES with an encrypted translated authorization ID and an encrypted password.

 WORKING-STORAGE SECTION.
⋮ 
***********************
* DSNLEUSR PARAMETERS *
***********************
 01  TYPE1                    PICTURE X(1).
 01  AUTHID.
     49   AUTHID-LN           PICTURE S9(4) COMP.
     49   AUTHID-DTA          PICTURE X(128).
 01  LINKNAME                 PICTURE X(8).
 01  NEWAUTHID.
     49   NEWAUTHID-LN        PICTURE S9(4) COMP.
     49   NEWAUTHID-DTA       PICTURE X(54).
 01  PASSWORD1.
     49 PASSWORD1-LN PICTURE S9(4) COMP.
     49 PASSWORD1-DTA PICTURE X(100).
 01  RETURNCODE               PICTURE S9(9) COMP VALUE +0.
 01  MSGAREA.
     49   MSGAREA-LN          PICTURE S9(4) COMP VALUE 500.
     49   MSGAREA-DTA         PICTURE X(500) VALUE SPACES.
*****************************************
* INDICATOR VARIABLES.                  *
*****************************************
 01  TYPE-IND                 PICTURE S9(4) COMP-4.
 01  AUTHID-IND               PICTURE S9(4) COMP-4.
 01  LINKNAME-IND             PICTURE S9(4) COMP-4.
 01  NEWAUTHID-IND            PICTURE S9(4) COMP-4.
 01  PASSWORD-IND             PICTURE S9(4) COMP-4.
 01  RETURNCODE-IND           PICTURE S9(4) COMP-4.
 01  MSGAREA-IND              PICTURE S9(4) COMP-4.
 PROCEDURE DIVISION.
? 
*********************************************************
* SET VALUES FOR DSNLEUSR INPUT PARAMETERS.             *
* THE SET OF INPUT VALUES REPRESENTS A ROW THAT         *
* DSNLEUSR INSERTS INTO SYSIBM.USERNAMES WITH           *
* ENCRYPTED NEWAUTHID AND PASSWORD VALUES.              *
*********************************************************
     MOVE 'O' TO TYPE1.
     MOVE 0 TO AUTHID-LN.
     MOVE SPACES TO AUTHID-DTA.
     MOVE 'SYEC1B  ' TO LINKNAME.
     MOVE 4 TO NEWAUTHID-LN.
     MOVE 'MYID' TO NEWAUTHID-DTA.
     MOVE 6 TO PASSWORD1-LN.
     MOVE 'MYPASS' TO PASSWORD1-DTA.
*****************
* CALL DSNLEUSR *
*****************
     EXEC SQL
      CALL SYSPROC.DSNLEUSR
      (:TYPE1          :TYPE-IND,
       :AUTHID         :AUTHID-IND,
       :LINKNAME       :LINKNAME-IND,
       :NEWAUTHID      :NEWAUTHID-IND,
       :PASSWORD1      :PASSWORD-IND,
       :RETURNCODE     :RETURNCODE-IND,
       :MSGAREA        :MSGAREA-IND)
     END-EXEC.

Output

If DSNLEUSR executes successfully, it inserts a row into SYSIBM.USERNAMES with encrypted values for the NEWAUTHID and PASSWORD columns and returns 0 for the ReturnCode parameter value. If DSNLEUSR does not execute successfully, it returns a non-zero value for the ReturnCode value and additional diagnostic information for the MsgArea parameter value. End general-use programming interface information.