August 25, 2017 | Written by: Henrik Loeser
Categorized: Data Analytics | How-tos | Security
Share this post:
I recently introduced you to Cloud App Security. Now, you have that new cloud-based app. The data is going to be stored in DB2 (on or off the cloud). You want to protect the data, but that cloud app user needs read and write access to the database. You don’t want to open the floodgates to the database for that user? Ok, here is a way to secure your Db2 data and still provide the required 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. To utilize it, specify connection attributes like the userid, the originating address and its security or encryption level (SSL in use?). Those properties define a scenario, the so-called trusted context. When those properties match the current connection attributes, 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:
- Grant the needed privileges to a new, application-specific role.
- Revoke all privileges from the userid utilized by the application.
- 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 address may be “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, we create the trusted context “webapptrust”:
create trusted context webapptrust
based upon connection using system authid webuser
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.
Learn more about that topic and 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