|
-- |
|
-- Subject: Seeing programs that are configured to use adopted authority |
|
-- Author: Scott Forstie |
|
-- Date : January, 2025 |
|
-- |
|
-- The request... |
|
-- I don’t find any examples for reporting on adopted authority. |
|
-- Specifically, we would like to know what pgms in non-IBM libraries use adopted authority. |
|
-- |
|
-- Features Used : This Gist uses object_statistics, object_privileges, and program_info |
|
-- |
|
-- |
|
-- Resources: |
|
-- https://www.ibm.com/docs/en/i/7.5?topic=words-reserved-schema-names |
|
-- https://www.ibm.com/docs/en/i/7.5?topic=services-object-statistics-table-function |
|
-- https://www.ibm.com/docs/en/i/7.5?topic=services-program-info-view |
|
-- |
|
stop; |
|
|
|
-- |
|
-- Which libraries are considered non-user libraries? (not the best solution) |
|
-- |
|
-- Note that Db2 for i libraries such as QSYS2, SYSIBM, SYSIBMADM, etc are not included in this non-user library listing |
|
-- In other words.... *ALLUSR returns some libraries that would be considered... IBM libraries |
|
-- |
|
SELECT a.objname AS ibm_library, a.* |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) a |
|
LEFT EXCEPTION JOIN TABLE ( |
|
qsys2.object_statistics('*ALLUSR', '*LIB') |
|
) b |
|
ON a.objname = b.objname |
|
order by a.objname; |
|
stop; |
|
|
|
-- |
|
-- Which libraries are considered non-user libraries? (better solution) |
|
-- |
|
SELECT a.objname AS ibm_library, a.* |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) a |
|
WHERE a.objname LIKE 'Q%' OR a.objname LIKE 'SYS%'; |
|
stop; |
|
|
|
-- |
|
-- Who defined the non-user libraries? |
|
-- |
|
-- Note: Ideally, we'd see only QLPINSTALL, *IBM, QSYS, QSECOFR, |
|
-- and other Qxxxx operating system names here |
|
-- |
|
SELECT objdefiner, count(*) as library_count |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) |
|
WHERE objname LIKE 'Q%' or objname LIKE 'SYS%' |
|
group by objdefiner |
|
order by library_count desc; |
|
stop; |
|
|
|
-- |
|
-- Who owns the non-user libraries? |
|
-- |
|
-- Note: Ideally, we'd see only QLPINSTALL, *IBM, QSYS, QSECOFR, |
|
-- and other Qxxxx operating system names here |
|
-- |
|
SELECT objowner, count(*) as library_count |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) |
|
WHERE objname LIKE 'Q%' or objname LIKE 'SYS%' |
|
group by objowner |
|
order by library_count desc; |
|
stop; |
|
|
|
-- |
|
-- What are the user library names? |
|
-- |
|
SELECT a.objname AS lib_name, a.* |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) a |
|
WHERE a.objname not LIKE 'Q%' and a.objname not LIKE 'SYS%'; |
|
stop; |
|
|
|
-- |
|
-- Who defined the user libraries? |
|
-- |
|
SELECT objdefiner, count(*) as library_count |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) |
|
WHERE objname not LIKE 'Q%' and objname not LIKE 'SYS%' |
|
group by objdefiner |
|
order by library_count desc; |
|
stop; |
|
|
|
-- |
|
-- Where is adopted authority configured to be used, |
|
-- across all user libraries? |
|
-- |
|
WITH user_libs (lib_name) AS ( |
|
SELECT objname |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) |
|
WHERE objname NOT LIKE 'Q%' |
|
AND objname NOT LIKE 'SYS%' |
|
) |
|
SELECT PROGRAM_LIBRARY, PROGRAM_NAME, OBJECT_TYPE, PROGRAM_TYPE, |
|
USE_ADOPTED_AUTHORITY, PROGRAM_OWNER, USER_PROFILE |
|
FROM user_libs, LATERAL ( |
|
SELECT * |
|
FROM qsys2.program_info |
|
WHERE PROGRAM_LIBRARY = lib_name |
|
AND user_profile = '*OWNER' |
|
) |
|
ORDER BY PROGRAM_LIBRARY, PROGRAM_NAME; |
|
stop; |
|
-- |
|
-- Or if you prefer classic join over lateral join... |
|
-- |
|
WITH user_libs (lib_name) AS ( |
|
SELECT objname |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) |
|
WHERE objname NOT LIKE 'Q%' |
|
AND objname NOT LIKE 'SYS%' |
|
) |
|
SELECT PROGRAM_LIBRARY, PROGRAM_NAME, OBJECT_TYPE, PROGRAM_TYPE, |
|
USE_ADOPTED_AUTHORITY, PROGRAM_OWNER, USER_PROFILE |
|
FROM user_libs, qsys2.program_info |
|
WHERE PROGRAM_LIBRARY = lib_name |
|
AND user_profile = '*OWNER' |
|
ORDER BY PROGRAM_LIBRARY, PROGRAM_NAME; |
|
stop; |
|
|
|
-- |
|
-- Where is adopted authority configured to be used, |
|
-- across all user libraries, where the library and |
|
-- program are configured to have *PUBLIC *USE? |
|
-- |
|
WITH user_libs (lib_name) AS ( |
|
SELECT objname |
|
FROM TABLE ( |
|
qsys2.object_statistics('*ALL', '*LIB') |
|
) a |
|
INNER JOIN qsys2.object_privileges b |
|
ON 'QSYS' = b.SYSTEM_OBJECT_SCHEMA |
|
AND object_name = b.SYSTEM_OBJECT_NAME |
|
AND '*LIB' = b.object_type |
|
WHERE objname NOT LIKE 'Q%' |
|
AND objname NOT LIKE 'SYS%' |
|
-- *PUBLIC has *USE (or higher) privilege to the library |
|
AND b.user_name = '*PUBLIC' |
|
AND object_operational = 'YES' |
|
AND data_read = 'YES' |
|
AND data_execute = 'YES' |
|
), |
|
adopting_pgms (lib_name, pgm_name, obj_type) AS ( |
|
SELECT PROGRAM_LIBRARY, PROGRAM_NAME, OBJECT_TYPE |
|
FROM user_libs, LATERAL ( |
|
SELECT * |
|
FROM qsys2.program_info |
|
WHERE (PROGRAM_LIBRARY = lib_name |
|
AND user_profile = '*OWNER') |
|
) |
|
ORDER BY PROGRAM_LIBRARY, PROGRAM_NAME) |
|
SELECT lib_name, pgm_name, obj_type, b.* |
|
FROM adopting_pgms a |
|
INNER JOIN qsys2.object_privileges b |
|
ON a.lib_name = b.SYSTEM_OBJECT_SCHEMA |
|
AND a.pgm_name = b.SYSTEM_OBJECT_NAME |
|
AND a.obj_type = b.object_type |
|
-- *PUBLIC has *USE (or higher) privilege to the program |
|
WHERE b.user_name = '*PUBLIC' |
|
AND object_operational = 'YES' |
|
AND data_read = 'YES' |
|
AND data_execute = 'YES'; |
|
stop; |
Hi Scott, great work. I'm letting you know that there's an error in your last query. You used the field obj_name which doesn't exist in the table qsys2.object_privileges. I've corrected the query with the correct column name, object_name, and it's working perfectly.