So, in part 1 of this two-part entry, which I posted a couple of weeks ago, I referred to a question someone had sent to the DB2-L discussion forum. The person asking the question wanted to know if the roles and trusted contexts functionality introduced with DB2 9 for z/OS could be used 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. I wrote in my part 1 post that roles and trusted contexts could address part, but not all, of the requirement raised in the discussion forum question. In particular, a role could be used with a trusted context to limit the scope of the use a set of privileges to DBAs in one or more specific geographic locations -- this by associating the role with a trusted context having as attributes a set of IP addresses corresponding to servers located in the geographic area(s) in which the data-access-restricted DBAs work (alternatively, if the DBAs were restricted to using a certain batch job for issuing SQL statements, the trusted context could have as an attribute the name of that job).
What the role-and-trusted-context combination couldn't do, in and of itself, is satisfy the requirement that the DBAs in question be able to do their job without having -- just because they are DBAs -- access to data in user tables. More specifically, roles and trusted contexts wouldn't offer an easy way of addressing this need in a DB2 9 for z/OS environment. That's because restricting DBAs' access to user data is a privilege thing, not a role thing; thus, what you'd do to provide DBAs with the ability to do their job without having access to data in user tables would depend on the release of DB2 for z/OS you were running. If it were DB2 10, you could use a new option of the GRANT statement (I'm showing a GRANT to an auth ID -- you could also grant the privilege to a role):
GRANT DBADM WITHOUT DATAACCESS TO SMITH;
If you were running a DB2 release prior to DB2 10, accomplishing the same objective would require you to assign to the DBAs (directly or through a role) DBCTRL authority plus any additional privileges needed to do the job and not included with DBCTRL authority (e.g., ALTER), while withholding privileges such as SELECT. If you're wondering what privileges included with DBADM authority aren't included with DBCTRL, look in the DB2 Administration Guide (in the DB2 9 manual -- eighth edition, December 2010 -- the information is found in Figure 16, in the section titled "Administrative authorities" in Chapter 5).
Regarding the WITHOUT DATAACCESS clause that can be added to a GRANT DBADM statement in a DB2 10 for z/OS environment, note that this is applicable to the new (with DB2 10) "system" DBADM authority. System DBADM differs from "regular" DBADM authority in two important ways:
- A system DBADM can manage ALL the databases in a DB2 10 subsystem, whereas the traditional DBADM authority (which is still available with DB2 10) is granted for a particular database or a list of databases.
- The system DBADM authority, as previously noted, can be granted in a way that disallows access to data in user tables.
Speaking of restricting access to user data, DB2 10 also provides a new system privilege, EXPLAIN, that allows a user to issue EXPLAIN statements (and PREPARE and DESCRIBE statements) without having the authority to execute the statements being EXPLAINed. I like that a lot. It's a way to enable developers to check the access paths of statements in (for example) a production environment without having to first grant to those developers the SELECT and/or INSERT and/or UPDATE and/or DELETE privileges on target tables that they'd otherwise need in order to run EXPLAINs. Because the EXPLAIN system privilege enables you to expand the pool of people who can issue EXPLAINs without broadening access to data in tables, you can give more people that privilege without weakening data security, and with more people able to issue EXPLAINs you'll have a better chance of catching potentially poorly-performing access paths before programs issuing the SQL statements go "live."
Now, on top of the application performance benefits you've probably heard about, you have another incentive to get to DB2 10: better management of data access privileges. Lock that data down, folks!
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.
