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: