Thursday, February 24, 2011

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

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):


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:
  1. 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.
  2. 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!


  1. Hi Robert,
    I've been re-reading your comments on Roles and Trusted Contexts, specifically to solve my problem of allowing our many programmers to run Optim 'Tuning/Explain' functions against our Production user objects. While many of our programmers have SELECT auth for these tables, they don't have 'change auths'. I'm still on V9 NFM so I'm concluding I'd be better of waiting until we migrate to V10. Frankly, I'd also like to let all the programmers share all the same one set of 'explain' tables that optim wants so I don't have so many to migrate from one DB2 version to the next. Any thoughts on that? Thanks,
    Roy Reynolds (univ of california, offc of the president)

  2. I've certainly heard of people using one set of EXPLAIN tables for many different individuals (aliases can be useful in this regard). Not at all uncommon (I believe that DB2 V8 introduced this capability).

    The new EXPLAIN privilege provided by DB2 10 for z/OS is tailor-made for the situation you're facing. A user (a programmer, for example) with this privilege can successfully EXPLAIN SQL statements without having the privileges needed to actually execute the statements.

  3. Hi Robert,

    I'm trying to restrict access (select,update,insert,delete) by assigning a bindagent priviledge by following the instructions in the z/OS DB2 manuals. I still notice that DML access are still checked on the submitter and not the plan owner. Any tips?

    Environment: DB2 v10 z/OS RACF security.

    Thanks in advance.

    1. Sorry about the delayed response.

      This is just the way it works in a DB2 10 (and earlier) environment - the OWNER keyword (referring to the owner of a DB2 package) is not honored when the RACF exit is used to control DB2 authorization. Instead - as you've seen - RACF checks the authorization of the ID running the BIND process (you referred to a plan owner, but you may have meant package owner - a plan owner simply has to be able to execute packages in the plan's PKLIST).

      There is some good news for you: this problem was fixed with DB2 11 for z/OS. You can read about the DB2 11 enhancements in this area in the IBM "redbook" titled "DB2 11 for z/OS Technical Overview," downloadable from this URL: Check out section 10.1, and especially section 10.1.1.


  4. Hey Robert,

    Craig again. This time I'm looking into whether TRUSTED CONTEXT makes sense (or indeed, whether it is possible) for a client that is using .NET with the IBM Data Server Driver Package (as we discussed on a different post here). I see in the DB2 10 Managing Security manual (that is the version they're at), that the DB2 Universal Java Driver, DB2 CLI/ODBC Driver and WSAS provide support for trusted context... but no mention of the .NET Driver... Also, no mention of Microsoft IIS (which is what they are currently using)... Thanks for any light you can shed on this!


    1. Hello, Craig.

      Yes, the .NET provider that's part of the IBM Data Server Driver Package supports trusted connection capability for .NET clients.

      As to whether you need the IBM Data Server Driver Package (to which organizations are entitled via DB2 Connect licensing) versus Microsoft IIS, that might depend on the degree to which you want to exploit DB2 trusted connection technology.

      At a basic level, one might want to create a DB2 ROLE and TRUSTED CONTEXT for the purpose of ensuring that a network-attached application's DB2 authentication credentials (ID and password) can be used to access DB2 data only in the context of connections that come from a particular application server instance (or instances), identified by IP address (or addresses). For that use, it's possible that IIS will suffice (I'm not certain of that).

      On a more sophisticated level, you might want to do something like this:
      * Have the actual end-user IDs of those who are using an application that network-connects to DB2 flow to the DB2 server, in addition to the ID used by the application to establish a DB2 connection. This might be desired for auditing purposes, or for more fine-grained data access control.
      * Map end users' network authentication IDs to RACF IDs, either in a 1-to-1 or a many-to-one way (the latter might involve mapping all user IDs in a particular registry to one RACF ID).

      For these and similar purposes, an application has to explicitly acquire a trusted connection to a DB2 server, and then has to "switch" IDs for that connection, from the application's ID to a user's ID. To do that, I'm virtually certain that you need support provided by the IBM client (in this case, the IBM Data Server Driver Package). Here is a link to information in IBM's Knowledge Center on the Web for establishing a trusted connection to a DB2 server from a .NET client:

      Don't get hung up on that item's reference to a DB2 for LUW server. From a client application perspective the mechanics of getting a trusted connection to a DB2 server are the same regardless of whether the server is on a z/OS or an LUW system.

      Hope this helps.