 |
|---|
| 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.