GIVE and TAKE Programs 4, 5, 6, 7

Db2 11+ 12 Audit+ SIEM, Access Path Recovery, Space Assurance, ZOWE and SQL Workload Performance


Limited free-of-Charge Db2 Applications


Previous Give & Take

This Program started in Europe in 2016. We have „GIVEn“ various free-of-charge Use Cases from our SQL WorkloadExpert for Db2 z/OS like:

1 Index Maintenance Costs

2 EXPLAIN Suppression

3 BIF Usage


What we GIVE in 2020

  • 90 days free trial – even in production
  • Two webinars covering installation and all pre-reqs
  • Two days – free of charge – onsite support
  • Offer of two days – free of charge – for potential realization of customer requests and enhancements

What we TAKE

  • Your Real World Experiences
  •  Your permission to use the gathered data in our presentations (Anonymous or, if you allow it, with your customer name)

In return, we receive the results. We’d like to share this inspiring experiences with you and communicate with local User Groups worldwide.


Current Give & Take 2020, Germany offers


4


Db2 11+12 Audit+ SIEM

with Optional Framework Eclipse or ZOWE IBM GUI

January-March 2020 (1Q) – Flyer Audit More


5


Access Path Recovery

April-June 2020 (2Q) – Presentation More


6


Space Assurance – K-no-w Limits

July – September 2020 (3Q) – PresentationFlyer SAX More

Db2 Space Assurance Recovery; give and Take Programm 4,5,6,7; SOFTWARE ENGINEERING GMBH


7


ZOWE IBM GUI and SQL Workload Performance for Db2 12

Oct.-December 2020 (4Q)


We TAKE the anonymized results for research

and will communicate with the local User Groups for discussions

Inspiring experiences

See the Customer Statements & more details on the past Give & Take


2020-04 Four Flavors of Db2 Audit

These days there is a lot of talk about audit, specifically regarding Db2 on z/OS. So, in this newsletter, I wish to run through four different ways that you can “Get Audit Done”.

As well as simply getting it done, I will also run through the four different ways that you can process the gathered data.


Four ways to get a Db2 z/OS Audit done


1- First up

First option is the simplest, cheapest and quickest:

Do nothing.

Whether or not this will help your company is a non-trivial question of course!

Naturally this is an absolute No No.


2- Then we have

Next option is relatively simple and cheap, but requires a bit of work: 

Write it all yourself but based on existing data that some other process already extracts for you, (SMF for example). 

If you happen to have the skills for extracting the required audit data from existing data that is being collected anyway, then this might well be the best method if you are really strapped for resources. 


3- Getting there 

Then we have not so simple, still cheap, but a ton of work: 

Write it all yourself and add all the IFCIDs you actually need to audit your system as well as capturing all the SQL. 

This needs a serious amount of skills to get and keep up with the agile world of Db2. You will also need to take care of the amount of data that you will be collecting.

However, the auditor will be happy as you have everything they could ask for.


4- Aha! The only true way 

Last option is simple, not so cheap but very quick: 

Third party software that does it all for you.

This is my preferred solution, especially as we just happen to sell one (WorkLoadExpert Audit).

This is actually the only real way to go. You probably don’t have the time to keep all these things up-to-date and running correctly. 

Data Collected – Now what? 

So, you have chosen one of these ways to gather the data. Now you must evaluate what you got. Here again we have four separate ways to go forward: 

First up 

There it is! 

Do nothing. Just point at the datasets, print outs, database objects and say “It is all in there…” 

This is not really a solution and any auditor worth his, or her, salt would quite rightly be extremely upset! 

Then we have 

A whole bunch of pre-written SQLs. 

SPUFI is ok, but much better would be to see these in a GUI where graphical viewing is built in and saving and sharing results is much easier.  

This is not bad, but still a manual “island” process. Just Db2 and nothing else plus it must be triggered by humans. 

Getting there

A whole bunch of pre-written and custom SQLs.

This time, all run in Batch and the results are emailed to the auditor directly. These emails can “just sit there” until the auditor checks the results. Naturally, if anything is found, then the underlying data must still be there for a detailed analysis.

