Changing user profiles using SQL

I have to admit when I read that a SQL table function to change user profiles had been introduced in the latest Technology Refreshes for IBM i 7.4 and 7.3 I was skeptical as to whether it was something that I would find practical and useful. Having had some time to play with it I can now appreciate its usefulness.

But before I get to that, let me explain how the CHANGE_USER_PROFILE table function works.

The table function will only change certain information of a user profile. The parameters you can change are:

SELECT * FROM TABLE FUNCTION(SYSTOOLS.CHANGE_USER_PROFILE ( P_USER_NAME => user-name, P_PASSWORD => password, P_PASSWORD_EXPIRED => password-expired, P_STATUS => status, P_INITIAL_PROGRAM => initial-program, P_LIMIT_CAPABILITIES => limit-capabilities, P_TEXT => text, P_PASSWORD_EXPIRATION_INTERVAL => password-expiration-interval, P_JOB_DESCRIPTION => job-description, P_GROUP_PROFILE => group-profile, P_USER_EXPIRATION_DATE => user-expiration-date, P_USER_EXPIRATION_INTERVAL => user-expiration-interval , PREVIEW => preview) ) ;

While the parameter names do well to explain their purpose I thought it was worthwhile to list them all with the equivalent parameter from the Change User Profile command, CHGUSRPEF.

Parameter	Description	CHGUSRPRF parameter
P_USER_NAME	User profile that will be changed	USRPRF
P_PASSWORD	New password.	PASSWORD
P_PASSWORD_EXPIRED	Is profile expired?	PWDEXP
P_STATUS	Status of user profile	STATUS
P_INITIAL_PROGRAM	Initial program to call. Must be in the format: library/program	INLPGM
P_LIMIT_CAPABILITIES	Limit user's capabilities	LMTCPB
P_TEXT	User profile description	TEXT
P_PASSWORD_EXPIRATION_INTERVAL	Password expiration interval in days	PWDEXPITV
P_JOB_DESCRIPTION	Job description. Must be in the format: library/job-description	JOBD
P_GROUP_PROFILE	Group profile	GPRPRF
P_USER_EXPIRATION_DATE	Date user profile expires	USREXPDATE
P_USER_EXPIRATION_INTERVAL	User expiration interval in days	USREXPITV
PREVIEW	YES = Preview only, no change. NO = Make changes. Default is YES.

Default for all parameters, except for P_USER_NAME and PREVIEW, is *SAME.

Do note that CHANGE_USER_PROFILE table function is found in the library SYSTOOLS.

The following columns are returned:

• USER_NAME:  User profile being changed or previewed
• CHANGE_ATTEMPTED:  NO = Preview mode, YES = Update mode
• CHANGE_SUCCESSFUL:  Null = change not attempted, NO = change failed, YES = change made
• CHGUSRPRF_COMMAND:  CHGUSRPRF command string
• FAILURE_MESSAGE_ID:  Null = Preview mode or change successful, message id if failed
• FAILURE_MESSAGE_TEXT:  Null = Preview mode or change successful, message text if failed

I made a user profile I will be using in these examples: SIMON2. I want to disable the profile by changing its status to *DISABLED. I can use the SQL view USER_INFO to view the current status of the user profile:

01 SELECT AUTHORIZATION_NAME AS "User", 02 STATUS AS "Sts" 03 FROM USER_INFO 04 WHERE AUTHORIZATION_NAME = 'SIMON2' ;

The results show that the user profile is enabled:

User Sts ------ -------- SIMON2 *ENABLED

Let me preview my change to this profile to disable it:

01 SELECT * 02 FROM TABLE(SYSTOOLS.CHANGE_USER_PROFILE ( 03 P_USER_NAME => 'SIMON2', 04 P_STATUS => '*DISABLED', 05 PREVIEW => 'YES') 06 ) ;

Line 5: I know the default for the preview parameter is YES, but I always like to make sure everyone knows that this is not going to update anything.

The results, show that this change to the profile would have been successful.

CHANGE_ CHANGE_ USER_NAME ATTEMPTED SUCCESSFUL --------- --------- ---------- SIMON2 NO <NULL> CHGUSRPRF_COMMAND ----------------------------------------------- QSYS/CHGUSRPRF USRPRF(SIMON2) STATUS(*DISABLED) FAILURE_MESSAGE_ID FAILURE_MESSAGE ------------------ --------------- <NULL> <NULL>

This time I want to update the user profile.

01 SELECT * 02 FROM TABLE(SYSTOOLS.CHANGE_USER_PROFILE ( 03 P_USER_NAME => 'SIMON2', 04 P_STATUS => '*DISABLED', 05 PREVIEW => 'NO') 06 ) ;

Line 5: Preview is NO, so the change will be attempted.

