How can I find out what objects are setup with more than just SELECT authority?

Products

• RC Compare for DB2 for z/OS

Issue/Introduction

How can I find out what objects are setup with more than just SELECT authority?.

Environment

Release:
Component: RCSDB2

Resolution

The PTI.PTRCS_APLDOM_0200 table contains the authorizations selected for the object. A SQL query can be executed to retrieve the information.

The AUTHS column on the table retains authorizations for the selected objects. The AUTHS column is defined VARCHAR(25). The first byte is empty. Bytes 2 through 25 indicate the authorizations granted for the object (Y, G, or blank).

The TABLE authorizations are defined in the following bytes of the column:

 Byte:                Authorization: 
 -----                -------------
  2                   ALL
  3                   Select
  4                   Insert
  5                   Delete
  6                   Update
  7                   Update Column
  8                   Index
  9                   Alter
  10                  Reference
  11                  Reference Column
  12                  Trigger

Additional information can be found in the RC/Secure for DB2 for z/OS User Guide, Appendix B which will provide more details on the table layout and additional objects.

Execute the following query to obtain a list of users who have a "Y" privilege:

SELECT *
FROM PTI.PTRCS_APLDOM_0200
WHERE 
 SUBSTR(AUTHS,2,1)  = X'E8' OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,4,1)  = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,5,1)  = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,6,1)  = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,7,1)  = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,8,1)  = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,9,1)  = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,10,1) = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,11,1) = X'E8') OR 
 (SUBSTR(AUTHS,3,1)  = X'E8' AND 
 SUBSTR(AUTHS,12,1) = X'E8') 

The X'E8' value represents a "Y" privilege. This value can be changed to X'C7' to accommodate the "G" privilege.