Author: Roy Boxwell

Which security values and security settings should never be left as default?

Let’s list your ROLES and Authorization IDs with some SQL queries and check the security ZPARMs

To complete this two part series, I want to do a deep dive today, down into the security innards of DB2, and to wrap up with a review of default values that can cause security concerns. A long time ago ROLEs were introduced, and, as you saw in the last newsletter, nearly all OWNERs have an associated indicator of whether they are a Role or an Authorization Id. With Roles came Trusted Contexts, and I hope that all of you out there using remote access have set up a whole bunch of Trusted Contexts.

Part 1 proposes a review of any and all GRANTs on the DB2 Directory and Catalog tables. Is your DB2 Catalog opened with a PUBLIC grant? Do you know how your DB2 z/OS System is looking on the security side ?

Role playing

To start this time, we will review the Roles that you have currently defined:

SELECT NAME
     , DEFINER
     , CASE DEFINERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DEFINERTYPE
 FROM SYSIBM.SYSROLES
 ORDER BY 1 , 2
 FETCH FIRST 50 ROWS ONLY
 WITH UR
 ;

If this finds no rows, then that is either brilliant – you have no dynamic access – or pretty bad – you are not using Roles.

Having found your list of Roles, make sure that they are all allowed and correct. Remove any that are not supposed to be there!

It’s all a matter of context

Now the three Trusted Context Tables are to be queried:

SELECT ENABLED
 , NAME
 , CONTEXTID
 , DEFINER
 , CASE DEFINERTYPE
 WHEN 'L' THEN 'ROLE   '
 WHEN ' ' THEN 'AUTH ID'
 ELSE          'UNKNOWN'
 END        AS DEFINERTYPE
 , DEFAULTROLE
 , CASE OBJECTOWNERTYPE
 WHEN 'L' THEN 'ROLE   '
 WHEN ' ' THEN 'AUTH ID'
 ELSE          'UNKNOWN'
 END        AS OBJECTOWNERTYPE
 , ALLOWPUBLIC
 , AUTHENTICATEPUBLIC
 , DEFAULTSECURITYLABEL
 FROM SYSIBM.SYSCONTEXT
 ORDER BY 3
 FETCH FIRST 50 ROWS ONLY
 WITH UR
 ;

This query just shows you what you have at the moment and whether or not it is active, plus the basic security information. The next two queries list out the actual details:

SELECT CONTEXTID
     , AUTHID 
     , AUTHENTICATE
     , ROLE
     , SECURITYLABEL
FROM SYSIBM.SYSCONTEXTAUTHIDS
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;
SELECT CONTEXTID
     , NAME
     , VALUE
FROM SYSIBM.SYSCTXTTRUSTATTRS
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Note that the key is always the CONTEXTID. The NAME and VALUE pair is where you actually find the details of how a Trusted Context is defined. Again, check and validate that all these values – and especially the IP addresses – are still valid.

Do I have your Permission?

Masks and Permissions came in a while ago but haven’t gained much traction in the DB2 user community yet. However, here is an SQL to show you what you do have:

SELECT ENABLE
     , CASE CONTROL_TYPE
       WHEN 'R' THEN 'ROW PERMISSION'
       WHEN 'M' THEN 'COLUMN MASK   '
       ELSE          'UNKNOWN       '
       END        AS CONTROL_TYPE
     , SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSCONTROLS
ORDER BY 2 , 3 , 4
FETCH FIRST 50 ROWS ONLY
WITH UR
;

You can then easily see whether or not the permission/mask is active, or not.

SECADM to the rescue

If running with SECADM and Audit Policies, then this little query will show you what is going on:

