--
-- IBM i Security with an SQL Chaser - Another Round, Please!
-- Companion SQL Script
-- April 2026
-- Presenters: Carol Woodbury & Scott Forstie
--
-- Sample SQL (provided 'as is') - courtesy of Scott Forstie
--
 
-- 
-- description: Look for processes creating something into root in the last two weeks before setting *PUBLIC to DTAAUT(*RX) OBJAUT(*NONE)
--
SELECT entry_timestamp,
       user_name,
       qualified_job_name,
       program_library,
       program_name,
       path_name
    FROM TABLE (
            systools.audit_journal_co(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 14 DAYS)
        )
    WHERE path_name NOT LIKE '/%/%';
stop;

--
-- description: Do the same thing for /QOpenSys before changing it to DTAAUT(*RX) OBJAUT(*NONE)
--
SELECT entry_timestamp,
       user_name,
       qualified_job_name,
       program_library,
       program_name,
       path_name
    FROM TABLE (
            systools.audit_journal_co(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 14 DAYS)
        )
    WHERE path_name LIKE '/QOpenSys%'
          AND path_name NOT LIKE '/QOpenSys/%/%';
stop;

--
-- description: List the first-level directories under root that are set to *PUBLIC DTAUT(*RWX) OBJAUT(*ALL)
--
WITH OBJS AS (
        SELECT PATH_NAME
            FROM TABLE (
                    QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/', 
                                                subtree_directories => 'NO', 
                                                object_type_list => '*ALLDIR')
    ))
    SELECT *
        FROM OBJS,
             TABLE (
                 QSYS2.IFS_OBJECT_PRIVILEGES(PATH_NAME)
             )
        WHERE data_authority = '*RWX'
              AND object_management = 'YES'      AND object_existence = 'YES'
              AND object_reference = 'YES'       AND object_alter = 'YES'
              and authorization_name = '*PUBLIC' and objs.path_name not in ('/');
  stop;

--
-- description: 
-- After configuring Authority Collection for the directory, view the entries after a period of time
-- Use this new Authority Collection IFS view to get a better / more accurate translation of IFS authority requirements
-- Added in IBM i 7.6 and IBM i 7.5 TR6
--
SELECT user_name, check_timestamp, path_name, 
  detailed_required_authority, detailed_current_authority
  FROM qsys2.authority_collection_ifs
  WHERE UPPER(path_name) LIKE '/PAYROLL_UPLOAD%';
 stop;

--
-- description: Find any files under an IFS path that have the wrong value for owner or *PUBLIC
--
WITH ifs_auts AS (
        SELECT path_name
            FROM TABLE (
                    qsys2.ifs_object_statistics(start_path_name => '/payroll_upload', subtree_directories => 'YES', IGNORE_ERRORS => 'YES')
                )
    )
    SELECT iop.*
        FROM ifs_auts,
             TABLE (
                 qsys2.ifs_object_privileges(path_name)
             ) iop
        WHERE owner <> 'PROD_OWNER'
              OR (authorization_name = '*PUBLIC'
                  AND (data_authority <> '*EXCLUDE'
                      OR object_management <> 'NO'
                      OR object_existence <> 'NO'
                      OR object_reference <> 'NO'
                      OR object_alter <> 'NO'));
stop;

--
-- description: Find old objects in /home
--
SELECT path_name, object_type, object_owner, last_used_timestamp, create_timestamp, data_size
  FROM TABLE (
      qsys2.ifs_object_statistics(start_path_name => '/home', subtree_directories => 'YES')
    ) AS oldifsstuff
  WHERE last_used_timestamp < CURRENT DATE - 6 MONTHS
  ORDER BY data_size DESC; 
  stop;

--
-- description: List objects in PROD_LIB not used in 6 months
--
SELECT objname, objtype, objattribute, objowner, objdefiner AS Creator, objlongname, objlongschema, sql_object_type,
       objcreated, objtext, last_used_timestamp, days_used_count
    FROM TABLE (
            qsys2.object_statistics('PROD_LIB', '*ALL')
        ) AS oldstuff
        where last_used_timestamp < CURRENT DATE - 6 months and 
        last_used_object = 'YES' 
        order by objtype;
 stop;


--
-- description: Add *NETSMBSVR to QAUDLVL and look at the VP audit journal entries to see which shares are in use (New in IBM i 7.6)
--
SELECT entry_timestamp,
       audit_user_name,
       share_name,
       entry_type_detail,
       share_authorization_list
    FROM TABLE (
            systools.audit_journal_vp(starting_timestamp => CURRENT TIMESTAMP - 7 DAYS)
        );
stop;

--
-- description:
-- Create a VP data mart only including share types of FILE. 
-- This will gather VP entries from all receivers currently on the system (*CURCHAIN)
--  
cl: qsys/crtlib VPDATAMART AUT(*EXCLUDE);

