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
In my last article, I gave you
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
$>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
Our diet DB2 Roles preparation
created two roles: HEADCHEF, LINECHEF
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
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