In the Db2 for z/OS module called DSNZPARM (ZPARM, for short), which provides the configuration settings for a Db2 subsystem, there is a parameter named SEPARATE_SECURITY. This ZPARM was introduced with Db2 10, and its purpose is to enable a Db2 for z/OS-using organization to separate security administration from system administration in a Db2 environment (and here I'm talking about Db2 internal security, which is concerned with what a user or process can do once the user or process has successfully connected to a Db2 subsystem). The default value for SEPARATE_SECURITY is NO, and that's the setting that's in effect at most Db2 for z/OS sites. A Db2 team will generally go with SEPARATE_SECURITY=YES when they've been informed that this is a requirement for a Db2 system. The Db2 folks at one site were recently presented with this directive, and I was asked for some guidance on implementing the change. The back-and-forth exchanges I had with people on this team included information that I think could be of interest to the larger Db2 for z/OS community, so I'm packaging it in this blog entry.
In a nutshell, SEPARATE_SECURITY=YES makes a Db2 authorization level called SECADM really important. Does this mean that there's no such thing as SECADM authority in an environment in which SEPARATE_SECURITY=NO? No, that's not what it means. When SEPARATE_SECURITY=NO, an ID with SYSADM authority has implicit SECADM authority (with one exception: the ability to execute the SQL statement TRANSFER OWNERSHIP requires explicit SECADM authority, unless the statement is issued by the ID away from which a database object's ownership is being transferred). Why is SECADM authority really important when SEPARATE_SECURITY is set to YES? Because in that case there are important security-related things that can ONLY be done by someone with SECADM authority; for example:
- Create, alter, activate or deactivate a column mask or a row permission.
- Grant a privilege (unless an ID holds the privilege in question WITH GRANT OPTION, or owns the object on which the privilege is being granted).
- Revoke the CREATE-SECURE_OBJECT privilege.
Another way to get flexibility for Db2 security administration when SEPARATE_SECURITY=YES is in effect is to specify a Db2 role for SECADM1 or SECADM2. This gets kind or interesting because it's a use of a Db2 role that differs from what in my experience has been the more common case: utilization of a role (and an associated trusted context) to help prevent misuse of a Db2 DDF-using application's ID and password (I blogged about that more-common use of roles and trusted contexts a couple of years ago). A bit of background: a role can be created by a Db2 DBA, and a privilege or privileges can be granted to that role, and a trusted context (also created by a DBA) specifies the conditions in which the privileges granted to the role can be used. When the role is used to help prevent misuse of a DDF application's ID and password, the associated trusted context will likely specify that the privileges of the role (the privileges required for the DDF application to execute its Db2-targeting SQL statements) can be used by the application that connects to Db2 using auth ID ABC (the application's auth ID) from a certain set of IP addresses (the addresses of the servers on which the application runs). What if the role instead were intended to enable a certain group of people to perform Db2 security administration tasks, with the understanding that these administrators will be locally connected to the Db2 system (i.e., not accessing the system through the distributed data facility)?
For the "local users" case, the set-up would be as follows. First, a Db2 role is created for the purpose. We'll call it SECROLE. Then, a trusted context is created to specify the circumstances in which the Db2 privilege held by the role (it will have SECADM authority) can be used. Db2 security administration actions will be performed by way of a batch job, and the trusted context associated with SECROLE will reference that job, as shown below (I'll explain the color-highlighted parts of the CREATE TRUSTED CONTEXT statement):
CREATE TRUSTED CONTEXT ADMIN_CTXBASED UPON CONNECTION USING SYSTEM AUTHID ADMNP01
ATTRIBUTES (JOBNAME 'PRDADMN')
DEFAULT ROLE SECROLE WITH ROLE AS OBJECT OWNER AND QUALIFIER
OK, here are some notes on the color-coded parts of the above statement:
- ADMNP01 - This is the value of the USER parameter of the JOB statement in the batch job's JCL.
- PRDADMN - This is the job name in the JOB statement in the batch job's JCL.
- SECROLE - This is the role whose privileges can be used when the connection to Db2 is by way of the PRDADMN job with ADMNP01 as USER.
- WITH ROLE AS OBJECT OWNER AND QUALIFIER - This is important given the purpose of the role SECROLE. That role will have SECADM authority in a SEPARATE_SECURITY=YES environment, and so will be needed to issue dynamic GRANT statements and, likely, some dynamic DDL statements such as CREATE and ALTER (especially for security objects such as column masks and row permissions). That makes SECROLE different versus a role created for a DDF-using application. In the latter case, the expectation is that the DDF application will be issuing dynamic SQL DML statements such as SELECT and INSERT, and for such dynamic statements the set of privileges checked by Db2 is the union of the privileges (if any) held by the application's auth ID and (if the application has a trusted connection to Db2) the privileges of the role associated with the trusted connection. When the SQL statements to be executed are dynamic GRANTs or dynamic DDL statements, as expected for the security administrators who will be using the batch job associated with the ADMIN_CTX trusted context, the Db2 privileges checked will be either those held by the primary auth ID of the process or those held by the role associated with the process. If it's the role's privileges that we want to be in effect (and we do want that in this case) then the trusted context needs to be defined WITH ROLE AS OBJECT OWNER (the additional AND QUALIFIER clause makes the role name the default value for the CURRENT SCHEMA special register when the batch job is executed).
- WITH USE FOR SALLY, FRED, DAVID - We can optionally limit use of the role associated with the trusted context to a set of user IDs. What this means in a practical sense: in this particular case, the batch job with the name PRDADMN is going to invoke the Db2 DSN command processor, and is going to execute the program DSNTEP2 (could just as well be the DSNTEP4 program), through which SQL statements (e.g., GRANT, CREATE, ALTER) will be issued. It has been decided that the privileges held by the role SECROLE will be in effect only when the PRDADMN job, with USER name ADMNP01, is executed by one of the user IDs SALLY, FRED or DAVID. SALLY (or FRED or DAVID) can provide her user ID by way of the ASUSER option of the DSN command, when she executes the PRDADMN job. Here's an example of what I'm talking about:
//SYSTSIN DD *DSN SYSTEM(xxxx) ASUSER(SALLY)RUN PROGRAM(DSNTEP2)//SYSIN DD *...
With the role and trusted context created, the value of SECADM1 (or SECADM2) in ZPARM can be set to SECROLE (and the value of SECADM1_TYPE (or SECADM2_TYPE) in ZPARM would be set to ROLE).
