The DB2 SYSIBM.USERNAMES encryption stored procedure - DSNLEUSR

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

SYSIBM.USERNAMES

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS
Stored Procedures

Software version:

8.0

Operating system(s):

z/OS

Reference #:

1208122

Modified date:

2011-09-22

Translate my page

Machine Translation

Content navigation