March 19, 2013

More on DB2 10 Security

Last week I noted a key new security feature found in DB2 10: increased granularity in DB2 administrative interfaces. As I said though, DB2 10 features numerous security-related enhancements that are designed to control who manages the security, who manages the system and who can access the data. So let's look at some other new features and capabilities.

I would say the most important security-related change with DB2 10 is the separation of DB2 administrative authorities. Prior to DB2 10, the system administrator privilege (SYSADM or INSTALL SYSADM) allowed you to have complete control over installation, configuration and security as well as DB2 object creation and data access. However, with DB2 10, the new system privilege SECADM can be made the only privilege that's authorized to manage all security-related objects and data access -- even though SECADM itself can't access data. This is accomplished by simply setting the SEPARATE_SECURITY system parameter to YES.

As noted last week, another new system privilege -- EXPLAIN -- provides even greater granularity over the use of SQLADM. Users with the EXPLAIN privilege can only use EXPLAIN PLAN or ALL; they're restricted from using STMTCACHE ALL, STMTID, STMTTOKEN, MONITORED STMTS. For a list of further restrictions, see the DB2 10 for z/OS Managing Security manual.

DB2 10 introduced row and column access controls that use SQL to manage access to the table. The row permission uses a database object that describes a specific row access control rule for a table. In the form of an SQL search condition, the rule specifies the conditions under which a user, group or role can access the rows of data in the table. The column access is controlled through a mask, which is a database object that describes a specific column access control rule for a column. In the form of an SQL CASE expression, the rule specifies the conditions under which a user, group or role can receive the masked values returned for a column.

DB2 10 introduces an audit policy that defines a set of criteria to determine the categories to be audited. These categories are:

  • CHECKING allows you to determine if a user was denied access through an attempt due to inadequate authority. 
  • VALIDATE records when authorities are assigned or switched to another user or a trusted connection.
  • OBJMAINT records information related to a table being altered or dropped.
  • EXECUTE records SQL statements executed against an audited DB2 table.  (See the manual for limitations.)
  • CONTEXT: utility start, objects or phase change or utility end.
  • SECMAINT records GRANT/REVOKE security activity.
  • SYSADMIN records administrative privilege activity.
  • DBADMIN records database administrative privilege activity.

To learn more about managing DB2  security, read these chapters in the manual:

•    Managing access through authorization IDs and roles
•    Managing access through RACF
•    Managing access through row permissions and column masks
•    Managing access through trusted contexts
•    Managing access through data definition control
•    Managing access through exit routines
•    Protecting data through encryption and RACF
•    Auditing access to DB2

If you're on DB2 10 and have taken advantage of some of the new features, please take a moment to share your experience in comments.