Enabling PassTicket support for SQL Tuning Services

SQL Tuning Services supports the use of RACF® PassTickets as an alternative to using RACF passwords and password phrases. The use of PassTickets removes the need to send RACF passwords and password phrases across the network and eliminates the need for password maintenance for tuning profiles and the repository database. Complete this step only if your environment uses PassTickets.

Procedure

  1. Select the appropriate scenario based on your SQL Tuning Services environment. The following three scenarios are covered in this topic:
    • Scenario 1: The repository database and the tuning target database are on the same system.
    • Scenario 2: The repository database and the tuning target database are on different systems, but are in the same RACF database.
    • Scenario 3: The repository database and the tuning target database are on different systems, and are also in a separate RACF database.
  2. Modify the example RACF commands for your environment:
    • Change <APPLNAME> to the correct value for your Db2® subsystem, which can correspond to any of the following values in the results of issuing the -DISPLAY DDF command for your subsystem:

      (1) IPNAME
      (2) GENERICLU (after the NETID)
      (3) LUNAME (after the NETID)

      These values are evaluated in the sequence indicated: an IPNAME takes precedence over a GENERICLU, which takes precedence over an LUNAME.

    • Change <SQLTSID> to the started task ID for the SQL Tuning Services started task.
    • Change <DIST_OWNER> to the ID assigned to the Db2 DIST address space.
    Note: In the following RACF commands, ACCESS(UPDATE) allows the generation of PassTickets and ACCESS(READ) allows the evaluation of PassTickets.
  3. Run the commands.

Scenario 1 - the repository database and the target database are on the same data sharing group or subsystem

The commands to be issued depend upon whether your environment is using legacy or enhanced PassTickets.

If you are using legacy PassTickets, issue the following commands:
SETROPTS CLASSACT(PTKTDATA)   
SETROPTS RACLIST(PTKTDATA)    
SETROPTS GENERIC(PTKTDATA)

RDEFINE PTKTDATA <APPLNAME> SSIGNON(KEYMASKED(1234567890ABCDEF)) +
 APPLDATA('NO REPLAY PROTECTION') UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<APPLNAME>.* UACC(NONE)                     
PERMIT IRRPTAUTH.<APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)
PERMIT IRRPTAUTH.<APPLNAME>.* CLASS(PTKTDATA) ID(<DIST_OWNER>) ACCESS(READ)

SETROPTS RACLIST(PTKTDATA) REFRESH
If you are using enhanced PassTickets, issue the following commands:
SETROPTS CLASSACT(PTKTDATA)   
SETROPTS RACLIST(PTKTDATA)    
SETROPTS GENERIC(PTKTDATA)

RDEFINE PTKTDATA <APPLNAME> SSIGNON(EPTKEYLABEL(MY.HMAC.KEY) +
 REPLAY(YES)) UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<APPLNAME>.* UACC(NONE)                     
PERMIT IRRPTAUTH.<APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)
PERMIT IRRPTAUTH.<APPLNAME>.* CLASS(PTKTDATA) ID(<DIST_OWNER>) ACCESS(READ)

SETROPTS RACLIST(PTKTDATA) REFRESH

Scenario 2 - the repository database and the target database are on different subsystems or data sharing groups, but in the same RACF database

The commands to be issued depend upon whether your environment is using legacy or enhanced PassTickets.

If you are using legacy PassTickets, issue the following commands:
SETROPTS CLASSACT(PTKTDATA)   
SETROPTS RACLIST(PTKTDATA)    
SETROPTS GENERIC(PTKTDATA)

RDEFINE PTKTDATA <METADATA_APPLNAME> SSIGNON(KEYMASKED(1234567890ABCDEF)) +
 APPLDATA('NO REPLAY PROTECTION') UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<METADATA_APPLNAME>.* UACC(NONE)                      
PERMIT IRRPTAUTH.<METADATA_APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)

RDEFINE PTKTDATA <TARGET_APPLNAME> SSIGNON(KEYMASKED(1234567890ABCDEF)) +     
 APPLDATA('NO REPLAY PROTECTION') UACC(NONE)                         
RDEFINE PTKTDATA IRRPTAUTH.<TARGET_APPLNAME>.* UACC(NONE)                     
PERMIT IRRPTAUTH.<TARGET_APPLNAME>.* CLASS(PTKTDATA) ID(<DIST_OWNER>) ACCESS(READ)

SETROPTS RACLIST(PTKTDATA) REFRESH
If you are using enhanced PassTickets, issue the following commands:
SETROPTS CLASSACT(PTKTDATA)   
SETROPTS RACLIST(PTKTDATA)    
SETROPTS GENERIC(PTKTDATA)

