DB2 Security Mysteries, Surrogates and Trusted Contexts

DB2 Security Question

Recently, I was contacted regarding an older blog entry discussing the DB2 security feature of surrogates. During an audit a strange entry was found in the catalog table SYSCAT.SURROGATEAUTHIDS. Whether I could take a look. So, let us take the security-themed tour through some DB2 catalog tables together.

What is the strange entry? In a new database, created as regular or restrictive database, the following entry is found. What does it mean and what is SYSATSCONTEXT (highlighted below)?

DB: HLTEST => select * from syscat.surrogateauthids

GRANTOR                                                                                                                          TRUSTEDID                                                                                                                        TRUSTEDIDTYPE SURROGATEAUTHID                                                                                                                  SURROGATEAUTHIDTYPE AUTHENTICATE CONTEXTROLE                                                                                                                      GRANT_TIME               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------
HLOESER                                                                                                                          SYSATSCONTEXT                                                                                                                    C             PUBLIC                                                                                                                           G                   N            -                                                                                                                                2016-10-18-18.08.20.374537

  1 record(s) selected.


The DB2 Knowledge Center provides an overview of all security-related catalog views. As can be seen, SURROGATEAUTHIS "lists the authorization IDs for which another authorization ID can act as a surrogate". The table is not mentioned in the document of default privileges granted on creating a new database. So let's go to the roadmap to catalog views and from there to the entry for SURROGATEAUTHIDS. SURROGATEAUTHIDS is found in the category of "protected tables".

The catalog entry above indicates that I, as database and instance owner, have granted something to "SYSATSCONTEXT" when the database was created. The TRUSTEDIDTYPE is "C" and means the record belongs to a trusted context. Thus, as next step, the catalog views SYSCAT.CONTEXTS and SYSCAT.CONTEXTATTRIBUTES should be visited:

DB: HLTEST => select * from syscat.contexts

CONTEXTNAME                                                                                                                      CONTEXTID   SYSTEMAUTHID                                                                                                                     DEFAULTCONTEXTROLE                                                                                                               CREATE_TIME                ALTER_TIME                 ENABLED AUDITPOLICYID AUDITPOLICYNAME                                                                                                                  AUDITEXCEPTIONENABLED REMARKS                                                                                                                                                                                                                                                      
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------- -------------------------- ------- ------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSATSCONTEXT                                                                                                                            100 SYSATS                                                                                                                           -                                                                                                                                2016-10-18-18.08.20.374537 2016-10-18-18.08.20.374537 Y                   - -                                                                                                                                N                     -                                                                                                                                                                                                                                                            

  1 record(s) selected.

DB: HLTEST => select * from syscat.contextattributes

CONTEXTNAME                                                                                                                      ATTR_NAME                                                                                                                        ATTR_VALUE                                                                                                                       ATTR_OPTIONS                                                                                                                   
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SYSATSCONTEXT                                                                                                                    ENCRYPTION                                                                                                                       NONE                                                                                                                             -                                                                                                                              

  1 record(s) selected.

The above entries show details for the trusted context. The "ATS" indicates it is part of the DB2 Administrative Task Scheduler. In a non-restrictive database, by default, any user could add a task to the scheduler. Those tasks are later executed as the specific user, i.e., using the authorization ID of that user. Hence, the trusted context is used. They allow to execute SQL statements on behalf of an user (see my old blog entry on "power of attorney and trusted contexts").

Conclusion: The strange catalog entry belongs to the infrastructure of the DB2 Administrative Task Scheduler and seems to be used run scheduled task on behalf of DB2 users.