Thoroughly Assessing Data Security in a Db2 for z/OS Environment - Part 1

I regularly get questions from Db2 for z/OS people that pertain to data security. Most of the time, these questions have a pretty narrow focus - a DBA, for example, wants to know more about Db2 roles and trusted contexts, or about SECADM authority, or about "at-rest" encryption of Db2 data on disk. Recently, I had a meeting with some people from a client's mainframe IT staff, and they wanted to know what a comprehensive Db2 for z/OS data security review would look like. For me, that was a refreshingly wide-scope question. What areas would one want to examine, if one wanted to thoroughly assess the data security posture of a Db2 for z/OS system? In this part one of a two-part blog entry I will give you my take on the matter, starting with four areas of Db2 data protection: privilege management, client authentication, data encryption, and column masks and row permissions. In the part two entry, which I hope to post in about two weeks, I will cover four other areas of Db2 data protection: auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.

Onward, then, to the first four areas of Db2 data protection I would recommend considering as part of a comprehensive Db2 security assessment:

• Privilege management - This is about the Db2 privileges that have been granted to user, group and application IDs. With regard to user privileges, the best-practice approach is to grant to a given individual only those privileges that minimally enable the individual to do his or her job. One area where there has been a lot of cracking down in recent years concerns the granting of SYSADM authority. That's basically super-user status, and years ago it was common for organizations to give the SYSADM authority level to quite a few people on the Db2 support team. Why did that practice become problematic? Mainly because someone with SYSADM authority can look at (and even change) the data in any table. You could say, "It's OK - no one on my Db2 team is a bad actor," but that argument is not likely to sway security auditors these days. For some organizations, the solution to over-granting of SYSADM is to change that authority, for many or even most of the people on the Db2 team, to DBADM WITHOUT DATAACCESS ON SYSTEM. That move can spark protest from someone who loses SYSADM authority, but in fact many DBAs can do the large majority of things they need to do with system DBADM authority. If access to data in a specific table is required for a DBA with system DBADM WITHOUT DATAACCESS authority, the SELECT privilege on the table can be granted and then revoked when the task requiring access to the table's data has been completed.

Here's another way in which super-user authority has been reigned in lately: organizations can set the value of the Db2 ZPARM parameter SEPARATE_SECURITY to YES. What does that do? It removes from SYSADM authority the ability to create and manage security objects (e.g., roles, trusted contexts, row permissions and column masks), and the ability to grant privileges to others (unless the ID with SYSADM authority holds the privilege in question WITH GRANT OPTION, or owns the object on which a privilege is being granted). How do those things get done, if a SYSADM can't do them? They are done by an ID with SECADM authority (more information in this area can be found in an entry I posted to this blog last year).

What about the group IDs that I mentioned above? Those have been around for a long time. A group ID is so called because it refers to a RACF (or equivalent z/OS security manager) group to which individual IDs can be connected. When the Db2-supplied sample connection and sign-on exits are used by an organization (very widely done), the RACF group IDs to which your primary authorization ID are connected become your secondary authorization IDs in Db2, and the ability to execute most Db2 SQL statements and commands depends on the privileges held by your primary authorization ID and by any of your secondary authorization IDs. This can make Db2 privilege management much simpler, especially if a set of privileges tailor-made to enable execution of a certain set of Db2 actions is fairly complex - you just grant that set of privileges to a RACF group ID, and then connect to that group ID the IDs of individuals who need to perform the associated set of Db2 actions.

Some security administrators and auditors worry - understandably - about the privileges granted to the ID of an application that issues dynamic SQL statements, especially when that application connects to Db2 via TCP/IP communication links and through the Db2 distributed data facility (DDF). Why the worry? Well, for a dynamic SQL statement, such as a SELECT, to execute successfully, the associated Db2 authorization ID needs to have the SELECT privilege on the target table. A DDF-using application that issues SQL statements in JDBC or ODBC form (these will be dynamic SQL statements on the Db2 side) usually connects to the Db2 system using a certain ID and an associated password. What if someone who knows that ID and password tries to use those credentials to connect to the Db2 system from a PC, and then view data in tables that the application can access? An effective defense against that scenario can be implemented using Db2 roles and trusted contexts, as described in a blog entry I posted a few years ago.

