Skip to content

Instantly share code, notes, and snippets.

@forstie
Forked from phpdave/Authority Collection.sql
Created June 15, 2019 05:51
Show Gist options
  • Save forstie/84474e86589f5932784f1b7a3a02a22e to your computer and use it in GitHub Desktop.
Save forstie/84474e86589f5932784f1b7a3a02a22e to your computer and use it in GitHub Desktop.
IBM i DB2 security - Scott's examples on create mask - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcrtmask.htm
-- 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 - 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 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 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;
-- 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
CALL SYSPROC.SET_COLUMN_ATTRIBUTE('TOYSTORE', 'EMPLOYEE', 'SSN', 'SECURE YES');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment