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.