Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A client asked how they could identify which objects were set to *PUBLIC *EXCLUDE (good!), but where the object owner also had *EXCLUDE (not so good). Herein lies one solution.
--
-- Subject: Owners typically have *ALL authority to objects. This gist shows how to find those objects that exclude the owner.
-- As an added bonus the 2nd form of the query shows how to re-establish *ALL object authority for the object owner.
-- Author: Scott Forstie
-- Date : May 2, 2022
-- Features Used : This Gist uses OBJECT_PRIVILEGES
--
--
-- To run, change TOYSTORE to the library of your choice
-- To run against all libraries, remove the TOYSTORE predicate completely
--
with owners_with_exclude (lib, obj, objt, user_name) as (
select SYSTEM_OBJECT_SCHEMA,
OBJECT_NAME,
OBJECT_TYPE,
user_name
from QSYS2.OBJECT_PRIVILEGES
where SYSTEM_OBJECT_SCHEMA = 'TOYSTORE' and
object_owner = 'YES' and
object_authority = '*EXCLUDE'
)
select *
from owners_with_exclude
where exists (select SYSTEM_OBJECT_SCHEMA,
OBJECT_NAME,
OBJECT_TYPE,
user_name
from QSYS2.OBJECT_PRIVILEGES
where SYSTEM_OBJECT_SCHEMA = lib and
object_name = obj and
object_type = objt and
object_authority = '*EXCLUDE');
stop;
--
-- To give object owners their *ALL object authority back, this technique could be used
--
with owners_with_exclude (lib, obj, objt, user_name) as (
select SYSTEM_OBJECT_SCHEMA,
OBJECT_NAME,
OBJECT_TYPE,
user_name
from QSYS2.OBJECT_PRIVILEGES
where SYSTEM_OBJECT_SCHEMA = 'TOYSTORE' and
object_owner = 'YES' and
object_authority = '*EXCLUDE'
)
select lib, obj, objt, user_name,
qsys2.qcmdexc('GRTOBJAUT OBJ(' concat lib concat '/' concat obj concat ') OBJTYPE(' concat objt concat ') USER(' concat user_name concat ') AUT(*ALL)')
from owners_with_exclude
where exists (select SYSTEM_OBJECT_SCHEMA,
OBJECT_NAME,
OBJECT_TYPE,
user_name
from QSYS2.OBJECT_PRIVILEGES
where SYSTEM_OBJECT_SCHEMA = lib and
object_name = obj and
object_type = objt and
object_authority = '*EXCLUDE');
stop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment