In part 1 of this two-part blog entry on thoroughly assessing data security in a Db2 for z/OS environment, I covered four aspects of Db2 data protection: privilege management, client authentication, data encryption and column masks/row permissions. In this part 2 entry we'll take a look at auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.
Off we go:
- Auditing - You can manage Db2 privileges in a careful and responsible way, but at the end of the day users have to have some privileges in order to perform assigned duties, and some users are going to require extensive privileges. Privileges that were properly granted can be abused. The best defense against that possibility is effective auditing of users' data access activities. What you want in this case is to identify improper data access activity so that steps can be taken to shut it down. An important assist in this area was delivered with Db2 10 for z/OS, which introduced audit policy functionality. Db2's audit policy capability enables you to monitor (among other things):
- Occurrences of access actions that failed due to inadequate authorization (if a particular user is getting a pretty good number of these, that could indicate attempts to probe for "holes" in your organization's data protection measures).
- Occurrences of a user changing his or her SQL ID (there are times when this is a legitimate action, and times when it is not)
- Occurrences of tables being altered (depending on the ALTER action, this could be an attempt to circumvent a data protection measure).
- Occurrences of a particular table being accessed in read or data-change mode (is a table holding sensitive data values being accessed at odd hours?).
- Utility execution (could someone be trying to use a Db2 utility as a "back door" means of data access?).
- Incidences of privileges being granted or revoked (inappropriate granting of Db2 privileges can be a warning sign).
- Use of system administration "super-user" privileges: install SYSADM, install SYSOPR, SYSOPR, SYSCTRL, or SYSADM (to quote a line from several "Spider-Man" movies: "With great power comes great responsibility").
- Use of database and security administration "super-user" privileges: DBMAINT, DBCTRL, DBADM, PACKADM, SQLADM, system DBADM, DATAACCESS, ACCESSCTRL, or SECADM (see the "Spider-Man" quote above).
Db2 12 function level 509 introduced an important audit policy enhancement: tamper-proof audit policies. With that enhancement, an audit policy can be set up so that it can be changed only with the permitting action of a person outside the Db2 team (specifically, a RACF administrator).
You can read all about developing, activating and using audit policies in the Db2 for z/OS online documentation.
If you're interested in a data access monitoring solution that can span all of your enterprise's data stores, both on-prem and cloud-based, check out IBM Security Guardium Data Protection.
- Application architecture - Can application architecture enhance Db2 data protection? Absolutely. Consider, for example, the security advantage of static versus dynamic SQL statements. If an application process will access data in table T1 via a dynamic query, the authorization ID of the application process will require the SELECT privilege on T1. If, on the other hand, the application process will access data in T1 by way of a static query, the application's ID will not need any table access privileges; instead, the ID will need only the EXECUTE privilege on the Db2 package associated with the static query. Reducing the granting of table-access privileges to application and/or user IDs can strengthen Db2 data security.
OK. But what about applications of the client-server variety, particularly those that access Db2 for z/OS data from network-connected Linux or UNIX or Windows servers? For such applications, use of client-issued static SQL statements is often either not possible or, if possible, not favored by client-side programmers (for example, a Java program can issue static SQL statements in SQLJ form, but in my experience Java programmers overwhelmingly prefer JDBC to SQLJ, and JDBC means dynamic SQL on the Db2 side). In those cases, two convenient ways to utilize static SQL are 1) Db2 stored procedures and 2) Db2 REST services.
Lots of application developers who prefer the JDBC and ODBC forms of SQL (to name two very popular forms of non-DBMS-specific SQL) are plenty happy with a stored procedure approach, as stored procedures are widely used with relational database management systems such as Db2. A programmer can use (for example) JDBC statements to call Db2 stored procedures and to retrieve rows from associated query result sets (when a stored procedure declares and opens a cursor). The stored procedure calls will be dynamic on the Db2 side, but the "table-touching" SQL statements issued by the stored procedures will be static, and that means that the application's ID will require only the EXECUTE privilege on the called stored procedures - not table access privileges.
Db2's built-in REST interface is another way to make static SQL easy to use from a client-side programmer's perspective. By way of this interface, which is an extension of the Db2 distributed data facility, a static SQL statement can be invoked via a REST request. The static SQL statement associated with a Db2 REST service can be a SELECT, an INSERT, an UPDATE, a DELETE, a TRUNCATE or a CALL (of a stored procedure).
Here's another security advantage of client-side programs invoking Db2 server-side static SQL statements, whether through the use of stored procedures or the Db2 REST interface (which can itself be used, as noted, to invoke stored procedures): when this approach is used, client-side programmers do not have to know anything about table or column names - that knowledge is needed only by the people who code the server-side static SQL statements. How does this shielding of database schema information enhance data security? Well, the fewer the people who know stuff like table and column names, the less likely it is that a database will be hacked by bad guys.
[Note: when a stored procedure is to be invoked through the Db2 distributed data facility, either through a SQL call or a REST request, that stored procedure will get up to 60% zIIP offload when executed IF the stored procedure is written in SQL PL (i.e., if it is a so-called native SQL procedure). A stored procedure written in a language other than SQL PL will get little to no zIIP offload when called through DDF.]
- Test data management - Let's say you have a production Db2 database in which some sensitive data values are stored (e.g., credit card numbers). Perhaps you have taken a number of steps to protect those sensitive data values. Great. But now an application team wants tables in their Db2 development environment populated with data from the production system. You could copy data over from the production to the development system, but will the sensitive data values be protected in the development environment as they are in production? Even if the data protection measures in the development environment are as strong as those in place for the production Db2 system, creating another copy of data that includes sensitive data values will still involve some data-security risk because the data copy increases what a security auditor might call the "threat area" - do you want to accept that risk?
Often, the best approach to use in this situation is to mask or otherwise obscure the sensitive data values before (or as part of) copying production data to a development or test system. How would you accomplish that? You could do it on your own, but that can be a time-consuming effort and the "roll-your-own" data masking could impact the CPU and elapsed times of a production-to-test data copy operation. An easier (and often better-performing) way to get this done would be to use a software tool designed for the purpose. Two options in this space that are available from IBM are IBM InfoSphere Optim Test Data Management Solution for z/OS and IBM Db2 Cloning Tool for z/OS (the former is useful for copying a referentially complete subset of data rows from one Db2 system to another, while the latter is more appropriate for copying entire table spaces and indexes - or even an entire subsystem's data - from one Db2 system to another). Both of those tools have data masking capabilities, to prevent sensitive data values from being copied "as-is" from a production environment to a test or development system.
- RACF (or equivalent) management of Db2-internal security - It is very common for RACF (or an equivalent z/OS security management subsystem) to be used for external Db2 security purposes - that is, to control which application processes and/or users can connect to a Db2 subsystem, and how. Once an application process or a user has successfully connected to a Db2 subsystem, what happens next is a matter of Db2-internal security: does the ID of the application or user have the Db2 privileges needed to (for example) read data in a table, or update data in a table, or create an index or bind a package? In my experience, Db2-internal security is most often managed within Db2 by the Db2 administration team, who use the SQL statements GRANT and REVOKE to provide privileges for, or remove privileges from, various authorization IDs. It is possible to use RACF (or equivalent) to manage Db2-internal security as well as Db2-external security, and a growing number of organizations are doing just that.
I posted an entry to this blog a couple of years ago with a good bit of information about using RACF to manage Db2-internal security. I won't repeat that content here; rather, I'll provide some thoughts and observations on this topic:
- Why do organizations do this? Quite often (in my experience) it's because someone - perhaps a security auditor - told them that it has to be done. Why might that pronouncement be made? Well, in the minds of many security people, it's a good thing for a single group of people to manage all aspects of security for a database management system. Because RACF can be used to manage both Db2-external and Db2-internal security, while Db2's security features apply mainly to internal security, if one team is going to manage all aspects of Db2 for z/OS security then it's going to be the RACF team.
- Db2 11 for z/OS eliminated what were just about the last two hassles that were formerly associated with RACF management of Db2-internal security. Prior to Db2 11, auto-rebinds could fail with authorization errors when RACF was used to manage Db2-internal security. Why? Because when an auto-rebind occurs you generally want Db2 to do that based on the privileges held by the ID of the owner of the package. It used to be that when RACF managed Db2-internal security, the authorization check for an auto-rebind looked at the privileges held by the ID of the application process that prompted the auto-rebind by requesting execution of a package that had been marked invalid by Db2, and that ID rarely has the privileges needed for a successful auto-rebind. Db2 11 fixed that problem by enabling RACF to check the ID of a package owner for auto-rebind authorization. The other nagging problem fixed by Db2 11 concerned caches of authorization information that Db2 maintains in memory. Information in those caches was formerly not updated to reflect security changes effected through RACF, the result being a frustrating lag between some RACF-side changes and enforcement of same in Db2. Db2 11 fixed that problem by having Db2 listen for ENF signals (referring to the z/OS event notification facility) sent by RACF when authorization changes are made.
- Organizations that have gone to RACF management of Db2-internal security are pretty happy with the arrangement, and that includes the Db2 for z/OS DBAs. Yes, there is a good bit of set-up work involved in making this transition, and that can seem more challenging than it really is because Db2 DBAs and RACF administrators speak different languages in a technical sense, but once things are set up and the transition has been completed, people find that it really works as advertised. Ask a Db2 DBA at a site that has gone to RACF management of Db2-internal security if he or she is OK with the change, and you'll likely get a thumbs-up. I haven't found many (any, actually) DBAs in these organizations that pine for the days when they had to issue GRANTs and REVOKEs to manage Db2-internal security. Letting the RACF team handle Db2-internal security lets the DBAs focus on database administration tasks (e.g., performance tuning, application enablement) that they generally find to me more satisfying.
