IBM DB2 9.7, DBADM and my Rubik's Cube
February 19, 2010
It's a challenge to adapt to change, but the changes in IBM DB2 9.7's Database Administrator authority bring significant database security benefits. Join Rebecca Bond as she shares some twists, some turns, and some clues regarding DB2 9.7's Database Administrator (DBADM) authority.
I never could figure out my Rubiks Cube, which is surprising considering I have almost learned how to manage the DBADM authority in DB2 9.7. Do you think this is a weird comparison? Well, let me explain.
Ive been working with the DB2 LUW product since DB2 5.0. So, DBADM authority has been a good friend for many years. During those years, with each release of DB2, there were significant enhancements and changes to understand and incorporate into our database environments. There was one static authority, however, that seemed to be comfortably unaffected by the massive changes swirling all around it with each new release. DBADM was my familiar friend, an authority I could count on and one that promised to always be there for me. In all those years, I dont remember my friend making any significant change or causing me any problems.
Then, along comes Separation of Duties in DB2 9.7 and my old friend, DBADM has changed. In fact, its like someone picked up DBADMs Rubiks cube and twisted it, moving the colors of DBADM around in that random Rubiks cube way. My DBADM is no longer as familiar, and sometimes, at least at first, it was hard for me to put the colors back where they belonged.
This is not necessarily a bad thing. True its a challenge at first to adapt to most change, but this one brings a significant security benefit, which means the effort to re-align my knowledge with the new authorities is well worth the time. Although I recognize that this change is a positive move in the right direction, it does take some understanding and perhaps a couple of trial runs to put all the DBADM colors back in the right places. Since DBADM has been such a good friend of mine for so long, I thought I would introduce the two of you and show you how to spin the sides on your DB2 9.7 DBADM authority so that you can put it back in the proper order at your shop.
Lets examine DBADM from the DB2 9.7 perspective
First the basics. The DBADM authority is a high-level administrative authority, and only applies to a specific database. In other words, the DBADM authority does not cross over from database to database and is not an instance level authority. In order to do their basic tasks, those who hold DBADM authority automatically get SELECT privilege on the system catalog tables and views and they can execute all system-defined routines, except audit routines.
DBADM authority can only be granted or revoked by the security administrator (SECADM) for the database. It can be granted to a USER, a GROUP, or a ROLE. For obvious security reasons, the PUBLIC group cannot obtain the DBADM authority either directly or indirectly.
Holding the DBADM authority for a database allows a user to perform these actions on that database:
Note: SQLADM authority and WLMADM authority are subsets of the DBADM authority. Additionally WLMADM authority can grant USAGE on workloads.
Let the Game Begin
The Three Twist Rule All Rubiks Cubes must be Twisted 3 times before you begin.
Twist Number 1 -- DBADM authority and DATAACCESS
As we know, in DB2 9.7, the security administrator (SECADM) gets to perform grants to confer DBADM authority. Depending on the business need and the syntax of those grant statements, the SECADM has the ability to separate those who hold DBADM from some of their previous power.
DATAACCESS is a new DB2 9.7 authority. To help us with Separation of Duties, it was carved out of the DBADM authority as a subset. DATAACCESS authority allows the holder to have access to user data within the database. The SECADM has the power to grant DBADM authority with the DATAACCESS option or without DATAACCCESS.
Twist Number 2 DBADM authority and ACCESSCTRL
In DB2 9.7, ACCESSCTRL authority allows a user to grant and revoke privileges and non-administrative authorities within a specific database.
Twist Number 3 DBADM and recent history
In previous versions of DB2, the DBADM authority conferred the ability to access user data and the ability to grant and revoke. If youve granted DBADM before, you probably remember the simple syntax:
db2 "connect to database ..." db2 "grant DBADM on database to ..."
The SECADM can still perform that simple grant statement in DB2 9.7. If so, then DATAACCESS and ACCESSCTRL authority will both be implicitly granted by default along with the remaining DBADM authorities. If using the simple grant statement (above), the DB2 9.7 DBADM authority will mimic your old school DBADM authority. However, from a security standpoint, you probably want to separate all these twists from each other.
Sometimes, whether its a puzzle or a new approach, examples can help shortcut the learning effort, so lets work through a few of the approaches you can take to solve this puzzle.
The Winning Approach?
To Fix the DBADM Twists
In each of the following scenarios, the database is at the DB2 9.7 version level, the user, locksmith, is a new user and currently does not hold any of: DBADM, DATAACCESS or ACCESSCTRL. So, we are starting with a clean slate for each example just to demonstrate how the grant syntax can be constructed.
$> db2 "grant DBADM without DATAACCESS on database to user locksmith"
The result of this grant is that user Locksmith will have DBADM authority AND ACCESSCTRL authority. In addition to performing DBADM tasks, Locksmith will be able to grant and revoke privileges and non-administrative authorities. However, locksmith will not be snooping through user data with only these grants in place.
Heres a bonus round for extra points. There is a little side twist on this grant that might surprise you.
$> db2 "grant DBADM with DATAACCESS on database to user locksmith"
The result of this grant is that user Locksmith will have DBADM, DATAACCESS AND ACCESSCTRL authority. Locksmith will get all three authorities from this grant.
Did you expect this?
The same holds true for ACCESSCTRL.
$> db2 "grant DBADM without ACCESSCTRL on database to user locksmith"
The result of this grant is that user Locksmith will have DBADM AND DATAACCESS authority, but will not have ACCESSCTRL.
$> db2 "grant DBADM with ACCESSCTRL on database to user locksmith"
Here again, we end up with all three authorities being granted: DBADM, ACCESSCTRL and DATAACCESS.
The winning syntax combination that grants DBADM without also granting ACCESSCTRL or DATAACCESS is:
$> db2 "grant DBADM WITHOUT ACCESSCTRL WITHOUT DATAACCESS on database to user locksmith"
There is one final twist to this new game. Since ACCESSCTRL and DATAACCESS are separate authorities, they can be appropriately granted to others (even users who arent DBAs), in keeping with a specific job function and without having to over-escalate privileges.
AND the Winners Are?
In the final puzzle tally, when high level database authorities are appropriately granted, the security tenant of Least Privilege is upheld; Security in Depth defenses are enhanced; and all the colors of DBADM are back in their proper places.
Please join me in congratulating todays overall First Place Winner You.