SQL: -551 in CICS application

Products

• Top Secret

Issue/Introduction

An SQL error -551 is produced in a CICS application accessing DB2 tables

                                                                             
MSG00020  ACCESS NOT POSSIBLE  VIEW/RECORD: UTLTEST, SQL: -551

 

The program TST001 which returns the message was bound to the PROD environment.

The user executing the CICS transaction (USER001)  has access rights to the DB2TABLE  PROD.UTLTEST

Environment

Top Secret  Release 16.0

Top Secret option for DB2 Release 1.3

Cause

The user did not have access to the DB2TABLE   PROD.UTLTEST

 

Resolution

In order to check if there is any violation during the security check it is necessary to enable the following traces in Top Secret and in Top Secret option for DB2

1.- Enable a sectrace for the user doing the test. To do so execute the following steps:

a.- Add the TRACE attribute to the user.

          TSS ADDTO(user) TRACE

 

b.- Update the Control Option SECTRACE to specify ACT,WTL

          F TSS,SECTRACE(ACT,WTL)

 

c.- Enable a TSS for DB2 trace running the following job:

//jobcard
//CADB2TRC EXEC PGM=CADB2TRC,
//         PARM='TRACE=ON,TYPE=ALL,SUBSYS=ssss'

      sssss = the DB2 subsystem where you are experiencing the problem.

 

d.-Reproduce the problem (all the activity done by the user will appear in Syslog)

 

e.- Update the Control Option SECTRACE to specify OFF and deactivate the trace

         F TSS,SECTRACE(OFF)

 

f.- Remove the TRACE attribute from acid.

        TSS REMOVE(user) TRACE

 

g.- Deactivate the TSS for DB2 trace by running the following job:

 

//jobcard
//CADB2TRC EXEC PGM=CADB2TRC,
//         PARM='TRACE=OFF,TYPE=ALL,SUBSYS=ssss'

 sssss = the DB2 subsystem used to enable the trace.

 

Reviewing the output of the TSS for DB2 trace you will probably see violations.

For example something like this:

  CADB2SEC - 00000598: ASCB=00FBBA00            TCB=009A0088
  CADB2SEC - 00000598: ----- DB2 Authorization Parameters -----
  CADB2SEC - 00000598: Privilege        = 0050     SELECT
  CADB2SEC - 00000598: Resource Class   = V        DB2VIEW
  CADB2SEC - 00000598: Object qualifier = PROD
  CADB2SEC - 00000598: Object name      = UTLTEST
  CADB2SEC - 00000598: ----- Authorization IDS -----
  CADB2SEC - 00000598: AUTHCHK ID       = USERTST
  CADB2SEC - 00000598: Primary authid   = USER001
  CADB2SEC - 00000598: Secondary IDs    = OMVSGR99 
  CADB2SEC - 00000598: ----- Control Information -----
  CADB2SEC - 00000598: Authid checked   = AUTHCHK
  CADB2SEC - 00000598: Static/dynamic   = Static
  CADB2SEC - 00000598: ----- Authorization Requests and Results -----
  CADB2LTS - 00000598: USERTST  signed on:  8D17BA50
  CADB2SEC - 00000598: |DB2TABLE|PROD.UTLTEST                         
       |SELECT  |USERTST
  CADB2SEC - 00000598: #SECUR results:  R15 = 08 feedback = 08 detail = 88
 

In this case the security check to the DB2TABLE PROD.UTLTEST was not done using the user USER001 but using the user USERTST.

The problem was solved by permitting the user USERTST access to  DB2TABLE PROD.UTLTEST  

 

It is possible to see other kinds of violations in the TSS for DB2 trace so the solution will depend on the violations seen in the trace.

Additional Information

Trace Top Secret Option for Db2 Authorization Calls