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