2016-01 Simply Synonyms in DB2 z/OS (again)

As SYNONYMS are dying out, it is a good idea to start DROPping them as soon as you can…

In this short newsletter I wish to remind you all that SYNONYMS are dead! They may still be nailed to their perch (old Monty Python reference here!), but they are dead! Even the DB2 documentation has removed them and a big sign says DEPRECATED over the SQL syntax. So much for the preamble… The question is: What can you do? I will attempt to show you how to discover what size of problem you have, and provide some help in sorting out the mess of synonyms.

UPDATE!

On Friday the 08.04.2016 Pat posted this update on the DB2-L LISTSERV:

Hello,

I wanted to share an update on synonyms.

Synonyms are similar to aliases, but are supported only for compatibility with previous releases. Synonyms behave differently with DB2 for z/OS than with the other DB2 family products. It is recommended that you not create or use synonyms when writing new SQL statements or creating portable applications; use aliases instead. The publications currently state that synonyms are deprecated. The publications are being updated to reflect that synonyms are not being removed from DB2 for z/OS at this time and are no longer considered deprecated.

Best regards,

Pat Bossman
DB2 for z/OS Query Optimizer Team at IBM’s Silicon Valley Lab (SVL)

Synonyms were a good idea many years ago but they brought with them a few problems: one is that you lose the connection between a synonym and the alias it is created on, another is that GRANTs on synonyms actually are recorded in the DB2 Catalog against the base tables, and finally the SYNONYM did not agree with the rest of the world’s SQL Standards.

On Thursday the 28.04.2016 Pat posted this correction on the DB2-L LISTSERV:

Hello,

I need to issue a correction on information I previously disseminated.  I apologize for inconvenience this caused.

Update on synonyms: I understand there are a lot of questions about the status of synonyms now. In hindsight, the original communication that synonyms were being removed from the deprecation list was based on an internal misunderstanding and miscommunication. This resulted in premature dissemination of inaccurate information. This disclosure should have been more thoroughly vetted, and I apologize for the confusion caused.

What is the status of synonyms? Synonyms remain deprecated. However, synonyms continue to be supported for compatibility with prior releases in DB2 12. Removing synonyms from the product is an incompatible change and that incompatible change has not yet been scheduled or planned for implementation.

Documentation changes coming to the effect of: Synonyms are similar to aliases, but are supported only for compatibility with previous releases. Synonyms behave differently with DB2 for z/OS than with the other DB2 family products. Aliases behave the same for the DB2 family of products. Recommendation: When writing new SQL statements or creating portable applications, use aliases instead. The publications currently state that synonyms are deprecated, however, IBM has no current plans to remove support for synonyms from DB2 for z/OS.

Addendum: There has been some confusion related to the meaning of deprecation as it relates to a feature of software, as opposed to removing support for a feature of software. Deprecation is the discouragement of use of some feature, design or practice, typically because it has been superseded or is no longer considered safe, without (at least for the time being) removing it from the system of which it is a part or prohibiting its use. This is distinct from blocking or removing support, where the feature is no longer available. For clarity, a definition of deprecation will be to our software Gallery.

Patrick Bossman
DB2 for z/OS Query Optimizer Team at IBM’s Silicon Valley Lab (SVL)

 

Finding the bad guys

Here’s a triplet of SQLs to find all the synonym-dependent items:

 

SELECT BSCHEMA    AS CREATOR                
     , BNAME      AS NAME                     
     , BCOLNAME   AS COLUMN_NAME              
     , CASE BTYPE                             
       WHEN 'A' THEN 'ALIAS                   '
       WHEN 'C' THEN 'COLUMN                  '
       WHEN 'F' THEN 'FUNCTION                '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE  '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'Q' THEN 'SEQUENCE                '
       WHEN 'S' THEN 'SYNONYM                 '
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD      '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD    '
       ELSE          'UNKNOWN                 '
       END        AS TYPE                     
     , BOWNER     AS OWNER                    
     , CASE BOWNERTYPE                        
       WHEN 'L' THEN 'ROLE   '                
       WHEN ' ' THEN 'AUTH ID'                
       ELSE          'UNKNOWN'                
       END        AS BOWNERTYPE               
     , DSCHEMA    AS DEP_CREATOR              
     , DNAME      AS DEP_NAME                 
     , DCOLNAME   AS DEP_COLUMN_NAME          
     , CASE DTYPE                             
       WHEN 'C' THEN 'GENERATED COLUMN'       
       WHEN 'F' THEN 'FUNCTION        '       
       WHEN 'I' THEN 'INDEX           '       
       WHEN 'X' THEN 'ROW PERMISSION  '       
       WHEN 'Y' THEN 'COLUMN MASK     '       
       ELSE          'UNKNOWN         '       
       END        AS DEP_TYPE                  
     , DOWNER     AS DEP_OWNER                
     , CASE DOWNERTYPE                        
       WHEN 'L' THEN 'ROLE   '                
       WHEN ' ' THEN 'AUTH ID'                
       ELSE          'UNKNOWN'                
       END        AS DEP_OWNERTYPE            
