Using table function rather than view for object privilege information

One of the new arrivals with the latest rounds of Technology Refreshes was a table function to retrieve the authority for a particular object. "Wait!" I hear you regular readers say, "Wasn't there already a SQL View added to show that information?" And you are correct in IBM i 7.3 TR2 the OBJECT_PRIVILEGES View was released.

With a Table function I pass it a set of parameters and only the results for those values are returned. If I use a View then the entire View has to be searched for the rows matching the selection criteria. By using a Table function, rather than a View, I can get the information I want quicker. I will demonstrate this in the examples below.

The OBJECT_PRIVILEGES Table function and View have the same columns. The only annoying difference is the name of the authorized user profile in the View is AUTHORIZATION_NAME, while in the Table function it is AUTHORIZATION_USER.

In these example I am only interested in determining if user profile is authorized to use a file, TESTFILE. The profiles authority is held in the column OBJECT_AUTHORITY. Therefore, if I want to see who is authorized to use the file TESTFILE in the library MYLIB I would need to pass the Table function the following:

1. Library name
2. Object name
3. Object type

My SQL Select for the Table function to return the user and authority is:

01 SELECT AUTHORIZATION_USER,OBJECT_AUTHORITY 02 FROM TABLE(QSYS2.OBJECT_PRIVILEGES('MYLIB','TESTFILE','*FILE'))

Which returns the following results in 430ms:

AUTHORIZATION_USER OBJECT_AUTHORITY ------------------ ---------------- *PUBLIC *EXCLUDE SIMON *ALL

As you can see my profile, SIMON, has all authority to the object, while everyone else, *PUBLIC, is excluded from it.

Let me do the same with the OBJECT_PRIVILEGES View:

01 SELECT AUTHORIZATION_NAME,OBJECT_AUTHORITY 02 FROM QSYS2.OBJECT_PRIVILEGES 03 WHERE SYSTEM_OBJECT_SCHEMA = 'MYLIB' 04 AND SYSTEM_OBJECT_NAME = 'TESTFILE' 05 AND OBJECT_TYPE = '*FILE'

The results returned are identical, and they took 520ms. 90ms in this case may not seem like much, but I am using an IBM i which has very little activity on it. I would expect the difference to be greater on a well utilized partition running a fully functional ERP application and people using it.

With this very simple example with no group profiles or authorization lists I could create a program to check if the current user profile is authorized to use TESTFILE:

01 **free 02 dcl-s Authority char(10) ; 02 dcl-s Who char(10) ; 03 exec sql WITH T1 AS 04 (SELECT AUTHORIZATION_USER, 05 OBJECT_AUTHORITY 06 FROM TABLE(QSYS2.OBJECT_PRIVILEGES('MYLIB', 07 'TESTFILE', 08 '*FILE')) 09 WHERE AUTHORIZATION_USER IN (CURRENT_USER,'*PUBLIC')) 10 SELECT OBJECT_AUTHORITY,AUTHORIZATION_USER 11 INTO :Authority,:Who 12 FROM T1 13 ORDER BY AUTHORIZATION_USER DESC 14 FETCH FIRST ROW ONLY ; 15 dsply ('Stmt 1 = ' + %trimr(Authority) + ' (' + %trimr(Who) + ')') ; 16 clear Authority ; 17 exec sql WITH T1 AS 18 (SELECT AUTHORIZATION_USER, 19 OBJECT_AUTHORITY 20 FROM TABLE(QSYS2.OBJECT_PRIVILEGES('MYLIB', 21 'TESTFILE', 22 '*FILE')) 23 WHERE AUTHORIZATION_USER IN ('NOBODY','*PUBLIC')) 24 SELECT OBJECT_AUTHORITY,AUTHORIZATION_USER 25 INTO :Authority,:Who 26 FROM T1 27 ORDER BY AUTHORIZATION_USER DESC 28 FETCH FIRST ROW ONLY ; 29 dsply ('Stmt 2 = ' + %trimr(Authority) + ' (' + %trimr(Who) + ')') ; 30 *inlr = *on ;

Line 1: My code just has to be free format RPG.

Lines 2 and 3: Definitions for the variables I will be using. The first for the authority and the second for the user profile.

Lines 3 – 14: A common table expression that will build a table in memory, and then use it to return the information I want.

Lines 3 – 9: First part of the table expression. All table expressions start with the word WITH. Following the WITH is the name of the temporary table I am building, T1. Within the parentheses is the Select that uses the OBJECT_PRIVILEGES table function to return the results for TESTFILE. I am only selecting the current user profile, CURRENT_USER and *PUBLIC. The results are placed in the temporary table T1.

Lines 10 – 14: This part of the common table expression uses the data in the temporary table T1 to determine if I am authorized to TESTFILE. By sorting the user profile in descending order will mean that the current user's profile comes first, and then *PUBLIC. The FETCH FIRST ROW ONLY ensures that only one row is fetched. If the user is authorized to the file their profile is retrieved, if they are not then *PUBLIC is retrieved.

Line 15: The results returned are shown using RPG's display operation code, DSPLY.

Line 16: I am clearing the variable Authority just to make sure that you can see that there is no "carry over" from the previous SQL statement.

Lines 17 – 28: Same common table expression before, except I only want the results from the Table function for the profiles NOBODY and *PUBLIC. The temporary table will only contain one row as there will not be a row for NOBODY. Therefore, when the second part of the statement is executed only the results for *PUBLIC are retrieved.

Line 29: Display the results from the second common table expression.

After compiling, when I run this program these are the results shown to me:

DSPLY Stmt 1 = *ALL (SIMON) DSPLY Stmt 2 = *EXCLUDE (*PUBLIC)

Will I use this Table function? Yes, as I can get the information I need quicker than the View I will be replacing some of the logic I have in some programs where I use the View.

For better example of checking authority, including group profiles see here.

 

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

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.