Better, as it is getting automatic but still not really “round”, as it is still Db2 in isolation…

Aha! The only true way

Use of LEEF or SYSLOGGER-style formats to export all audit data.

The data is then in a data-lake where SPLUNK, QRADAR et al can happily slice and dice their way through the data.

This is the best way!

You also get an extra bonus point for *removing* the data from the mainframe. As auditors *love* a single point of control, this is the only real way forward. It also pushes the Db2 data into the world of other data that auditors use and require.


Db2 Audit with „GIVE&TAKE“ :


Software Engineering GmbH and SEGUS Inc are launching a new free Give&Take which this time is the Audit support from WorkLoadExpert.

If you would like to take part, then please just fire off an email to db2support@segus.com telling us who you are and which firm you work for and we will get in touch!

Give and Take 

By the way, it is called “Give&Take” because :

  • we Give you the software, for free, to run for a trial period, and
  • we would like to Take away what you think, feel, and find about the software after the trial period. 

More about Give&Take


TTFN, 

Roy Boxwell 

2018-06 – DST Db2 timestamp problems: I really hate Daylight Saving Time

How to avoid timestamp problems while going from winter to summer time in a Db2 for z/OS system ?

Is the CHAR or Timestamp use, the safest timestamp procedure ?

How do you fix it?

This year, as every year, the moment arrives for most of us when the clocks go forward and then in autumn back again. I really hate this, as I still have a bunch of clocks that do not automatically do it for me. My PC, phone, laptop, TV etc. all do it for me but the rest… anyway what has this got to do with Db2 I hear you all wonder? Well it really is quite a horrible little story coming up…

Same procedure as every year

At precisely 02:00 on the 25th of March a SET CLOCK console command was issued to change the UTC Offset to +2 thus leaping from 02:00 to 03:00 in the blink of an eye.

How long?

Now “how long” is the blink of an eye? For Db2 these days – too long!

Day of reckoning

At 2018-03-25-02.00.00.006999 a transaction was logged in the Audit system, in fact *lots* of transactions were in-flight at this time. Normally it is not a problem and, in fact, nothing happened until nearly three months later when someone found that there was possibly some data missing.

Alarm!

Alarm bells are ringing as these inventory checks cannot have missing data. The code is nowadays all JAVA and the developer in charge of the problem found out that the data was indeed missing!

Oh no it isn’t!

The DBA group were then involved, as it could be data corruption, and they looked and found the data – but it was not the same data as the developers had… then the penny dropped!

Clever old JAVA

In fact, the data the developer had was *exactly* one hour later than the data found by the DBA group. I mentioned earlier that the 25th March was the switch to summer time and, perhaps, the JAVA Driver is “helping” us, a bit too much help if you ask me!

Date Check

Here is a bit of SQL for you to recreate the problem and gaze in wonder at how cool/horrible (delete what is not applicable) JAVA really is.

CREATE TABLE BOXWELL.DAY_LIGHT                           
  (COL1 SMALLINT     NOT NULL                            
  ,COL2 TIMESTAMP    NOT NULL)                           
;                                                         
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (1 , '2018-03-25-01.59.59.999999');               
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (2 , '2018-03-25-02.00.00.006999');               
INSERT INTO BOXWELL.DAY_LIGHT                            
VALUES (3 , '2018-03-25-03.00.00.000099');               
COMMIT ;                                                 

SELECT * FROM BOXWELL.DAY_LIGHT                           
ORDER BY 1                                               
;
---------+---------+---------+---------+---------+--------
  COL1   COL2                                         
---------+---------+---------+---------+---------+--------
     1   2018-03-25-01.59.59.999999                     
     2   2018-03-25-02.00.00.006999                      
     3   2018-03-25-03.00.00.000099                      
DSNE610I NUMBER OF ROWS DISPLAYED IS 3

The output of SPUFI looks great! Timestamps are correct and all is fine.

It is a GUI world

Now do the select using a JAVA driver of your choice, here I am using DataStudio:

