Checking my authority using a SQL Scalar Function

There are times I discover little things in IBM i that I wish that had been available before. An example in the new SQL enhancements that came as part of IBM i 7.5 and 7.4 TR6 release and refresh. It is a SQL Scalar Function that allows me to check the user's special authority, as a member group profile, or acquired by adopted authority.

I have written many programs in the past where the user needed a particular special authority, for example *SECADM, and if they do not have authority when they try to execute the command it errors. The snippet below, from a RPG program, is an example of this:

09 Command = 'CHGUSRPRF USRPRF(' + %trimr(User) + ') JOBD(FINLIB/FINJOBD)' ; 10 monitor ; 11 QCMDEXC(Command : %len(%trimr(Command)) ) ; 12 on-error ; 13 dsply 'You are not authorized this command' ; 14 endmon ;

If I do not have *SECADM authority the call to QCDMEXC fails:

> CALL PGM(PGM1) The call to *LIBL/QCMDEXC ended in error (C G D F). C DSPLY You are not authorized this command

As I put the call to the QCMDEXC API within a Monitor group the program does not "hard error".

IMHO it makes more sense to check that I have the necessary authority before I execute any command. Fortunately the new Scalar Function gives me that functionality.

SQL_CHECK_SPECIAL_AUTHORITY has just one parameter, the special authority you want to check that the user has. For example:

VALUES QSYS2.SQL_CHECK_SPECIAL_AUTHORITY(SPECIAL_AUTHORITY => '*SECADM')

It returns a single character return code that can either be:

• 0:  User is does not have this special authority
• 1:  User has this special authority

The above SQL statement returns the following:

00001 ----- 0

Which tells me I do not have *SECADM authority.

The Scalar Function can also be used without the parameter name:

VALUES QSYS2.SQL_CHECK_SPECIAL_AUTHORITY('*SECADM')

I can use this in a RPG program to validate the user's special authority:

01 **free 02 dcl-s SpecialAuthority char(10) ; 03 dcl-s Authorized char(1) ; 04 SpecialAuthority = '*JOBCTL' ; 05 exec sql SET :Authorized = QSYS2.SQL_CHECK_SPECIAL_AUTHORITY(:SpecialAuthority) ; 06 if (Authorized = '0') ; 07 dsply ('You do not have the necessary authority: ' + SpecialAuthority) ; 08 elseif (Authorized = '1') ; 09 dsply ('You have the necessary authority: ' + SpecialAuthority) ; 10 else ; 11 dsply 'Special authority check failed' ; 12 endif ; 13 *inlr = *on

Line 4: I am going to check if I have *JOBCTL authority.

Line 5: I am using the SQL SET statement to set the value returned by SQL_CHECK_SPECIAL_AUTHORITY into the RPG variable Authorized, which has a colon ( : ) before it so that SQL knows it is a RPG variable.

Lines 6 – 12: Depending upon the returned result I am using the Display operation code, DSPLY, to show a message as to whether I have the necessary authority.

I do not have *JOBCTL authority on this partition, therefore, when I call this program the following is displayed:

DSPLY You do not have the necessary authority: *JOBCTL

This is a great little Scalar Function I will be using in my "system" programs.

 

You can learn more about the SQL_CHECK_SPECIAL_AUTHORITY SQL Scalar Function from the IBM website here.

 

This article was written for IBM i 7.5 and 7.4 TR6.