Thursday, August 6, 2020

Viewing IFS object's authority using SQL

using sql to view authority of files and folders in ifs

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.

3 comments:

  1. Simon, it get better and better every release. This function will make life easy when researching auth/security levels.. great examples and read..Thanks for sharing..

    ReplyDelete
  2. Nice. Now, how about a script that allows me to remove the members of a specified folder in the IFS that are of a certain age and older?

    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.