March 12, 2013

Managing Security in DB2 10

DB2 10 is a very significant release in terms of the new security-related features and enhancements it provides. For instance, when I was recently updating my collection of DB2 10 manuals, I scanned the latest version of the DB2 10 for z/OS Managing Security manual.

Here are the subheads for the What’s New in DB2 10 for z/OS chapter:
  • Increased granularity in DB2 administrative authorities
  • Separation of DB2 administrative authorities
  • EXPLAIN system privilege
  • Row and column access control
  • Audit policy

That's quite a list. Let's dig into the first topic: increased granularity in DB2 administrative authorities. With prior DB2 versions, many enterprises felt they had no choice but to give system privilege (SYSADM) to numerous system users so they could perform various system operations for which they were responsible. SYSADM, of course, is a very powerful authority. It allows complete control over DB2, including system configuration, security management, DB2 objects management, application execution and data access control.

However, with DB2 10, a new set of privileges have been defined to split up the authority by the type of work needing to be done. Here's a quick list. The manual offers extensive details.

SECADM: With this authority, you can manage security-related objects in DB2 and control access to all database resources. However, SECADM doesn't have any inherent privilege to access any user data in those databases.

ACCESSCTRL: With this authority, you can grant explicit privileges to authorization IDs or roles by issuing SQL GRANT/REVOKE statements.

DATAACCESS: With this authority, you can access data in tables, views and materialized query tables in a DB2 subsystem. DATAACCESS also allows you to execute plans, packages, functions, and procedures.

System DBADM: With this authority, an administrator can manage all databases in a DB2 subsystem. By default, the system DBADM has all the privileges of the DATAACCESS and ACCESSCTRL authorities.

SQLADM: With this authority, you can issue the SQL EXPLAIN statements, execute the PROFILE commands and run the RUNSTATS and MODIFY STATISTICS utilities on all user databases. In addition, you can execute system-defined routines such as stored procedures or functions, and any packages that are executed within the routines.

This new granularity solves many management problems. For example, an SQL performance analyst can now be granted the authority to EXPLAIN an application program or SQL statement without having the data access control authority that's included with SYSADM.

Next week I’ll discuss the controls put around data access and the capabilities to audit these controls.