Finding out if I am authorized to a function usage identifier

Function Usage Identifiers are a way to be authorized to a particular function, without having the authority changed on my user profile. I have found it is a lot easier to persuade the sys admins to grant me authority to the Function Usage Identifier for something, than it is to have my user profile changed.

In this post I am going to show ways you can find the Function Usage Identifiers, see which ones someone is authorized to, and finally have a way to check in a program whether or not you are authorized to one of the functions. Fortunately all of this can be achieved simply using SQL. What I will be using are:

• FUNCTION_INFO View:  This returns a list of all Function Usage Identifiers
• FUNCTION_USAGE View:  This will return a list off all the functions that users are authorized to
• SQL_CHECK_FUNCTION_USAGE scalar function:  Returns to you if you are authorized a function or not

 

FUNCTION_INFO View:  This returns a list of all Function Usage Identifiers

This was introduced in IBM i 7.2, but I do not know if this View was introduced as part of the base release or in a subsequent Technology Refresh, TR.

The only other ways I know to display this information is with the Work With Function Usage command, WRKFCNUSG, or the Display Function Usage command, DSPFCNUSG, neither of which allows me to create an output file. I also have to "drill in" to each function to see its details.

The FUNCTION_INFO View is found in the QSYS2. library, and returns to me everything I could want to know about the functions. With a View like this I always recommend you look at all of the returned columns to determine which ones you find interesting or useful.

SELECT * FROM QSYS2.FUNCTION_INFO ;

In these examples I am only interested in a few of the columns returned in the results:

01 SELECT FUNCTION_ID,FUNCTION_CATEGORY,FUNCTION_TYPE, 02 FUNCTION_DESCRIPTION_MESSAGE_TEXT, 03 DEFAULT_USAGE 04 FROM QSYS2.FUNCTION_INFO 05 ORDER BY 1 ;

The columns I am interested in are:

• FUNCTION_ID:  Identifier of the function
• FUNCTION_CATEGORY:  All of the functions I am interested in are either '1-CLIENT', locally managed by the client function of the IBM i Navigator, or '3-HOST', function is on the host. To find what the other possible options are click on the link to the documentation for this View at the bottom of this post
• FUNCTION_TYPE:  Type of function
• FUNCTION_DESCRIPTION_MESSAGE_TEXT:  The function's description
• DEFAULT_USAGE:  The function's default for usage

Line 5: I am sorting the results by the first column, the function identifier.

I am only going to show the first five results as there are too many to show them all here:

FUNCTION_ FUNCTION_ FUNCTION DESCRIPTION_ FUNCTION FUNCTION_ID CATEGORY _TYPE MESSAGE_TEXT _USAGE -------------------------- --------- ------------- --------------- -------- QIBM_ACCESS_ALLOBJ_JOBLOG 3 - HOST ADMINISTRABLE If a user ha... DENIED QIBM_ALLOBJ 3 - HOST GROUP Group for us... <NULL> QIBM_ALLOBJ_TRACE_ANY_USER 3 - HOST ADMINISTRABLE Trace any us... DENIED QIBM_BASE_OPERATING_SYSTEM 3 - HOST PRODUCT IBM i <NULL> QIBM_DB 3 - HOST GROUP Database Fun... <NULL>

 

FUNCTION_USAGE View:  This will return a list off all the functions that users are authorized to

This is another View that was introduced to IBM i 7.2, again I do not know if it was part of the base release or was part of a Technology Refresh.

As I now know all the functions, now I want to know who is authorized to a particular function or which ones am I authorized to. This is where the FUNCTION_USAGE comes into play. It returns the following columns:

• FUNCTION_ID:  Function identifier
• USER_NAME  User profile
• USAGE:  Whether the user is authorized to the function
• USER_TYPE:  This can be either 'USER' or 'GROUP'

In the first example I want to know which functions I am authorized to. Here I would use the following statement:

01 SELECT * FROM QSYS2.FUNCTION_USAGE 02 WHERE USER_NAME = 'SIMON' 03 ORDER BY 1 ;

Line 1: I want all the View's columns in my results. And the View is found in the QSYS2 library.

Line 2: I only want to see the results for my user profile.

Line 3: And I want to sort the results by the first column, function id.

