Pages

Wednesday, August 12, 2020

Using table function rather than view for object privilege information

object

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.

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.