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.