• Client authentication - I just now referred to applications that access Db2 systems via TCP/IP communication links and through the Db2 distributed data facility, and there are often individual users that do the same thing, perhaps using a workstation-based query and reporting tool. How are these applications and users authenticated at connection time? As noted above, this is usually done by way of a password. Typically, an organization requires a user to change his or her password on a regular basis - for example, every three months. What about the password associated with an application's ID? There was a time when it was quite common for such a password to be of the "never expire" type. That kind of password is increasingly deemed unacceptable by security auditors, who insist that the password associated with an application's ID be regularly changed, just as is done for passwords associated with user IDs. That is in fact a good policy from a security perspective, but it can lead to authentication-related connection errors when an application's password is changed. What if an application's password is changed in RACF before it is changed on the app server side, or vice versa? The strategy I've seen employed for non-disruptively changing a Db2 client-server application's password involves having two IDs for a given application. Shortly before the password for ID1 is set to expire, the application starts connecting to Db2 using ID2 (whose password will be good for the next three months or whatever). Once all instances of the application have switched over to ID2, the password for ID1 can be updated (and maybe that doesn't happen until a little before ID2's password is set to expire). As long as the application is running on at least two app servers, the switch from the one ID to the other can be accomplished with no application outage. What makes this work on the Db2 side is the fact that all privileges needed for the application's dynamic SQL statements to execute successfully are granted to ID1 and to ID2.

What if you'd prefer for an application or a user ID to be authenticated by some means other than a password? Are there alternatives? Yes. One alternative is to use RACF PassTickets - you can find more information about RACF PassTickets in the online z/OS documentation. Another option is to use certificate-based authentication. Often, when one thinks about certificates in a Db2 for z/OS context, it is in relation to SSL encryption for communication between Db2 and a network-connected client application (more on encryption to come - see below); however, in my experience Db2 SSL encryption typically involves use of a server certificate versus client certificates (the host certificate is transmitted to the client system and is presented at connection time by the client application - the host recognizes its own certificate and the "SSL handshake" can proceed to successful completion). That said, it is also possible for a client system to present its own certificate as a means of authentication when requesting a connection to a Db2 system. A good source of information on certificate-based authentication for a Db2 client is a document titled, "Db2 for z/OS: Configuring TLS/SSL for Secure Client/Server Communications," which can be downloaded from the IBM redbooks Web site. Refer to the information under the heading, "Client access to Db2 using TLS/SSL client authentication," on page 61 of the document.

One other thing regarding client authentication. There is a parameter in the Db2 DSNZPARM module called TCPALVER. The default value for that parameter is NO, and that is almost certainly the value you want. If TCPALVER is set to YES in your Db2 environment, it means that Db2 assumes that a process wanting to connect to the Db2 system via TCP/IP is already verified. That being the case, the Db2 system will accept a TCP/IP client connection request that provides an ID but no authentication credential - no password or PassTicket or client certificate is required. Now, if you see that TCPALVER is set to YES for a Db2 subsystem, don't panic - almost certainly, RACF (or equivalent) will block a connection request that lacks an authentication credential; still, in the interest of having "belt and suspenders" security safeguards (a good idea), you'll probably want to change the TCPALVER value from YES to NO in the very near future. Before making that change, consider that a setting of TCPALVER=YES might have been put in place a long time ago, when the only clients connecting to the Db2 system via TCP/IP were other Db2 for z/OS systems. When that kind of communication was first happening (again, a long time ago), a requester Db2 for z/OS system might not have been sending a password when requesting a connection to a server Db2 for z/OS system, the thinking being that user authentication had already happened on the requester Db2 for z/OS side. If you have Db2 for z/OS systems communicating with other Db2 for z/OS systems using DRDA, without passwords being sent, those connection requests will fail after you change the TCPALVER value from YES to NO. To avoid that problem, update the Db2 communications database on the Db2 for z/OS requester side to have a password sent with a connection request to a DRDA server, and THEN change the TCPALVER setting from YES to NO.

