IBM i DB2 security - Scott's examples on create mask - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcrtmask.htm

attribute to restrict odbc and jdbc.sql

-- QIBM_DB_ZDA (restrict ODBC and JDBC Toolbox from the server side, including Run SQL Scripts, System i Navigator and DB2 specific portions of Systems Director Navigator for i) 

Authority Collection.sql

--Authority Collection - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzarl/rzarlautcolstart.htm

--https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cl/strautcol.htm
STRAUTCOL USRPRF(USER1) LIBINF(*ALL) OBJ(*ALL) OBJTYPE(*ALL)  OMITLIB((MYLIB1))

--https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cl/endautcol.htm
ENDAUTCOL   USER(USER1)

--View Data collected
SELECT * FROM QSYS2.AUTHORITY_COLLECTION 
    WHERE USER_NAME = 'USER1' AND 
    SYSTEM_OBJECT_NAME = 'PAYROLL'  AND SYSTEM_OBJECT_SCHEMA = ‘PAYLIB' AND 
    SYSTEM_OBJECT_TYPE = '*FILE'
    
--Analyze detailed_required_authority, detailed_current_authority to see where you may have had too much permissions to the object_name

--Review details of authorization failures
SELECT * 
FROM AUTHORITY_COLLECTION A 
WHERE AUTHORITY_CHECK_SUCCESSFUL = '0'

--Find what commands people are using SYSTEM_OBJECT_TYPE='*CMD'
SELECT * 
FROM AUTHORITY_COLLECTION A 
WHERE AUTHORITY_CHECK_SUCCESSFUL = '0' AND SYSTEM_OBJECT_TYPE='*CMD'

create mask.sql

CREATE MASK SSN_MASK ON EMPLOYEE
   FOR COLUMN SSN RETURN
      CASE
         WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'PAYROLL') = 1)
            THEN SSN
         WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'MGR') = 1)
            THEN 'XXX-XX-' || SUBSTR(SSN,8,4)
         ELSE NULL
      END
   ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
   ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SSN FROM EMPLOYEE
   WHERE EMPNO = 123456;
SET SESSION AUTHORIZATION = testmgr;
select user,ssn from toystore5.employee;
SET SESSION AUTHORIZATION = joeemployee;
select user,ssn from toystore5.employee;

create permissions.sql

CREATE PERMISSION SALARY_ROW_ACCESS ON EMPLOYEE
   FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER,'MGR','ACCOUNTING') = 1
                  AND
                  ACCOUNTING_UDF(SALARY) < 120000
   ENFORCED FOR ALL ACCESS
   ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
ACTIVATE ROW ACCESS CONTROL;

COMMIT;

SELECT SALARY FROM EMPLOYEE
   WHERE EMPNO = 123456;

display_journal .sql

-- https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM+i+Technology+Updates/page/DISPLAY_JOURNAL+%28easier+searches+of+Audit+Journal%29
SELECT * FROM TABLE ( DISPLAY_JOURNAL( 'TESTLIB', 'QSQJRN')) AS JT;
SELECT journal_code, journal_entry_type, object, object_type, X.* 
FROM TABLE (
  QSYS2.Display_Journal(
            'PRODDATA', 'QSQJRN', -- Journal library and name
            OBJECT_LIBRARY=>'PRODDATA', OBJECT_NAME=>'SALES',
            OBJECT_OBJTYPE=>'*FILE', OBJECT_MEMBER=>'SALES' 
    ) ) AS X
WHERE journal_entry_type in ('DL', 'PT', 'PX', 'UP') AND "CURRENT_USER" = 'SUPERUSER'
ORDER BY entry_timestamp DESC  

set column attribute SECURE YES.sql

CALL SYSPROC.SET_COLUMN_ATTRIBUTE('TOYSTORE', 'EMPLOYEE', 'SSN', 'SECURE YES');