It's always a good
idea to verify that revokes did what you wanted them to do. With DB2 9.7, when revoking DBADM, you may leave
behind ACCESSCTRL and/or DATAACCESS without realizing it.
To illustrate, I first grant DBADM to user
locksmith. Since I don't specify WITHOUT
ACCESSCTRL and WITHOUT DATAACCESS, then by
default, DATAACCESS and ACCESSCTRL authority are also granted to
Locksmith.
$>db2 grant dbadm on database
>to
locksmith
$> db2 "SELECT char(AUTHORITY,25) AUTHORITY, D_USER from TABLE
(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('LOCKSMITH', 'U') ) AS T
where AUTHORITY in ('ACCESSCTRL','DATAACCESS','DBADM') ORDER BY AUTHORITY"
AUTHORITY D_USER
------------------------- ------
ACCESSCTRL Y
DATAACCESS Y
DBADM Y
Uh Oh. I didn't mean to grant
ACCESSCTRL and DATAACCESS. Oh well, I'll just revoke DBADM and start
again.
$>db2 revoke dbadm on database from locksmith
$> db2 "SELECT char(AUTHORITY,25) AUTHORITY, D_USER from TABLE
(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('LOCKSMITH', 'U') ) AS T
where AUTHORITY in ('ACCESSCTRL','DATAACCESS','DBADM') ORDER BY AUTHORITY"
face=Courier>AUTHORITY
D_USER
-------------------------
------
ACCESSCTRL
Y
DATAACCESS
Y
DBADM
N
Wait. That's not what I
wanted. Guess I have more work to do.
$>db2 "revoke accessctrl, dataaccess on database from locksmith"
$> db2 "SELECT char(AUTHORITY,25) AUTHORITY, D_USER from TABLE
(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('LOCKSMITH', 'U') ) AS T
where AUTHORITY in ('ACCESSCTRL','DATAACCESS','DBADM') ORDER BY AUTHORITY"
AUTHORITY D_USER
------------------------- ------
ACCESSCTRL N
DATAACCESS N
DBADM N
Ok, That's what I wanted.
face=Arial>Now let's take it from the top and see what happens if we
use this
command to grant DBADM :
>$>db2
"grant dbadm without accessctrl without dataaccess on database to
locksmith"
$> db2 "SELECT char(AUTHORITY,25) AUTHORITY, D_USER from TABLE
(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('LOCKSMITH', 'U') ) AS T
where AUTHORITY in ('ACCESSCTRL','DATAACCESS','DBADM') ORDER BY AUTHORITY"
AUTHORITY D_USER
------------------------- ------
ACCESSCTRL N
DATAACCESS N
DBADM Y
What happens if I do the revoke
now?
face=Courier>$>db2 revoke
dbadm on database from locksmith
$> db2 "SELECT char(AUTHORITY,25) AUTHORITY, D_USER from TABLE
(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('LOCKSMITH', 'U') ) AS T
where AUTHORITY in ('ACCESSCTRL','DATAACCESS','DBADM') ORDER BY AUTHORITY"
AUTHORITY D_USER
------------------------- ------
ACCESSCTRL N
DATAACCESS N
DBADM N
Now there is no authority left over for
ACCSSCTRL or DATAACCESS, so
I don't have to revoke those authorities this
time.
Like my math teachers always said, "Please check your
work". Mrs. Parker would be so proud that I remembered that.
face=Arial>_________________________________________________________________
_________
IDUG NA registration is now open:
IDUG North America Conference
My column on
Database Journal.
Database Journal - DB2Locksmith's Column
I WELCOME YOUR EMAILS TO:
size=2>db2locksmith at securedb2.com