DST Db2 timestamp problems - Char - Daylight Saving Time

And then running it gives:
DST Db2 timestamp problems - Char- Daylight Saving Time

Spot the difference!

Isn’t that

great/terrifying (delete what is not applicable)

as the JAVA driver is “looking” at the timestamp data and seeing “oh oh! That timestamp is impossible! I know – I will add one hour to correct it!”

This scares me a little…actually quite a lot!

Docu – What Docu?

The only place I could find anything about this was in a chapter about not using 24 as midnight and the problem of using timestamps between October 5th 1582 and October 14th 1582:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/java/src/tpc/imjcc_r0053436.html

If you read it you can find this one sentence:

If a string representation of a date, time, or timestamp value does not correspond to a real date or time, Java adjusts the value to a real date or time value.

Which, of course, explains everything!

The quick fix…

There is no quick fix!

1 – The customer must either change all SQL to use the CHAR function – Not good!

Or

2 – Check all of their important timestamp columns for the range 02.00.00.000000 -> 02.59.59.999999 data and then update them with plus one hour – Not good!

Faster and Faster : the best fix ?

This problem will get worse the faster the machines get and so my idea to solve it next year is simply issue a

SET LOG SUSPEND

at one second before 02:00 which flushes the log, issues a system checkpoint (non data-sharing), updates the BSDS and basically pauses the system. Then do the SET CLOCK command and then do a

SET LOG RESUME

It all takes about three seconds and so should not cause any timeouts.

 

I really hope that, one day, we simply get rid of daylight saving time…

 

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

TTFN,

Roy Boxwell

2018-05 Audit 2.0 – GDPR Audit guide and checklist for Db2 z/OS

Or: How I Learned to Stop Worrying and Love the Auditor

A guideline in 5 big Steps to Audit for Personal Data Protection

