Skip to content

Instantly share code, notes, and snippets.

Avatar

Scott Forstie forstie

View GitHub Profile
@forstie
forstie / Row level auditing.sql
Last active Feb 12, 2021
I was asked how to incorporate row level auditing detail into tables. While Temporal tables with Generated columns is a powerful combination, the following example demonstrates a different approach.
View Row level auditing.sql
--
-- I was asked how to incorporate row level auditing detail into tables.
-- While Temporal tables with Generated columns is a powerful combination,
-- the following example demonstrates a different approach.
--
-- One fun aspect about the solution is the use of INCLUDE SQL on the triggers...
--
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
@forstie
forstie / Protect the IFS root for *PUBLIC
Created Feb 8, 2021
When an IFS directory includes W (write), you are exposed to malware attacks. Use this to review and overcome this topic for the all important ROOT directory.
View Protect the IFS root for *PUBLIC
--
-- When an IFS directory includes W (write), you are exposed to malware attacks
-- Use this to review and overcome this topic for the all important ROOT directory
--
-- For help on this or related security topics, contact Robert and team...
-- http://ibm.biz/IBMiSecurity
-- Robert Andrews - robert.andrews@us.ibm.com
--
stop;
@forstie
forstie / Gist look at the library list
Created Jan 29, 2021
I was asked how object_statistics could be used with *LIBL and *USRLIBL to produce accurate and ordered results. Gist look at this...
View Gist look at the library list
--
-- Use NAMING(*SYS) - system naming mode to leverage the power of the library list
--
cl: crtlib lib1;
cl: crtlib lib3;
cl: crtlib lib2;
cl:CRTDUPOBJ OBJ(QSQPTABL) FROMLIB(QSYS2) OBJTYPE(*FILE) TOLIB(LIB1) DATA(*YES);
cl:CRTDUPOBJ OBJ(QSQPTABL) FROMLIB(QSYS2) OBJTYPE(*FILE) TOLIB(LIB2) DATA(*YES);
cl:CRTDUPOBJ OBJ(QSQPTABL) FROMLIB(QSYS2) OBJTYPE(*FILE) TOLIB(LIB3) DATA(*YES);
cl: addlible lib1;
@forstie
forstie / auditing a job queue
Last active Jan 8, 2021
I was asked, "how can you detemine which user held a job queue"? While there is more than one approach to answering this question, here's an example that leverages the secure audit journal log.
View auditing a job queue
-- To be able to audit holding of a job queue, you need to:
-- 1) Enable object auditing
-- 2) Configure object auditing for specific job queues
cl: CHGSYSVAL SYSVAL(QAUDCTL) VALUE('*AUDLVL *OBJAUD *NOQTEMP');
cl: CHGOBJAUD OBJ(QGPL/KIDDIEJOBQ) OBJTYPE(*JOBQ) OBJAUD(*CHANGE);
stop;
--
-- T-ZC audit journal entry:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzarl/rzarlf77.htm
@forstie
forstie / controlling adopted authority.sql
Last active Dec 10, 2020
If you use adopted authority, how do you avoid allowing code that you call from taking a free ride on your elevated authority? One answer lies within this gist...
View controlling adopted authority.sql
-- =========================================================================================
--
-- Adopted authority... if you use it, how do you control it?
--
-- =========================================================================================
-- Q: How can you avoid propagating authority to code you need to call?
--
-- A: MODIFY INVOCATION AUTHORITY ATTRIBUTES
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzatk/MODINVAU.htm
-- =========================================================================================
@forstie
forstie / Interactive Users and usage.sql
Created Dec 7, 2020
This gist shows a glimpse into the realm of what's possible when using SQL and IBM i Services to monitor and manage interactive users.
View Interactive Users and usage.sql
--
-- description: What are ALL my interactive users doing right now? (summary)
-- ====
--
select j.authorization_name as user_name, u.text_description as description,
count(*) as job_count
from qsys2.user_info as u
join table (
qsys2.active_job_info(
subsystem_list_filter => 'QINTER', detailed_info => 'ALL')
@forstie
forstie / Managing MSGW jobs.sql
Created Dec 4, 2020
For this gist, I was asked to provide a query that would find jobs that have been stuck on Message Wait (MSGW) status for > 90 minutes. There's a 3 part progression to reach the solution.
View Managing MSGW jobs.sql
--
-- Find active jobs (in QBATCH) that are in message wait status
--
with msgw_jobs (jn) as (
select job_name
from table (
qsys2.active_job_info(subsystem_list_filter => 'QBATCH')
) x
where job_status = 'MSGW'
)
@forstie
forstie / Well isnt that special.sql
Last active Dec 3, 2020
I was asked to provide a technique for the SQL user to access the special authorities granted to user and group profiles, and return the data in a non-list form for ease of reporting and analysis. To accomplish this request, I used the SYSTOOLS.SPLIT table function, but had to be careful to use the perfect split character (3 spaces), trim off bl…
View Well isnt that special.sql
--
-- Complete review of special authorities granted to user profiles
--
select user_name, ordinal_position, ltrim(element) as special_authority
from qsys2.user_info, table (
systools.split(rtrim(special_authorities), ' ')
) b
where user_name not in (select authorization_name
from qsys2.authids
where authorization_attr = 'GROUP');
@forstie
forstie / Library sizes and more.sql
Created Nov 27, 2020
With Db2 PTF Group SF99703 level 22 and Db2 PTF Group SF99704 level 10 (aka TR9 and TR3 timed enhancements), the LIBRARY_INFO UDTF has optional input paramters to provide better performing queries for library specific questions.
View Library sizes and more.sql
-- category: IBM i Services
-- description: Librarian - Library Info
-- minvrm: V7R3M0
--
create or replace variable coolstuff.library_report_stmt varchar(10000) for sbcs data default
'create or replace table coolstuff.library_sizes
(library_name, schema_name,
-- qsys2.library_info() columns
@forstie
forstie / Who am i.sql
Last active Jan 21, 2021
I was asked to provide a query that pulls together some of the basic identity detail for the IBM i you're connect to... which got me to this....
View Who am i.sql
--
-- Who am i?
--
select s.host_name, e.host_name, 'IBM i ' concat os_version concat '.' concat os_release as ibmi_level,
serial_number, machine_type, machine_model,
ipl_mode, ipl_type, attention_light,
total_cpus, e.configured_cpus,
configured_memory, total_memory,
s.*
from sysibmadm.env_sys_info e, qsys2.system_status_info s;