IT solutions for global businesses
db2-zos-audit
SEGUS & SOFTWARE ENGINEERING präsentieren
Audit and Compliance is one of the big challenges companies are facing these days. This presentation shows how the entire DB2 workload can be tracked without the overhead of a trace, or monitor, enabling to answer Who did What When ‐ whenever needed.
SEGUS & SOFTWARE ENGINEERING präsentieren
Audit and Compliance is one of the big challenges companies are facing these days. This presentation shows how the entire DB2 workload can be tracked without the overhead of a trace, or monitor, enabling to answer Who did What When ‐ whenever needed.
SEGUS & SOFTWARE ENGINEERING sponsern diese Veranstaltung und präsentieren
Compliance with compliments! Viable DB2 z/OS workload tracking. Präsentation
Audit and Compliance is one of the big challenges companies are facing these days. This presentation shows how the entire DB2 workload can be tracked without the overhead of a trace, or monitor, enabling to answer Who did What When ‐ whenever needed.
SEGUS & SOFTWARE ENGINEERING präsentieren
HOADUG – am Montag den 12. September 2016
Audit and Compliance is a need that many companies want and have to fulfill.
There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces DB2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a DB2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.
| 1-Audit needs and musts Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5. | |
| 2-Solution overview and their Pros/Cons Get an overview about the existing solutions and understand how they work. | |
| 3-The viable way – let DB2 do the magic! Learn about DB2 enhancements in DB2 10/11 that deliver the DB2 workload being processed and understand why it’s so efficient. | |
| 4-Customer results from the banking industry Receive some experience from a large banking company and how they successfully replaced their DB2 Audit feature based reporting by a modern SQL tracking and analytics process. |
How to discover incompatible changes in your DB2 System
| The what and why of ICIs If you’ve been paying close attention, you will have noticed that over the course of the past couple releases of DB2 for z/OS, IBM has been making changes that can modify the behavior of your DB2 application programs. The number of incompatible changes being introduced by IBM started off slowly in DB2 9 but has grown to a significant number today. So much so that there are ways to trace incompatible changes (using IFCIDs and ICIs, or Incompatible Change Indicators), as well as methods to repress the changes, even if only for a period of time. | |
| Ways to deal with incompatibilities This session will discuss the incompatible changes, their potential impact on your applications, as well as provide guidance on how to tackle the whole experience. And you’ll also see how SEG’s Workload Expert technology makes it easier to manage and control these incompatible changes. With all of this information at your disposal, you can make sure that your DB2 applications do not wind up in the ICU (intensive care unit)! | |
| Customer experiences Now that we all know everything about ICI’s, let us have a look at how the separatly licensable BIF/ICI Use Case of WorkloadExpert can help you find where the “bad guys” are and how to continuously check that everything is ok. This presentation will show how you can find out ICI Details for static and dynamic SQL, and then we will show real customer results from a “BiF hunt”. |
Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’s involved in the development of SE’ maintenance and recovery Solutions.
SEGUS & SOFTWARE ENGINEERING präsentieren
15. November 2016
What is the number one reason for bad access paths? Bad statistics!
Why do you have bad statistics? Bad timing is the number one reason, followed by manually updated stats and then forgotten about.
RUNSTATS Rescue for DB2 z/OS saves you when these events happen. The basis is a complete copy of all productive required statistical data in the DB2 catalog. This should be regularly executed and the data saved away. The best way to do this is to use a Generation Gengroup which automatically stores “copies” of the data. RUNSTATS Rescue then provides a front end where you can use a variety of inputs to get back you good access paths.
Beginner, Intermediate, Advanced
Roy Boxwell has more than 30 years of experience in MVS, OS/390, and z/OS environments – 26 of those in DB2. He specializes in installation, migration, and performance monitoring and tuning. Roy leads the SEG development team responsible for the real time database maintenance solutions. He is also an active participant, speaker and contributor on the IDUG DB2 Listserv and sends out a monthly DB2 z/OS Newsletter.
16. November, 2016
Audit and Compliance is a need that many companies want and have to fulfill.
There’s different ways and tools that promise to be able to do it, but what can they really do and what are the associated costs? This presentation introduces DB2 10/11 technology exploitation that delivers any DML, DDL, DCL being executed in a DB2 environment along with identification details. Learn how you can run Audit analytics against a long‐term repository, pinpointing who executed a query, when and from where. Analyze your entire workload to understand access patterns and abnormalities.
Beginner, Intermediate, Advanced
| Objective 1 | Audit needs and musts Take a journey to GLB HIPAA PCI‐DSS Basel III Sarbanes‐Oxley CA SB1386 Federal Information Security Management Act “ed Flag”Rules (FRCA)5. |
| Objective 2 | Solution overview and their Pros/Cons Get an overview about the existing solutions and understand how they work. |
| Objective 3 | The viable way – let DB2 do the magic! Learn about DB2 enhancements in DB2 10/11 that deliver the DB2 workload being processed and understand why it’s so efficient. |
| Objective 4 | Customer results from the banking industry Receive some experience from a large banking company and how they successfully replaced their DB2 Audit feature based reporting by a modern SQL tracking and analytics process. |
Ulf Heinrich is the Director of Solutions Delivery at SOFTWARE ENGINEERING GmbH. He specializes in DB2 recovery issues and database maintenance, focusing on the growing requirement for cost reduction and 24×7 operations. As a consultant at large customer sites, he has implemented database maintenance procedures and recovery strategies, and also experienced the pitfalls of recovery scenarios under real‐world recovery pressure. His activities cover EMEA, as well as North America through SE’ U.S. subsidiary, SEGUS Inc. He’ involved in the development of SE’ maintenance andrecovery Solutions.
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)
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?
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.
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!
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!
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.
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
I use the Monty Python title as it reminded me of an old telephone book joke in England: look up „boring“ in the Yellow pages – there you would find a little bit of text that simply said „See Civil Engineering.“ It still makes me laugh these days. Anyway, back to the newsletter…
Auditing is often looked down upon as being boring, tedious and of no worth. This is rubbish of course! Without auditing we would not be allowed to do anything these days. I hardly know any DBAs who still have SYSADM.
It is just too darn powerful!
The statistics also tell us that nearly all “hack” attacks are “inside jobs” from the very people we know and trust. The statistics are also pretty brutal on the Mainframe/Server divide – Mainframes are very rarely successfully attacked, but Servers (see Sony et al.) are all the time. I think there should be a lot more auditing on the little iron really!
Do you actively check what is happening? Or do you just wait for the thought police to arrive?
When the enhanced IFCID 316 and the new IFCIDs 400/401 were introduced, it closed a gap in the ability to actually Audit your system “on the fly”. Using these IFCIDs you could actually trap/monitor/audit all of the SQL running in your Plex. These IFCIDs are also nearly free as the overhead is “background noise” levels of CPU.
Now, let us imagine that you are capturing all of this data. That you are regularly snapping both the DSC and the SSC, (that’s what I call the EDMPOOL cache for Static SQL statements), that this data is all being rolled up and saved into a DB2 Data warehouse, and that you are triggering Batch jobs to analyse for Audit – reporting using various queries just to see if anything “untoward” is starting/or is happening!
– I have a few straight off the bat here: Who is reading from the Payroll table?
– Who is updating the Payroll or Employee tables?
– Who is accessing *any* table from the internet?
– Is anybody being really clever and using ODBC to select from my production tables?
– How many userids are out there using my data?
– Has any SYSADM enabled userid done any work on my system today?
All good Audit questions that you could put into operation very simply indeed!
Now as luck would have it, we have a software product called SQL WorkloadExpert that actually does all this for you! What’s more you can expand it as much as you like! Cool huh?
Audit Video (5 min.) – Presentation
Looking at this screen shot you can get an idea of the possibilities – Look at the Workstation name column for instance. “192.xxx” is the intranet. If any other tcp/ip address showed up here, it would be, shall we say, “worrying”. You can also see great stuff like “EXCEL.EXE” in the Transaction name column, and that a certain Mr. Boxwell has been running a few things from lots of data sources, I wonder what he’s up to?
Here you can see a nice list of “Intents” against a given table (in this case SYSIBM.SYSTABLES)
Now you can see who did what type of insert against a given object.
This is a list of *all* Primary Authorization IDs or Collections and Packages that have run
– Any intruders?
This is a list of all User Data Updates done by users with SYSADM authority in the last workload.
All of this with drill down to the actual SQL that was executed. Cool stuff and very handy indeed! But this is “past the point” and what you really need is a Batch Style interface that runs the SQLs and triggers alarms on the host before someone looks at the pretty GUI!
I would do this with a nice little set of batch Spufi’s that get post processed and either e-mailed directly to the Auditors, or WTO’d as an alarm action that then triggers a batch job to do something else.
What would you like to Audit? Or what would your Auditors like to see? How do you currently accomplish this?
I would be fascinated to hear from you!
TTFN,
Roy Boxwell