FROM SYSIBM.SYSDEPENDENCIES                
WHERE BTYPE = 'S'                             
ORDER BY 1 , 2 , 3 , 5 , 7 , 8 , 9 , 11       
WITH UR                                       
;
SELECT BQUALIFIER AS CREATOR                 
     , BNAME      AS NAME                             
     , CASE BTYPE                                 
       WHEN 'A' THEN 'ALIAS                      '    
       WHEN 'B' THEN 'BUSINESS_TIME              '  
       WHEN 'C' THEN 'SYSTEM_TIME                '    
       WHEN 'F' THEN 'UDF OR CAST FUNCTION       '    
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE     '    
       WHEN 'I' THEN 'INDEX                      '    
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE   '    
       WHEN 'O' THEN 'STORED PROCEDURE           '    
       WHEN 'P' THEN 'LARGE OR DSSIZE PART. SPACE'    
       WHEN 'Q' THEN 'SEQUENCE OBJECT            '    
       WHEN 'R' THEN 'TABLESPACE                 '    
       WHEN 'S' THEN 'SYNONYM                    '    
       WHEN 'T' THEN 'TABLE                      '    
       WHEN 'U' THEN 'DISTINCT TYPE              '    
       WHEN 'V' THEN 'VIEW                       '    
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD         '    
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD       '    
       ELSE          'UNKNOWN                    '    
       END        AS OBJECT_TYPE                       
     , DCOLLID AS COLLECTION                          
     , DNAME   AS PACKAGE                             
     , HEX(DCONTOKEN) AS DCONTOKEN                    
     , CASE DTYPE                                     
       WHEN 'F' THEN 'COMPILED SQL SCALAR FUNCTION    '
       WHEN 'N' THEN 'NATIVE SQL ROUTINE PACKAGE      '
       WHEN 'O' THEN 'ORIGINAL COPY OF A PACKAGE      '
       WHEN 'P' THEN 'PREVIOUS COPY OF A PACKAGE      '
       WHEN 'R' THEN 'RESERVED FOR IBM USE            '
       WHEN 'T' THEN 'TRIGGER PACKAGE                 '
       WHEN ' ' THEN 'NOT A TRIGGER/NATIVE SQL PACKAGE'
       ELSE          'UNKNOWN                         '
       END        AS PACKAGE_TYPE                     
     , DOWNER     AS OWNER                            
     , CASE DOWNERTYPE                                
       WHEN 'L' THEN 'ROLE   '                        
       WHEN ' ' THEN 'AUTH ID'                        
       ELSE          'UNKNOWN'                         
       END        AS DOWNERTYPE                       
FROM SYSIBM.SYSPACKDEP                                
WHERE BTYPE = 'S'                                     
ORDER BY 1 , 2 , 7                                    
WITH UR                                                
;

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

If you get no data from using these queries, then that is very good indeed! If you do get data, then you must make a note of all the objects listed, as they will be affected by what we are going to do next. “Affected”, in this case, could be as simple as a REBIND, but could also be as complex as recreating UDFs.

 

Generate the drop

-- CREATE DRIVER TABLE FOR CARTESIAN JOIN PROCESSING
DECLARE GLOBAL TEMPORARY TABLE DRIVER (NUMBER SMALLINT) ;
INSERT INTO SESSION.DRIVER VALUES 1 ;
INSERT INTO SESSION.DRIVER VALUES 2 ;
INSERT INTO SESSION.DRIVER VALUES 3 ;
INSERT INTO SESSION.DRIVER VALUES 4 ;
INSERT INTO SESSION.DRIVER VALUES 5 ;
-- NOW GENERATE DROP AND CREATE ALIAS COMMANDS
WITH T1 ( CREATOR
        , NAME
        , ALIAS_NAME
        , ALIAS_TABLE
          )
     AS ( SELECT STRIP(CREATOR)
               , STRIP(NAME)
               , STRIP(CREATOR)   CONCAT '.' CONCAT STRIP(NAME)
               , STRIP(TBCREATOR) CONCAT '.' CONCAT STRIP(TBNAME)
          FROM SYSIBM.SYSSYNONYMS
          ORDER BY 1
        )
SELECT CAST(CASE NUMBER
            WHEN 1 THEN 'SET CURRENT SQLID = '''
                        CONCAT CREATOR CONCAT ''' $'
            WHEN 2 THEN 'DROP SYNONYM ' CONCAT NAME CONCAT ' $'
            WHEN 3 THEN 'CREATE ALIAS ' CONCAT ALIAS_NAME
            WHEN 4 THEN 'FOR ' CONCAT ALIAS_TABLE
            WHEN 5 THEN '$'
            END AS CHAR(72))
FROM T1, SESSION.DRIVER
;

 

The output of this contains the “$” as command terminator, so you must either use a “C ALL $ ;” style ISPF command, or just use a “–#SET TERMINATOR $” line in the SPUFI.

One thing to remember, before executing the generated output, is the problem of the GRANTs. Remember that all GRANTs are recorded at the table level not at the synonym level, so you have two choices here:

  1. Ignore the GRANTs and clean up later
  2. Analyze the SYSIBM.SYSTABAUTH table to see if any GRANTs are there

Whichever way you choose, there will be work involved! However, as SYNONYMS are dying out, it is a good idea to start DROPping them as soon as you can.

I hope you liked this month’s Topic.

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

TTFN,

Roy Boxwell

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