SELECT AUDITPOLICYNAME
     , OBJECTSCHEMA
     , OBJECTNAME
     , CASE OBJECTTYPE
       WHEN 'C' THEN 'CLONE TABLE                  '
       WHEN 'P' THEN 'IMPLICIT TABLE FOR XML COLUMN'
       WHEN 'T' THEN 'TABLE                        '
       WHEN ' ' THEN 'XML, CLONE OR TABLE          '
       ELSE          'ERROR AT POLICY START TIME   '
       END AS OBJECTTYPE
     , CREATEDTS
     , ALTEREDTS
     , CASE DB2START
       WHEN 'Y' THEN 'STARTED AT DB2 START                '
       WHEN 'S' THEN 'STARTED AT DB2 START. SECADM TO STOP'
       WHEN 'N' THEN 'POLICY NOT STARTED AT DB2 START     '
       ELSE          'UNKNOWN                             '
       END AS DB2START
     , CASE CHECKING
       WHEN 'A' THEN 'AUDIT ALL FAILURES        '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS CHECKING
     , CASE VALIDATE
       WHEN 'A' THEN 'AUDIT ALL FAILURES        '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS VALIDATE
     , CASE OBJMAINT
       WHEN 'A' THEN 'AUDIT ALTER/DROP          '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS OBJMAINT
     , CASE EXECUTE
       WHEN 'A' THEN 'AUDIT FIRST ACCESS        '
       WHEN 'C' THEN 'AUDIT FIRST UPDATE        '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS EXECUTE
     , CASE CONTEXT
       WHEN 'A' THEN 'AUDIT ALL UTILITIES       '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS CONTEXT
     , CASE SECMAINT
       WHEN 'A' THEN 'AUDIT ALL                 '
       WHEN ' ' THEN 'AUDIT NONE                '
       ELSE          'ERROR AT POLICY START TIME'
       END AS SECMAINT
     , SYSADMIN
     , DBADMIN
     , DBNAME
     , COLLID
FROM SYSIBM.SYSAUDITPOLICIES
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Now you can see what is happening with your policies, and are they working as designed?

Are you a ROLE model?

The next large set of queries returns *all* AUTH relevant DB2 catalog data from any DB2 Table that has any possible link to a ROLE:

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN 'P' THEN 'PACKAGE'
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , CREATOR
     , TNAME AS NAME
     , COLNAME
     , CASE PRIVILEGE
       WHEN 'R' THEN 'REFERENCES'
       WHEN ' ' THEN 'UPDATE    '
       ELSE          'UNKNOWN   '
       END AS PRIVILEGE
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSCOLAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR , NAME, COLNAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'C' THEN 'DBCTRL    '
       WHEN 'D' THEN 'DBADM     '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'M' THEN 'DBMAINT   '
       WHEN 'S' THEN 'SYSADM    '
       WHEN ' ' THEN 'N/A       '
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , CREATETABAUTH
     , CREATETSAUTH
     , DBADMAUTH
     , DBCTRLAUTH
     , DBMAINTAUTH
     , DISPLAYDBAUTH
     , DROPAUTH
     , IMAGCOPYAUTH
     , LOADAUTH
     , REORGAUTH
     , RECOVERDBAUTH
     , REPAIRAUTH
     , STARTDBAUTH
     , STATSAUTH
     , STOPAUTH
FROM SYSIBM.SYSDBAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN 'P' THEN 'PLAN   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , COLLID
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'A' THEN 'PACKADM (COLLECTION *)    '
       WHEN 'C' THEN 'DBCTRL                    '
       WHEN 'D' THEN 'DBADM                     '
       WHEN 'E' THEN 'SECADM                    '
       WHEN 'G' THEN 'ACCESSCTRL                '
       WHEN 'L' THEN 'SYSCTRL                   '
       WHEN 'M' THEN 'DBMAINT                   '
       WHEN 'P' THEN 'PACKADM (NOT COLLECTION *)'
       WHEN 'S' THEN 'SYSADM                    '
       WHEN 'T' THEN 'DATAACCESS                '
       WHEN ' ' THEN 'N/A                       '
       ELSE          'UNKNOWN                   '
       END AS AUTHHOWGOT
     , BINDAUTH
     , COPYAUTH
     , EXECUTEAUTH
FROM SYSIBM.SYSPACKAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'C' THEN 'DBCTRL    '
       WHEN 'D' THEN 'DBADM     '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'M' THEN 'DBMAINT   '
       WHEN 'S' THEN 'SYSADM    '
       WHEN ' ' THEN 'N/A       '
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , BINDAUTH
     , EXECUTEAUTH
