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 your 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 user id, 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 user id can either switch to another user id or inherit privileges from a specified role. This allows you to secure the database via three simple steps:
- Grant the needed privileges to a new, application-specific role.
- Revoke all privileges from the user id utilized by the application.
- Create a trusted context for that user id and the application's connection and grant the role from step 2 and its privileges to the user id.
What would those steps look like in actual SQL? For this example, we assume that the user id "webuser" is utilized by the application. The app may be hosted at mytrustedapp.example.com. We want to allow access on SSL-secured connections. For the first step, we need to create a new role and grant all the necessary 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 an easy way to retrieve the existing privileges. Revoke them and later check back that no privilege has been left out. If you started with a "fresh" user id, there is probably only the CONNECT privilege and maybe a 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
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 an SSL-protected connection is necessary. When the attributes are matched successfully, the user id, "webuser," is granted the role webapprole and its associated privileges for the duration of the trusted connection. Therefore, "webuser" is able to connect to the database and to access the table "webtable" again. If the user tries to access the database via other means, e.g., by a local IPC- or TPC-based connection, the attempt would fail.