DB2 LUW Security - The DB2 DBA as a Locksmith

January 21, 2010

I’m sure you’ve noticed that many security related web sites incorporate a lock as a part of their logo. I’ve always thought that a single lock was a good start, but that security should have multiple locks at multiple layers with multiple keys (and perhaps some barbed wire, an electric fence, a moat, a mean junkyard dog, an armed guard and a few iron bars as well). With all these locks and keys, some locksmith skills are certainly useful. I’d like to introduce you to some MY favorite DB2 Locksmith tips. I hope that they will help you as you apply your own locksmith skills to your DB2 9.7 Linux, UNIX or Windows databases.

DB2 9.7 and Separation of Duties (SoD)

In DB2 9.7, there are numerous opportunities for achieving a more robust security architecture. One of those is the ability to truly employ the concept of “Separation of Duties” also known as SoD. In thinking about security and some of the governmental and industry security regulations that are in place, one mandate is to lessen security risk by lessening the power held by one individual or group. Even without a mandate, common sense and “security in depth” principles serve to remind us that a multi-layered defense, incorporating least privilege and separation of duties, is a wise approach toward data protection. Given recent headlines regarding security breaches, data certainly seems to need to be protected; in fact, it almost seems to be begging us to do a better job of protecting it. DBAs and technologists who live close to all this valuable data serve at the forefront of this battle to protect and secure these enterprise assets. Making SoD a reality is one step that they can incorporate into their defense-in-depth armor.

Before we look at the current DB2 security product features, it may help to understand some history regarding the high level DB2 LUW authorities and how they behaved in previous versions of DB2. In the past, the SYSADM authority, the highest level of authority, conferred a lot of power; power which included the ability to configure, start, stop, prune or extract any audit data as well as the ability to access and manipulate data for any database in that instance. Next in the power hierarchy was the DBADM authority, the highest database authority, which also conferred the ability to access data. Even though it might not have been wise or necessary, many shops allowed DBAs to hold SYSADM (which also implicitly conferred DBADM) just to make sure anyone on the DBA team could do anything they might possibly need to do in the day-to-day performance of their tasks. In other words, in a many enterprises, a DBA could do pretty much anything that they wanted regarding the instance or the database, including reading or changing user data if they desired. This functional situation certainly didn’t meet the security principles of least privilege or separation of duties.

Beginning in DB2 9.1, a new security functional job designation was introduced, that of the Security Administrator (SECADM) for the database. With DB2 9.7, the SECADM tasks are intensified and Separation of Duties abilities are specifically introduced. If your shop has only one DBA, then making SoD work is going to be a challenge, but most shops will be able to easily track their functional job tasks to the DB2 9.7 authorities and privileges.

In general, the High Level SoD features for newly created databases in DB2 9.7 are:

• The SYSADMs no longer implicitly get DBADM authority, which means they don’t automatically get data access.
• The Database Administration authority, DBADM, has changed significantly in a manner that tracks well to SoD security concepts and practices. New options in the “grant DBADM…” statement can be specified to prevent those who hold DBADM authority from also having the ability to access database user data in user defined tables (DATAACCESS) or perform grants and revokes (ACCESSCTRL). The specific SoD benefit, depending on how the DBADM authority is granted in a DB2 9.7 database, is that holders of that authority do not necessarily acquire the ability to access user data or to perform grants/revokes as they did in past releases.
• With DB2 9.7, the SECADM functional role has been separated from the DBADM functional role and the two no longer need to overlap in order to complete security tasks.

There are also some new and re-defined authorities. Some of these were carved out of previous authorities that, in the past, were conferred with DBADM authority. Whether new or re-vamped, all of them lend themselves to the Separation of Duties approach to task delegation including:

• DATAACCESS authority, which can be granted to allow users to have data access within a specific database. This conveys:
• LOAD authority
• SELECT, INSERT, UPDATE, DELETE privileges on tables, views, MQTs and nicknames
• EXECUTE on packages and routines (except audit routines)
• ACCESSCTRL authority
• Gives the ability to grant/revoke database privileges
• EXPLAIN authority
• Allows the holder to EXPLAIN, PREPARE and DESCRIBE SQL statements. (Note: This authority does not include explicit ability to execute the SQL.)
• WLMADM authority
• Allows management of workload objects for the database.
• SQLADM authority
• Ability to manage event monitors
• Ability to manage package and optimization caches
• Also conveys EXPLAIN authority (see above)

There are some important considerations regarding SoD functionality for enterprises currently using previous versions of DB2 as they prepare to move to DB2 9.7. Because previous versions of DB2 databases must go through a migration process when moving to DB2 9.7, there are some specific differences in how the authorities are assigned for ‘migrated’ versus ‘newly created’ databases. For migrated databases:

• DATAACCESS and ACCESSCTRL authorities will be granted to ids that currently hold DBADM.
• DBADM, DATAACCESS, and ACCESSCTRL are granted to the SYSADM GROUP.
• If migrating from a version of DB2 prior to 9.1, or a database that does not have a SECADM authority assigned, then, by default, the SECADM authority for the migrated database will be granted to the user id performing the migration. In other words, ‘post migration’, you will have an id holding SECADM authority even though the original database did not have one.

Once the migration is complete, however, the SECADM may want to revoke these “migrated authorities” and re-grant them based on the new SoD security model. Of course, since this is a pre-existing database, authority changes should only be undertaken following some solid analysis and testing to make sure that nothing is going to break once the changes are made, unless you just happen to like chaos.

Obviously, the true separation of the SECADM and DBADM roles is a major boost to the principles of SoD and least privilege. However, to me, one of the best SoD features is that DBAs no longer have to gain access to the data as a “ride along” with the ability to do their administration tasks. The SYSADM authority now matches the role it should play, as the “manager of the instance.” The high level DBADM authority has been scaled back to allow functionality without privilege escalation. DBAs typically don’t need to see the data and now, they don’t have to automatically acquire the ability to do so. In this case, a little less power means an enhanced security architecture.

So, what does all this mean for DBAs when thinking about SoD? The answer is, “it depends”. I’ve worked in enough shops to know that there is no standard template for aligning DBAs to tasks, so what might work regarding SoD in one enterprise could be a complete disaster in another. However, thinking about this logically, DB2 9.7 has provided exactly what we wanted -- multiple security layers, multiple locksmith opportunities and a greatly enhanced layered security configuration ability. Now we have to do our part to make Separation of Duties work for our enterprise.

A Few Thoughts & Some Important Acknowledgements

The more I write on technical subjects, the more I realize that I never have enough time or enough words to completely cover a topic. For example, in this article, I touched briefly on the DBADM and SYSADM authorities, but there is so much more that could be discussed about those two authorities and how they relate to a robust security architecture than I was able to cover here. I would encourage you to delve deeper into the specifics for these two high level authorities and consider all the security implications.

Sometimes writing a technical article such as this is like taking a wicked IQ test. You turn the box slightly and the problem looks completely different from the new angle. For that reason, it is always a good idea to have a network of technical experts to call on for clarification or assistance. I have been very fortunate to have found a small army of individuals who put up with my incessant questions and who recognize that sharing technical information is good for us all. I can’t thank them all here, there are just too many, but Kevin See, Susan Visser and Roger Sanders have been there for me from the beginning and I’d like to take this opportunity to publicly acknowledge their continued support and encouragement.

» See All Articles by Columnist Rebecca Bond