![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
- Read this article in our interactive digital edition format!
- Subscribe to IBM Data Management magazine
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.
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.
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
propagateClientIdentityUsingTrustedContextto 'true'. - There are application programming interfaces (APIs) for JDBC (such as
getDB2Connection), CLI (theSQL_ATTR_TRUSTED_CONTEXT_USERIDattribute and theSQLSetConnectAddrfunctions), and .NET (where the connection string keywordUserIDcorresponds 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.USERNAMEStable). 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.
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.
- Learn more in the the previous column
"
DB2 Indexes and Query Performance: Part 1" (IBM Data Management Magazine, Issue 4, 2010).
- Read more articles in
IBM Data Management Magazine.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
