DB2 LUW Security – The DB2 DBA as a Locksmith

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

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

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.
  • 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

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

Rebecca Bond
Rebecca Bond
Rebecca Bond, an IBM Information Champion, industry recognized independent consultant and author of the only published book specific to DB2 LUW security, "Understanding DB2 9 Security", enjoys sharing technical lessons learned from her experiences in government, healthcare and financial consulting roles. Rebecca holds numerous advanced IBM certifications covering all aspects of DB2 and is an expert at balancing the twin needs of robust security and accelerated performance. Her unique background provides a wealth of pertinent database and security puzzlers, which she delights in helping us understand and solve via articles, blog posts and presentations.

Latest Articles