Feb 15 ’11

DB2 10 for z/OS: The Most Secure DB2 Yet

by Willie Favero in z/Journal

Do you hear that silence? It’s how your data sounds as it slips out the door. More companies are facing the unpleasant task of reporting the loss of their data to the government and their customers. Although many recent instances of data loss were attributed to hackers—with carelessness a close second—insider theft accounts for a good percentage of data losses. Though embarrassing, loss of customer information isn’t as devastating as the loss of the data that drives your company—data that makes your company successful and identifies who you are, including trade secrets, sales and profit forecasts, financial statements, and employee information.

A place to start in preventing internal corporate data loss is examining the data custodians. Are the people responsible for securing corporate information also responsible for the integrity and maintenance of that same data? Does your database management system make you feel secure? Can you separate the privileges necessary for a DBA to do his or her job from those privileges necessary to protect a company’s data?

DB2 9 started addressing some of these issues when it introduced the ROLE and TRUSTED CONTEXT. However, DB2 10 really completes the practical solution to the separation of data security from data maintenance with the addition of new system privileges, such as the SECADM administrative authority, that draw a distinct line between who can manage DB2’s security and who can manage its data maintenance and data access.

SECADM System Authority

SECADM is an enhancement that’s increasingly being used as companies move to DB2 10. For the first time, DB2 will natively allow the complete and total separation of the privileges needed to manage and access data from the privileges necessary to perform security tasks.

The SECADM authorization is designed to separate security functions from database maintenance functions. SECADM manages the security functions while having no access to any data in a DB2 object. When the SEPARATE_SECURITY system parameter is also set to YES, the authorization ID (authid) or role defined as SECADM is the only system authority that can:

SECADM also has access to the DB2 catalog tables and can issue the DB2 –START, -STOP, and –DISPLAY TRACE commands. Object owners still have implicit authority to issue GRANT and REVOKE against objects they own.

Enabling SECADM authority requires several steps. First, decide if you want to use SECADM as a standalone, or separate, administrative authority. SECADM may not be for everybody. In previous versions of DB2, the privileges delivered as part of SECADM have traditionally been part of the SYSADM administrative authority. If using SECADM separately from SYSADM, the DSNZPARM, or subsystem parameter, SEPARATE_SECURITY, new in DB2 10, will need to be set to YES. This keyword is on the DSN6SPRM macro and can be set only to YES or NO, with the default being NO. NO allows DB2 to behave similarly to previous versions of DB2.

Next, a ROLE can be optionally defined and associated with the SECADM administrative authority. However, if using a ROLE, make sure it’s created before establishing SECADM’s use of that role. We’ll explain more on SECADM and ROLEs later. Authids can, of course, be either primary or secondary.

Although SECADM can be associated with a role or an authid, we suggest you set authid SECADM1 to a ROLE and SECADM2 to an authid or vice versa. Either way, the intent is to specify one SECADM as a ROLE and the other as an authid. You may wish to define the two SECADMs differently while you’re getting used to this new feature. Authids, even secondary IDs, are associated with a person while a ROLE is a standalone object not dependent on any one authid. A ROLE can easily be moved to wherever it’s needed.

Finally, there are a couple of DSNZPARM keywords to set up. Seven subsystem parameters are delivered in DB2 10 in support of the security administrator function. All seven DSNZPARMs can be changed dynamically using the DB2 –SET SYSPARM command:   

Be careful when deciding whether to define SECADM with a ROLE. If both SECADM1 and SECADM2 are defined using a ROLE that doesn’t exist at the time the SECADMs are established, no one will have management authority over DB2’s security objects.

The use of the value HEX is valid only for SECADM1_INPUT_STYLE when SECADM1_TYPE or SECADM2_INPUT_STYLE for SECADM2_TYPE is set to ROLE. If hex is specified for SECADM1_INPUT_STYLE or SECADM2_INPUT_STYLE, be careful to ensure that only an even number of bytes—between two and 256—are specified. The string must also consist of the characters 0 through 9 and A through F, exclusively.

