Thursday, January 30, 2020

Db2 for z/OS and RACF, Part 2: Db2-Internal Security

In the Part 1 post of this two-part blog entry, I described how RACF can be used to provide "external" security for a Db2 for z/OS system - external security being concerned with who (i.e., which IDs) can connect to the Db2 system, and how (e.g., in the form of a batch program and/or through CICS and/or as a DRDA requester and/or as a REST client, etc.). That is a very common use of RACF in a Db2 security sense. RACF can optionally be used to also manage Db2-internal security, which is all about what one can do (i.e., what a process with a given ID can do) when one has successfully connected to a Db2 system. This Part 2 blog entry is focused on that second topic: using RACF to manage Db2-internal security. [Note: as in the Part 1 entry, I will be referring explicitly to IBM's RACF - if you use another vendor's z/OS security management product, you can check with the vendor to see how what I have written about RACF pertains to the alternate product.]

For quite a few years, the only way to manage Db2-internal security was through Db2 itself. This was done using the SQL statements GRANT and REVOKE. For example, if an application connecting to Db2 using ID XYZ needed to be able to read data from table T1 using dynamic SQL statements, a DBA would execute a statement like this one:


Several Db2 versions ago, a new option for managing Db2-internal security was introduced: you could do that with RACF, via a Db2-provided exit routine called DSNXRXAC. Before getting further into that, I'll bring up the obvious question: why would an organization want to use RACF to manage Db2-internal security, given that the job could be effectively done with Db2 GRANTs and REVOKEs? I have seen that the RACF route is usually taken because a decision has been made that one team will be responsible for both external and internal Db2 for z/OS security. In such a case, the one team will likely choose the RACF-based approach because RACF can be used for external and internal Db2 security (thanks to the aforementioned Db2-supplied exit), while Db2's built-in functionality is aimed at managing only Db2-internal security.

So, if RACF is going to be used to manage Db2-internal security, you implement the DSNXRXAC routine and that's it, right? Nope. There's more to be done. Why? Because when a Db2 thing requiring some privilege or authority is to be done, Db2 drives the RACF exit and essentially asks RACF whether ID X can do action Y. To answer that question, RACF needs to see that ID X has been RACF-permitted to perform action Y. If RACF has not been set up to manage authorization for performing Db2 action Y, RACF will defer to Db2 ("This doesn't appear to be in my purview, Db2 - you make the call"), and Db2 will check to see if the required privilege or authority has been provided for ID X through a SQL GRANT statement.

How does RACF get set up to manage Db2 privileges and authorities? That involves activating various RACF classes and defining profiles in those classes. Consider the privilege of reading data from a table using a dynamic SQL statement. Permission to do that, from a RACF perspective, requires that a RACF profile, specifying the SELECT privilege for the table in question, be defined in the RACF class MDSNTB (and that RACF class has to be active). Additionally, the ID needing the privilege has to be RACF-permitted to access the profile associated with the privilege. Another example: if a user needs to be able to execute a certain Db2 package then on the RACF side a profile is defined for the execute privilege on the package in question (or perhaps for that package and all others in the associated package collection), and that profile is defined in the RACF class MDSNPK, and that RACF class has to be active. On top of that, the user's ID (or the ID of an application process that needs to be able to execute the package) is RACF-permitted to access the aforementioned profile covering the EXECUTE privilege on the package (or on all packages in the collection, if that is how the RACF profile was defined).

Got it? In RACF, profiles are defined - in the appropriate RACF classes - that cover specific privileges that can be provided for specific resources (e.g., SELECT on table T1, EXECUTE on package PKG1, etc.), and IDs are RACF-permitted to access the various profiles as needed. Information on all of this - the RACF classes that cover various Db2 privileges and authorities, defining profiles, permitting access to profiles, and more - can be found in a Db2 for z/OS manual titled RACF Access Control Module Guide. The information in that manual is also available online, in the Db2 for z/OS Knowledge Center on IBM's Web site, at

I want to wrap up this entry with information about a couple of important enhancements pertaining to RACF management of Db2-internal security, both of which were delivered with Db2 11 for z/OS. The first of these enhancements has to do with the way that RACF would do authorization checking for a package auto-rebind situation. Consider this scenario: package PKG1 is dependent on table T1. A DBA alters T1 in a way that invalidates PKG1. A request to execute PKG1 comes along, and because PKG1 has been marked invalid by Db2, an attempt is made to auto-rebind the package. Historically, in a situation in which RACF is managing Db2-internal security, the Db2-RACF authorization exit is driven, and RACF checks to see if the ID of the process that wants to execute PKG1 has the privileges needed to successfully rebind the package. That was a problem, because the ID that wants to execute the invalidated PKG1 (thereby triggering the auto-rebind) almost certainly does NOT have the privileges needed to successfully rebind the package. More than likely, those privileges are instead held by the ID of the OWNER of the package. So, ID APPL1, which wants to execute the invalidated PKG1, is checked for privileges needed for a successful rebind of PKG1, and because those privileges are not held by ID APPL1 the auto-rebind fails, and PKG1 ends up getting marked "inoperative" by Db2. Bummer. To address that situation, Db2 11 for z/OS introduced a new ZPARM parameter called AUTHEXIT_CHECK. If that parameter is set to its default value of PRIMARY, and an auto-rebind operation occurs and RACF is managing Db2-internal security, the primary authorization ID of the process that triggered the auto-rebind (by attempting to execute an invalidated package) will be checked by RACF for privileges needed to successfully complete the rebind of the package in question. If AUTHEXIT_CHECK is set to DB2, in the auto-rebind scenario RACF will check to see if the ID of the package OWNER has the privileges required for a successful rebind of the package. Problem solved.

The second of these Db2 11-delivered enhancements pertaining to RACF management of Db2-internal security has to do with in-memory caches of authorization information that Db2 maintains to speed up authorization checks. Consider this scenario: ID SMITH has the EXECUTE privilege for package PKG1, and Db2 has that information stored in the package authorization cache for quick look-aside reference (the size of this cache is specified via the CACHEPAC parameter in ZPARM). RACF is being used to manage Db2-internal security, and on the RACF side the ID SMITH is de-permitted access to the profile that relates to the EXECUTE privilege on PKG1. The RACF change is made active via a SETROPTS RACLIST REFRESH command, and then ID SMITH tries to execute PKG1 again, and SMITH is successful in doing that. How can that be, given that SMITH was de-permitted access to the profile relating to the EXECUTE privilege on PKG1? SMITH can still execute package PKG1 because the information in Db2's in-memory package authorization cache has not been changed to reflect the change in package execution authority made on the RACF side. Bummer. To address that situation, the ZPARM parameter AUTHEXIT_CACHEREFRESH was added with Db2 11 for z/OS. When the value of that ZPARM is set to ALL (versus the default value of NONE), authorization information cached in memory by Db2 (in the package cache or the routine cache or the dynamic statement cache) will be updated to reflect RACF-side changes after those changes have been RACF-activated via a SETROPTS RACLIST REFRESH command. Problem solved.

Those two Db2 11-introduced ZPARMs eliminated just about the last two irritating snags associated with RACF management of Db2-internal security. A growing number of organizations are managing both external and internal Db2 security through RACF. It's a totally viable approach, so don't fret if you learn that your organization wants to go this route. Sure, there's some set-up work that has to be done, and RACF people will need to pick up some Db2 terminology and vice versa, but your Db2 security situation will continue to be robust and manageable.