Running DB2 v9.105 FP 5 on AIX

I'm trying to learn about controlling user access at a table level but things aren't working the way I expect.

I've created a new database - DBACTION - with 2 tables - general_access and restricted_access. The goal is to allow user HIREM to access general_access but not restricted_access.

I've revoked all database level privileges from PUBLIC - my id, WHOUSE, is the only entry that appears in the DBAUTH table. I've not granted any privileges to user HIREM for table restricted_access - I verified in the tabauth table that there is no entry for that table for that user (or for PUBLIC).

So - based on that - I would expect HIREM not to be able to access the database, and even if he could he shouldn't be able to access the restricted_access table.

However, not only can he connect to the database, he has full access to the table.

What am I missing???

I am assuming that the user is not the instance owner.

See if the user belongs to the SYSADM group (as defined in AIX) as specified in the dbm config:
db2 get dbm cfg

As Marcus already pointed out please provide output for the following.

db2 get dbm cfg | grep -i 'group name'

lsuser hirem ------- make sure that (hirem) is low case

db2 "select * from syscat.dbauth where GRANTEE in (list UNIX groups and this id that this user belongs in here separated by comma and in single quate)"


db2 "select * from syscat.tabauth where GRANTEE in (list UNIX groups and this id that this user belongs in here separated by comma and in single quate)"

Last sql can be modified to only run for those particular tables.

will give you some of this information

$ db2 get dbm cfg |grep -i 'group name'
SYSADM group name (SYSADM_GROUP) =
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =


$ lsuser hirem
hirem id=1101 pgrp=ext_mast groups=ext_mast,staff home=/home/ext_users/hirem shell=/usr/bin/ksh gecos=test id registry=compat


$ db2 "select * from syscat.dbauth where GRANTEE in ('HIREM','EXT_MAST','STAFF')"
0 record(s) selected.


$ db2 "select * from syscat.tabauth where TABNAME='RESTRICTED_ACCESS' and GRANTEE in ('HIREM','EXT_MAST','STAFF')"
0 record(s) selected.

db2 get authorizations gave me this -
So, it looks to me like that Indirect SYSADM authority could be the root of my problem. Now I just need to find out what that even means.

Thanks for the help so far.

Quote from the manual :

What is the instance name and which group is the primary group of the instance owner ?

lsuser <instance name>
and look for pgrp ..
I guess this will be one of ext_mast,staff or ext_mast

When you ran sathyaram command, were you logged in as inst owner or your id? do lsuser on both inst and your id.

I might be wrong, but I think that is only true if you explicitly set SYSADM to NULL. If you do not set it, then only inst id can do SYSADM bidding.

Instance name is whouse.

I ran the 'db2 get authorizations' from the hirem user.

>lsuser whouse
whouse id=250 pgrp=ext_mast groups=ext_mast,dasadm1 home=/whouse shell=/bin/ksh gecos=Whouse user id registry=compat roles=

>lsuser hirem
hirem id=1101 pgrp=ext_mast groups=ext_mast,staff home=/home/ext_users/hirem shell=/usr/bin/ksh gecos=test id registry=compat

Hmm, maybe sathyaram was right about NULL. Never had to deal with it as I like to set it and forget it and not leave anything to a chance.

Bigger question why is your end user is in the same UNIX group as your inst owner ID? That is a HUGE NONO.

Try setting your admin to dasadm1 unix group and then check if your user still has access.

Changed the SYSADM group from EXT_MAST to DASADM1 -

run from instance owner 'whouse'
>db2 get dbm cfg |grep -i 'group name'
SYSADM group name (SYSADM_GROUP) = DASADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =

run from user 'hirem' still shows -
$ db2 get dbm cfg |grep -i 'group name'
SYSADM group name (SYSADM_GROUP) =
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =

And hirem can still query that table.

You will have to stop and start the instance for this group name change to be effective.

I would actually setup a different group - say db2adm or something like that for the db2 instance(s).

You're right, stop and start is important.

That worked!

And now the grants and revokes are working the way I expected them to.

I totally agree with setting up a db2adm group, I'll have to talk to the sys admin and work that out. This was all proof of concept before implementing it on the real db.

Thanks for all your help!

Make sure to create a stand alone group just for DBA's and inst id. Then set up a daily script to monitor for intrusion to this group. Do not ask me how I learned that this is a MUCH needed step.

OK, so now I've encountered my next problem - politics.

Boss says 'Yeah, well we can't really make changes like that. I need you to restrict access to these users but keep them in the SYSADM_GROUP.'

I feel like I'm in a Dilbert strip today.