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

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:

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!