CHANGE_ CHANGE_ USER_NAME ATTEMPTED SUCCESSFUL --------- --------- ---------- SIMON2 YES YES CHGUSRPRF_COMMAND ----------------------------------------------- QSYS/CHGUSRPRF USRPRF(SIMON2) STATUS(*DISABLED) FAILURE_MESSAGE_ID FAILURE_MESSAGE ------------------ --------------- <NULL> <NULL>

The return results say the change was completed successfully. But I am going to run that USER_INFO view statement again just to make sure:

01 SELECT AUTHORIZATION_NAME AS "User", 02 STATUS AS "Sts" 03 FROM USER_INFO 04 WHERE AUTHORIZATION_NAME = 'SIMON2' ; User Sts ------ --------- SIMON2 *DISABLED

And, yes, the SIMON2 profile was successfully disabled.

What happens if I try to disable a profile that does not exist? In this case I am using the user profile C?C?C?.

01 SELECT * 02 FROM TABLE(SYSTOOLS.CHANGE_USER_PROFILE ( 03 P_USER_NAME => 'C?C?C?', 04 P_STATUS => '*DISABLED', 05 PREVIEW => 'NO') 06 ) ;

A user profile cannot contain a question mark ( ? ) so I know this statement will fail.

CHANGE_ CHANGE_ USER_NAME ATTEMPTED SUCCESSFUL --------- --------- ---------- C?C?C? YES NO CHGUSRPRF_COMMAND ------------------------------------------------ QSYS/CHGUSRPRF USRPRF(C?C?C?) STATUS(*DISABLED) FAILURE_MESSAGE_ID FAILURE_MESSAGE ------------------ -------------------------------- CPD0020 Character '?' not valid follo...

Now I get to see a message id and its text in the last two columns returned by CHANGE_USER_PROFILE.

This is where I admit if I had to disable a single user profile I would not use this table function, in my opinion it is just easier to use the CHGUSRPRF.

Notice that I said single user profile. If I want to change multiple profiles then I would use this. For example, if I wanted to disable all user profiles that had not signed on for the last three months.

I can use the USER_INFO view again to identify all of those profiles:

01 SELECT AUTHORIZATION_NAME AS "USER", 02 STATUS, 03 DATE(LAST_USED_TIMESTAMP) AS LAST_DATE 04 FROM QSYS2.USER_INFO A 05 WHERE STATUS = '*ENABLED' 06 AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 3 MONTHS ;

This is a subset of the results returned.

USER STATUS LAST_DATE ---------- -------- ---------- G********* *ENABLED 2021-02-21 H********* *ENABLED 2021-03-06 O********* *ENABLED 2020-11-24 K********* *ENABLED 2020-07-06

I can add CHANGE_USER_PROFILE into that statement so that it will disable the profiles returned by USER_INFO.

01 SELECT A.AUTHORIZATION_NAME AS "USER", 02 A.STATUS, 03 DATE(A.LAST_USED_TIMESTAMP) AS LAST_DATE, 04 B.CHANGE_SUCCESSFUL AS SUCCESS, 05 B.FAILURE_MESSAGE_ID AS MSGID 06 FROM QSYS2.USER_INFO A, 07 LATERAL 08 (SELECT CHANGE_SUCCESSFUL,FAILURE_MESSAGE_ID 09 FROM TABLE(SYSTOOLS.CHANGE_USER_PROFILE( 10 P_USER_NAME => A.AUTHORIZATION_NAME, 11 P_STATUS => '*DISABLED', 12 PREVIEW => 'YES'))) B 13 WHERE A.STATUS = '*ENABLED' 14 AND A.LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 3 MONTHS ;

Lines 1 – 3: These columns are from USER_INFO view.

Lines 4 and 5: Columns from CHANGE_USER_PROFILE table function.

Line 7: The LATERAL allows me to join the results from the view to the table function.

Lines 8 – 12: This sub select is what does the changing of the user profiles.

Line 8: I want to return these two columns to the results from the view.

Line 12: In real life I cannot go and disable every user profile that fits the selection criteria. Therefore, I am going to preview this set of results.

Line 13 and 14: Only profiles that are enabled, and were last used over three months ago are included.

My results are:

USER STATUS LAST_DATE SUCCESS MSGID ---------- -------- ---------- ------- ------ G********* *ENABLED 2021-02-21 <NULL> <NULL> H********* *ENABLED 2021-03-06 <NULL> <NULL> O********* *ENABLED 2020-11-24 <NULL> <NULL> K********* *ENABLED 2020-07-06 <NULL> <NULL>

The SUCCESS and MSGID columns are null. If this was run without the preview I would see YES in the SUCCESS column and hopefully there would be no errors so the MSGID column's entries, which would remain null.

Using CHANGE_USER_PROFILE in a scenario like the one shown above will make my life so much easier, as is all done in one step.

 

You can learn more about the CHANGE_USER_PROFILE SQL table function from the IBM website here.

 

This article was written for IBM i 7.4 TR4, and will work for 7.3 TR10 too.