Surrogates, my other self, SET SESSION AUTHORIZATION, and where it is recorded (DB2 Security)

DB2 allows users to switch to different identities. One way is through the means of so-called trusted contexts (which I won't discuss today), the other option is to call SET SESSION AUTHORIZATION (old: SET SESSION_USER). If properly authorized as individual user or member of a group, then a user can switch from the current userid to an authorized other userid and execute SQL statements within that authorization context.

As an example, this feature can be used to work with both personal userids (e.g., for password management purposes) and technical accounts (no login or connection possible, but able to execute SQL and own database objects). In early versions of DB2 this user switching capability was a privilege of DBADM and SYSADM users. However, with database security tightening from release to release already in DB2 9.1 the SETSESSIONUSER privilege was introduced (link to old DB2 9.1 Information Center).

I recently got asked where this privilege is recorded in the DB2 system catalog. Search engines don't seem to easily return an answer for that question. So here we go:

• DB2 has a page "Using the system catalog for security information" in its Information Center.
• On that page the SYSCAT.SURROGATEAUTHIDS catalog view is listed, but not linked to.
• The system catalog SYSCAT.SURROGATEAUTHIDS records information about users and groups who hold the SETSESSIONUSER privilege for userids and also information about trusted contexts.

Let's give it a try:

hloeser@rotach:~$ db2 connect to hltest

   Database Connection Information

 Database server        = DB2/LINUX 10.1.2
 SQL authorization ID   = HLOESER
 Local database alias   = HLTEST

hloeser@rotach:~$ db2 "select * from syscat.surrogateauthids"
GRANTOR                                                                                                                          TRUSTEDID                                                                                                                        TRUSTEDIDTYPE SURROGATEAUTHID                                                                                                                  SURROGATEAUTHIDTYPE AUTHENTICATE CONTEXTROLE                                                                                                                      GRANT_TIME               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------
HLOESER                                                                                                                          SYSATSCONTEXT                                                                                                                    C             PUBLIC                                                                                                                           G                   N            -                                                                                                                                2013-02-01-10.39.34.817754

  1 record(s) selected.


hloeser@rotach:~$ db2 "grant setsessionuser on user db2userl to user db2tech1"
DB20000I  The SQL command completed successfully.
hloeser@rotach:~$ db2 "grant setsessionuser on user db2user2 to group mqm"
DB20000I  The SQL command completed successfully.

hloeser@rotach:~$ db2 "select * from syscat.surrogateauthids"
GRANTOR                                                                                                                          TRUSTEDID                                                                                                                        TRUSTEDIDTYPE SURROGATEAUTHID                                                                                                                  SURROGATEAUTHIDTYPE AUTHENTICATE CONTEXTROLE                                                                                                                      GRANT_TIME               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------
HLOESER                                                                                                                          SYSATSCONTEXT                                                                                                                    C             PUBLIC                                                                                                                           G                   N            -                                                                                                                                2013-02-01-10.39.34.817754
HLOESER                                                                                                                          DB2TECH1                                                                                                                         U             DB2USER1                                                                                                                         U                                -                                                                                                                                2013-04-15-11.25.39.514764
HLOESER                                                                                                                          MQM                                                                                                                              G             DB2USER2                                                                                                                        U                                -                                                                                                                                2013-04-15-11.26.08.813650

  3 record(s) selected.

All information seems to be in.