CALL qsys2.manage_audit_journal_data_mart
(journal_entry_type => 'VP',
 data_mart_library  => 'VPDATAMART',
 starting_timestamp => '*FIRST',
 ending_timestamp   => DEFAULT,
 DATA_MART_ACTION   => 'CREATE',
 DATA_MART_FILTER   =>  ' share_type = ''FILE'' ' );
stop;

-- At some later date, but before the audit journal receivers are removed...
-- Add any entries generated since the last run
CALL qsys2.manage_audit_journal_data_mart(
    journal_entry_type => 'VP',
    data_mart_library  => 'VPDATAMART',
    starting_timestamp => '*CONTINUE',
    ending_timestamp   => default,
    data_mart_action   => 'ADD'
);
stop;

--
-- description: View the data mart data
--
select * from VPDataMart.audit_journal_VP;
-- OR
select * from VPDataMart.aj_vp;
stop; 

---
-- description: Configure Authority Collection on the object being shared then view the results to determine who's using the share
--
SELECT user_name, check_timestamp, path_name, 
  detailed_required_authority, detailed_current_authority
  FROM qsys2.authority_collection_fsobj
  WHERE job_name LIKE 'QZLSFILE%' AND UPPER(path_name) LIKE '/HOME/TESTNAV/%';
 stop;

--
-- description: 
-- Use this new Authority Collection IFS view to get a better / more accurate translation of IFS authority requirements
-- Added in IBM i 7.6 and IBM i 7.5 TR6
--
SELECT user_name, check_timestamp, path_name, 
  detailed_required_authority, detailed_current_authority
  FROM qsys2.authority_collection_ifs
  WHERE job_name LIKE 'QZLSFILE%' AND UPPER(path_name) LIKE '/HOME/TESTNAV/%';
stop;

--
-- description:
-- Which users have *IOSYSCFG authority either directly
-- or via a Group or Supplemental profile?
--
SELECT user_name, status, no_password_indicator, DATE(previous_signon) AS last_signon,
       DATE(last_used_timestamp) AS last_used, text_description
  FROM qsys2.user_info
  WHERE special_authorities LIKE '%*IOSYSCFG%' OR authorization_name IN (SELECT user_profile_name
            FROM qsys2.group_profile_entries
            WHERE group_profile_name IN (SELECT authorization_name
                  FROM qsys2.user_info
                  WHERE special_authorities LIKE '%*IOSYSCFG%'))
  ORDER BY user_name;
stop;

--
-- description: Repopulate the data within the Special Authority data mart
--
REFRESH TABLE SYSTOOLS.SPECIAL_AUTHORITY_DATA_MART;
stop;

--
-- description: Who has *IOSYSCFG special authority, and from what authority source? (Using the data mart)
--
SELECT user_name, authority_source, group_profile_name, 
       status, text_description, last_used_date
  FROM systools.special_authority_data_mart
  WHERE special_authority = '*IOSYSCFG'
  ORDER BY user_name;
stop;


--
-- description: What's the Default Access for the new QIBM_IOSYSCFG_VIEW?  (IBM i 7.6)
--
SELECT default_usage, allobj_indicator, function_name_message_text
  FROM qsys2.function_info
  WHERE function_id = 'QIBM_IOSYSCFG_VIEW';
stop;

--
-- description: Who is allowed to use the new QIBM_IOSYSCFG_VIEW function usage?  (IBM i 7.6)
--
select * from qsys2.function_usage where function_id = 'QIBM_IOSYSCFG_VIEW';
stop;

--
-- description: Which users can be impersonated by anyone? (Which profiles aren't the default of *PUBLIC *EXCLUDE?)
--
SELECT object_name AS user_name,
       object_authority
    FROM qsys2.object_privileges
    WHERE system_object_schema = 'QSYS'
          AND object_type = '*USRPRF'
          AND user_name = '*PUBLIC'
          AND object_authority <> '*EXCLUDE'
          AND object_name NOT IN ('QDBSHR', 'QDBSHRDO', 'QTMPLPD');
stop;
   
--
-- description: After adding *AUTWARN to the user action auditing, determine whether the profile is currently being exploited
--
SELECT entry_timestamp,
       qualified_job_name,
       user_name,
       user_profile_name AS profile_exploited,
       function_registration_operation AS failure_or_warning,
       fail_operation
    FROM TABLE (
            systools.audit_journal_gr()
        )
    WHERE function_name LIKE ('QIBM_RUN_UNDER%') 
  --  and function_registration_operation = 'USAGE WARNING';
stop;


--  category:  IBM i Services
--  description:  Security - IFS home directories (detail)
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Detailed report of IFS home directories that are not secured from *PUBLIC use
--  
SELECT ios.path_name AS ifs_home_directory, ios.object_owner AS owner,   
       data_authority as public_data_authority 
  FROM TABLE(qsys2.ifs_object_statistics(
        start_path_name     => '/home/', 
        subtree_directories => 'NO', 
        object_type_list    => '*ALLDIR')
    ) ios, lateral (
           select *
             from table (
                 qsys2.ifs_object_privileges(path_name => ios.path_name)
               )
         ) z where ios.path_name not in ('/home/') and 
                   z.authorization_name = '*PUBLIC' and 
                   data_authority not in ('*EXCLUDE', '*NONE')  
  order by ifs_home_directory;
stop;

--
-- If you got down this far in the script, well done!
-- Here are some director's cut extras!
--
--
--  description: Discover User profiles that haven't been used in the last 3 months
--
SELECT user_name, DATE(last_used_timestamp) AS last_used,
       TIMESTAMP(previous_signon, 0) AS last_signon,
       TIMESTAMP(creation_timestamp, 0) AS create_time, status, text_description
  FROM qsys2.user_info
  WHERE (last_used_timestamp IS null OR last_used_timestamp < CURRENT TIMESTAMP - 3 MONTHS) AND (
          creation_timestamp < CURRENT TIMESTAMP - 3 MONTHS);
stop;

--
--  description: DISABLE User profiles that haven't been used in the last 3 months - No changes will be made.
--
SELECT cup.*
    FROM qsys2.user_info u,
         TABLE (
             systools.change_user_profile(
                 p_user_name => user_name, p_status => '*DISABLED', p_text => 'Careful - do not re-enable', preview => 'YES')
         ) cup
    WHERE (u.user_name NOT IN (SELECT aidprf
                      FROM QUSRSYS.QASECIDL))
          AND ((last_used_timestamp IS NULL
                  OR last_used_timestamp < CURRENT TIMESTAMP - 3 MONTHS)
              AND (creation_timestamp < CURRENT TIMESTAMP - 3 MONTHS));
stop;

--
--  description: DISABLE User profiles that haven't been used in the last 3 months and indicate they shouldn' t re-enabled !
--  ...Automated - Note that this is preview NO - running this WILL set the old profiles to *DISABLED !!!!!
--
create or replace procedure coolstuff.user_dis()
SET OPTION USRPRF=*user, DYNUSRPRF=*user, commit=*none
begin
  create or replace table qtemp.just_do_it as
        (select cup.*
            from QSYS2.USER_INFO u, table (
                   SYSTOOLS.CHANGE_USER_PROFILE(
                     P_USER_NAME => USER_NAME, P_STATUS => '*DISABLED',
                     P_TEXT => 'Careful - do not re-enable', PREVIEW => 'NO')
                 ) cup
            where (u.user_name not in (select aidprf
                        from QUSRSYS.QASECIDL)) and
                  ((last_used_timestamp is NULL or
                      last_used_timestamp < current timestamp - 3 months) and
                    (creation_timestamp < current timestamp - 3 months)))
        with data
    on replace delete rows;
end;

-- description: Schedule a job to disable profiles regularly
cl: ADDJOBSCDE JOB(USER_DIS) CMD(QSYS/RUNSQL SQL('call coolstuff.user_dis()') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(235500);
stop;

--
-- Note: running this WILL delete inactive profiles !!!
--  description: Delete User profiles that are at least 6 months old and have not been
--               used in the last six months
--               Reassign ownership of any object to the user's primary group
--               If the user has no primary group, reassign object ownership to CAROL
--               If the user profile is in the "do not delete list", skip it
--
SELECT qsys2.qcmdexc('DLTUSRPRF USRPRF(' concat user_name concat ') ' 
                concat 'OWNOBJOPT(*CHGOWN ' concat 
                case GROUP_PROFILE_NAME when '*NONE' then 'CAROL' 
                  else GROUP_PROFILE_NAME end concat ')' )
    FROM QSYS2.USER_INFO u 
    WHERE (coalesce(last_used_timestamp, previous_signon, current timestamp - 7 months) 
             < CURRENT TIMESTAMP - 6 MONTHS)
      AND (creation_timestamp < CURRENT TIMESTAMP - 6 MONTHS)
      AND (user_creator not in ('*IBM', 'QLPINSTALL', 'QSYS', 'QSECOFR'))
      AND u.user_name not in (select aidprf from QUSRSYS.QASECIDL);
stop;

--
--  description: User profiles with default passwords
--
SELECT user_name, status, password_expiration_interval, special_authorities, group_profile_name,
       supplemental_group_list, last_used_timestamp, creation_timestamp, user_creator,
       text_description
  FROM qsys2.user_info
  WHERE user_default_password = 'YES'
  ORDER BY status;
stop;

--
--  description: Find unused profiles with a specific special authority, e.g.,  *IOSYSCFG
--
WITH iosyscfg_users (user_name) AS (
    SELECT user_name
      FROM systools.special_authority_data_mart
      WHERE special_authority = '*IOSYSCFG' AND authority_source = 'USER PROFILE' AND status =
            '*ENABLED' AND (last_used_date IS null OR last_used_date < current_date - 90 DAYS)
      ORDER BY user_name)
  SELECT user_name
    FROM iosyscfg_users;
stop;
