CHANGE_USER_PROFILE table function

The CHANGE_USER_PROFILE table function changes a subset of user profile attributes.

For a detailed description of the parameters and their values, refer to CHGUSRPRF CL command.

Authorization: See Note below.

The schema is SYSTOOLS.

user-name

A character string containing the name of the user profile whose values are to be changed.

password

A character string containing a new password value for the user profile. This is the PASSWORD parameter. The default is *SAME.

password-expired

A character string that specifies whether the password for this user is set to expired. This is the PWDEXP parameter. The default is *SAME.

status

A character string that specifies the status of the user profile. This is the STATUS parameter. The default is *SAME.

initial-program

A character string that specifies the initial program to call. This is the INLPGM parameter. The default is *SAME.

limit-capabilities

A character string that specifies the capabilities for a user. This is the LMTCPB parameter. The default is *SAME.

text

A character string that specifies the descriptive text for the user profile. This is the TEXT parameter. The default is *SAME.

password-expiration-interval

A character string that specifies the password expiration interval, in days. This is the PWDEXPITV parameter. The default is *SAME.

job-description

A character string that specifies the job description associated with this user profile This is the JOBD parameter. The default is *SAME.

group-profile

A character string that specifies the group profile associated with this user profile. This is the GRPPRF parameter. The default is *SAME.

user-expiration-date

A character string that specifies the date when the user profile expires and is automatically disabled. This is the USREXPDATE parameter. The default is *SAME.

user-expiration-interval

An integer value that specifies the expiration interval, in days, before the user profile is automatically disabled. This is the USREXPITV parameter. The default is NULL, meaning the value will not be changed.

special-authority-action

A character string that specifies the action that applies to the special-authority parameter. The default is *SAME. End of change

*ADD: The special authority should be added to the user profile.
*REMOVE: The special authority should be removed from the user profile.

special-authority

A character string that specifies a single special authority value to be added to or removed from the user profile when the special-authority-action parameter has a value or *ADD or *REMOVE. This is the SPCAUT parameter. The default is NULL, meaning the value is not changed. End of change

preview

A character string that indicates whether the table function should execute the CHGUSRPRF command that has been constructed based upon the input parameters or whether only a preview of the potential action should be shown.

NO: The table function should change the user profiles
YES: The table function should return a preview of the changes. This is the default.

The result of the function is a table containing a single row with the details about the user profile change. The columns of the result table are described in the following table. The result columns are nullable.

Table 1. CHANGE_USER_PROFILE table function
Column Name	Data Type	Description
USER_NAME	VARCHAR(10)	The user profile being changed or previewed.
CHANGE_ATTEMPTED	VARCHAR(3)	Indicates whether the change was attempted. NO The change was not attempted YES The change was attempted
CHANGE_SUCCESSFUL	VARCHAR(3)	Indicates whether the change was successful. NO The change was not successful YES The change was successful Contains the null value if CHANGE_ATTEMPTED is NO.
CHGUSRPRF_COMMAND	VARCHAR(1000)	The CHGUSRPRF command string.
FAILURE_MESSAGE_ID	CHAR(7)	The message ID. Contains the null value if CHANGE_ATTEMPTED is NO or CHANGE_SUCCESSFUL is YES.
FAILURE_MESSAGE_TEXT	VARGRAPHIC(1024) CCSID 1200	The text of the message. Contains the null value if CHANGE_ATTEMPTED is NO or CHANGE_SUCCESSFUL is YES.

Note

This function is provided in the SYSTOOLS schema as a helper function to manage user profiles. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Example

Disable all enabled user profiles that have no password set.

Preview the list of profiles that will be affected.

SELECT * FROM QSYS2.USER_INFO, 
              TABLE(SYSTOOLS.CHANGE_USER_PROFILE(
                                                 P_USER_NAME  => AUTHORIZATION_NAME, 
                                                 P_STATUS     => '*DISABLED',
                                                 PREVIEW      => 'YES'))
  WHERE STATUS = '*ENABLED' AND 
        NO_PASSWORD_INDICATOR = 'YES';

Build and execute the CHGUSRPRF commands..

SELECT * FROM QSYS2.USER_INFO, 
              TABLE(SYSTOOLS.CHANGE_USER_PROFILE(
                                                 P_USER_NAME  => AUTHORIZATION_NAME, 
                                                 P_STATUS     => '*DISABLED',
                                                 PREVIEW      => 'NO'))
  WHERE STATUS = '*ENABLED' AND 
        NO_PASSWORD_INDICATOR = 'YES';