The seventh subsystem parameter is SEPARATE_SECURITY on the DSN6SPRM macro. If you intend to use SECADM’s security administration, separate from SYSADM’s database administration (removing the GRANT and REVOKE privileges completely from SYSADM), the DSNZPARM SEPARATE_SECURITY must be set to YES. This keyword can be set only to YES or NO, with the default being NO.

SECADM with the separation feature turned on is a new DB2 10 function you should consider.  Managing DB2’s security separate from DB2’s database administration makes good sense. It lets the database professional focus on the database while leaving the database’s security in the hands of a security administrator. Additionally, since a DBA authid associated with SECADM can’t access sensitive data, SECADM can protect the DBA from being subject to audits.

Cascade Revoke

Wouldn’t it be nice to not have to worry about cascade revoke? Every few months, posts appear on the DB2-L listserv about revoking SYSADM without all the potential, devastating side effects. Removing privileges from any authid that was granted those privileges because the SQL GRANT statement had the “WITH GRANT OPTION” is potentially dangerous. A typical scenario would be authid B is granted a privilege by authid A. Authid A was granted that privilege from someone else via the “WITH GRANT OPTION.” When authid A loses that privilege or has that privilege revoked, the revoke cascades down and authid B also loses that privilege. That process is known as a cascade revoke.

Again, DB2 10 for z/OS has a potential solution: It delivers a new system parameter, or DSNZPARM, along with a new SQL REVOKE clause that controls whether a cascade revoke can happen. The new system parameter REVOKE_DEP_PRIVILEGES on the DSN6SPRM macro, or REVOKE DEP PRIV field on the installation panel DSNTIPP1, gives you the choice via three acceptable values for the keyword:

The system parameter REVOKE_DEP_PRIVILEGES is updatable using the DB2 –SET SYSPARM command.   

The default for the SQL REVOKE statement will be INCLUDING DEPENDENT PRIVILEGES unless one of these two conditions exists:

The SQL REVOKE has two new clauses to support whether revocation of privileges from an authid will remove the privileges granted by that authid:

Other System Privileges

The ACCESSCTRL system authority lets the holder explicitly grant most privileges to any authid using the SQL GRANT statement. Having this authority also lets you revoke most explicitly granted privileges using the BY clause. Also, the authid with ACCESSSTRL can implicitly read all catalog tables and update  all updatable catalog tables with the exception of  SYSIBM.SYSAUDITPOLICIES. SYSIBM.SYSAUDITPOLICIES contains audit policies and security enhancement new to DB2 10. Because of the potential sensitively of an audit policy, only SECADM can implicitly update this table.

The DATAACCESS system authority lets the authid or role access data; specifically read and update all user tables, views, and Materialized Query Tables (MQTs) in the DB2 subsystem. Execution of all plans, packages, and procedures is included along with the ability to run RECOVERDB, REORG, REPAIR, and LOAD. DATAACCESS also lets the authid issue ALTER and TERM UTILITY commands and access catalog tables.

System DBADM is another new DB2 10 system authority granted on all databases in the entire DB2 subsystem, allowing database administrative tasks to be performed on the objects in those databases without allowing access to the data. Be careful when passing out this authority. Depending on options chosen, this authority can be powerful.  

An authid or role with system DBADM authority can issue SQL CREATE, ALTER, or DROP statements to manage most objects in the DB2 subsystem, along with issuing SQL statements such as EXPLAIN, LABEL, PREPARE, and RENAME. This doesn’t include security objects. It can issue most DB2 commands. The DB2 10 Administration Guide (SC19-2968) provides a complete list of the commands allowed and other privileges included in system DBADM.

System DBADM can alter and terminate the execution of utility job steps and run many of the DB2 utilities. The utilities most noticeably missing include LOAD, REORG, and UNLOAD.