FROM SYSIBM.SYSPLANAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , QUALIFIER
     , NAME
     , CASE AUTHHOWGOT
       WHEN 'A' THEN 'PACKADM (COLLECTION *)    '
       WHEN 'C' THEN 'DBCTRL                    '
       WHEN 'D' THEN 'DBADM                     '
       WHEN 'E' THEN 'SECADM                    '
       WHEN 'G' THEN 'ACCESSCTRL                '
       WHEN 'L' THEN 'SYSCTRL                   '
       WHEN 'M' THEN 'DBMAINT                   '
       WHEN 'P' THEN 'PACKADM (NOT COLLECTION *)'
       WHEN 'S' THEN 'SYSADM                    '
       WHEN 'T' THEN 'DATAACCESS                '
       WHEN ' ' THEN 'N/A                       '
       ELSE          'UNKNOWN                   '
       END AS AUTHHOWGOT
     , CASE OBTYPE
       WHEN 'B' THEN 'BUFFER POOL  '
       WHEN 'C' THEN 'COLLECTION   '
       WHEN 'D' THEN 'DISTINCT TYPE'
       WHEN 'R' THEN 'TABLE SPACE  '
       WHEN 'S' THEN 'STORAGE GROUP'
       WHEN 'J' THEN 'JAR FILE     '
       ELSE          'UNKNOWN      '
       END        AS OBJECT_TYPE
     , USEAUTH
FROM SYSIBM.SYSRESAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY QUALIFIER, NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE             '
       WHEN 'P' THEN 'PLAN/PACKAGE     '
       WHEN 'R' THEN 'INTERNAL USE ONLY'
       WHEN ' ' THEN 'AUTH ID          '
       ELSE          'UNKNOWN          '
       END AS GRANTEETYPE
     , SCHEMA       AS CREATOR
     , SPECIFICNAME AS NAME
     , CASE ROUTINETYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION'
       WHEN 'P' THEN 'STORED PROCEDURE    '
       ELSE          'UNKNOWN             '
       END        AS ROUTINETYPE
     , CASE AUTHHOWGOT     
       WHEN '1' THEN 'GRANTOR SCHEMA.* AT TIME'
       WHEN 'E' THEN 'SECADM                  '
       WHEN 'G' THEN 'ACCESSCTRL              '
       WHEN 'L' THEN 'SYSCTRL                 '
       WHEN 'S' THEN 'SYSADM                  '
       WHEN 'T' THEN 'DATAACCESS              '
       WHEN ' ' THEN 'N/A                     '
       ELSE          'UNKNOWN                 '
       END AS AUTHHOWGOT
     , EXECUTEAUTH
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSROUTINEAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR , NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTEETYPE
     , SCHEMANAME
     , CASE AUTHHOWGOT
       WHEN '1' THEN 'GRANTOR SCHEMA.* AT TIME'
       WHEN 'E' THEN 'SECADM                  '
       WHEN 'G' THEN 'ACCESSCTRL              '
       WHEN 'L' THEN 'SYSCTRL                 '
       WHEN 'S' THEN 'SYSADM                  '
       ELSE          'UNKNOWN                 '
       END AS AUTHHOWGOT
     , CREATEINAUTH
     , ALTERINAUTH
     , DROPINAUTH