• Data encryption - I posted an entry to this blog in 2020 about data encryption in a Db2 for z/OS context, and I won't repeat that content here. Instead, I'll add a few extra nuggets of information:
• APAR PH08188 (April of 2019) made it possible to configure a Db2 for z/OS system so that its only SQL listener port is a "secure" port (i.e., one that requires a requester to use SSL encryption). When a Db2 system is set up that way, it is not possible for a client application to establish a non-SSL connection to the Db2 server.
• Db2's leveraging of the data set encryption feature of z/OS for encryption of Db2 table space and index data "at rest" (i.e., on disk) involves associating an encryption key label with a data set and RACF-permitting use of that key label. This has caused some people to be concerned about the need to provide RACF permission for various encryption ket labels to various application and user IDs. That concern is unfounded. When a user or an application issues a SQL statement that targets a Db2 table, and table space and/or index data sets on disk are accessed as a result, from the z/OS perspective it is not the user or application accessing the data sets - it's Db2 accessing the data sets (and the same is true for the "online" IBM Db2 utilities, such as LOAD and COPY and REORG - they access database objects through Db2). That being the case, only the IDs of the Db2 database services and system services address spaces need to be RACF-permitted to use the key labels associated with encrypted Db2 data sets (if a "standalone" Db2 utility, such as DSN1PRNT, is to be run for an encrypted database object, the ID of that utility job will need RACF permission for the object's key label, as the standalone utilities operate outside of Db2).
• Plenty of people get data encryption and data masking mixed up. They are two different things (see below)

• Column masks and row permissions - This is another area I covered pretty thoroughly in a previous blog post, and I won't repeat that content in this entry. What I will do is try to clear up some misunderstandings I've encountered over the years since Db2 introduced column mask and row permission functionality:
• Data masking and data encryption really are two different things. One difference is that encryption is reversible (if you have access to the encryption key) while a data masking transformation can be irreversible (if, for example, a column mask changes a credit card number to XXXXXXXXXXX1234, there is no way for a user or a program to reverse those X's back to their pre-masked values).
• A Db2 column mask changes no values in a table; instead, the mask transforms the values in a column before they are returned to a user or a program; so, the actual unmasked values are in the table, but a column mask prevents a user (or a set of users, if it checks for a certain group ID) from being able to see the unmasked values. The masking is accomplished by a SQL CASE expression that is automatically added to a query when the ID (or group ID) of a query-issuing process matches one specified in the CREATE MASK statement.
• One of the nice things about a column mask is that it doesn't change values in a column of a table. That means a column mask will not affect a query's predicates (including join predicates) - those predicates will be evaluated using the unmasked values in the referenced column. The mask is applied (as previously noted) when the column in question appears in a query's select-list. If the mask changed values in a column, it could really throw query results out of whack in a bad way.
• Column masks and row permissions really are a very robust way to prevent access to certain data values (a row permission prevents a process with a certain ID or group ID from being able to access particular rows in a table, through addition of a row-filtering predicate to queries issued by an ID that matches one specified in the CREATE PERMISSION statement). Here's what I mean by that: the CASE expression associated with a column mask, and the predicate associated with a row permission, will be automatically added to ANY query - static OR dynamic - issued by ANY ID specified in the CREATE MASK or the CREATE PERMISSION statement. Does your ID (which I'll call SMITH) have SYSADM authority? Doesn't matter - if I have a column mask or a row permission that states that SMITH (or, maybe, any ID other than, for example, XYZ) cannot see certain rows in a table, or unmasked values in a certain column, you're not going to be able to access those rows or those unmasked column values. The Db2 privileges held by your ID are irrelevant.

And that's a wrap for this part 1 blog entry. Check back in a couple of weeks for part 2, which will cover auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.