The challenge... How do we protect ourselves from an 'Uncontrolled Search Path Element' attack?

Library list security.sql

--
-- Author: Scott Forstie
-- Date:   April, 2025
--
-- The request:
-- How do we protect ourselves from an 'Uncontrolled Search Path Element' attack?
--
-- Purpose:
-- The IBM i has a wonderful feature known as the Library List (aka *LIBL).
-- This feature allows applications, scripts, and end users to call program, interact with database files, and many other
-- actions where the object names are not library qualified. 
-- 
-- This wonderful concept of library transparency is a potential security risk.
-- So... how do we protect ourselves?
-- Read on to discover how SQL and IBM i (SQL) Services can lend a hand.
--
-- If a library in the library list has *PUBLIC configured to allow ANY user to create, rename, restore, delete, or 
-- otherwise mess with the contents of that library, a bad actor could use this as an attack vector.
-- All libraries in the library list, should typically have *PUBLIC set to *USE.
-- QTEMP is an exception to the rule.
--
-- Note, this gist does not show you how to protect yourself against libraries that are added dynamically to the
-- library list via the Add Library List Entry (ADDLIBLE) and Remove Library List Entry (RMVLIBLE) commands.            
--
-- Features Used : This Gist uses system_value_info, library_list_info, and the object_privileges (View & UDTF)

-- Resources:
-- https://www.ibm.com/docs/en/i/7.6.0?topic=overview-library-lists-system-values-system-library-list
-- https://www.ibm.com/docs/en/i/7.6.0?topic=overview-library-lists-system-values-user-library-list
stop;

--
-- How is the system portion of the library list configured? (not normalized)
--
SELECT system_value
  FROM qsys2.system_value_info
  WHERE system_value_name = 'QSYSLIBL';
stop;

--
-- How is the user portion of the library list configured? (not normalized)
--
SELECT system_value
  FROM qsys2.system_value_info
  WHERE system_value_name = 'QUSRLIBL';
stop;

--
-- If this isn't set to *EXCLUDE, you are exposed to a library list attack
--
SELECT object_authority
  FROM qsys2.object_privileges
  WHERE system_object_schema = 'QSYS' AND system_object_name = 'CHGSYSLIBL' AND object_type = '*CMD'
        AND authorization_name = '*PUBLIC';
stop;

--
-- What is the system portion of the library list? (normalized)
--
SELECT *
  FROM qsys2.library_list_info
  WHERE "TYPE" = 'SYSTEM';
stop;

--
-- What is the complete library list?
--
SELECT *
  FROM qsys2.library_list_info;
stop;

--
-- Complete library list, with *PUBLIC configuration
--
SELECT libl.*, priv.*
  FROM qsys2.library_list_info libl, LATERAL (
         SELECT *
           FROM TABLE (
               qsys2.object_privileges(
                 system_object_schema => 'QSYS',
                 system_object_name   => system_schema_name, 
                 object_type          => '*LIB')
             )
       ) priv
  WHERE priv.authorization_user = '*PUBLIC';
stop;

--
-- Complete library list, with *PUBLIC configuration set to something other than *USE
--
SELECT libl.system_schema_name AS lib_name, priv.authorization_user AS user_name,
       priv.object_authority
  FROM qsys2.library_list_info libl, LATERAL (
         SELECT *
           FROM TABLE (
               qsys2.object_privileges(
                 system_object_schema => 'QSYS', system_object_name => system_schema_name,
                 object_type => '*LIB')
             )
       ) priv
  WHERE priv.authorization_user = '*PUBLIC' AND 
        priv.object_authority   <> '*USE'   AND
        libl.system_schema_name not in ('QTEMP');
stop;