Giving the ability to see all of the objects on the system

I understand why IBM i developers should not have all object authority, *ALLOBJ, but at times I am frustrated by my inability to find objects in my partition. I do not want to do anything to them, just know that they exist. I have to find someone with a security office equivalent user profile and ask them to do a search for me.

I have found a way that this frustration can be removed. It will work on all partitions that are IBM i 7.5 or higher. I think what I am going to describe is included in the initial release. As none of the partition I have access to are running just base 7.5 I cannot check that this did not come in a Technology Refresh, TR.

Function Usages are way to be granted access to perform certain higher authorization functions, without being given that higher authorization. I was going through IBM's documentation about them when I came across:

• QIBM_LIST_ALL_OBJS
• QIBM_LIST_ALL_OBJS_SQL

I can check if these are available on my partition by using the FUNCTION_INFO view:

01 SELECT FUNCTION_ID, 02 FUNCTION_DESCRIPTION_MESSAGE_TEXT, 03 DEFAULT_USAGE 04 FROM QSYS2.FUNCTION_INFO 05 WHERE FUNCTION_ID LIKE 'QIBM_LIST_ALL_OBJS%'

Lines 1 – 3: I only want to have the Function id, its description, and what the default for this function is.

Line 5: I am using a LIKE with a wildcard in the Where clause two retrieve both functions in one statement.

The results are:

FUNCTION_ID FUNCTION_DESCRIPTION_MESSAGE_TEXT DEFAULT_USAGE ---------------------- ----------------------------------------------- ------------- QIBM_LIST_ALL_OBJS Return list of all objects from list interfaces DENIED QIBM_LIST_ALL_OBJS_SQL Return list of all objects from SQL services DENIED

Both functions have a denied status, which means only people with all object authority can see all the objects on the partition.

If I wanted to quickly check if I am authorized to any function I can use the SQL_CHECK_FUNCTION_USAGE table function. As the table function can only take one parameter, the statement below shows me checking if I am authorized to use these functions:

01 VALUES (QSYS2.SQL_CHECK_FUNCTION_USAGE('QIBM_LIST_ALL_OBJS'), 02 QSYS2.SQL_CHECK_FUNCTION_USAGE('QIBM_LIST_ALL_OBJS_SQL'))

the result is:

00001 00002 ------ ------ 0 0

The value of zero means that I cannot use these functions, I am "denied".

Using the FUNCTION_USAGE view I can get a list of all users who have been added to the function:

01 SELECT FUNCTION_ID,USER_NAME,USAGE 02 FROM QSYS2.FUNCTION_USAGE 03 WHERE FUNCTION_ID LIKE 'QIBM_LIST_ALL_OBJS%'

Line 1: I want the function id, user's profile, and their status for using this function.

Line 3: I am using a wildcard for the function id to get the results for both functions.

It comes as no surprise that no-one has been added to either function.

FUNCTION_ID USER_NAME USAGE ---------------------- ---------- -------

For testing how these functions work, I created a library, called TESTLIB, and a file within it, called TESTFILE. I can use the OBJECT_PRIVILEGES view to check my authorization to both. Let me start with the library:

01 SELECT SYSTEM_OBJECT_SCHEMA AS "Library", 02 SYSTEM_OBJECT_NAME AS "Object", 03 OBJECT_TYPE AS "Obj type", 04 AUTHORIZATION_NAME AS "User", 05 OBJECT_AUTHORITY AS "Auth" 06 FROM QSYS2.OBJECT_PRIVILEGES 07 WHERE SYSTEM_OBJECT_NAME = 'TESTLIB'

I am only interested in the following columns:

• Library name
• Object name
• User profile
• User profile's authority to the object

The results were as I expected:

Library Object Obj type User Auth ------- -------- -------- --------- -------- QSYS TESTLIB *LIB *PUBLIC *USE QSYS TESTLIB *LIB ADMIN_GRP *ALL

*PUBLIC has the authority for my test user profile, SIMON2, to use the library object.

Next step is to show the authority to TESTFILE:

01 SELECT SYSTEM_OBJECT_SCHEMA AS "Library", 02 SYSTEM_OBJECT_NAME AS "Object", 03 OBJECT_TYPE AS "Obj type", 04 AUTHORIZATION_NAME AS "User", 05 OBJECT_AUTHORITY AS "Auth" 06 FROM QSYS2.OBJECT_PRIVILEGES 07 WHERE SYSTEM_OBJECT_SCHEMA = 'TESTLIB' 08 AND SYSTEM_OBJECT_NAME = 'TESTFILE'