First up, this is not another Audit review and health check sell! We, and I mean especially in the European Union (EU), are about to get the biggest update to “my data”, “my right to know” and “my right to forget (delete)” that has ever happened, when the General Data Protection Regulation (GDPRKicks off on May 25th, 2018.

Who cares?

Well, we all should really! Very soon hordes of people could well start demanding to know if you have any data about them and if so where it is and who you give it to. Personal Data will be big business!

Age of Consent

Just assuming all is ok is also no longer valid or lawful – any personal data that you keep must be kept with the *active* consent of the data owner… This is going to get awfully messy awfully quickly! Do not forget that the definition of personal data has also changed quite a bit e.g. IP Addresses. For the record here is the actual text in the GDPR regulations:

Art. 4 GDPR Definitions

For the purposes of this Regulation:

1.      ‘personal data’ means any information relating to an identified or identifiable natural person (‘data subject’); an identifiable natural person is one who can be identified, directly or indirectly, in particular by reference to an identifier such as a name, an identification number, location data, an online identifier or to one or more factors specific to the physical, physiological, genetic, mental, economic, cultural or social identity of that natural person;

And

Art. 9 GDPR Processing of special categories of personal data

1.      Processing of personal data revealing racial or ethnic origin, political opinions, religious or philosophical beliefs, or trade union membership, and the processing of genetic data, biometric data for the purpose of uniquely identifying a natural person, data concerning health or data concerning a natural person’s sex life or sexual orientation shall be prohibited.

Security by design and Security of processing

Very important are Articles 25 and 32:

Art. 25 GDPR Data protection by design and by default

1.       Taking into account the state of the art, the cost of implementation and the nature, scope, context and purposes of processing as well as the risks of varying likelihood and severity for rights and freedoms of natural persons posed by the processing, the controller shall, both at the time of the determination of the means for processing and at the time of the processing itself, implement appropriate technical and organisational measures, such as pseudonymisation, which are designed to implement data-protection principles, such as data minimisation, in an effective manner and to integrate the necessary safeguards into the processing in order to meet the requirements of this Regulation and protect the rights of data subjects.

2.       The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual’s intervention to an indefinite number of natural persons.

Last part of Paragraph two is important here.

Art. 32 GDPR Security of processing

1.      Taking into account the state of the art, the costs of implementation and the nature, scope, context and purposes of processing as well as the risk of varying likelihood and severity for the rights and freedoms of natural persons, the controller and the processor shall implement appropriate technical and organisational measures to ensure a level of security appropriate to the risk, including inter alia as appropriate:

1.      the pseudonymisation and encryption of personal data;

2.      the ability to ensure the ongoing confidentiality, integrity, availability and resilience of processing systems and services;

3.      the ability to restore the availability and access to personal data in a timely manner in the event of a physical or technical incident;

4.      a process for regularly testing, assessing and evaluating the effectiveness of technical and organisational measures for ensuring the security of the processing.

2.      In assessing the appropriate level of security account shall be taken in particular of the risks that are presented by processing, in particular from accidental or unlawful destruction, loss, alteration, unauthorised disclosure of, or access to personal data transmitted, stored or otherwise processed.

Here paragraphs 1.2, 1.4 and 2 are the biggies!

A small or large fine for you today?

The fines are also amazingly high. First, for the “minor” problem of being over 72 hours late when data leaks have occurred (a breach), is 2% of global turnover or €10,000,000 – whichever is *higher* and, if you are really naughty, like disregarding basic data laws, moving data abroad or ignoring an individual’s rights then you get hit for 4% of global turnover or €20,000,000 – again whichever is *higher*

First up?

I, for one, do not want to be the first company that makes the headlines… How about you?

Who are you afraid of?

Now I know that most of the talk about GDPR is “right to data” for the general public but we all know, as IT specialists, that the people to be really afraid of are ex-employees who did not leave on happy terms. They know *exactly* where the knife could best be put… They know exactly where, and on which platforms, all of the data really is.

Protect yourself – Due diligence

What can you do? Well the first thing is to actually understand what GDPR means to you and your firm’s data. Then you must be able to prove to auditors that you tried your very best. We will all probably get hacked at some time, but if you tried your best it is enough. Using all of the data you gather in a post-mortem, or for forensics, is also a very good idea as can be seen in paragraphs 2.3 and 2.4 below:

Art. 83 GDPR General conditions for imposing administrative fines

1. Each supervisory authority shall ensure that the imposition of administrative fines pursuant to this Article in respect of infringements of this Regulation referred to in paragraphs 4, 5 and 6 shall in each individual case be effective, proportionate and dissuasive.

 2. Administrative fines shall, depending on the circumstances of each individual case, be imposed in addition to, or instead of, measures referred to in points (a) to (h) and (j) of Article 58(2). When deciding whether to impose an administrative fine and deciding on the amount of the administrative fine in each individual case due regard shall be given to the following:

1. the nature, gravity and duration of the infringement taking into account the nature scope or purpose of the processing concerned as well as the number of data subjects affected and the level of damage suffered by them;

2. the intentional or negligent character of the infringement;

3. any action taken by the controller or processor to mitigate the damage suffered by data subjects;

4. the degree of responsibility of the controller or processor taking into account technical and organisational measures implemented by them pursuant to Articles 25 and 32;

The list goes on after these of course…

Db2 Audit – a new way?

Most sites use SMF and do daily cuts to then offload to a repository system of some sort on some sort of hardware. The problem here is that the amount of SMF data you are generating can swamp you and this method is not nearly quick enough. Waiting a day is 24 hours of the 72 that are available to you…

Faster, Better, Securer?

Can you do it faster?  Can you do it yourself faster?  Can you do it better? Maybe…

 

If you can handle OPx and a bit of High Level Assembler then “Bob’s your uncle!” as long as Bob is your Auditor of course…There are more than enough examples in the web about how to write and do OPx reading (There are even vendors that are willing to sell you their software 🙂 )


A guideline in 5 big steps to Audit in a new way for Personal Data protection :


1 – To do list – IFI commands, IFCIDS & Audit Class

Create a program that can issue the required IFI commands to start and stop traces and to issue the READA and READS IFI calls that you wish to have. If you don’t know which Audit Class is which IFCID – Here’s my handy list:

Audit ClassIFCIDsWhatWhy
1140Authorization failuresPossible brute force password attack
2141GRANTs and REVOKEsPossible temporary raising of privilege
3142CREATE/ALTER/DROP of table with AUDIT attributeRemoving the AUDIT attribute is a big red flag
41431st Change of table with AUDIT attributeSensitive data updates
51441st Select of table with AUDIT attributeSensitive data usage
6145BIND/PREPARE using table with AUDIT attributePossible usage of sensitive data
755, 83, 87, 169, 319SET CURRENT SQLID, end of identify, end of signon CICS/IMSElevation of privilege
823, 24, 25, 219, 220Db2 UtilitiesUNLOADing data to where?
9146, 392For customer usen/a
10269, 270, 271*

 

271 is not started automatically with Class 10

Establish reuse trusted context, trusted context CREATE/ALTER and Column Mask/Row Permission CREATE/DROP/ALTERAnything with MASKs or ROW Permissions must be checked
11361Successful accessOnly makes sense if working with AUDIT POLICY SYSADMIN or DBADMIN

So, what is missing from this picture?

Well, what about the COMMANDs to actually see what people are issuing on the machine? So you must also add IFCIDs 90 and 91. Then you do not see any of the DDL so you must start IFCID 62 to get that as you do want to see what people are ALTERing, creating and dropping don’t you?


2 – All done in design?

Once you have a system that gathers all these you must buffer them in memory and then do one of two things:

1) Write them out to a file for post-processing and enrichment later
2) Directly write them to your SIEM system of choice

