Viewing object authority using a SQL view

One of the new SQL Views added with the latest Technical Refreshes for IBM i, 7.3 TR2 and 7.2 TR6, is going to be very useful to me. Once a year, at audit time, I am asked to produce a list of all objects within the production library list that are not adequately secured. With the new Object Privileges SQL view I can generate all of the information I need in minutes, using a few SQL statements. This is going to save me a lot of time and effort.

The Object Privileges view, OBJECT_PRIVILEGES, like many of the new Views is to be found in the library QSYS2. The information it contains is the same as the information I can generate using in the Display Object Authority command, DSPOBJAUT. Rather than list all of the columns I will list them when I use them in various SQL statements. If you want to see a full list of all the columns visit the link I have provided a link to the IBM documentation for this View at the bottom of this post.

Let me start with the Display Object Authority command for my example file, TESTFILE.

DSPOBJAUT OBJ(MYLIB/TESTFILE) OBJTYPE(*FILE)

Which shows me:

Display Object Authority Object . . . . . : TESTFILE Owner . . . . . : QPGMR Library . . . : MYLIB Primary group . : *NONE Object type . . : *FILE ASP device . . . : *SYSBAS Object secured by authorization list . . . . . . : *NONE Object User Group Authority *PUBLIC *CHANGE *GROUP QPGMR *ALL

To display the equivalent from the new View I would just use the following Select statement.

01 SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT, 02 OBJTYPE,USER_NAME,OBJ_AUTH,OWNER 03 FROM QSYS2.OBJECT_PRIVILEGES 04 WHERE SYS_DNAME = 'MYLIB' 05 AND SYS_ONAME = 'TESTFILE'

In these examples I am going to be using the short, or system, names for the columns. In this statement I will be displaying the following columns:

1. SYS_DNAME - The library name the object is contained within. I am renaming this column to "LIBRARY" so it will match the DSPOBJAUT display.
2. SYS_ONAME - Object name. I am renaming this column to "OBJECT".
3. OBJTYPE - Type of object.
4. USER_NAME - Name of the authorized user, equivalent of the user column on the DSPOBJAUT display.
5. OBJ_AUTH - The authority the given user has to this object.
6. OWNER - The object owner's user profile.

Line 3: I am getting these columns from the Object Privileges View.

Lines 4 and 5: Just retrieving the information for my example object.

The results generated matches the DSPOBJAUT command initial screen.

LIBRARY OBJECT OBJTYPE USER_NAME OBJ_AUTH OWNER MYLIB TESTFILE *FILE *PUBLIC *CHANGE QPGMR MYLIB TESTFILE *FILE QPGMR *ALL QPGMR

On the DSPOBJAUT screen there is a function key to display the detailed object authorities:

F11=Display detail object authorities

When F11 is pressed I now see:

Object ----------Object----------- User Group Authority Opr Mgt Exist Alter Ref *PUBLIC *CHANGE X *GROUP QPGMR *ALL X X X X X

To display the same information from the SQL View I just need to add some columns, shown on lines 3 and 4:

01 SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT, 02 OBJTYPE,USER_NAME,OBJ_AUTH, 03 OBJOPER AS OPR,OBJMGT AS MGT,OBJEXIST AS EXIST, 04 OBJALTER AS ALTER,OBJREF AS REF 05 FROM QSYS2.OBJECT_PRIVILEGES 06 WHERE SYS_DNAME = 'MYLIB' 07 AND SYS_ONAME = 'TESTFILE'

1. OBJOPER - Object operational authority, I am renaming all of these columns to match the descriptions on the DSPOBJAUT screen.
2. OBJMGT - Object management authority.
3. OBJEXIST - Object existence authority.
4. OBJALTER - Object alter authority.
5. OBJREF - Object reference authority.

My results look just look like the DSPOBJAUT screen:

LIBRARY OBJECT OBJTYPE USER_NAME OBJ_AUTH MYLIB TESTFILE *FILE *PUBLIC *CHANGE MYLIB TESTFILE *FILE QPGMR *ALL OPR MGT EXIST ALTER REF YES NO NO NO NO YES YES YES YES YES

On this DSPOBJAUT screen there is another F11 function key to display the detailed data authorities:

F11=Display detail object authorities

When F11 is pressed I now see:

Object ---------------Data--------------- User Group Authority Read Add Update Delete Execute *PUBLIC *CHANGE X X X X X *GROUP QPGMR *ALL X X X X X

To display the same information from the SQL View I just need to remove the object authority columns and add the data authority ones, shown on lines 3 and 4:

01 SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT, 02 OBJTYPE,USER_NAME,OBJ_AUTH, 03 DATA_READ AS READ,DATA_ADD AS ADD,DATA_UPD AS UPDATE, 04 DATA_DEL AS DELETE,DATA_EXEC AS EXECUTE 05 FROM QSYS2.OBJECT_PRIVILEGES 06 WHERE SYS_DNAME = 'MYLIB' 07 AND SYS_ONAME = 'TESTFILE'

1. DATA_READ - Data read authority, I am renaming all of these columns to match the descriptions on the DSPOBJAUT screen.
2. DATA_ADD - Data add authority.
3. DATA_UPD - Data update authority.
4. DATA_DEL - Data delete authority.
5. DATA_EXEC - Data execute authority.

Again my results look just look like the DSPOBJAUT screen:

LIBRARY OBJECT USER_NAME OBJ_AUTH MYLIB TESTFILE *PUBLIC *CHANGE MYLIB TESTFILE QPGMR *ALL READ ADD UPDATE DELETE EXECUTE YES YES YES YES YES YES YES YES YES YES