FROM SYSIBM.SYSSCHEMAAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY SCHEMANAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE             '
       WHEN 'P' THEN 'PLAN/PACKAGE     '
       WHEN 'R' THEN 'INTERNAL USE ONLY'
       WHEN ' ' THEN 'AUTH ID          '
       ELSE          'UNKNOWN          '
       END AS GRANTEETYPE
     , SCHEMA AS CREATOR
     , NAME
     , CASE AUTHHOWGOT
       WHEN ' ' THEN 'N/A       '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'S' THEN 'SYSADM    '
       WHEN 'T' THEN 'DATAACCESS'
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , ALTERAUTH
     , USEAUTH
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSSEQUENCEAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR, NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE        '
       WHEN 'P' THEN 'PLAN/PACKAGE'
       WHEN ' ' THEN 'AUTH ID     '
       ELSE          'UNKNOWN     '
       END AS GRANTEETYPE
     , DBNAME
     , TCREATOR AS CREATOR
     , TTNAME   AS NAME
     , CASE AUTHHOWGOT
       WHEN ' ' THEN 'N/A         '
       WHEN 'B' THEN 'SYSTEM DBADM'
       WHEN 'C' THEN 'DBCTRL      '
       WHEN 'E' THEN 'SECADM      '
       WHEN 'G' THEN 'ACCESSCTRL  '
       WHEN 'K' THEN 'SQLADM      '
       WHEN 'L' THEN 'SYSCTRL     '
       WHEN 'M' THEN 'DBMAINT     '
       WHEN 'S' THEN 'SYSADM      '
       WHEN 'T' THEN 'DATAACCESS  '
       ELSE          'UNKNOWN     '
       END AS AUTHHOWGOT
     , ALTERAUTH
     , DELETEAUTH
     , INDEXAUTH
     , INSERTAUTH
     , SELECTAUTH
     , UPDATEAUTH
     , REFERENCESAUTH
     , TRIGGERAUTH
     , UPDATECOLS
     , REFCOLS
     , COLLID
     , HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSTABAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY CREATOR, NAME, GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT GRANTOR
     , CASE GRANTORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS GRANTORTYPE
     , GRANTEE
     , CASE GRANTEETYPE
       WHEN 'L' THEN 'ROLE        '
       WHEN 'P' THEN 'PLAN/PACKAGE'
       WHEN ' ' THEN 'AUTH ID     '
       ELSE          'UNKNOWN     '
       END AS GRANTEETYPE
     , CASE AUTHHOWGOT
       WHEN ' ' THEN 'N/A       '
       WHEN 'C' THEN 'DBCTRL    '
       WHEN 'E' THEN 'SECADM    '
       WHEN 'G' THEN 'ACCESSCTRL'
       WHEN 'K' THEN 'SQLADM    '
       WHEN 'L' THEN 'SYSCTRL   '
       WHEN 'M' THEN 'DBMAINT   '
       WHEN 'O' THEN 'SYSOPR    '
       WHEN 'S' THEN 'SYSADM    '
       ELSE          'UNKNOWN   '
       END AS AUTHHOWGOT
     , BINDADDAUTH
     , BSDSAUTH
     , CREATEDBAAUTH
     , CREATEDBCAUTH
     , CREATESGAUTH
     , DISPLAYAUTH
     , RECOVERAUTH
     , STOPALLAUTH
     , STOSPACEAUTH
     , SYSADMAUTH
     , SYSOPRAUTH
     , TRACEAUTH
     , MON1AUTH
     , MON2AUTH
     , CREATEALIASAUTH
     , SYSCTRLAUTH
     , BINDAGENTAUTH
     , ARCHIVEAUTH
     , CREATETMTABAUTH
     , DEBUGSESSIONAUTH
     , EXPLAINAUTH
     , SQLADMAUTH
     , SDBADMAUTH
     , DATAACCESSAUTH
     , ACCESSCTRLAUTH
     , CREATESECUREAUTH
FROM SYSIBM.SYSUSERAUTH
WHERE NOT GRANTOR = GRANTEE
ORDER BY GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;

It’s a pretty good idea to modify all of the above with an appropriate GRANTOR = or GRANTEE = predicate, as otherwise there is too much data.

You can DEPend on me!

Nearly all objects in DB2 have a dependency on another object. All of this data is stored in the xxxxxDEP tables. Some of them also have ROLE based data that could well be of interest to the security minded Person:

SELECT BSCHEMA
     , BNAME
     , BTYPE
     , DTBCREATOR AS CREATOR
     , DTBNAME    AS NAME
     , DTBOWNER   AS OWNER
     , CASE OWNERTYPE
       WHEN 'R' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSCONSTDEP
