Wednesday, March 1, 2023

Easy way to validate if user has a certain group profile

This is another example of something I found by accident. Checking IBM's documentation I can find that this is found in IBM i releases 7.5 – 7.1, it might even be in earlier releases but IBM's online documentation only goes back to IBM i 7.1.

VERIFY_GROUP_FOR_USER is a SQL function that allows me to verify if the user of the current job is a member of any group profiles.

I could use the Display User Profile command, DSPUSRPRF, to display which groups and supplemental groups an user profile has. What is the fun in that when I can get the same information using SQL?

01  SELECT AUTHORIZATION_NAME AS "User",
02         GROUP_PROFILE_NAME AS "Group",
03         SUPPLEMENTAL_GROUP_LIST AS "Supplementals"
04    FROM QSYS2.USER_INFO_BASIC 
05   WHERE AUTHORIZATION_NAME = 'SIMON'

Lines 1 – 3: I want to have the User profile, AUTHORIZATION_NAME, Group profile, GROUP_PROFILE_NAME, and the Supplemental Group Profiles, SUPPLEMENTAL_GROUP_LIST in my results. I have given all of these columns new headings too.

Line 4: I am getting these columns from the USER_INFO_BASIC View.

Line 5: I only want the results for my user profile, SIMON.

The results are:

User        Group        Supplementals
----------  -----------  --------------------
SIMON       GROUP1       GROUP2    GROUP3

This shows that I have one Group Profile, GROUP1, and two Supplemental Groups, GROUP2 and GROUP3.

If I needed to check in a program whether I am a member of the group 'GROUP2', and I was to use the USER_INFO_BASIC View, the program's code could look like:

01  dcl-s Found ind inz ;

02  exec sql SELECT '1' INTO :Found
03             FROM QSYS2.USER_INFO_BASIC
04            WHERE AUTHORIZATION_NAME = CURRENT_USER
05              AND (GROUP_PROFILE_NAME = 'GROUP2'
06               OR SUPPLEMENTAL_GROUP_LIST LIKE '%GROUP2 %') ;

07  dsply ('Group found = ' + Found) ;

Line 1: I have defined an indicator variable which will be used to contain the result of my "look up" using USER_INFO_BASIC.

Line 2: If the row I desired is found the value of '1' will be moved to the program variable, Found.

Line 4: I only want the result for the current user.

Lines 5 and 6: This part of the statement is enclosed in parentheses ( ( ) ). Which means that I can do an OR test without having the repeat the condition on line 4.

Line 5: The group profile I am checking for could be in the Group Profile Name column.

Line 6: Or the group profile could be in the Supplemental Groups column. This column has all of the Supplemental Groups concatenated together. To find the group I am looking for I do a LIKE, wildcard search. Notice that I am looking for the string 'GROUP2 ' (GROUP2 + space) I have done this just in case there is another group that starts with 'GROUP2', for example: 'GROUP22'.

Line 7: I use the RPG Display operation code to show the value contained within Found.

The result looks like:

DSPLY  Group found = 1

The VERIFY_GROUP_FOR_USER provides me with a simpler statement to use:

QSYS2.VERIFY_GROUP_FOR_USER(< user-profile > , < group-profile >)

The User Profile parameter can only contain one of three values:

  • CURRENT_USER
  • SESSION_USER
  • USER

The Group Profile is not validated to determine if this is a valid profile or not.

The function returns an integer result:

  • 0 The user is not a member of the group
  • 1 The user is a member of the group

I have tested the three user profile values to see what I get returned in 'Run SQL Scripts':

VALUES CURRENT_USER,SESSION_USER,USER

Which returned three rows of results:

0001
-------
SIMON
SIMON
SIMON

I even checked what they would be in a RPG program:

01  dcl-s Profile char(10) ;

02  exec sql SET :Profile = CURRENT_USER ;
03  dsply ('CURRENT_USER = ' + Profile) ;

04  exec sql SET :Profile = SESSION_USER ;
05  dsply ('SESSION_USER = ' + Profile) ;

06  exec sql SET :Profile = USER ;
07  dsply ('USER = ' + Profile) ;

Line 1: This variable will contain what is retrieved from the three different user values.

Lines 2, 4, and 6: Use the SQL SET to retrieve the value from the user values into the program variable, Profile.

Lines 3, 5, and 7: Use the RPG Display Operation to display the contents of Profile.

The results are the same:

DSPLY  CURRENT_USER = SIMON
DSPLY  SESSION_USER = SIMON
DSPLY  USER = SIMON

I have decided to use CURRENT_USER for no other reason than people are more familiar with that than the other two.

I created a program to check where the CURRENT_USER has a particular Group or Supplemental profile:

01  dcl-s Found ind inz ;

02  exec sql SET :Found = QSYS2.VERIFY_GROUP_FOR_USER(CURRENT_USER,'GROUP1') ;
03  dsply ('Group profile = ' + Found) ;

04  Found = *off ;
05  exec sql SET :Found = QSYS2.VERIFY_GROUP_FOR_USER(CURRENT_USER,'GROUP2') ;
06  dsply ('Supplemental group = ' + Found) ;

Line 1: Same variable as I defined in the program where I used USER_INFO_BASIC.

Line 2: Using the SQL SET to update the program variable Found with the value returned by the VERIFY_GROUP_FOR_USER. As we learned above GROUP1 is my Group Profile.

Line 3: I display the value of the indicator Found.

Line 4: Setting the indicator to ensure that the value returned by line 5 really is a positive return code.

Line 5: Here I am using VERIFY_GROUP_FOR_USER with one of my Supplemental groups.

line 6: Display the value in Found.

After compiling the above, and calling the program the following is shown:

DSPLY  Group profile = 1
DSPLY  Supplemental group = 1

As both of the results are positive it shows that I have both of these user profiles as Group Profiles in my own user profile.

A good practical example of how to use this is in IBM Documentation I have linked to below.

You have to agree that using VERIFY_GROUP_FOR_USER makes it a lot easier to check if the current user has a particular Group Profile, when compared to using USER_INFO_BASIC.

 

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

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

No comments:

Post a Comment

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.