Db2 for z/OS: SEPARATE_SECURITY and SECADM

 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.

Now, here’s something interesting about SECADM authority: it can’t be granted. How, then, is that authority conferred? It’s conferred by way of two ZPARM parameters, SECADM1 and SECADM2. This being so, the decision regarding values for those ZPARMs should be made thoughtfully. One option, of course, would be to supply two user IDs for SECADM1 and SECADM2, but that would be pretty restrictive – quite possibly more restrictive than you want. Another option is to make SECADM1 or SECADM2 a RACF group ID (or RACF-equivalent, if you’re using some other z/OS security management subsystem). In a typical Db2 environment, if user ID ABC is connected to RACF group ID XYZ then XYZ will be a Db2 secondary authorization ID for ABC. That means that privileges granted to the RACF group ID are available to users whose auth IDs are connected to the group ID (in some cases, successful execution of a SQL statement or command requires that a privilege be held by the SQL ID of the associated process, and that can necessitate issuance of a SET CURRENT SQLID statement to change one’s SQL ID from the primary auth ID to a secondary auth ID). Setting SECADM1 or SECADM2 to a RACF group ID offers some flexibility by enabling one to then connect the user IDs of people who will have security administration responsibility in the Db2 environment to the RACF group ID associated with the ZPARM parameter.

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_CTX
    BASED UPON CONNECTION USING SYSTEM AUTHID ADMNP01
    ATTRIBUTES (JOBNAME 'PRDADMN')
    DEFAULT ROLE SECROLE WITH ROLE AS OBJECT OWNER AND QUALIFIER
    WITH USE FOR SALLY, FRED, DAVID    
ENABLE;

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

If you set SECADM1 or SECADM2 to a role name, and you want to test out the functionality of the role and its trusted context before changing SEPARATE_SECURITY to YES, consider issuing the SQL statement TRANSFER OWNERSHIP via the batch job referenced in the trusted context. That statement should only succeed if the privileges of the role are in effect, since only SECADM has the authority to execute TRANSFER OWNERSHIP, even when SEPARATE_SECURITY is set to NO (a TRANSFER OWNERSHIP statement that transfers ownership of a database object away from ID ABC will be successful when issued by ID ABC, so don’t use that ID in your test).

And there you have it. If you are going to go to SEPARATE_SECURITY=YES, have a plan in place for SECADM1 and SECADM2. It would probably be a good idea for one of the two SECADMs to be an authorization ID – either a user ID or a RACF (or equivalent) group ID. The other SECADM could be a different authorization ID, or it could be a role. The main point: give this some thought, and have your ducks in a row before you throw the SEPARATE_SECURITY=YES switch.

Originally published on Robert’s Db2 Blog.