Wednesday, May 3, 2017

Viewing object authority using a SQL view

getting authority data from object_privileges view rather than dspobjaut or edtobjaut

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.

6 comments:

  1. Just last week our auditors wanted a list of who had access to what object. Downloaded this entire file into a million plus row Excel spreadsheet and said "here you go".
    Rob B.

    ReplyDelete
    Replies
    1. Excellent news!

      I cannot wait until next year when I will be able to do this with my auditors.

      Delete
  2. Hi Simon, thanks for that dspobjaut tip using sql and is that example showing how to dspobjaut for 1 file only? What's the syntax to dspobjaut on all objects in a lib...or is that not possible?

    ReplyDelete
    Replies
    1. Good question! I have added the answer at the bottom of the post (above).

      Delete
  3. Hello!
    Any SQL to see all the objects a user is authorized to? either directly or through a user group or authorization list?

    ReplyDelete
    Replies
    1. I would use the same view & work "backwards". Look for all objects the the user is authorized to: WHERE USER_NAME = :UserName

      Delete

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.