Feb 15 ’11
DB2 10 for z/OS: The Most Secure DB2 Yet
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:
- Issue SQL GRANT and REVOKE statements implicitly
- Manage ROLEs, TRUSTED CONTEXTs, row permissions, and column masks
- Audit policies.
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:
- Two installation SECADMs are available in DB2—SECADM1 and SECADM2. They’re defined using the SECADM1 and SECADM2 keywords on the system parameter macro DSN6SPRM, or through the SECURITY ADMIN 1 and SECURITY ADMIN 2 fields on the installation panel DSNTIPP1. The two installation SECADMs can be defined only as DSNZPARM keywords. No SQL GRANT statement exists for defining SECADM.
- Each SECADM must be defined using either a ROLE or an authid. The new system parameter SECADM1_TYPE for SECADM1 and SECADM2_TYPE for SECADM2 on the DSN6SPRM macro, or the SECURITY ADMIN 1 and SECURITY ADMIN 2 fields on the installation panel DSNTIPP1, are for this purpose. By default, this field is set to authid; you may wish to use ROLE for at least one SECADM.
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.
- There are two more subsystem parameters on the DSN6SPRM macro not available through the DB2 10 installation panels—SECADM1_INPUT_STYLE and SECADM2_INPUT_STYLE. They identify to DB2 whether the SECADM1 or SECADM2 setting is passed as a character string or hex string. This allows the specification of SECADM authid in something other than the EBCDIC CCSID.
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.
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:
- NO prevents removal of dependent privileges or prevents a cascade revoke.
- YES allows the removal of dependent privileges, allowing a cascade revoke.
- SQLSTMT lets you decide whether or not removing dependent privileges, or cascading the REVOKE, should be allowed by specifying a new clause on the SQL REVOKE statement. This is the default.
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:
- If ACCESSCTRL, DATAACCESS or system DBADM is being revoked, there’s no cascade. The default is always NOT INCLUDING DEPENDENT PRIVILEGES and must be specified on the REVOKE SQL statement.
- When the subsystem parameter REVOKE_DEP_PRIVILEGES is set to NO, the default will always be NOT INCLUDING DEPENDENT PRIVILEGES.
The SQL REVOKE has two new clauses to support whether revocation of privileges from an authid will remove the privileges granted by that authid:
- INCLUDING DEPENDENT PRIVILEGES: Specifying this clause causes the cascade revoke behavior found in previous versions of DB2. If an authorization or privilege is revoked from an authid or a role, any dependent authorizations or privileges are also revoked. This clause can’t be specified if subsystem parameter REVOKE_DEP_PRIVILEGES has been set to NO.
- NOT INCLUDING DEPENDENT PRIVILEGES: If this clause is specified, cascade revoke is prevented. This clause can’t be specified if subsystem parameter REVOKE_DEP_PRIVILEGES has been set to YES.
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:
- The privileges and authorities associated with all three belonged to SYSADM in prior versions, making DB2 10 more granular than previous versions.
- All three can be granted or revoked only by an authid or role with the SECADM authority.
- All three privileges don’t allow granting to PUBLIC.
- All three will ignore the WITH GRANT OPTION, with a warning, if specified on the SQL GRANT statement.
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.
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:
- Issue the DB2 commands -START, -STOP, and -DISPLAY PROFILE
- Execute RUNSTATS or MODIFIFY STATISTICS utilities for any database
- Apply MONITOR2 privileges
- Execute system-defined routines (stored procedures and functions) and any packages executed within those routines
- Provide implicit SELECT access to any DB2 catalog tables
- Apply implicit INSERTs, DELETEs, and UPDATEs on updatable catalog tables except SYSIBM.SYSAUDITPOLICIES.
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:
- Do an EXPLAIN on an SQL statement specifying the keywords ALL/PLAN, STMTCACHE ALL, STMTCACHE STMTID, STMTCACHE STMTTOKEN, and MONITORED STMTS
- Issue the SQL statements PREPARE and DESCRIBE TABLE
- Perform a BIND specifying EXPLAIN(ONLY) and SQLERROR(CHECK)
- Explain dynamic SQL statements executing under the new special register CURRENT EXPLAIN MODE.
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:
- NO turns off capturing EXPLAIN information. This is the initial value the special register is set to.
- YES turns the feature on, causing EXPLAIN information to be collected and plan tables to be updated. Specifying YES has no effect on execution of the dynamic SQL statement.
- The EXPLAIN option is the same as YES. However, dynamic SQL statements aren’t executed.
- host-variable, an EXPLAIN special register, can be set using a host variable that contains one of the previously mentioned values. These three values are the only values that can be specified for this character or variable character-defined host variable.
The other two keywords mentioned are for the BIND command:
- EXPLAIN(ONLY) allows the SQL statements in the applications to be explained without allowing execution of the SQL statements. No package is created when this BIND command keyword is specified.
- SQLERROR(CHECK) is a BIND keyword that allows all syntax and semantic checks to be performed on the SQL in an application with the binder not having the privileges to execute that SQL.
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.