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 Apr 20, 2010

Database Security Puzzle Solving with IBM DB2 LUW Roles

By Rebecca Bond

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

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

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

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

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

DB2 Archives