RDEFINE PTKTDATA <METADATA_APPLNAME> SSIGNON(EPTKEYLABEL(MY.HMAC.KEY) +
 REPLAY(YES)) UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<METADATA_APPLNAME>.* UACC(NONE)                      
PERMIT IRRPTAUTH.<METADATA_APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)

RDEFINE PTKTDATA <TARGET_APPLNAME> SSIGNON(EPTKEYLABEL(MY.HMAC.KEY) +
 REPLAY(YES)) UACC(NONE)                         
RDEFINE PTKTDATA IRRPTAUTH.<TARGET_APPLNAME>.* UACC(NONE)                     
PERMIT IRRPTAUTH.<TARGET_APPLNAME>.* CLASS(PTKTDATA) ID(<DIST_OWNER>) ACCESS(READ)

SETROPTS RACLIST(PTKTDATA) REFRESH

Scenario 3 - the repository database and the tuning target database are on different subsystems or data sharing groups, and also in a separate RACF database

Issue the following commands.
Note: If PassTickets have already been configured for your Db2 systems, verify that the keys that have been used match. See Db2 support for RACF PassTickets.
  • The following commands are for the RACF repository database system.

    The commands to be issued depend upon whether your environment is using legacy or enhanced PassTickets.

    If you are using legacy PassTickets, issue the following commands:
    SETROPTS CLASSACT(PTKTDATA)   
SETROPTS RACLIST(PTKTDATA)    
SETROPTS GENERIC(PTKTDATA)

RDEFINE PTKTDATA <METADATA_APPLNAME> SSIGNON(KEYMASKED(1234567890ABCDEF)) +
 APPLDATA('NO REPLAY PROTECTION') UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<METADATA_APPLNAME>.* UACC(NONE)                      
PERMIT IRRPTAUTH.<METADATA_APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)

RDEFINE PTKTDATA <TARGET_APPLNAME> SSIGNON(KEYMASKED(1234567890ABCDEF)) +
 APPLDATA('NO REPLAY PROTECTION') UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<TARGET_APPLNAME>.* UACC(NONE)                     
PERMIT IRRPTAUTH.<TARGET_APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)

SETROPTS RACLIST(PTKTDATA) REFRESH
    If you are using enhanced PassTickets, issue the following commands:
    SETROPTS CLASSACT(PTKTDATA)   
SETROPTS RACLIST(PTKTDATA)    
SETROPTS GENERIC(PTKTDATA)

RDEFINE PTKTDATA <METADATA_APPLNAME> SSIGNON(EPTKEYLABEL(MY.HMAC.KEY) +
 REPLAY(YES)) UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<METADATA_APPLNAME>.* UACC(NONE)                      
PERMIT IRRPTAUTH.<METADATA_APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)

RDEFINE PTKTDATA <TARGET_APPLNAME> SSIGNON(EPTKEYLABEL(MY.HMAC.KEY) +
 REPLAY(YES)) UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.<TARGET_APPLNAME>.* UACC(NONE)                     
PERMIT IRRPTAUTH.<TARGET_APPLNAME>.* CLASS(PTKTDATA) ID(<SQLTSID>) ACCESS(UPDATE)

SETROPTS RACLIST(PTKTDATA) REFRESH

  • The following commands are for the RACF target database system.

    If you are using legacy PassTickets, issue the following commands:
    SETROPTS CLASSACT(PTKTDATA)                                          
SETROPTS RACLIST(PTKTDATA)                                           
SETROPTS GENERIC(PTKTDATA)     
                                      
RDEFINE PTKTDATA <TARGET_APPLNAME> SSIGNON(KEYMASKED(1234567890ABCDEF)) +   
 APPLDATA('NO REPLAY PROTECTION') UACC(NONE)                         
RDEFINE PTKTDATA IRRPTAUTH.<TARGET_APPLNAME>.* UACC(NONE)                      
PERMIT IRRPTAUTH.<TARGET_APPLNAME>.* CLASS(PTKTDATA) ID(<DIST_OWNER>) ACCESS(READ) 

SETROPTS RACLIST(PTKTDATA) REFRESH
    If you are using enhanced PassTickets, issue the following commands:
    SETROPTS CLASSACT(PTKTDATA)                                          
SETROPTS RACLIST(PTKTDATA)                                           
SETROPTS GENERIC(PTKTDATA)                   

RDEFINE PTKTDATA <TARGET_APPLNAME> SSIGNON(EPTKEYLABEL(MY.HMAC.KEY) +
 REPLAY(YES)) UACC(NONE)                         
RDEFINE PTKTDATA IRRPTAUTH.<TARGET_APPLNAME>.* UACC(NONE)                      
PERMIT IRRPTAUTH.<TARGET_APPLNAME>.* CLASS(PTKTDATA) ID(<DIST_OWNER>) ACCESS(READ) 

SETROPTS RACLIST(PTKTDATA) REFRESH