Modernize the way you find objects that don’t match your security requirements.

When clients rework their applications’ security configurations, one step I emphasize is that, once the project is complete, they’ll need to routinely check the applications’ objects to make sure they are still secured correctly. If this type of process isn’t in place, it’s quite likely that some of the objects will end up with the wrong security setting. How does that happen? A developer debugs a problem and, rather than promoting the fix through approved change-management processes, creates the program and restores it to the system themselves. Rather than the application profile owning the program, the developer owns it. (This an example of what can happen when developers have too much authority on production systems.) I’ve also seen authorization lists get changed from *PUBLIC *EXCLUDE to *PUBLIC *ALL while attempting to debug a production issue. Because security is always the cause of an issue (NOT!), the authorization list was changed to fix the problem. Of course, it didn’t resolve the problem, but no one remembered to go back and set the authorization list back to the correct setting.

In an effort to keep their applications secured correctly, I’ve seen organizations attempt to check an application’s authority settings “by hand,” literally printing out all of the authorities and making a visual check. Or by running Display Object Authority (DSPOBJAUT) and Display Program to ensure all settings are correct. If you didn’t fall asleep from the boredom of performing this task in this manner, you’d surely miss something during one of these torturous exercises. Fortunately, using IBM i Services, checking an application’s authority settings has become a relatively painless process. Let’s take a look.

Program Settings

Let’s first verify that all programs in an application library are owned by the application owner profile.

SELECT program_library,

       program_name,

       object_type,

       program_owner,

       user_profile,

       use_adopted_authority

   FROM QSYS2.PROGRAM_INFO

 WHERE program_library = 'PROD_LIB' and

         program_owner <> 'APP_OWNER';

Many clients rework their applications to have all programs adopt authority except for the application’s initial program. Let’s add that check.

SELECT program_library,

       program_name,

       object_type,

       program_owner,

       user_profile,

       use_adopted_authority

   FROM QSYS2.PROGRAM_INFO

   WHERE program_library = 'PROD_LIB'

         AND (user_profile <> '*OWNER'

             OR program_owner <> 'APP_OWNER')

         AND program_name <> 'INITIAL';

Now that we have the programs’ ownership and the adopt settings are verified so that the exceptions can be discovered, let’s look at the application objects’ *PUBLIC authority. Different object types require different *PUBLIC authority. The following looks at all objects in a library except *FILEs (which I’ll address in a bit) and *DEVDs and *MSGQs (because the operating system requires users to have *CHANGE to those objects).

SELECT *

   FROM qsys2.object_privileges

   WHERE system_object_schema = 'PROD_LIB'

         AND object_type not in ('*FILE’, ‘*DEVD’, ‘*MSGQ’)

         AND (authorization_name = '*PUBLIC'

                 AND object_authority <> '*USE');

Building on, you’ll also want to check for the correct owner.

SELECT *

   FROM qsys2.object_privileges

   WHERE system_object_schema = 'PROD_LIB'

         AND object_type NOT IN ('*FILE', '*DEVD', '*MSGQ')

         AND ((authorization_name = '*PUBLIC'

                 AND object_authority <> '*USE')

             OR (OWNER <> 'PROD_OWNER'

                 AND authorization_name = '*PUBLIC'));

Now you can check the *DEVDs and *MSGQs.

SELECT *

   FROM qsys2.object_privileges

   WHERE system_object_schema = 'PROD_LIB'

         AND object_type IN ('*DEVD', '*MSGQ')

         AND ((authorization_name = '*PUBLIC'

                 AND object_authority <> '*CHANGE')

             OR (OWNER <> 'PROD_OWNER'

                 AND authorization_name = '*PUBLIC'));

*FILE objects require special consideration. My clients’ typical configuration is to secure physical and logical files with an authorization list and point the files’ *PUBLIC authority setting to *AUTL so that the *PUBLIC authority comes from the authorization list rather than the file itself.

SELECT *

   FROM qsys2.object_privileges

   WHERE system_object_schema = 'PROD_LIB'

         AND object_type = '*FILE'

         AND ((authorization_name = '*PUBLIC'

                 AND object_authority <> '*AUTL')

             OR (authorization_list <> 'PRODAUTL')

             OR (OWNER <> 'PROD_OWNER'

                 AND authorization_name = '*PUBLIC'));

Now let’s check to make sure the authorization list is set to *PUBLIC *EXCLUDE.

SELECT *

   FROM qsys2.object_privileges

   WHERE system_object_schema = 'QSYS'

         AND object_type = '*AUTL'

         and object_name = 'PRODAUTL'

         and authorization_name = '*PUBLIC'

        AND object_authority <> '*EXCLUDE';

Finally, use the following to list the users authorized to the authorization list. These results should be reviewed at least quarterly, and users no longer requiring access should be removed.

SELECT *

   FROM qsys2.authorization_list_user_info

   WHERE authorization_list = 'PROD_AUTL';

You may also want to find objects where private authorities have been granted. This SQL will list the authority but not for *PUBLIC or the owner. If an object isn’t listed, it has no private authorities. (Note: The operating system does not consider the owner’s authority a private authority; therefore, there’s no reason to remove it. In fact, removing it from IFS objects can cause issues.)

SELECT *

   FROM qsys2.object_privileges

   WHERE system_object_schema = 'PROD_LIB'

         and (authorization_name <> '*PUBLIC'

         and authorization_name <> owner);

And finally, let’s not forget that we need to do similar checks to ensure application objects in the IFS are secured properly. The following SQL starts at the /prod_directory directory and examines it and all subdirectories and will list any not owned by PROD_OWNER and those not set to *PUBLIC DTAAUT(*EXCLUDE) OBJAUT(*NONE).

WITH ifs_auts AS (

       SELECT path_name

           FROM TABLE (

                   qsys2.ifs_object_statistics(start_path_name => '/home')

               )

   )

   SELECT iop.*

       FROM ifs_auts,

             TABLE (

                 qsys2.ifs_object_privileges(path_name)

            ) iop

       WHERE (authorization_name = '*PUBLIC'

                 AND object_type = '*DIR')

             AND (owner <> 'PROD_OWNER'

                             or ((data_authority <> '*EXCLUDE')

                 and (object_management <> 'NO')

                 and (object_existence <> 'NO')

                 and (object_alter <> 'NO')

                 and (object_reference <> 'NO')));    

Looking for private authorities assigned to IFS objects is good for ensuring your security policy is followed. But the way the IFS propagates private authorities, it’s also a good method for discovering those directories where private authorities may be accumulating.

WITH ifs_auts AS (

       SELECT path_name

           FROM TABLE (

                   qsys2.ifs_object_statistics(start_path_name => '/home')

               ))

   SELECT iop.*

       FROM ifs_auts,

             TABLE (

                 qsys2.ifs_object_privileges(path_name)

             ) iop

       WHERE (authorization_name <> '*PUBLIC'

                 AND authorization_name <> owner);

Summary

I hope you can see from this article that you can use IBM i Services to easily find objects whose security settings don’t match your policy, eliminating any need to visually examine individual object settings. I encourage you to copy this SQL into your own SQL file, modify it to use your own application library names and directory paths, and then save it so you can run the SQL regularly to discover where objects don’t match your security configuration requirements. Or add it into a CL program using the RUNSQL command to fully automate this reporting. Numerous examples of using RUNSQL can be found by searching the Internet. You can find the complete list of IBM i Services here.

More examples of using SQL to manage your IBM i security can be found in my newest book, Mastering IBM i Security. Click here to order.