These examples show what this View can do compared to DSPOBJAUT, but it is not something auditors ask for. They would ask a question like: are there any objects in the production libraries where Public has *ALL authority?

Below is a CL program I would make to gather those results using the Run SQL command, RUNSQL, to get the results from the View:

01 PGM 02 DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1) 03 RTVJOBA TYPE(&JOBTYPE) 04 SELECT 05 WHEN COND(&JOBTYPE = '0') THEN(CALLSUBR SUBR(SR_BATCH)) 06 WHEN COND(&JOBTYPE = '1') + THEN(SBMJOB CMD(CALL PGM(AUDITPGM01)) JOB(PUBLIC_ALL)) 07 ENDSELECT /*=======================================================*/ 08 SUBR SUBR(SR_BATCH) 09 RUNSQL SQL('DROP TABLE MYLIB.PUBLIC_ALL') + COMMIT(*NONE) NAMING(*SQL) 10 MONMSG MSGID(SQL9010) 11 RUNSQL SQL('CREATE TABLE MYLIB.PUBLIC_ALL AS + 12 (SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT,+ 13 OBJTYPE,USER_NAME,OBJ_AUTH,OWNER + 14 FROM QSYS2.OBJECT_PRIVILEGES + 15 WHERE SYS_DNAME IN (''PRODLIB1'',+ 16 ''PRODLIB2'',+ 17 ''PRODLIB3'') + 18 AND USER_NAME = ''*PUBLIC'' + 19 AND OBJ_AUTH = ''*ALL'') + 20 WITH DATA') + 21 COMMIT(*NONE) NAMING(*SQL) 22 ENDSUBR /*=======================================================*/ 22 ENDPGM

As this program will take some time to gather the desired data I am going to run it in batch. I am not going to discuss lines 2 – 7, instead I will refer you to the post about a program submitting itself to batch.

Line 8: If you are not using subroutines in your CL programs you really ought to look into doing so.

Line 9: The DROP TABLE is the equivalent of the Delete file.

Line 10: This monitor message command is in place in case the table PUBLIC_ALL is not in the library MYLIB.

Line 11 – 21: I am going to create a SQL table "on the fly" from my SQL Select statement. My SQL Select statement is pretty similar to the ones I have shown in my previous examples. The difference is the WHERE clause, line 15 – 19. First I am giving the libraries to look in, lines 15 – 17, then I am only interested if the user is *PUBLIC, and it has *ALL object authority. I need the WITH DATA , line 20 as without it I will not get data in my Table, just its definition.

When the program is compiled and run the look like:

LIBRARY OBJECT OBJTYPE USER_NAME OBJ_AUTH OWNER PRODLIB2 PGM001 *PGM *PUBLIC *ALL JEVANS2 PRODLIB2 FILE01 *FILE *PUBLIC *ALL JEVANS2

Another question I have been asked is: are there any objects not owned by the default user profile? (QPGMR).

I am going to show this example in RPGLE just to show what I can do in a RUNSQL command in CL I can do in embedded SQL in a RPG program.

01 **free 02 exec sql SET OPTION COMMIT = *NONE ; 03 exec sql DROP TABLE MYLIB.OBJ_OWNER ; 04 exec sql CREATE TABLE MYLIB.OBJ_OWNER AS 05 (SELECT DISTINCT SYS_DNAME AS LIBRARY, 06 SYS_ONAME AS OBJECT,OBJTYPE, 07 OWNER 08 FROM QSYS2.OBJECT_PRIVILEGES 09 WHERE SYS_DNAME IN ('PRODLIB1', 10 'PRODLIB2', 11 'PRODLIB3') 12 AND OWNER <> 'QPGMR' 13 ORDER BY SYS_DNAME,SYS_ONAME,OBJTYPE) 14 WITH DATA ; 15 *inlr = *on ;

Line 1: As this example was written on a partition running IBM i 7.3 I am going to use totally free RPG.

Line 2: I do not want to use commitment control with my output table, by including the SET OPTION statement ensures that it will not be used.

Line 3: I am going to try to drop the table before I create it.

Lines 4 – 13: Again I am creating an output table.

Line 5: This time I want to use a SELECT DISTINCT to return only one row for each object that is not owned by QPGMR.

Line 5 – 7: I only need the library, object, object type, and owner columns.

Lines 9 – 12: I am only interested in the first three production libraries where the object is not owned by QPGMR.

Line 13: I want my table's data to be sorted by library, object, and object type.

My results would look like:

LIBRARY OBJECT OBJTYPE OWNER PRODLIB1 PGM021 *PGM JEVANS2 PRODLIB1 QQMQRYSRC *FILE SIMON

I am sure I will be using this View a lot, and I will not be surprised to learn that you will be doing the same during auditor season.

 

You can learn more about the OBJECT_PRIVILEGES View from the IBM website here.

 

This article was written for IBM i 7.3 TR2, and should work for 7.2 TR6 too.

---

After publishing this article I was asked the very good question: what would be the syntax to view all objects in a library? Not just one file, as the above examples show.

01 SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT, 02 OBJTYPE,USER_NAME,OBJ_AUTH 03 FROM QSYS2.OBJECT_PRIVILEGES 04 WHERE SYS_DNAME = 'MYLIB' 05 ORDER BY SYS_ONAME,USER_NAME

Line 4: I have only one Where clause, which is the name of the library.

Line 5: The Order By clause sorts the results by Object name and User name order.