The results are:

FUNCTION_ID USER_NAME USAGE USER_TYPE -------------- --------- ------- --------- QIBM_DB_SQLADM SIMON ALLOWED USER

I can also use this View to find which user profiles have been authorized to a function. In this example I want to find which user profiles have been allowed to use the QIBM_QSY_SYSTEM_CERT_STORE function.

01 SELECT * FROM QSYS2.FUNCTION_USAGE 02 WHERE FUNCTION_ID = 'QIBM_QSY_SYSTEM_CERT_STORE' 03 ORDER BY 2 ;

Line 2: I only want to select rows where the function is 'QIBM_QSY_SYSTEM_CERT_STORE'.

Line 3: I am sorting the results by the second column, which is the user profile.

The results are:

FUNCTION_ID USER_NAME USAGE USER_TYPE -------------------------- --------- ------- --------- QIBM_QSY_SYSTEM_CERT_STORE QBRMS ALLOWED USER QIBM_QSY_SYSTEM_CERT_STORE QDIRSRV ALLOWED USER QIBM_QSY_SYSTEM_CERT_STORE QOBJC ALLOWED USER QIBM_QSY_SYSTEM_CERT_STORE QTCP ALLOWED USER QIBM_QSY_SYSTEM_CERT_STORE QYPSJSVR ALLOWED USER

 

SQL_CHECK_FUNCTION_USAGE scalar function:  Returns to you if you are authorized a function or not

The SQL_CHECK_FUNCTION_USAGE scalar function was introduced in the release notes for IBM i 7.5 TR1 and 7.4 TR7. I also find it in the documentation for IBM i 7.3. Could this have been part of the secret TR for 7.3?

This scalar function's purpose is to return a return code to tell me if I am authorized to a particular function. Yes, I can only use this to check for the current job. The codes returned are:

• 0 = denied
• 1 = allowed

If I am using ACS's Run SQL Scripts I can use the VALUES statement with the SQL_CHECK_FUNCTION_USAGE scalar function:

VALUES QSYS2.SQL_CHECK_FUNCTION_USAGE('QIBM_DB_SQLADM') ;

Which returns:

00001 ------ 1

This means that I am allowed to use the QIBM_DB_SQLADM function.

Let me try another function:

VALUES QSYS2.SQL_CHECK_FUNCTION_USAGE('QIBM_QYPS_MGTCTRL_SUPER_USER') ;

This returns:

00001 ------ 0

Which means I am not authorized to the QIBM_QYPS_MGTCTRL_SUPER_USER.

How could I use this in a RPG program? This is an example of how I could use it:

01 **free 02 dcl-s Function varchar(30) ; 03 dcl-s Result int(5) ; 04 Function = 'QIBM_DB_SQLADM' ; 05 exec sql SELECT QSYS2.SQL_CHECK_FUNCTION_USAGE(:Function) 06 INTO :Result 07 FROM SYSIBM.SYSDUMMY1 ; 08 dsply (Function + ' = ' + %char(Result)) ; 09 *inlr = *on ;

Line 1: My RPG is always totally free.

Lines 2 and 3: Here I am declaring the two variables I will be using in this program.

Line 4: I am placing the name of the function I am going to test in the variable Function.

Lines 5 – 7: I am using a Select statement, rather than the Values I used before. The value returned by the scalar function is placed in the Result variable.

Line 8: I am using the Display operation code to show the value returned.

The value shown is:

DSPLY QIBM_DB_SQLADM = 1

This shows that I am allowed to use QIBM_DB_SQLADM function.

I am going to change the function I am testing:

04 Function = 'QIBM_QYPS_MGTCTRL_SUPER_USER' ;

When I call the program the result displayed is:

DSPLY QIBM_QYPS_MGTCTRL_SUPER_USER = 0

Which means I have been denied using QIBM_QYPS_MGTCTRL_SUPER_USER.

 

These Views and scalar function certainly make my life a lot easier to discover what functions are available and who is authorized to use them.

 

You can learn more about this from the IBM website:

• FUNCTIOON_INFO SQL View
• FUNCTION_USAGE SQL View
• SQL_CHECK_FUNCTION_USAGE scalar function

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.