DB2 for z/OS Roles: Trust Me on This One (Part 1)

Although roles and trusted contexts have been on the mainframe DB2 scene for over three years (they were introduced with DB2 9 for z/OS), there is still some misunderstanding in the DB2 community regarding what these features are and how they can be used. That point was underscored by a question posted a few weeks ago to the DB2-L forum. The person asking the question had a need to provide DBAs in certain geographies with the privileges needed to get their work done, but in a way that would deny them access to data in user (versus system) tables. Could DB2 roles address this need? The answer to that question is, "Partially." That is to say, roles could provide part of the solution the questioner was seeking, though maybe not the part that she had in mind. In the rest of this entry I'll try to clear the air with respect to the meaning and utility of roles (and their relatives, trusted contexts). In a follow-on Part 2 entry, I'll address the challenge of enabling a DBA or system administrator to do his or her job whilst preventing that person from being able to access data in user tables.

First, it's important to understand that the introduction of roles and trusted contexts did not introduce any new DB2 privileges. Rather, this new security capability gives you a new way to assign privileges (via roles, instead of directly to users' IDs) and to limit the scope of the exercising of those privileges (by restricting their use to trusted connections that conform to defined trusted contexts). The ability to manage DB2 security in this way can be particularly welcome when you're faced with a common client-server computing scenario: an application, which may run on a Java or a .NET or some other such application server, issues SQL statements that are executed through DB2's Distributed Data Facility (DDF). Individual users authenticate at the app 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 for z/OS server (as is typically the case for programs that use database interfaces such as JDBC or ODBC or ADO.NET), the application's generic authorization ID has to be granted table privileges (SELECT, INSERT, UPDATE, DELETE) on target objects to enable successful statement execution. Trouble is, any number of programmers might know the application's DB2 authorization ID and password (because, as mentioned, these are embedded in program code), and someone might use that ID and its privileges to access data in the database from outside the application. Security is weakened in that case, and the result could be unauthorized viewing and changing of data values in DB2 tables.

Enter roles and trusted contexts. In a DB2 9 (new function mode) or DB2 10 environment, instead of granting a set of privileges required to execute an application's dynamic SQL statements to a generic authorization ID, you could grant the privileges to a role. Now, here's another important point: granting privileges to a role and then stopping there accomplishes next to nothing. Why? Because DB2 has no way of knowing who can use the role's privileges and under what circumstances the role can be used. That's where a trusted context comes in. You could define a trusted context that would limit the exercise of a role's privileges to users connecting to DB2 from a particular application server (identified by an IP address), using an application that provides to DB2 a particular authorization ID (referred to as a "system" auth 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 of no use unless that ID can have the privileges of the aforementioned role, and it can have those privileges only when the connection to DB2 is from the app server whose IP address is an attribute of the trusted context that specifies the conditions of the role's utilization; thus, security is way tighter than it would be if the application's generic ID had privileges that could be exercised regardless of the "come from" connection type.

That's pretty cool, but you can go further still in protecting your DB2 data from unauthorized access. You see, in addition to restricting the exercise of a role's privileges to (for example) connections from a particular application server through an application that provides a particular "system" auth ID to DB2, you can set things up so that only certain individual user IDs can use the role in the defined connection context. You might be wondering how that's done, given a situation in which an application provides a generic authorization ID to DB2. Actually, you have a couple of choices here:

• If you use IBM's WebSphere Application Server, you can set the database property ‘propagateClientIdentityUsingTrustedContext’ to ‘true’.
• There are APIs for JDBC (e.g., getDB2Connection), CLI (e.g., the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute and the SQLSetConnectAddr function) and .NET (the connection string keyword UserID corresponds to the end user) that enable an application to establish a trusted connection to DB2 and to reuse a trusted connection with different end-user IDs.

Not only does this functionality enable you to restrict a role's privileges to particular users of a particular trusted connection, it also enables you to get DB2 (and RACF) audit information that contains end user's individual IDs, even when those users are connecting to DB2 through an application that itself provides a single generic authorization ID to DB2.

Now, a few additional items of information about trusted contexts:

• I have been using the example of an off-mainframe application server that provides a generic authorization ID to DB2, but a trusted context can also be defined for a local connection to DB2 through a batch job or a started task.
• Restricting the use of a role to individual IDs using a trusted connection is done through the WITH USE FOR clause of the CREATE TRUSTED CONTEXT statement (and the ADD USE FOR and DROP USE FOR and REPLACE USE FOR clauses of ALTER TRUSTED CONTEXT). In this way you can even assign different roles to different users of a defined trusted connection -- just keep in mind that your app server needs to be able to propagate individual user IDs to DB2, or your application needs to use the previously mentioned APIs to send individual user IDs to DB2.
• When you omit the WITH USE FOR clause of CREATE TRUSTED CONTEXT, it is as though you'd specified WITH USE FOR PUBLIC WITHOUT AUTHENTICATION. This means that the privileges of the role associated with the trusted context can be used by any individual that (for example) connects to DB2 from a particular application server through an application that provides DB2 with a particular system auth ID. In some cases, this will be a plenty good set-up.
• A trusted context can be set up so as to make the context's default role the owner of any object created using the role's privileges.
• A DB2 for z/OS requester can use a trusted context (and can switch use of an existing trusted connection to different individual user IDs) based on entries in the requesting DB2's Communications Data Base. 
• When a user establishes a trusted connection with a DB2 subsystem (a connection that's 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 is that roles and trusted contexts are effective in limiting the exercise of privileges if those privileges are not widely granted to users' DB2 auth IDs.

Recall that the DB2-L question-asker to whom I referred at the start of this entry was interested in restricting DBAs' access to data in user tables, based on the geographic location of said DBAs. The geography-specific aspect of this requirement could potentially be addressed through the use of roles and trusted contexts. The privileges that would let a DBA be a DBA -- but without access to data in user tables -- could be granted to a role, and that role could be associated with a trusted context that would have as attributes a set of IP addresses of servers located in the geographic area in which the data-access-restricted DBAs work. Alternatively, if these DBAs were restricted to using a certain batch job for issuing SQL statements, the trusted context could have as an attribute the associated job name.

But what about that restriction concerning the DBAs' access to data in user tables? How can that be implemented? With DB2 9 for z/OS, it would be a bit of a chore. In a DB2 10 environment, it would be easy. I'll fill you in on this and some other new DB2 10 security-related features (including some that pertain to trusted contexts) in my next entry. Look for that Part 2 post in a week or so.