CARLa SORTLIST and SUMMARY commands are good at generating line oriented reports, but lack a direct mechanism to create a MATRIX of values. For example, the Compare IDs function in RA.3.G shows PERMITs that 2 users have on datasets:
Profile key ROBVH ROBVH2 ROBVH.*.** ALTER ROBVH4.** ALTER ROBVH5.** ALTER
but that requires pretty complicated CARLa:
n required type=report_scope
define access_u1("ROBVH",8,access_nz) max(access) where id=ROBVH
define access_u2("ROBVH2",8,access_nz) max(access) where id=ROBVH2
summary key("Profile name",firstonly) access_u1 access_u2
report permit=ROBVH permit=ROBVH2
Basically, it calculates a statistic that has a value for only one of the ids, and prints it in a summary command. So the statistic applies to the cross of a profile and the user id that is specified in the WHERE clause. As you can see in the sample, the user id was coded 3 times:
- specify the column header "ROBVH"
- select when the statistic is calculated "where id=ROBVH"
- select the profile to include in the report "report permit=ROBVH"
SUMMARY creates a single line for a combination of fields with a unique set of values, and prints the statistics for this combination into the cell fields of the matrix. This means that a matrix report like this only works for fields that can be expressed as a MAX, MIN, BOOLEAN or other statistic. Most fields do not support statistics that copies a useful value into the cell field.
The crosstab utility was written to take a line based CARLa report and turn it into a generic cross table report.
The blue fields are called cells and contain one or more cell fields. The PERMIT report from RA.3.G would be written as
crosstab key * id access
The field names before the * are the row keys. The first field after the * is the cell key, any more fields on the command are the cell fields.
Crosstab does not know about the structure of fields, it is designed to read a CARLa report file, use the column headers as field names and to determine the width of the fields. As such, the column headers in the report must be single word values. Use overrides in the CARLa to determine the layout of Crosstab output.
//JCLLIB JCLLIB ORDER=(IBMZSEC.CKRPARM,CKR.V2R2.SCKRPROC) // INCLUDE MEMBER=C2R$PARM // EXEC C2RC //OUTPUT DD DISP=(,PASS),DSN=&REPORT //SYSIN DD * newlist type=racf_access dd=output select id=(robvh*,ted*) exclude class=group or access=qualown sortlist class profile id access(7) //* //CROSSTAB EXEC PGM=IKJEFT1A,COND=(8,LT) //SYSEXEC DD DISP=SHR,DSN=IBMUSER.REXXLIB //INPUT DD DISP=(OLD,PASS),DSN=&REPORT //OUTPUT DD SYSOUT=*,RECFM=VBA,LRECL=255 //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * CROSSTAB class profile * id access //
The result puts the ACCESS field into cells and writes out ID as the cell header:
R A C F A C C E S S A U T H O R I Z A T I O N S 1 Feb 2016 11:01
Class Profile ROBVH ROBVH2 ROBVH3
DATASET ROBVH.*.** ALTER
DATASET ROBVH4.** ALTER
DATASET ROBVH5.** ALTER
TSOAUTH JCL READ
TSOPROC TIVUSER READ
XFACILIT CKR.READALL NONE
Another example uses Access Monitor data to show the access reasons used by team members, to help build data access groups:
//JCLLIB JCLLIB ORDER=(IBMZSEC.CKRPARM,CKR.V2R2.SCKRPROC)
// INCLUDE MEMBER=C2R$PARM
//RBACGEN EXEC C2RC
//REPORT DD DISP=(NEW,PASS),DSN=&REPORT
//SYSIN DD *
alloc type=ckfreeze DSN=IBMZSEC.DATA.SZT01.C2POLICE.CKFREEZE
alloc type=access DSNPREF=IBMZSEC.DATA.SZT01.C2PACMON.Y
alloc type=racf active /* Active RACF database */
alloc type=output dd=report /* Detail Report to DSN */
newlist type=access name=detail dd=report pl=0
define maxintent("Highest") max(intent)
select rectype=(auth,fast), /* RACHECK and FRACHECK */
access_result=0, /* ignore access violations */
exists(sim_profile), /* when there is a profile */
userid=robvh* /* Includes input userids */
exclude req_status_access
/* Excludes ALTER access to profiles done by someone
with the RLIST or LISTDSD command which shows up
as a RACHECK and captured by Access Monitor
started task and ends up in the data. */
exclude sim_via=(global,privtrus,qualown,uacc,id_star)
exclude intent<<==:uacc /* ignore access that would be allowed by UACC */
summary sim_class sim_profile("Profile",32) maxintent userid sim_via,
* sim_via_groups
//*
//CROSSTAB EXEC PGM=IKJEFT1A,COND=(8,LT)
//SYSEXEC DD DISP=SHR,DSN=IBMUSER.REXXLIB
//INPUT DD DISP=(OLD,PASS),DSN=&REPORT
//OUTPUT DD SYSOUT=*,RECFM=VBA,LRECL=255
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
CROSSTAB class profile * userid highest via viagrps
//
The sim_profile field would normally have an output header "Profile current DB" which is more than 1 word, so we must override the header in CARLa. The resulting cross table shows three fields for each user id: Highest with the highest access level used, Via with the access reason and ViaGrps with the list of matching group names in the user and in the data set profile for the requested access level:
1A C C E S S T R A C E R E C O R D L I S T I N G 1 Feb 2016 11:16 list 1
ROBVH ROBVH2 ROBVH3
Class Profile Highest Via ViaGrps Highest Via ViaGrps Highest Via ViaGrps
DATASET #E.** ALTER SYS_SPEC
DATASET DBB0CFG.** READ ID_GROUP DB2ADM
SYSPROG
DATASET DB10CFG.** READ ID_GROUP DB2ADM
DATASET DB2CAT0.** READ ID_GROUP DB2ADM
SYSPROG
DATASET DB2DUET.** READ ID_GROUP DB2ADM
DATASET DB2GCFG.** READ ID_GROUP DB2ADM
SYSPROG
DATASET IBMZSEC.** UPDATE ID_GROUP SYSAUDIT READ ID_GROUP SYSPROG
SYSPROG ZSECUSR
ZSECUSR
DATASET IBMZSEC.CKR* READ ID_GROUP SYSAUDIT READ ID_GROUP SYSPROG
SYSPROG ZSECUSR
ZSECUSR
DATASET RES.** READ SYS_SPEC
DATASET ROBVH.*.** ALTER ID_USER
DATASET ROBVH4.** ALTER ID_USER
DATASET ROBVH5.** ALTER ID_USER
You could also use Crosstab to collect information from different profile segments by using the COMPLEX field as the cell key, after all, COMPLEX has the same value for all profiles in one RACF database:
//JCLLIB JCLLIB ORDER=(IBMZSEC.CKRPARM,CKR.V2R2.SCKRPROC)
// INCLUDE MEMBER=C2R$PARM
//CARLA EXEC C2RC
//REPORT DD DISP=(NEW,PASS),DSN=&REPORT
//SYSIN DD *
alloc type=racf active /* Active RACF database */
alloc type=output dd=report
newlist type=racf dd=report
define tso(flag) boolean where segment=tso
select class=user mask=robvh*
sortlist complex profile(8) special operations auditor uid(9),
tso tmsize("MaxSize",7)
//*
//CROSSTAB EXEC PGM=IKJEFT1A,COND=(8,LT)
//SYSEXEC DD DISP=SHR,DSN=IBMUSER.REXXLIB
//INPUT DD DISP=(OLD,PASS),DSN=&REPORT
//OUTPUT DD SYSOUT=*,RECFM=VBA,LRECL=255
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
CROSSTAB profile * complex spc opr aud uid tso maxsize
//
The result is:
P R O F I L E L I S T I N G 1 Feb 2016 11:46
ZT00PLEX
Profile Spc Opr Aud Uid TSO MaxSize
ROBVH YES 61634 YES 0
ROBVH2 YES 61634 YES 0
ROBVH3 YES 61634 YES 0
ROBVH4 61634 YES 0
ROBVH6 YES 0
ROBVH7 YES
You could even combine information from different newlist types, provided that:
- The fields added by the subsequent newlists are all defined in the header of the first newlist
- Blanks are left in the output records for field that are unavailable in a newlist
//JCLLIB JCLLIB ORDER=(IBMZSEC.CKRPARM,CKR.V2R2.SCKRPROC)
// INCLUDE MEMBER=C2R$PARM
//CARLA EXEC C2RC
//REPORT DD DISP=(NEW,PASS),DSN=&REPORT
//SYSIN DD *
alloc type=racf active /* Active RACF database */
/* CKFREEZE with catalog information */
alloc type=ckfreeze DSN=IBMZSEC.DATA.SZT01.CKFREEZE
alloc type=output dd=report
newlist type=racf dd=report
define tso(flag) boolean where segment=tso
define has_alias("Alias",5,hdr$blank) boolean where class='impossib'c
select class=user mask=robvh*
sortlist complex profile(8) has_alias,
special operations auditor uid(9) tso,
tmsize tlproc
newlist type=dsn dd=report
define has_alias("Alias",5,flag) true
select dsn=robvh*.** exists(catalog_alias)
summary complex catalog_alias("Profile",8) has_alias count(nd)
//*
//CROSSTAB EXEC PGM=IKJEFT1A,COND=(8,LT)
//SYSEXEC DD DISP=SHR,DSN=IBMUSER.REXXLIB
//INPUT DD DISP=(OLD,PASS),DSN=&REPORT
//OUTPUT DD SYSOUT=*,RECFM=VBA,LRECL=255
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
CROSSTAB profile * complex alias spc opr aud uid tso maxkb
//
The result looks like
P R O F I L E L I S T I N G 1 Feb 2016 14:46
ZT00PLEX
Profile Alias Spc Opr Aud Uid TSO MaxKB
ROBVH YES YES 61634 YES 0
ROBVH2 YES YES 61634 YES 0
ROBVH3 YES 61634 YES 0
ROBVH4 61634 YES 0
ROBVH6 YES 0
ROBVH7 YES
Usage notes
If the layout of columns appears distorted, this may be caused by the format of the (temporary) work file. Crosstab works best with RECFM=VBA files, it uses the record length to determine the length of the last field on the lines. Adding a dummy field at the end of the SORTLIST command may give Crosstab the necessary information about the length of the last field.
Crosstab supports up to 19 fields in both the input file and in the command parameters. You do not have to use all fields from the input file.
Download (right-click and Save Link As...):