Viewing IFS object's authority using SQL

Included with the latest Technology Refreshes is a new table function which allows me to view objects' privileges of the folders and files in the IFS. Object privileges/authorities are something that I am asked for during audits, therefore, I was really interested to learn what information I can get from it.

The only gotcha is as a table function IFS_OBJECT_PRIVILEGES returns the results for one set of objects, rather give me the ability to get information for all objects as I would using a view.

As I have done in many of my previous examples I am not going to show you all the columns returned by this table function, just the ones I find interesting. There is a link at the bottom of this post to IBM's documentation for this table function which includes a full list and description for all of the columns.

The columns I am going to work with are:

1. PATH_NAME:  The path to either the folder or the file
2. OBJECT_TYPE:  In my example I only found *DIR, directory, and *STMF, stream file
3. OWNER:  User profile that owns the object
4. AUTHORIZATION_LIST:  If the object is secured using an authorization list, the list's name
5. AUTHORIZATION_NAME:  User profile. Can also be *PUBLIC to show the public's authority to the object
6. DATA_AUTHORITY:  The actual authority to the object. I am not going to list all the possible values, as you will find that in the IBM documentation

There are many more columns that contain all kinds of information that describe in detail the object's privileges, but I do not have the space to show them here I am going to omit them, and ask to you to visit IBM's documentation.

Let me start by retrieving the privileges for my folder in the IFS:

01 SELECT PATH_NAME, 02 OBJECT_TYPE, 03 OWNER, 04 AUTHORIZATION_LIST, 05 AUTHORIZATION_NAME, 06 DATA_AUTHORITY 07 FROM TABLE(QSYS2.IFS_OBJECT_PRIVILEGES('/RPGPGM'))

Line 7: Notice that there is a forward slash ( / ) before the folder's name.

Three rows are returned, one for each of the profiles authorized to my folder:

PATH_NAME OBJECT _TYPE OWNER AUTHORIZATION_LIST --------- ------------ ----- ------------------ /RPGPGM *DIR SIMON - /RPGPGM *DIR SIMON - /RPGPGM *DIR SIMON - AUTHORIZATION_NAME DATA_AUTHORITY ------------------ -------------- *PUBLIC *RWX SIMON *RWX QDIRSRV *X

It would appear that QDIRSRV profile is used for file shares.

The privileges listed are:

1. *RWX:  All authority to object
2. *X:  Use of the object only

Let me perform the same statement for a file in my folder, list.txt. Don't forget that the IFS is case sensitive with the objects' name, therefore, LIST.TXT is not the same file as list.txt.

01 SELECT PATH_NAME, 02 OBJECT_TYPE, 03 OWNER, 04 AUTHORIZATION_LIST, 05 AUTHORIZATION_NAME, 06 DATA_AUTHORITY 07 FROM TABLE(QSYS2.IFS_OBJECT_PRIVILEGES('/RPGPGM/list.txt'))

These results look pretty similar to the previous set:

PATH_NAME OBJECT _TYPE OWNER AUTHORIZATION_LIST ---------------- ------------ ----- ------------------ /RPGPGM/list.txt *STMF SIMON - /RPGPGM/list.txt *STMF SIMON - /RPGPGM/list.txt *STMF SIMON - AUTHORIZATION_NAME DATA_AUTHORITY ------------------ -------------- *PUBLIC *EXCLUDE SIMON *RWX QDIRSRV *X

The only difference is that *PUBLIC has been excluded from using this file.

If I wanted to get a list of all the authorities *PUBLIC have to my folder and its contents I don't want to have to manually list all of the files, and then use this table function for each one. What I would want to do is to create a statement that would make a list of the objects, and then use the IFS_OBJECT_PRIVILEGES for them all. I can do this using a "common table expression", I need to write about these. Without going into too much detail in the example I show will build a temporary file in memory, and then use the contents of that temporary table to get the results from IFS_OBJECT_PRIVILEGES.

01 WITH IFS_OBJS AS 02 (SELECT PATH_NAME 03 FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS('/RPGPGM'))) 04 SELECT B.PATH_NAME, 05 B.OBJECT_TYPE, 06 B.OWNER, 07 B.DATA_AUTHORITY 08 FROM IFS_OBJS,TABLE(QSYS2.IFS_OBJECT_PRIVILEGES(PATH_NAME)) B 09 WHERE AUTHORIZATION_NAME = '*PUBLIC'

Line 1: All "common table expressions", CTE, start with the word WITH. The first part of this CTE is to create a temporary table called IFS_OBJ, which will be a list of the objects in the given folder.

Lines 2 and 3: This is the SQL statement that fills the temporary table using the table function IFS_OBJECT_STATISTICS.

I placed a space between the two parts of this CTE to make it easier to understand what is going on.

Lines 4 – 7: Are the columns I want returned from the IFS_OBJECT_PRIVILEGES table function.

Line 8: No need to join the files as the path name from IFS_OBJS is used as the parameter in IFS_OBJECT_PRIVILEGES.

Line 9: I am only interested in the privileges for public.

The results:

PATH_NAME OBJECT _TYPE OWNER DATA_AUTHORITY ---------------- ------------ ----- -------------- /RPGPGM *DIR SIMON *RWX /RPGPGM/report.csv *STMF SIMON *RWX /RPGPGM/list.txt *STMF SIMON *EXCLUDE /RPGPGM/xmlfile.xml *STMF SIMON *RWX /RPGPGM/test_file.txt *STMF SIMON *RWX

This table function will make my work for the auditors easier when they visit next year.

 

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

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.