ORDER BY DTBCREATOR , DTBNAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT BSCHEMA   AS CREATOR
     , BNAME      AS NAME
     , BCOLNAME   AS COLUMN_NAME
     , CASE BTYPE
       WHEN 'A' THEN 'ALIAS                   '
       WHEN 'C' THEN 'COLUMN                  '
       WHEN 'F' THEN 'FUNCTION                '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE  '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'Q' THEN 'SEQUENCE                '
       WHEN 'S' THEN 'SYNONYM                 '
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD      '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD    '
       ELSE          'UNKNOWN                 '
       END        AS TYPE
     , BOWNER     AS OWNER
     , CASE BOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS BOWNERTYPE
     , DSCHEMA    AS DEP_CREATOR
     , DNAME      AS DEP_NAME
     , DCOLNAME   AS DEP_COLUMN_NAME
     , CASE DTYPE
       WHEN 'C' THEN 'GENERATED COLUMN'
       WHEN 'F' THEN 'FUNCTION        '
       WHEN 'I' THEN 'INDEX           '
       WHEN 'X' THEN 'ROW PERMISSION  '
       WHEN 'Y' THEN 'COLUMN MASK     '
       ELSE          'UNKNOWN         '
       END        AS DEP_TYPE
     , DOWNER     AS DEP_OWNER
     , CASE DOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DEP_OWNERTYPE
FROM SYSIBM.SYSDEPENDENCIES
ORDER BY 1 , 2 , 3 , 5 , 7 , 8 , 9 , 11
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT ROLENAME
     , DEFINER
     , CASE DEFINERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DEFINERTYPE
     , DSCHEMA    AS CREATOR
     , DNAME      AS NAME
     , CASE DTYPE
       WHEN 'A' THEN 'ALIAS                   '
       WHEN 'B' THEN 'TRIGGER                 '
       WHEN 'D' THEN 'DATABASE                '
       WHEN 'E' THEN 'DISTINCT TYPE           '
       WHEN 'F' THEN 'USER-DEFINED FUNCTION   '
       WHEN 'I' THEN 'INDEX                   '
       WHEN 'J' THEN 'JAR FILE                '
       WHEN 'L' THEN 'ROLE                    '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'N' THEN 'TRUSTED CONTEXT         '
       WHEN 'O' THEN 'STORED PROCEDURE        '
       WHEN 'Q' THEN 'SEQUENCE              '
       WHEN 'R' THEN 'TABLE SPACE             '
       WHEN 'S' THEN 'STORAGE GROUP           '
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'X' THEN 'ROW PERMISSION          '
       WHEN 'Y' THEN 'COLUMN MASK             '
       ELSE          'UNKNOWN                 '
       END        AS TYPE
FROM SYSIBM.SYSOBJROLEDEP
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT BQUALIFIER AS CREATOR
     , BNAME      AS NAME
    , CASE BTYPE
       WHEN 'A' THEN 'ALIAS                      '
       WHEN 'B' THEN 'BUSINESS_TIME              '
       WHEN 'C' THEN 'SYSTEM_TIME                '
       WHEN 'F' THEN 'UDF OR CAST FUNCTION       '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE     '
       WHEN 'I' THEN 'INDEX                      '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE   '
       WHEN 'O' THEN 'STORED PROCEDURE           '
       WHEN 'P' THEN 'LARGE OR DSSIZE PART. SPACE'
       WHEN 'Q' THEN 'SEQUENCE OBJECT            '
       WHEN 'R' THEN 'TABLESPACE                 '
       WHEN 'S' THEN 'SYNONYM                    '
       WHEN 'T' THEN 'TABLE                      '
       WHEN 'U' THEN 'DISTINCT TYPE              '
       WHEN 'V' THEN 'VIEW                      '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD         '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD       '
       ELSE          'UNKNOWN                    '
       END        AS OBJECT_TYPE
     , DCOLLID AS COLLECTION
     , DNAME   AS PACKAGE
     , HEX(DCONTOKEN) AS DCONTOKEN
     , CASE DTYPE
       WHEN 'F' THEN 'COMPILED SQL SCALAR FUNCTION    '
       WHEN 'N' THEN 'NATIVE SQL ROUTINE PACKAGE      '
       WHEN 'O' THEN 'ORIGINAL COPY OF A PACKAGE      '
       WHEN 'P' THEN 'PREVIOUS COPY OF A PACKAGE      '
       WHEN 'R' THEN 'RESERVED FOR IBM USE            '
       WHEN 'T' THEN 'TRIGGER PACKAGE                 '
       WHEN ' ' THEN 'NOT A TRIGGER/NATIVE SQL PACKAGE'
       ELSE          'UNKNOWN                         '
       END        AS PACKAGE_TYPE
     , DOWNER     AS OWNER
     , CASE DOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS DOWNERTYPE
