Putting IBM DB2 Database Security Setup Work on a Diet
May 13, 2010
Database administrators consistently strive to find faster ways to accomplish tasks in an effort to maintain some semblance of sanity as they navigate through persistent chaos. Learn how to simplify your IBM DB2 Database Security Setup using DB2 ROLEs.
DBAs, can you relate? It seems like I'm always on a diet and that there are always more "TO DO" items on my list than there are hours in the day to do them. I don't know about the diet part, but the "TO DO" list overload seems to be typical for most DBAs. We consistently strive to find faster ways to accomplish our tasks in an effort to maintain some semblance of sanity as we navigate through persistent chaos. Lately, I've been reading up on diets and I've found that all that extra job stress can make losing weight a challenge. In an attempt to find my own personal synergy and avoid any weight gain brought on by too much stress, I have decided to put my DB2 Security setup tasks on a diet by using DB2 Roles.
In my last article, I gave you the rules for DB2 Roles, which have been available to ease setup tasks since DB2 9.5. Now that we know the rules about roles, would you care to join me as I begin to daydream about putting my security tasks on a diet? If you dare to join this daydream, we will be transported to a world where diets can provide a fun learning exercise without the pain of counting calories.
Using one of our elegant New Orleans restaurants as a backdrop for our dieting delight, I will create a role, HEADCHEF and a role LINECHEF. I want to allow my Head Chef, known only by the mysterious user id, Menumaster, a lot of oversight latitude. The Head Chef will have the ultimate authority for all diet menus. My ID (Locksmith, of course) holds SECADM authority, so I am going to be the security diet setup hostess. I'll do the prep work and then I'll turn things over to the expert.
For starters, I'll create two tables, one named LUNCH, for the exclusive use of the HEAD CHEF, and one called LMENU that the LINE CHEFs can use to propose menus that they would like the HEAD CHEF to consider.
$ >db2 "create table menumaster.lunch (menudate date, appetizer varchar(40), entree varchar(40), dessert varchar(40), price decimal(12, 2))" $ >db2 "create table menumaster.lmenu (menudate date, appetizer varchar(40), entree varchar(40), dessert varchar(40), price decimal(12, 2))"
Let's create our two roles now and make some initial grants.
$>db2 "create role HEADCHEF" $>db2 "grant role HEADCHEF to user MENUMASTER" $>db2 "create role LINECHEF" $>db2 "grant role LINECHEF to role HEADCHEF"
(notice I granted a role to a role which is known as a role hierarchy.)
Let's check the work so far. No need putting more menu choices out there if we don't have the proper ingredients to prepare them. First, we might try a query using the SYSCAT.ROLES view to see what we can find there. SYSCAT.ROLES can give you information on ROLENAME, ROLEID, CREATE_TIME, AUDITPOLICYID and AUDITPOLICYNAME, but for the purposes of getting results while keeping things slim, let's just search for the date these roles were created using the following query.
$>db2 "select char(rolename,35) rolename, date(create_time) date_created from syscat.roles where rolename in ('HEADCHEF', 'LINECHEF')" ROLENAME DATE_CREATED ----------------------------------- ------------ HEADCHEF 04/28/2010 LINECHEF 04/28/2010
I also want to check that the HEAD CHEF has been granted the role LINECHEF to make sure my role hierarchy is valid.
$>db2 "select char(rolename,35) rolename, granteetype from table (sysproc.auth_list_roles_for_authid ('HEADCHEF', 'R')) as X" ROLENAME GRANTEETYPE ----------------------------------- ----------- LINECHEF R
Looks like the initial preparations are starting to come together nicely. The user, MENUMASTER, has been granted the role, HEADCHEF. The LINECHEF Role has also been granted to the HEADCHEF Role (a role granted to a role).
The LINECHEF Role needs the ability to SELECT, INSERT and UPDATE on the table LMENU, but we don't want them to have the ability to DELETE rows from that table, so we grant:
$>db2 "grant select, insert, update on MENUMASTER.LMENU to role LINECHEF"
In case the LINECHEF makes poor diet menu choices, the HEADCHEF Role may need to delete rows on the LMENU table and the HEADCHEF will also need SUID on the LUNCH table since that will be the actual menu planning table.
$>db2 "grant select, insert, update, delete on table MENUMASTER.LUNCH to role HEADCHEF" $>db2 "grant delete on table MENUMASTER.LMENU to role HEADCHEF"
Because we previously granted the LINECHEF Role (which has Select, Insert and Update on MENUMASTER.LMENU) to the HEADCHEF Role, we only needed to grant DELETE on MENUMASTER.LMENU to the HEADCHEF Role now. Don't worry, those who hold the HEADCHEF Role will be able to Select, Insert, Update and Delete on the LMENU table now.
While we're making skinny choices, why don't we just go ahead and look at one of the newer diet friendly ways to get some good information about authorizations. The administrative view, SYSIBMADM.AUTHORIZATIONIDS is becoming one of my favorite approaches to help me easily sort out some robust security information. This view can be used to retrieve information about privileges or authorities that have been granted on the database. I've written this particular security diet preparation plan using a simple case statement so that it displays the actual AUTHIDTYPE instead of just the single character representation (U, G, R). Using this case statement to display these values can help those who have to read items on the menu by candlelight.
$>db2 "SELECT char(AUTHID,35) as AUTHID, CASE AUTHIDTYPE when 'U' then 'USER' when 'G' then 'GROUP' when 'R' then 'ROLE' ELSE 'ERROR' END as AUTHIDTYPE from SYSIBMADM.AUTHORIZATIONIDS where authid in ('LINECHEF', 'HEADCHEF') " AUTHID AUTHIDTYPE ----------------------------------- ---------- HEADCHEF ROLE LINECHEF ROLE
If you've noticed that we can use a variety of options to confirm that our setup steps are valid, you get extra diet points. With DB2 security, as with most diets, those who succeed often display an ability to adapt and use different strategies as necessary to reach their long term goals. That's why I always like to be aware of different approaches, just in case I need to change my strategy.
The HEAD CHEF, user Menumaster, has just informed me that she has hired 3 expert diet gurus to start planning our menus. Let's add these new users to the LINECHEF role and grant CONNECT on the database to the roles LINECHEF and HEADCHEF.
$> db2 "grant connect on database to role linechef, headchef" $> db2 "grant role linechef to dietchef1, dietchef2, dietchef3"
Well, I think that takes care of lunch setup preparation steps which reminds me it's lunchtime now. Of course, I still have breakfast and dinner diet prep work to consider, but since my stomach is making loud, angry noises, this seems like a good place to stop. But before I sit down to my diet meal du jour, let's recap how we've built a mental daydream diet model that used DB2 ROLES to put our security setup tasks on a diet.
Our diet DB2 Roles preparation steps:
1. We created two roles: HEADCHEF, LINECHEF
2. We created a role hierarchy by granting the role Linechef to the role Headchef
3. We made some grants to those roles for SUID and CONNECT on the database
4. We also granted the roles to the appropriate users
5. We checked our work using three different approaches via SYSCAT.ROLES, SYSPROC.AUTH_LIST_ROLES_FOR_AUTHID and SYSIBMADM.AUTHORIZATIONIDS.
I'm confident that we have burned enough calories to allow us to enjoy our lunch totally guilt free. After lunch, we can start working on the dinner and breakfast setup steps, but now that we know how to truly achieve DB2 security diet successes using DB2 Roles, that work will be easy.
Ok, I'll admit, I can't REALLY put my security setup work on a diet, but given how much easier the security administration tasks are when I'm using DB2 ROLES, I'm still going to declare this lighthearted attempt to explain how to use DB2 ROLES as a "DBA Diet Success Fable". Despite what my Fourth grade teacher said, sometimes daydreaming can be a good thing.
IBM: Understanding roles in DB2 9.5