Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 19, 2010

IBM DB2 9.7, DBADM and my Rubik's Cube

By Rebecca Bond

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 Rubik’s 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.

I’ve 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 don’t 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, it’s like someone picked up DBADM’s Rubik’s cube and twisted it, moving the colors of DBADM around in that random Rubik’s 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 it’s 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.

Let’s 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:

  • Create, alter, and drop database objects
          (Exception: security related objects)
  • Read log files
  • Create, activate, and drop event monitors
  • Query tablespace states
  • Update log history files for the database
  • Quiesce a tablespace
  • Reorganize tables
  • Collect catalog statistics (via RUNSTATS)

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 Rubik’s 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 you’ve 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.

CAUTION: One of the old cheats for the Rubik’s cube dilemma was to peel the labels off the cube and just paste them in the correct places. Don’t take that approach with permissions and authorities on your databases. Changing permissions and/or authorities without understanding the impact can be perilous to your continued employment. Before you make a change to the DBADM authority in your shop, perform the appropriate analysis and testing and have a “back out” plan, just in case.

Sometimes, whether it’s a puzzle or a new approach, examples can help shortcut the learning effort, so let’s 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.

Here’s 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 aren’t 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 today’s overall First Place Winner – You.

» See All Articles by Columnist Rebecca Bond

DB2 Archives