FROM SYSIBM.SYSPACKDEP
ORDER BY 1 , 2 , 7
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT BCREATOR   AS CREATOR
     , BNAME      AS NAME
     , CASE BTYPE
       WHEN 'A' THEN 'ALIAS                      '
       WHEN 'E' THEN 'INSTEAD OF TRIGGER         '
       WHEN 'F' THEN 'UDF OR CAST FUNCTION       '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE     '
       WHEN 'I' THEN 'INDEX                      '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE   '
       WHEN 'O' THEN 'STORED PROCEDURE           '
       WHEN 'P' THEN 'LARGE OR DSSIZE PART. SPACE'
       WHEN 'Q' THEN 'SEQUENCE OBJECT            '
       WHEN 'R' THEN 'TABLESPACE                 '
       WHEN 'S' THEN 'SYNONYM                    '
       WHEN 'T' THEN 'TABLE                      '
       WHEN 'V' THEN 'VIEW                       '
       ELSE          'UNKNOWN                    '
       END        AS OBJECT_TYPE
     , DNAME   AS PLAN_NAME
FROM SYSIBM.SYSPLANDEP
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT DCREATOR   AS CREATOR
     , DNAME      AS NAME
     , DCOLNAME   AS COLNAME
     , CASE DTYPE
       WHEN 'F' THEN 'SQL FUNCTION       '
       WHEN 'I' THEN 'IDENTITY COLUMN    '
       WHEN 'X' THEN 'IMPLICIT DOCID     '
       WHEN ' ' THEN 'OLD IDENTITY COLUMN'
       ELSE          'UNKNOWN            '
       END        AS SEQUENCETYPE
     , DOWNER     AS OWNER
     , CASE DOWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSSEQUENCESDEP
ORDER BY CREATOR , NAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT BCREATOR   AS CREATOR
     , BNAME      AS NAME
     , CASE BTYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION    '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE  '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD      '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD    '
       ELSE          'UNKNOWN                 '
       END        AS OBJECT_TYPE
     , DNAME    AS VIEW_NAME
     , DCREATOR AS VIEW_CREATOR
     , CASE DTYPE
       WHEN 'F' THEN 'COMPILED SQL SCALAR FUNCTION'
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE    '
       WHEN 'V' THEN 'VIEW                      '
       ELSE          'UNKNOWN                     '
       END        AS TABLE_TYPE
     , DOWNER     AS OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSVIEWDEP
ORDER BY 1 , 2 , 4 , 5
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Again, judicious use of extra WHERE predicates is recommended!

ROLE based meta-data

Then there is the “base” data of the objects themselves, which can have ROLE based information in it.

SELECT NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
FROM SYSIBM.SYSDATABASE
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSDATATYPES
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT NAME
     , PLNAME AS PLAN_NAME
     , PLCREATOR AS CREATOR
     , CASE PLCREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END AS PLCREATORTYPE
FROM SYSIBM.SYSDBRM
ORDER BY NAME, 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSINDEXES
ORDER BY 3 , 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT JARSCHEMA AS CREATOR
     , JAR_ID AS NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSJAROBJECTS
ORDER BY 3 , 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT COLLID AS COLLECTION
     , NAME   AS PACKAGE
     , HEX(CONTOKEN) AS CONTOKEN
     , CREATOR
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSPACKAGE
ORDER BY 1 , 2 , 3
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT COLLID AS COLLECTION
     , NAME   AS PACKAGE
     , HEX(CONTOKEN) AS CONTOKEN
     , COPYID
     , CREATOR
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSPACKCOPY
ORDER BY 1 , 2 , 3 , 4
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT SCHEMA     AS CREATOR
     , NAME       AS NAME
     , OWNER      AS OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