This returns:

Library Object Obj type User Auth ------- -------- -------- --------- -------- TESTLIB TESTFILE *FILE *PUBLIC *EXCLUDE TESTLIB TESTFILE *FILE ADMIN_GRP *ALL

As *PUBLIC is excluded I am not authorized to the object, not even to see it. This is shown when I use the OBJECT_STATISTICS table function:

01 SELECT OBJLIB,OBJNAME,OBJTYPE 02 FROM TABLE(QSYS2.OBJECT_STATISTICS( 03 OBJECT_SCHEMA => 'TESTLIB', 04 OBJTYPELIST => '*FILE', 05 OBJECT_NAME => 'TESTFILE'))

Line 1: I am only interested in the object library, name, and its type.

Line 4: "Look" in the library TESTLIB…

Line 5: For a file…

Line 6: Called TESTFILE.

Below shows there is no result as I am not authorized to the object.

OBJLIB OBJNAME OBJTYPE ------- ---------- -------

I add the user profile SIMON2 to these functions by using the CL Change Function Usage command, CHGFCNUSG, for each function.

01 CHGFCNUSG FCNID(QIBM_LIST_ALL_OBJS) + 02 USER(SIMON2) + 03 USAGE(*ALLOWED) + 04 DEFAULT(*SAME) ALLOBJAUT(*SAME) 05 CHGFCNUSG FCNID(QIBM_LIST_ALL_OBJS_SQL) + 06 USER(SIMON2) + 07 USAGE(*ALLOWED) + 08 DEFAULT(*SAME) ALLOBJAUT(*SAME)

Lines 1 and 5: These are the functions I want to add the user profile to.

Lines 2 and 6: This is the user profile I want to add.

Lines 3 and 7: I want the user to be allowed to use the function.

Lines 4 and 8: These are the defaults for these parameters, and they can be omitted if you like.

I can use the FUNCTION_USAGE view to confirm that this profile was added with the correct usage:

01 SELECT FUNCTION_ID,USER_NAME,USAGE 02 FROM QSYS2.FUNCTION_USAGE 03 WHERE FUNCTION_ID LIKE 'QIBM_LIST_ALL_OBJS%'

When I ran the above, I can see that SIMON2 is allowed to both functions.

FUNCTION_ID USER_NAME USAGE ---------------------- ---------- ------- QIBM_LIST_ALL_OBJS SIMON2 ALLOWED QIBM_LIST_ALL_OBJS_SQL SIMON2 ALLOWED

I want to show the results from SQL_CHECK_FUNCTION_USAGE so that you can see what the result when I am authorized to the functions:

01 VALUES (QSYS2.SQL_CHECK_FUNCTION_USAGE('QIBM_LIST_ALL_OBJS'), 02 QSYS2.SQL_CHECK_FUNCTION_USAGE('QIBM_LIST_ALL_OBJS_SQL'))

Which displays:

00001 00002 ------ ------ 1 1

The number one is returned to confirm that I am allowed to use both functions.

I was not authorized to the file before I was added to the functions. Now that I have been added I can execute the following again:

01 SELECT OBJLIB,OBJNAME,OBJTYPE 02 FROM TABLE(QSYS2.OBJECT_STATISTICS( 03 OBJECT_SCHEMA => 'TESTLIB', 04 OBJTYPELIST => '*FILE', 05 OBJECT_NAME => 'TESTFILE'))

Below shows I can now "see" the file.

OBJLIB OBJNAME OBJTYPE ------- ---------- ------- TESTLIB TESTFILE *FILE

Just to confirm that I can just "see" the file, I can try to access the data within it by running the following statement:

01 SELECT * FROM TESTLIB.TESTFILE

I see the following error message.

SQL State: 42501 Vendor Code: -551 Message: [SQL0551] Not authorized to object TESTFILE in TESTLIB type *FILE.

This leaves me happy as it confirms I can just "see" all of the objects, and not use them in any way.

With QIBM_LIST_ALL_OBJS and QIBM_LIST_ALL_OBJS_SQL the System Admin no longer has an excuse for not letting me see all of the objects in this partition.

 

You can learn more about the QIBM_LIST_ALL_OBJS and QIBM_LIST_ALL_OBJS_SQL functions from the IBM website here.

 

This article was written for IBM i 7.6 and 7.5 only.