Secure Your Db2 Data Base - Using Trusted Contexts

Db2 Security - Trusted Context

You have that new app, maybe even in the cloud, with that user needing read and write access to the database? Don't want to open the floodgates for that favorite users? Ok, here is a way to secure your Db2 data and still provide that access. Even if that sounds impossible, trust me. And, I am going to put that "trust me" in context. Read on.

Db2 has a security feature named Trusted Context. Utilizing it, connection attributes like the userid, the originating address and its security or encryption level (is SSL used?) can be used to define a scenario, the so-called trusted context. When those properties are matched, when that trusted relationship has been established, then the userid can either switch to another userid or inherit privileges from a specified role. This allows to secure the database by three simple steps:

1. Grant the needed privileges to a new, application-specific role.
2. Revoke all privileges from the userid utilized by the application.
3. Create a trusted context for that userid and the application's connection and grant the role from step 2 and its privileges to the userid.

How would those steps look like in actual SQL? For the example we assume that the userid "webuser" is utilized by the application. The app may be hosted at mytrustedapp.example.com. We want to allow on SSL-secured connections. In the first step, we need to create a new role and grant all the needed privileges:

create role webapprole;
grant connect on database to role webapprole;
grant grant insert,update,delete,select on webtable to role webapprole;

The above would grant the connect privilege on the database as well as privileges to access and modify data in a table "webtable" to the newly created role "webapprole".

If the "webuser" already had privileges and you want to improve database security, how could you find out the existing privileges? This is something needed for the second step, too. Db2 offers several security-related routines and views. The view PRIVILEGES and the table function AUTH_LIST_AUTHORITIES_FOR_AUTHID provide and easy way to retrieve the existing privileges. Revoke them and later check back that no privilege is left. If you started with a "fresh" userid, there is probably only the CONNECT privilege and maybe few others to take care of.

In the final step, the trusted context "webapptrust" is created:

create trusted context webapptrust based upon connection using system authid webuser enable attributes (address 'mytrustedapp.example.com', encryption 'high') default role webapprole;


The trusted context is enabled. It can be created as disabled or switched on and off using ALTER TRUSTED CONTEXT. Note that the address 'mytrustedapp.example.com' is checked during the creation process and it needs to exist. Thus, the above statement will give an error message. A property of high encryption indicates that a SSL-protected connection is necessary. When the attributes are matched successfully, the userid "webuser". for the duration of the trusted connection, is granted the role webapprole and its associated privileges. Therefore, "webuser" is able to connect to the database and to access the table "webtable" again. If the user would try to access the database via other means, e.g., by a local IPC- or TPC-based connection, the attempt would fail.


If you want to learn more about that topic or discuss the blog with me, then meet me at the upcoming IDUG EMEA and Db2 Aktuell conferences. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.