FROM SYSIBM.SYSPARMS
ORDER BY 1 , 2 , 3
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , NAME       AS NAME
     , BOUNDBY
FROM SYSIBM.SYSPLAN
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , ACTIVE
     , CASE ROUTINETYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION'
       WHEN 'P' THEN 'STORED PROCEDURE    '
       ELSE          'UNKNOWN             '
       END        AS ROUTINETYPE
     , CASE EXTERNAL_SECURITY
       WHEN 'D' THEN 'DB2 - AUTH ID FROM WLM               '
       WHEN 'U' THEN 'SESSION_USER - AUTH ID OF INVOKER    '
       WHEN 'C' THEN 'DEFINER - AUTH ID OF OWNER OF ROUTINE'
       WHEN ' ' THEN 'N/A                                  '
       ELSE          'UNKNOWN                              '
       END        AS ROUTINETYPE
     , SPECIFICNAME
FROM SYSIBM.SYSROUTINES
ORDER BY CREATOR , NAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT SCHEMA     AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , CASE SEQTYPE
       WHEN 'I' THEN 'IDENTITY COLUMN      '
       WHEN 'S' THEN 'USER-DEFINED SEQUENCE'
       WHEN 'X' THEN 'IMPLICIT DOCID       '
       ELSE          'UNKNOWN              '
       END        AS SEQUENCETYPE
FROM SYSIBM.SYSSEQUENCES
ORDER BY CREATOR , NAME
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT NAME       AS CREATOR
     , PLNAME     AS PLAN_NAME
     , PLCREATOR  AS CREATOR
     , CASE PLCREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS PLCREATORTYPE
FROM SYSIBM.SYSSTMT
ORDER BY 2 , 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , CREATEDBY
FROM SYSIBM.SYSSTOGROUP
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , TBCREATOR
     , TBNAME
     , CREATEDBY
FROM SYSIBM.SYSSYNONYMS
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , NAME
     , CREATOR
     , CASE TYPE
       WHEN 'A' THEN 'ALIAS                                    '
       WHEN 'C' THEN 'CLONE TABLE                              '
       WHEN 'G' THEN 'CREATED GLOBAL TEMPORARY TABLE           '
       WHEN 'H' THEN 'HISTORY TABLE                            '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE                 '
       WHEN 'P' THEN 'TABLE THAT WAS IMPLICITLY CREATED FOR XML'
       WHEN 'T' THEN 'TABLE                                    '
       WHEN 'V' THEN 'VIEW                                     '
       WHEN 'X' THEN 'AUXILIARY TABLE                          '
       ELSE          'UNKNOWN                                  '
       END AS TABLE_TYPE
     , CASE AUDITING
       WHEN ' ' THEN '            '
       WHEN 'A' THEN 'AUDIT ALL   '
       WHEN 'C' THEN 'AUDIT CHANGE'
       ELSE          'UNKNOWN     '
       END AS AUDIT_LEVEL
     , TABLESTATUS
     , CASE SECURITY_LABEL
       WHEN ' ' THEN '       '
       WHEN 'R' THEN 'MLS    '
       ELSE          'UNKNOWN'
       END AS SECURITY_LABEL
     , CASE CONTROL
       WHEN ' ' THEN 'NO ACCESS CONTROL ENFORCEMENT'
       WHEN 'B' THEN 'ROW AND COLUMN ACCESS CONTROL'
       WHEN 'C' THEN 'COLUMN ACCESS CONTROL        '
       WHEN 'R' THEN 'ROW ACCESS CONTROL           '
       ELSE          'UNKNOWN                      '
       END AS SECURITY_LABEL
     , CREATEDBY
