Wednesday, August 16, 2023

List and changing object owner with SQL

At my work one of the things that auditors look for is who owns all the objects in the production libraries. If there are objects that do not belong to the expected user profile then they want an explanation of why they do not. The week before the auditors are due I do a "scan" of all the production libraries, I am responsible, for to look for any objects that do not belong to the expected user profile, it's better to be prepared than be surprised by the auditors.

Fortunately I can perform the "scan" of all those libraries at once with a SQL View, OBJECT_OWNERSHIP, to return a list of objects that do not belong to the expected user profile.

For the purposes of this post I am going to limit my examples to the objects in my personal library, MYLIB. And I am only interested in a few of the columns of this View:

  • AUTHORIZATION_NAME:  User profile that owns the object
  • OBJECT_TYPE:  The type of the object
  • OBJECT_LIBRARY:  Library the object is on
  • OBJECT_NAME:  Name of the object

The following example gives a couple more columns of information in the results that, at times, I also find useful:

01  SELECT AUTHORIZATION_NAME AS "Owner",
02         OBJECT_NAME AS "Object",
03         OBJECT_TYPE AS "Type",
04         OBJECT_ATTRIBUTE AS "Attr",
05         OBJECT_AUTHORITY AS "Auth"
06    FROM QSYS2.OBJECT_OWNERSHIP 
07   WHERE OBJECT_lIBRARY = 'MYLIB' 
08   ORDER BY "Owner","Object","Type"

Lines 1 – 5: These are the columns I want in my results. The long names do a good job to describe what is in each one, so I will not bother to describe them. I have also given them all a column heading field to make the results fit on this page.

Line 6: The SQL View OBJECT_OWNERSHIP is in the library QSYS2.

Line 7: I only want the results for my library.

Line 8: I have used the column heading names in the ORDER BY clause, rather than the columns or their location in the results.

The results take a long time to generate as the View contains information for every object in your partition, except those in QTEMP. That is a lot of objects. After several minutes, my results are returned:

Owner    Object      Type     Attr    Auth
-------  ----------  -------  ----    ------------
QDFTOWN  JRN001P     *FILE    PF      *ALL
QDFTOWN  JRN002P     *FILE    PF      *ALL
QPGMR    INLPGM      *CMD     <NULL>  *USE
QPGMR    INLPGM      *PGM     CLLE    *USE
SIMON    PGM0001     *PGM     CLLE    *ALL
SIMON    MYUSRIDX    *USRIDX  <NULL>  *ALL
SIMON    USERSIGNON  *FILE    LF      USER DEFINED

This is only a subset of the objects in my library, but enough to illustrate what I find in it.

The "Auth", OBJECT_AUTHORITY, column lets me check if the objects in my library are secured the way I want.

There are objects in my library that are not owned by my user profile SIMON, and I want to change them to be owned by my profile.

I to do this I am going to use a SQL Common Table Expression, CTE. I will construct a CTE in two parts:

  1. To select the objects that are not owned by my user profile
  2. Make the change

I prefer this approach, rather than having one Select statement to make the change, as I can refine my search before I change the second part of the CTE statement to make the change.

At first my CTE looks like:

01  WITH T0 (OWNER,TYPE,LIB,OBJ)
02  AS
03  (SELECT AUTHORIZATION_NAME,OBJECT_TYPE,
04          OBJECT_LIBRARY,OBJECT_NAME
05     FROM QSYS2.OBJECT_OWNERSHIP
06    WHERE OBJECT_LIBRARY = 'MYLIB'
07      AND AUTHORIZATION_NAME <> 'SIMON')

08  SELECT * FROM T0 ;

Line 1: All CTE start with WITH. I am creating a temporary table, called T0, to contain the columns for the object owner, object type, object library, and object name.

Lines 3 – 7: This is the Select statement that returns the list of objects that are not owned by my user profile.

Line 8: In the first version of my CTE I am just returning all of the rows from my temporary table T0.

The results are:

