Database Security Puzzle Solving with IBM DB2 LUW Roles

The task of keeping up with database security can sometimes be monumental! A major challenge when it comes to administering authorities and privileges is how to approach this puzzle in a sensible, time-saving, ‘security robust’ manner. There is no need for concern; we have a hint or two that will help solve this puzzler.

Have you noticed that when it comes to database privileges
and authorities, the task of keeping up with “who holds what?” can sometimes be
monumental! Perhaps you have a user base that tends to change job functions
frequently or maybe your organization has a high amount of employee turnover.
A major challenge when it comes to administering authorities and privileges is
how to approach this puzzle in a sensible, time-saving, ‘security robust’
manner. There is no need for concern; we have a hint or two that will help us
solve this puzzler.

If you are using DB2 LUW 9.5 or later, I’d like to introduce
you to IBM DB2 roles. If you’re not using roles yet, you’re missing out on a
time-saving, puzzle solving, database security shortcut. Database roles allow
the database security administrator (SECADM) to avoid limitations that are
problematic when using operating system groups, while still enjoying the
benefits of enhanced security strength and a greatly reduced administrative overhead.

In too many ways, keeping up with “who gets what privileges
and authorities” on a database is like trying to re-assemble a 1000 piece jig
saw puzzle. It takes a lot of persistence and attention to detail to solve
the puzzle’s initial setup tasks and even more diligence to keep the pieces
pristine when your kids keep walking by and shaking the card table (or, in the
case of DB2, when your users change responsibilities or move on to other
jobs). Any shortcut we can use to eliminate some of the DB2 security puzzle
pieces easily, without sacrificing security, is going to be appealing. I like
to envision database roles as my personal SECADM security puzzle aggregation
mechanism that allows me to design my security architecture using many less
puzzle pieces. You can also think of roles as a plug and play security
dynamic, since it’s easy to re-work roles when things change. If you make
modifications for privileges or authorities for a role, then every user who was
granted that role will now receive the change as well. All these puzzle
helpers make roles look like a solid option.

An added bonus of granting to roles versus groups (which are
created and managed outside the database) is that I don’t have to involve any
operating system personnel to create or administer a role. As SECADM, I can
create and manage roles entirely within the database and without the assistance
of any operating system administrators. This also means that I have complete
visibility into the entire “granting, revoking” process and don’t have to rely
on others to make sure the correct users are in the correct groups. I’m sure
the operating system administrators are happy about this as well since they
were constantly getting emails from me asking for information about groups and
users. When it comes to security, I am not shy about asking for proof that
things are set up correctly.

Some Non-Puzzling Facts about Roles:

DB2 roles are database objects that can only be
created or dropped by someone who holds SECADM authority. Roles don’t actually
have an object owner (of course, we DBAs take virtual ownership of everything
in our databases, but that’s another topic). One or more authorities,
privileges or even other roles can be granted or revoked to (or from) a role.

Once created, a role can be granted to one or more
users, groups, trusted contexts, other roles or even to the PUBLIC group. I
would suggest you avoid granting to PUBLIC (a special group that consists of
all current and future users) for a stronger approach to your security
architecture, but I digress. When a user connects to the database, roles that
are assigned to that user are verified during the authorization process to
ensure that the user receives all the privileges and authorities for any roles
that they have been granted. LBAC security labels and exemptions, database
privileges, database authorities and even Role membership can be granted to a

When one role is granted membership in another role,
you can achieve a role hierarchy, which enhances design granularity. This
allows one role to inherit all the privileges and authorities of the other role
they have been granted. For example, in an insurance company you might create
two roles, one for Claims Leads who need some special privileges in addition to
those necessary to adjust the claims; and another role just for Adjusters. If
the role ADJUSTER is granted to the role CLAIMSLEAD, then CLAIMSLEAD is said to
contain ADJUSTER. The role CLAIMSLEAD inherits all the privileges of
role ADJUSTER while also getting their special privileges via the role,
CLAIMSLEAD. However, by design, you cannot create a cyclical role hierarchy.
In our example, once the role ADJUSTER is granted to role CLAIMSLEAD, if you
try to grant the role CLAIMSLEAD back to the role ADJUSTER an error is

An added benefit of using roles versus groups is
that you get around some issues. Group privileges and authorities granted to
groups are not considered when a user is creating views, materialized query
tables (MQTs), SQL routines, triggers and packages containing static SQL. One
advantage to using groups, however, is that group membership can be managed
centrally. Of course, you can assign roles to groups for a hybrid

If your security administrator (SECADM) is
overworked, some of the responsibilities for managing roles can be delegated to
others by granting the authorization id ‘membership in the role’ using the WITH
ADMIN OPTION. This allows that user’s authorization id the ability to grant or
revoke roles to/from others or provide a comment on the role. Fortunately, there
are some built-in protections. This user will not be allowed to drop the role
and they cannot grant or revoke the WITH ADMIN OPTION unless they also happen
to hold SECADM authority.

A Review of the Puzzle Cheat Sheet

I always like to know a fast approach to solving
security puzzles. So if you want some cheats, read on. With roles, I like to
use the views, SYSCAT.ROLES and SYSCAT.ROLEAUTH. SYSCAT.ROLES lets me find out
what roles are currently created for the database and clues me in about the
role creation date, role identifiers and even tells me about any associated
audit policies. SYSCAT.ROLEAUTH provides information on users who have been
granted authority over a role and whether they were given the ‘WITH ADMIN
OPTION’. Another helpful item is a system function called AUTH_LIST_ROLES_FOR_AUTHID,
which can be used to show which roles have been granted to a user, group, or

Game On!

Now that you have read the rules of ROLE play and
even have some nice cheats, I turn this challenge over to you. You have all the
pieces of the ROLE puzzler laid out on the card table; it is time to begin
building your own ROLE PUZZLE solution.

Additional Resources

IBM developerWorks
Implement new security capabilities in DB2 9.5, Part 1: Understanding roles in DB2 9.5


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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles