--
-- Securing your System without Breaking your Business
-- Companion SQL Script
-- May 2026
-- Presenters: Steve Riedmueller Carol Woodbury
--
-- Sample SQL (provided 'as is') 
--
stop;

--
-- Authority Collection: Display results of the objects DEVELOPER needs access to
--
SELECT DISTINCT authorization_name,
                system_object_name,
                system_object_schema,
                system_object_type,
                detailed_required_authority,
                current_authority,
                authority_source,
                current_adopted_authority
    FROM qsys2.authority_collection
    WHERE authorization_name = 'DEVELOPER'
          AND current_adopted_authority IS null
          AND check_any_authority = '0';
stop;

--
-- Authority Collection: Display the profiles accessing the ALLOBJUSRS file
--
SELECT DISTINCT authorization_name,
                system_object_name,
                detailed_required_authority,
                authority_source
    FROM qsys2.authority_collection_libraries
    WHERE system_object_name = 'ALLOBJUSRS'
          AND adopting_program_owner IS null
          AND adopt_authority_used = 0
          AND check_any_authority = '0'
          AND authorization_name IS NOT null
          order by authority_source;
stop;
-----------------------------------------------------------------------------------
--  category:  IBM i Services
--  description:  Security - Review *ALLOBJ users (Sample SQL from Examples->Insert from Examples in Run SQL Script)
--
-- Which users have *ALLOBJ authority either directly
-- or via a Group or Supplemental profile?
-- To find profiles with other special authorities, replace  all instances of *ALLOBJ with the other value, e.g., *IOSYSCFG
--
SELECT AUTHORIZATION_NAME,
       STATUS,
       NO_PASSWORD_INDICATOR,
       PREVIOUS_SIGNON,
       TEXT_DESCRIPTION
    FROM QSYS2.USER_INFO
    WHERE SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%'
          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 '%*ALLOBJ%'))
    ORDER BY AUTHORIZATION_NAME;
stop;

--
-- Bonus SQL:
-- description: What's the Default Access for the QIBM_ACCESS_ALLOBJ_JOBLOG function?
--
SELECT default_usage, allobj_indicator, function_name_message_text
  FROM qsys2.function_info
  WHERE function_id = 'QIBM_ACCESS_ALLOBJ_JOBLOG';
stop;

-- 
-- Bonus SQL:
-- description: Who is allowed to use the new QIBM_ACCESS_ALLOBJ_JOBLOG function usage?  
--
select * from qsys2.function_usage where function_id = 'QIBM_ACCESS_ALLOBJ_JOBLOG';
stop;
-----------------------------------------------------------------------------------
--
--  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
-- Preview YES
--
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 (action WILL BE TAKEN)
--
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;

-- Bonus: Add a job schedule entry to automate the disabling of user profiles
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;

--
--  Bonus: Note: Profiles WILL BE DELETED when this SQL is run!!!
-- 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;


-----------------------------------------------------------------------------------------------------------------------
--
-- Get more information on an authority failure (AF) that occurred during a specific timeframe
--
SELECT user_name,
       program_library,
       program_name,
       violation_type_detail,
       object_library,
       object_name,
       object_type,
       path_name
    FROM TABLE (
            SYSTOOLS.AUDIT_JOURNAL_AF(
                STARTING_TIMESTAMP => '2026-03-29 13:00:00',
                ENDING_TIMESTAMP => '2026-03-29 14:59:59')
        );
 stop;

-- 
-- 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;

--
--  What jobs did a specific profile run (in this example - CWOODBURY)?
--
SELECT entry_timestamp,
       user_name,
       qualified_job_name,
       remote_address,
       entry_type,
       entry_type_detail,
       job_type,
       job_type_basic,
       job_subtype
    FROM TABLE (
            systools.audit_journal_js(starting_timestamp => CURRENT TIMESTAMP - 7 DAYS)
        )
    WHERE user_name = 'CWOODBURY'
    ORDER BY entry_timestamp;
 stop;

--
-- Find users of ODBC
--
SELECT DISTINCT user_name 
    FROM TABLE (
            systools.audit_journal_GR(starting_timestamp => CURRENT TIMESTAMP - 7 DAYS)
        )
    WHERE program_name LIKE 'QZDAS%';
stop;


--
-- Find unsecured connections - specifically unsecured telnet (connections to port 23)
--
SELECT DISTINCT remote_address,
                local_port
    FROM TABLE (
            systools.audit_journal_sk(starting_timestamp => CURRENT TIMESTAMP - 7 DAYS)
        )
    WHERE local_port = '23';
stop;

--
-- Find the protocol and encryption algorithm being used 
--
SELECT remote_address,
       secure_version,
       secure_properties
    FROM TABLE (
            systools.audit_journal_sk(starting_timestamp => CURRENT TIMESTAMP - 7 DAYS)
        )
    WHERE entry_type = 'S';
stop;

--
-- Establish and update an Audit Journal Data Mart for the SK entry type
--
-- Create the SK data mart
CALL qsys2.manage_audit_journal_data_mart(
  journal_entry_type => 'SK',
  data_mart_library  => 'YOUR_LIB', 
  starting_timestamp => '*FIRST', 
  ending_timestamp   => default,
  data_mart_action   => 'CREATE');
  stop;

-- Add new entries to the SK data mart
CALL qsys2.manage_audit_journal_data_mart(
  journal_entry_type => 'SK',
  data_mart_library => 'YOUR_LIB', 
  starting_timestamp => '*CONTINUE', 
  ending_timestamp => default,
  data_mart_action => 'ADD');
stop;


----------------------------------------------------------------------------------------------------------------------
--
-- 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;
 
--
-- To determine who's using a file share in IBM i 7.4 and 7.5, configure Authority Collection on the object being shared
-- This enhanced/simplified Authority Collection view added in IBM i 7.6 and IBM i 7.5 TR6
--
cl:CHGAUTCOL OBJ('/HOME/TESTNAV/') AUTCOLVAL(*OBJINF);
CL:STRAUTCOL TYPE(*OBJAUTCOL);
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;


--
-- Check which user profiles do not yet have a QPWDLVL 3 (or 4) password stored
--
SELECT authorization_name, password_change_date, text_description
    FROM qsys2.user_info_basic
    WHERE no_password_indicator = 'NO'
          AND password_level_2_3 ='NO';

SELECT authorization_name, password_change_date, text_description
    FROM qsys2.user_info_basic
    WHERE no_password_indicator = 'NO'
          AND password_level_4 = 'NO';
stop;

------------------------------------
