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.

Updates to Chatbot API and Tooling

Build Chatbots with IBM Bluemix

If you have been coding up your own chatbot using the IBM Watson Conversation service on IBM Bluemix, then you might be aware of the new features in Conversation API. Earlier this year I wrote a small tool to manage chatbot workspaces (read here the blog about the management tool). I updated the tool to the newest API version and added basic support for one of the new API functions.

You can now invoke my "Watson Conversation Tool" with the "-logs" parameter. It retrieves recent interactions, i.e., processed messages with all the details. The logs are great to see what's going on (of course), to understand why a specific response was generated by Watson Conversation and hence to improve a chatbot.

[henrik@mymachine] python wctool.py -logs -id "123123b8-123b-1234-a656-6dxx5a2xxf15"


{
  "pagination": {},
  "logs": [
    {
      "log_id": "fba37b8c-a4a8-4ec1-a2d0-7c361d24f441",
      "response_timestamp": "2017-05-24T08:02:20.882Z",
      "request": {
        "input": {
          "text": "@db2bm Was liegt heute an, wie wird das Wetter?"
        },

...

      "request_timestamp": "2017-05-24T08:02:18.494Z"
    }
  ]
}


The API allows to filter, sort and paginate the output. If you want that added to my tool, let me know by leaving feedback or opening an issue.

Chatbot Hackathon: Lessons Learned and Tips

Recently, I was a coach at a hackathon in Germany. Students were tasked to build solutions for a given topic and the solution had to include a chatbot based on the IBM Watson Conversation service. It was impressive how broad the solutions were, what other services were integrated with a chatbot and how easy it was to build a solution using IBM Bluemix. The following “lessons learned” and tips are from my work as coach. They hopefully help you design your own great chatbot.

Chatbots with IBM Watson

Lessons Learned

Hackathons are intense events and require sharp focus and quick decisions. They pack everything from finding the right idea (brainstorming), defining a solution, its users and development phases (using Design Thinking) to building a prototype / minimal viable product (MVP) and pitching it. The first lessons learned and tips deal with the conversation, the dialog, the language itself: