Wednesday, November 2, 2022

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).
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:


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:


Which tells me I do not have *SECADM authority.

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


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.

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.