FROM SYSIBM.SYSTABLES
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT
       DBNAME
     , NAME
     , CREATOR
     , CASE CREATORTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS CREATORTYPE
     , CASE TYPE
       WHEN ' ' THEN 'NORMAL '
       WHEN 'G' THEN 'PBG    '
       WHEN 'L' THEN 'LARGE  '
       WHEN 'O' THEN 'LOB    '
       WHEN 'P' THEN 'XML PBG'
       WHEN 'R' THEN 'PBR    '
       ELSE          'UNKNOWN'
       END AS SPACE_TYPE     , CASE STATUS
       WHEN 'A' THEN 'OK                    '
       WHEN 'C' THEN 'PI MISSING            '
       WHEN 'P' THEN 'CHECKP STATUS         '
       WHEN 'S' THEN 'CHECKP STATS ON A PART'
       WHEN 'T' THEN 'NO TABLE DEFINED      '
       ELSE          'UNKNOWN               '
       END AS STATUS
     , CREATEDBY
FROM SYSIBM.SYSTABLESPACE
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT SCHEMA AS CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , SECURE
     , CASE TRIGTIME
       WHEN 'A' THEN 'AFTER  '
       WHEN 'B' THEN 'BEFORE '
       WHEN 'I' THEN 'INSTEAD'
       ELSE          'UNKNOWN'
       END AS TRIGGER_TIME
     , CREATEDBY
FROM SYSIBM.SYSTRIGGERS
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

SELECT CREATOR
     , NAME
     , OWNER
     , CASE OWNERTYPE
       WHEN 'L' THEN 'ROLE   '
       WHEN ' ' THEN 'AUTH ID'
       ELSE          'UNKNOWN'
       END        AS OWNERTYPE
     , CASE TYPE
       WHEN 'F' THEN 'UDF OR CAST FUNCTION    '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'V' THEN 'VIEW                    '
       ELSE          'UNKNOWN                 '
       END        AS TABLE_TYPE
FROM SYSIBM.SYSVIEWS
ORDER BY 1 , 2
FETCH FIRST 50 ROWS ONLY
WITH UR
;

Once more: Add your own WHERE predicates here please!

Lost in translation

Finally, and the last SQL for this newsletter, is for the Inbound and Outbound AUTHID translation table:

SELECT AUTHID
     , CASE TYPE
       WHEN 'I' THEN 'INBOUND AND COME-FROM CHECKING               '
       WHEN 'O' THEN 'OUTBOUND TRANSLATION                         '
       WHEN 'S' THEN 'OUTBOUND SYSTEM AUTHID FOR TRUSTED CONNECTION'
       ELSE          'UNKNOWN                                      '
       END        AS NAME_TYPE
     , NEWAUTHID
FROM SYSIBM.USERNAMES
ORDER BY 1
FETCH FIRST 50 ROWS ONLY
WITH UR
;

We have now gone through all the Role based, and some more, DB2 Catalog tables. What about seeing what is happening on your machine? Have you anything in place that can give you 100% coverage of audit issues? With Software Engineering’s SQL WorkloadExpert for DB2 z/OS you can audit everything that is running in your enterprise. For example here is a view of the Audit pop-up window:

In the bottom half you can see the Authorization failures radio button. This shows all -551 SQL Codes and also the +562 “ping” of a double GRANT. Why this? Well, to prove that the Audit is actually alive and well you should do a “dummy” GRANT every day like, for example,

GRANT SELECT ON SYSIBM.SYDUMMY1 TO PUBLIC ;

that is already there and therefore gets a +562 which is also externalized and available in this selection. This way auditors can see that the Audit trace was running and so any -551 would also be there.

You also get GRANTs and REVOKEs and DDL that is running – optionally the software can even generate the DDL so you can follow the lifecycle of changes over time. All very handy stuff and available real-time!

Death by Default?

Now onto the last part of these newsletters: Default settings which can harm security.

Here’s a little list of any security ZPARM or BSDS settings that should never ever be left “as default”.

If you have any of the above values in your shop – Time to plan some changes!

I hope you enjoyed this last part of a Quick Security Audit. Thanks again for reaching the end.

As always, any questions or comments would be most welcome!

TTFN,

Roy Boxwell