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.