When granting system DBADM to an authid, WITH ACCESSCTRL and WITH DATAACCESS can be optionally specified, giving the system DBADM the additional privileges associated with those system privileges. For example, specifying WITH ACCESSCTRL would allow the system DBADM to run the LOAD, REORG and UNLOAD utilities, along with giving data access for all tables, views, and MQTs in the subsystem. This privilege is also affected by the DSNZPARM keyword SEPARATE_SECURITY. System DBADM can set the owner to a BIND or REBIND to any ID if SEPARATE_SECURITY is equal to NO.

System DBADM authority is new in DB2 10 and shouldn’t be confused with DBADM authority, which has existed for many years. System DBADM is granted for the entire DB2 subsystem and allows management of all non-security-related objects. DBADM is granted over a database and allows management of only that specific database the privilege is granted for.

ACCESSCTRL, DATAACCESS, and system DBADM have four things in common:

Although a warning is issued when the WITH GRANT OPTION is designated, the authorizations specified on the statement are successfully granted. SYSADM has the SECADM authority if SEPARATE_SECURITY is set to NO.

System Privileges

SQLADM is another new system authority in DB2 10 that provides lots of capability without allowing access to data or the ability to run SQL Data Definition Language (DDL) statements. It supports all the privileges associated with the new system EXPLAIN privilege and lets you:

Only a role or authid with SECADM or ACCESSCTRL authority can grant or revoke SQLADM authority.

What if you have a bunch of SQL and want to check out the access paths? You just need to EXPLAIN the SQL and determine what EXPLAIN wrote to the plan tables at bind, right? Not exactly. First, you need access to the tables the SELECTs will run against. Oh, by the way, this is on a production DB2 subsystem. I’m sure no one will have an issue with a few more people having read access to a bunch of production customer data.

Next, you’re assigned the task of checking out the access paths of a bunch of dynamic SQL statements or maybe you want to do a Smart Analytics Optimizer assessment on all of the Business Intelligence (BI) queries? That’s pretty simple, also. Check active the dynamic statement cache and few traces, and once the targeted workload completes, dump the statement cache. Oh, wait. None of us have the SYSADM authority, so we can’t snap the statement cache (EXPLAIN STMTCACHE ALL). We all know that passing out the SYSADM authority to anyone who wants it is a pretty common practice, right? Not!

You get the point. These scenarios are huge failures that DB2 10 for z/OS solves with the new system privilege called EXPLAIN.

By granting the EXPLAIN privilege to an authid or role, you’re giving that authid or role the ability to:

You can also grant EXPLAIN to the public but that would be defeating the purpose.

A few phrases here should be new to you. The first is CURRENT EXPLAIN MODE = EXPLAIN. This new special register controls how EXPLAIN behaves for eligible dynamic SQL statements. Eligible SQL statements include SELECT, INSERT, and the searched form of UPDATE and DELETE. When used, you can set CURRENT EXPLAIN MODE to these values:

The other two keywords mentioned are for the BIND command:

Only an authid or role with SECADM or ACCESSCTRL authority, SYSADM authority if SEPARATE SECURITY is set to NO, or the EXPLAIN privilege with the WITH GRANT OPTION can grant the EXPLAIN privilege.


DB2 10 didn’t stop with just enhancing DB2’s privilege sets; it also has made some significant improvements in row- and column-level security and audit policies. With the security changes in DB2 10, DB2 for z/OS and DB2 for Linux, UNIX and Windows (LUW) get one step closer to each other. The SECADM, system DBADM, ACCESSCTRL, DATAACCESS, and SQLADM authorities, and the EXPLAIN privilege are all identical in syntax and semantics to their equivalent LUW authorities introduced in DB2 LUW 9.7.

If all this is of interest, your goal now is to migrate to DB2 10. You can get to DB2 10 from either DB2 V8 using skip-level migration or directly from DB2 9. With these migration options, migrating to these and all the DB2 10 enhancements is well within your reach.

The security enhancements in DB2 10 for z/OS are terrific and extensive. DB2 10 does an outstanding job of separating security, data access, and commonly performed administrative tasks; the EXPLAIN system privilege is just one example of this separation. While almost everything described in this article isn’t available until New Function Mode (NFM), it isn’t too early to plan for a DB2 migration.