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.
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.