OWNER    TYPE   LIB    OBJ
-------  ----   -----  -------
QDFTOWN  *FILE  MYLIB  JRN001P
QDFTOWN  *FILE  MYLIB  JRN002P
QPGMR    *CMD   MYLIB  INLPGM
QPGMR    *PGM   MYLIB  INLPGM

I will use the Change Object Owner command, CHGOBJOWN, to make the change of the owner. In the second version of the CTE I keep the selection, lines 1 – 7, the same and change the second part of the CTE. The changes replace line 8 in the first version of the CTE with:

08  SELECT 'CHGOBJOWN OBJ(' || LIB || '/' || OBJ || ') OBJTYPE(' || TYPE || ') 
              NEWOWN(SIMON)' 
09    FROM T0 ;

Line 8: In the new line 8 I use concatenation, denoted by the double pipe ( || ), to build the CHGOBJOWN command for each row of the results. I do not have to trim any of the columns are they are all variable character, VARCHAR, type.

The result look like:

00005
-------
CHGOBJOWN OBJ(SIMON/JRN001P) OBJTYPE(*FILE) NEWOWN(SIMON)
CHGOBJOWN OBJ(SIMON/JRN002P) OBJTYPE(*FILE) NEWOWN(SIMON)
CHGOBJOWN OBJ(SIMON/INLPGM) OBJTYPE(*CMD) NEWOWN(SIMON)
CHGOBJOWN OBJ(SIMON/INLPGM) OBJTYPE(*PGM) NEWOWN(SIMON)

I know that my command string all look valid, and I am ready to change second part of the CTE again, this time to make the change:

08  SELECT OWNER,TYPE,LIB,OBJ,
09       QSYS2.QCMDEXC('CHGOBJOWN OBJ(' || LIB || '/' || OBJ || ') 
                                  OBJTYPE(' || TYPE || ') 
                                  NEWOWN(SIMON)') 
           AS "Success?"
10    FROM T0 ;

Line 8: I want to return the object owner, type, library, and name.

Line 9: I am using the QCMDEXC Scalar function to execute the CHGOBJOWN command. I know these strings will be valid as I tested them with the second version of this CTE. I have also given this column a column heading for the results.

The results are:

OWNER    TYPE   LIB    OBJ      Success?
-------  ----   -----  -------  --------
QDFTOWN  *FILE  MYLIB  JRN001P         1
QDFTOWN  *FILE  MYLIB  JRN002P         1
QPGMR    *CMD   MYLIB  INLPGM          1
QPGMR    *PGM   MYLIB  INLPGM          1

The QCMDEXC Scalar function returns "1" if the command contained within completed successfully, and "-1" if it failed. As all of my rows have "1" in the Success column I know that all of those objects had their owner changed.

The OBJECT_OWNERSHIP has saved me a lot of time both with the identifying of objects owned by the wrong profile, and also as a way to change the owner.

 

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

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

1 comment:

  1. Hi Simon,

    I was able to use this as a guide to create a script to automatically delete some objects from a library that are left over from an automated report email tool... I've been manually purging these objects for years... It will be nice to automate this cleanup routine.

    I'm planning to insert this into an SQLRPGLE program and output the results to a file and then email that results file to myself every time it runs...

    -- Delete DSTDTA objects from PROJEXENV library automatically

    With T1 (OBJNAME, OBJTYPE, OBJTEXT, OBJLIB)
    AS
    (SELECT OBJNAME AS "Name", OBJTYPE AS "Type", OBJTEXT, OBJLIB
    FROM TABLE(QSYS2.OBJECT_STATISTICS('PROJEXENV','*ALL'))
    Where OBJTEXT = 'DSTDTA Work file - I can be deleted!')

    SELECT OBJLIB, OBJNAME, OBJTYPE, OBJTEXT,
    QSYS2.QCMDEXC('DLTOBJ OBJ(' || OBJLIB || '/' || OBJNAME || ') OBJTYPE(' || OBJTYPE || ')') As "Success ?"

    FROM T1

    Now I have to master your article about sending emails using SQL...

    This is really useful knowledge, as usual... Thanks for sharing...

    Jeff Klipa

    ReplyDelete

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.