Wednesday, August 25, 2021

Changing user profiles using SQL

chgusrprf 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_ATTEMPTEDNO = Preview mode, YES = Update mode
  • CHANGE_SUCCESSFUL:  Null = change not attempted, NO = change failed, YES = change made
  • CHGUSRPRF_COMMANDCHGUSRPRF 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.

1 comment:

  1. Harold Adolfo Mendoza AvendaƱoAugust 25, 2021 at 9:19 AM

    Gracias excelente tema mago

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.