I am not a fan of number 2 because if the SIEM system cannot accept the data then your data is lost…

With No. 1 you can keep processing even if the SIEM system cannot ingest, for whatever reason, and as long as you trigger yourself every 5 – 10 minutes or when the OPx buffer is full then that is “real time” enough for me!


3 – Care for something else for the weekend?


Plus you can enrich the data with extra stuff that the naked IFCIDs do not have attached to them e.g. map dbid/psid to a real database and tablespace etc.


Store all of this data in a bunch of Db2 tables


then kick-off a Db2 to a LEEF re-formatter program that reads all the audit data that has been collected since last time


writes it all out.

4 – Code Page – don’t forget the Code Page

Then you must do an ICONV for code page conversion, swiftly followed by a gzip to get it down to size and then copy it down to a USS file ready for your SIEM system to ingest its prey.


5 – Done, Finished and Complete

To prove that this system is always available you should also schedule a simple

GRANT SELECT ON SYSIBM.SYSDUMMY1 TO PUBLIC;

Every morning at 06:00 which you should see in the output every day. Remember – Due Diligence!


 

So now you are done and finished and can let the SIEM system do the completion!

 

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

TTFN,

Roy Boxwell

PS: Please note that the bold of parts of the GDPR text is only from me!

2015-10 DB2 Quick Security Audit – part 1

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:

 

DB2 z/OS newsletter 2015-10: DB2 Quick Security Audit -Part 1

 

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

2015-02: DB2 z/OS AUDIT – Boring Boring Boring

 

 

 

 

New IFCIDs 316 – 400/401 for DB2 z/OS Audit „on the fly“

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!

 

So how do you audit on the host side of the street?

Do you actively check what is happening? Or do you just wait for the thought police to arrive?

 

Enabling Auditing from DB2 10

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!

 

What queries would you want to run?

– 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!

 

Oh Lucky Man!

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?

– Who is accessing *any* table from the Internet?…
– Has any SYSADM enabled userid done any work on my system today?…
– How many userids are out there using my data?…

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?

News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

Here you can see a nice list of “Intents” against a given table (in this case SYSIBM.SYSTABLES)
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

Now you can see who did what type of insert against a given object.
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

This is a list of *all* Primary Authorization IDs or Collections and Packages that have run
– Any intruders?
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

This is a list of all User Data Updates done by users with SYSADM authority in the last workload.
News from the labs Newsletter 2015-02: DB2 AUDIT-Boring, Boring, Boring-Screenshot1

 

Triggering the Auditor

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