Is your DB2 Catalog opened with a PUBLIC grant?
Do you know how your DB2 z/OS System is looking on the security side ?
Today, I’d like to offer up some help around Audit and Security – always an issue. Please bear with me, as this is a really long newsletter. In fact, I had to make it into two parts. In this first part I wish to share a bunch of SQLs with you that will give a quick appraisal of how your DB2 system is looking – on the Security side of things. In the second part, we will then delve down more into Roles and conducting a deep analysis of your DB2 Catalog as well as quick review of any defaults that can cause security risks.
To get the ball rolling, let’s review any and all GRANTs on the DB2 Directory and Catalog tables. I know lots of shops where the whole Catalog is simply open with a PUBLIC grant, or two. Perhaps you should reconsider that these days? Remember that in the RUNSTATS data there are indeed data values stored in the Catalog.
Part 2: is dedicated to a deep Analysis of your DB2 Catalog (Newsletter 2015-11)
Let’s start the System appraisal with some SQLs… by applying the following laws:
0.Catalog and Directory Special Cases
1.With GRANT OPTION is a bad idea
2.Know your SYSADM userids
3.Is anything PUBLIC?
4.“Trusted” Trusted Contexts?
Catalog and Directory Special Cases
The first SQL is for Packages and Plans that access the Catalog:
SELECT A.GRANTOR
, CASE A.GRANTORTYPE
WHEN 'L' THEN 'ROLE '
WHEN ' ' THEN 'AUTH ID'
ELSE 'UNKNOWN'
END AS GRANTORTYPE
, A.GRANTEE
, CASE A.GRANTEETYPE
WHEN 'L' THEN 'ROLE '
WHEN 'P' THEN 'PLAN/PACKAGE'
WHEN ' ' THEN 'AUTH ID '
ELSE 'UNKNOWN '
END AS GRANTEETYPE
, A.DBNAME
, A.TCREATOR AS CREATOR
, A.TTNAME AS NAME
, CASE A.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
, A.ALTERAUTH
, A.DELETEAUTH
, A.INDEXAUTH
, A.INSERTAUTH
, A.SELECTAUTH
, A.UPDATEAUTH
, A.REFERENCESAUTH
, A.TRIGGERAUTH
, A.UPDATECOLS
, A.REFCOLS
, A.COLLID
, HEX(A.CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSTABAUTH A
, SYSIBM.SYSTABLES B
WHERE NOT A.GRANTOR = A.GRANTEE
AND A.GRANTEETYPE = 'P'
AND B.DBNAME IN ('DSNDB01', 'DSNDB06', 'DSNXSR' )
AND B.TYPE = 'T'
AND B.CREATOR = A.TCREATOR
AND B.NAME = A.TTNAME
ORDER BY CREATOR, NAME, A.GRANTOR, A.GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;Note that this query is decoding various fields for you as well – especially the Role of the Grantor/Grantee – and you will see this all the way through the following queries. You will also see the “FETCH FIRST 50 ROWS ONLY”, as you might have vastly more info than can easily be reviewed. Feel free to add predicates for your shop!
Now we want to see all the non-plan/package GRANTs:
SELECT A.GRANTOR
, CASE A.GRANTORTYPE
WHEN 'L' THEN 'ROLE '
WHEN ' ' THEN 'AUTH ID'
ELSE 'UNKNOWN'
END AS GRANTORTYPE
, A.GRANTEE
, CASE A.GRANTEETYPE
WHEN 'L' THEN 'ROLE '
WHEN 'P' THEN 'PLAN/PACKAGE'
WHEN ' ' THEN 'AUTH ID '
ELSE 'UNKNOWN '
END AS GRANTEETYPE
, A.DBNAME
, A.TCREATOR AS CREATOR
, A.TTNAME AS NAME
, CASE A.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
, A.ALTERAUTH
, A.DELETEAUTH
, A.INDEXAUTH
, A.INSERTAUTH
, A.SELECTAUTH
, A.UPDATEAUTH
, A.REFERENCESAUTH
, A.TRIGGERAUTH
, A.UPDATECOLS
, A.REFCOLS
, A.COLLID
, HEX(A.CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSTABAUTH A
, SYSIBM.SYSTABLES B
WHERE NOT A.GRANTOR = A.GRANTEE
AND NOT A.GRANTEETYPE = 'P'
AND B.DBNAME IN ('DSNDB01', 'DSNDB06', 'DSNXSR' )
AND B.TYPE = 'T'
AND B.CREATOR = A.TCREATOR
AND B.NAME = A.TTNAME
ORDER BY CREATOR, NAME, A.GRANTOR, A.GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;Here it gets interesting when you have “G’s” in the xxxxAUTH columns of course! See later for the “First Law”.
Next, I would like to see whatever has been GRANTed to public:
SELECT A.GRANTOR
, CASE A.GRANTORTYPE
WHEN 'L' THEN 'ROLE '
WHEN ' ' THEN 'AUTH ID'
ELSE 'UNKNOWN'
END AS GRANTORTYPE
, A.GRANTEE
, CASE A.GRANTEETYPE
WHEN 'L' THEN 'ROLE '
WHEN 'P' THEN 'PLAN/PACKAGE'
WHEN ' ' THEN 'AUTH ID '
ELSE 'UNKNOWN '
END AS GRANTEETYPE
, A.DBNAME
, A.TCREATOR AS CREATOR
, A.TTNAME AS NAME
, CASE A.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
, A.ALTERAUTH
, A.DELETEAUTH
, A.INDEXAUTH
, A.INSERTAUTH
, A.SELECTAUTH
, A.UPDATEAUTH
, A.REFERENCESAUTH
, A.TRIGGERAUTH
, A.UPDATECOLS
, A.REFCOLS
, A.COLLID
, HEX(A.CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSTABAUTH A
, SYSIBM.SYSTABLES B
WHERE NOT A.GRANTOR = A.GRANTEE
AND B.DBNAME IN ('DSNDB01', 'DSNDB06', 'DSNXSR' )
AND B.TYPE = 'T'
AND B.CREATOR = A.TCREATOR
AND B.NAME = A.TTNAME
AND ( A.GRANTOR = 'PUBLIC'
OR A.GRANTOR = 'PUBLIC*'
OR A.GRANTEE = 'PUBLIC'
OR A.GRANTEE = 'PUBLIC*' )
ORDER BY CREATOR, NAME, A.GRANTOR, A.GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;Ok that’s the “special” case of the Catalog and Directory. Now onto the “normal” Catalog tables, to check what is in them, and whether it matches up to modern ideas of security, or not.
First Law: WITH GRANT OPTION is a bad idea
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
AND ( CREATETABAUTH = 'G'
OR CREATETSAUTH = 'G'
OR DBADMAUTH = 'G'
OR DBCTRLAUTH = 'G'
OR DBMAINTAUTH = 'G'
OR DISPLAYDBAUTH = 'G'
OR DROPAUTH = 'G'
OR IMAGCOPYAUTH = 'G'
OR LOADAUTH = 'G'
OR REORGAUTH = 'G'
OR RECOVERDBAUTH = 'G'
OR REPAIRAUTH = 'G'
OR STARTDBAUTH = 'G'
OR STATSAUTH = 'G'
OR STOPAUTH = 'G' )
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
AND ( BINDAUTH = 'G'
OR COPYAUTH = 'G'
OR EXECUTEAUTH = 'G' )
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
AND ( BINDAUTH = 'G'
OR EXECUTEAUTH = 'G' )
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
AND USEAUTH = 'G'
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
AND EXECUTEAUTH = 'G'
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
AND ( CREATEINAUTH = 'G'
OR ALTERINAUTH = 'G'
OR DROPINAUTH = 'G' )
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
AND ( ALTERAUTH = 'G'
OR USEAUTH = 'G' )
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
AND ( ALTERAUTH = 'G'
OR DELETEAUTH = 'G'
OR INDEXAUTH = 'G'
OR INSERTAUTH = 'G'
OR SELECTAUTH = 'G'
OR UPDATEAUTH = 'G'
OR REFERENCESAUTH = 'G'
OR TRIGGERAUTH = 'G' )
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
AND ( BINDADDAUTH = 'G'
OR BSDSAUTH = 'G'
OR CREATEDBAAUTH = 'G'
OR CREATEDBCAUTH = 'G'
OR CREATESGAUTH = 'G'
OR DISPLAYAUTH = 'G'
OR RECOVERAUTH = 'G'
OR STOPALLAUTH = 'G'
OR STOSPACEAUTH = 'G'
OR SYSADMAUTH = 'G'
OR SYSOPRAUTH = 'G'
OR TRACEAUTH = 'G'
OR MON1AUTH = 'G'
OR MON2AUTH = 'G'
OR CREATEALIASAUTH = 'G'
OR SYSCTRLAUTH = 'G'
OR BINDAGENTAUTH = 'G'
OR ARCHIVEAUTH = 'G'
OR CREATETMTABAUTH = 'G'
OR DEBUGSESSIONAUTH = 'G'
OR EXPLAINAUTH = 'G'
OR SQLADMAUTH = 'G'
OR SDBADMAUTH = 'G'
OR DATAACCESSAUTH = 'G'
OR ACCESSCTRLAUTH = 'G'
OR CREATESECUREAUTH = 'G' )
ORDER BY GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;Now you must review all of the output, and try to decide which, (if any!) of these should be allowed. I think they should all be not allowed, as you very quickly lose the ability to see who GRANTed what to whom!
Second Law: Know your SYSADM userids
If you do not know who is SYSADM that is a very bad place to be!
Here’s an SQL to help you find out:
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
, SYSADMAUTH
FROM SYSIBM.SYSUSERAUTH
WHERE NOT GRANTOR = GRANTEE
AND NOT SYSADMAUTH = ' '
ORDER BY GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;
Again, review the output *very* carefully and make very sure that all of the User Ids are well protected! One point to mention here is that lots of people use a surrogate User Id for Job scheduling (TWS, UC4, ESP, Control-M etc.) and normally these User Ids are very powerful, but they do *not* need to be SYSADM, and, if they are SYSADM, at least make them unavailable to TSO and remote Logon!
Third Law: Is anything PUBLIC?
There was a time, when nearly everything was PUBLIC, or even PUBLIC AT ALL LOCATIONS. These days this should never really be used, apart from exceptional circumstances. For example, the four SYSIBM.SYSDUMMYx tables can all safely get a
GRANT SELECT ON TABLE SYSIBM.SYSDUMMY1
, SYSIBM.SYSDUMMYE
, SYSIBM.SYSDUMMYA
, SYSIBM.SYSDUMMYU
TO PUBLIC ;but you must weigh up the pros and cons of any other table being opened up like this.
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
, CREATOR
, TNAME AS NAME
, COLNAME AS COLUMN
, CASE PRIVILEGE
WHEN 'R' THEN 'REFERENCES'
WHEN ' ' THEN 'UPDATE '
ELSE 'UNKNOWN '
END AS AUTHHOWGOT
, COLLID
, HEX(CONTOKEN) AS CONTOKEN
FROM SYSIBM.SYSCOLAUTH
WHERE NOT GRANTOR = GRANTEE
AND ( GRANTOR = 'PUBLIC'
OR GRANTEE = 'PUBLIC' )
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
, 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
AND ( GRANTOR = 'PUBLIC'
OR GRANTEE = 'PUBLIC' )
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
AND ( GRANTOR = 'PUBLIC'
OR GRANTOR = 'PUBLIC*'
OR GRANTEE = 'PUBLIC'
OR GRANTEE = 'PUBLIC*' )
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
AND GRANTEE = 'PUBLIC'
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
AND GRANTEE = 'PUBLIC'
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
AND GRANTEE = 'PUBLIC'
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
AND GRANTEE = 'PUBLIC'
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
AND GRANTEE = 'PUBLIC'
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
AND ( GRANTOR = 'PUBLIC'
OR GRANTOR = 'PUBLIC*'
OR GRANTEE = 'PUBLIC'
OR GRANTEE = 'PUBLIC*' )
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
AND GRANTEE = 'PUBLIC'
ORDER BY GRANTOR, GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR
;Once again, go through the output and make informed decisions! The Hacker loves PUBLIC.
Fourth Law: “Trusted” Trusted Contexts?
Trusted Contexts are great, but have you got some that are a bit too open? Have you – by accident – left the back door open? Run this to find out:
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
WHERE ALLOWPUBLIC = 'Y'
AND AUTHENTICATEPUBLIC = 'N'
ORDER BY 3
FETCH FIRST 50 ROWS ONLY
WITH UR
;
Allowing public access, but without authentication, is not a good idea.
Now all we have done is validate the DB2 Catalog. 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:
One of the options here, is to see exactly how many Authorization Ids are running at your shop, or even what the SYSADMs of the world have been doing. All handy stuff and available real-time!
I hope you enjoyed this first part and thanks for getting this far. Next up is knowing your Roles and Authorization Ids.
As always, any questions or comments would be most welcome!
TTFN,
Roy Boxwell
