Securing DB2 Data

Grant privileges to a what, not a who

These days, executives are more concerned than ever about unauthorized access to data entrusted to their organizations. Their fears are justified: a recent survey showed that about a third of those polled would quit doing business with a company they perceived to be guilty of a data security breach. This is why there is such high demand for DB2 experts who can tighten up data access controls.

Role-based security is a great way to protect your organization’s information assets, and it’s probably easier to implement than you think. DB2 roles—and their close relatives, trusted contexts—have been available since the release of DB2 9.5 for Linux, UNIX, and Windows (LUW) and DB2 9 for z/OS. But although these DB2 releases became generally available more than three years ago, many users still seem confused regarding the purpose and advantages of roles and trusted contexts. I’ll try to clear things up in this article.

A better way to manage DB2 privileges

First, the introduction of roles and trusted contexts did not introduce any new DB2 privileges. Rather, this security capability provided a new way to assign and manage privileges. They can now be granted to roles instead of being assigned directly to users’ authorization IDs. You can also limit the scope of granted privileges by restricting their use to trusted connections that conform to defined trusted contexts.

Managing DB2 security this way can be particularly useful when dealing with a common client/server computing scenario: An application running on a Java or a .NET (or some other) application server issues SQL statements that are executed on a DB2 database server (in a DB2 for z/OS environment, the SQL statements would likely flow through the Distributed Data Facility, or DDF). Individual users authenticate themselves at the application server, but the application itself presents to DB2 a generic authorization ID and password that are hard-coded in a program.

If the SQL statements are dynamically prepared at the DB2 server—as is often the case for programs that use database interfaces such as JDBC or ODBC or ADO.NET—the application’s generic authorization ID must be granted table privileges (SELECT, INSERT, UPDATE, DELETE) on target objects to enable successful statement execution.

But lots of programmers could know the application’s DB2 authorization ID and password—because, as mentioned, these are embedded in program code. Someone could then use that ID and its privileges to access data in the database from outside the application, seriously weakening security.

A useful analogy

To use an analogy from the real world, my eldest daughter is less than a year away from getting her driver’s license. If only I could manage her driving with something like DB2 roles and trusted contexts, perhaps I could better control her access to our cars. I could set something up so that she could exercise the privilege of driving only between home and school, and only in the minivan (not the sport sedan). An impossible dream for me, of course, and probably a nightmare from my daughter’s perspective.

Using roles and trusted contexts

But you can do something very similar in DB2 if you use DB2 9 for z/OS in new function mode, or DB2 10 for z/OS. Administrators of DB2 9.5 for LUW, 9.7, and later versions have the same capability: instead of granting to a generic authorization ID a set of privileges required to execute an application’s dynamic SQL statements, you could grant the privileges to a role.

Now, merely granting privileges to a role accomplishes next to nothing. Why? Because DB2 has no way of knowing either who can use the role’s privileges, or the circumstances under which the role can be used at all.

That’s where defining a trusted context comes in. The trusted context limits the exercise of a role’s privileges to users connecting to DB2 from a particular application server—identified by an IP address—through an application that provides to DB2 a particular authorization ID, referred to as a “system” authorization ID.

Because the privileges needed to execute the dynamic SQL statements issued by the application are assigned to a role and not to an ID, the application’s generic authorization ID is useless (in terms of providing someone with a means of accessing DB2 data) unless it has the privileges of the aforementioned role. And it can have those privileges only when it is used to connect to DB2 from the application server whose IP address is an attribute of the trusted context that specifies the conditions under which the role can be used. This way, security is much tighter than it would be if the application’s generic ID had privileges that could be exercised regardless of the “come from” connection type.

But wait, there’s more!

That’s pretty cool, but you can also set things up so that only certain individual user IDs can use the role in the defined connection context.

You have a few choices here:

  • If you use the IBM WebSphere Application Server, you can propagate an end user’s identity to DB2 by setting the database property propagateClientIdentityUsingTrustedContext to 'true'.
  • There are application programming interfaces (APIs) for JDBC (such as getDB2Connection), CLI (the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute and the SQLSetConnectAddr functions), and .NET (where the connection string keyword UserID corresponds to the end user) that can be used by an application to establish a trusted connection to DB2, reuse a trusted connection with a different end-user ID, and propagate that end-user ID to the DB2 server.
  • If the requester is a DB2 for z/OS system, you can provide the “system” authorization ID for a trusted connection in the requester’s communications database (specifically, in the SYSIBM.USERNAMES table). End users’ IDs will be propagated to the DB2 server as the trusted connection is reused.

Not only does this functionality let you restrict a role’s privileges to designated users of a particular trusted connection, it also lets you get DB2 (and Resource Access Control Facility, or RACF) audit information that contains end users’ individual IDs. This works even when those users are connecting to DB2 through an application that itself provides a single generic authorization ID when establishing connections to DB2.

If you do send end-user IDs to DB2 from an application server, you can get even more granular with respect to the roles associated with a given trusted context. For example, a trusted context could have a default role, ROLE_A. Assuming that the application for which the trusted context is defined propagates end-user identities to DB2, you could indicate that another role, ROLE_B, is usable by end user SMITH for a trusted connection by specifying WITH USE FOR SMITH ROLE ROLE_B on the CREATE TRUSTED CONTEXT statement. If you require authentication information—a password, for example—for SMITH to use ROLE_B, you’d add WITH AUTHENTICATION to the preceding WITH USE FOR clause.

Note that when you omit the WITH USE FOR clause of CREATE TRUSTED CONTEXT, it is as though you specified WITH USE FOR PUBLIC WITHOUT AUTHENTICATION. This means that the privileges of the default role associated with the trusted context are available to any individual who uses a trusted connection as defined by the trusted context.

You can even specify in a trusted context definition that a requester must communicate with DB2 using the Secure Sockets Layer (SSL) cryptographic protocol. Just make ENCRYPTION 'HIGH' one of the attributes of the trusted context. (ENCRYPTION 'LOW'  corresponds to 64-bit DRDA encryption.)

Now, here are a couple of important things to remember about trusted contexts:

  • For a mainframe DB2 server, a trusted context can also be defined for a local connection to DB2 through a batch job or a started task.
  • A trusted context can be set up to make the context’s default role the owner of any object created using the role’s privileges.

The catch

When a user establishes a trusted connection with a DB2 subsystem—in accordance with a defined trusted context—he or she has the privileges of the associated role plus any privileges granted directly to his or her ID. The point here: roles and trusted contexts limit the exercise of DB2 privileges only if those privileges are not widely granted to users’ DB2 authorization IDs. The assumption is that you’ll begin to REVOKE privileges previously granted to individual user IDs (and/or to RACF—or equivalent—group IDs) as you phase in the use of roles and trusted contexts.

Setting up role-based security is easier than most people think. And as long as organizations seek better control over their information, there will be a demand for the increasingly fine control over data assets that DB2 provides. It’s the wave of the future, folks. Catch it now, and you’ll be ahead of the game.