DB2 LUW Security -- Revoking DBADM (REALLY)

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"       

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.

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?

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

__________________________________________________________________________

IDUG NA registration is now open:  IDUG North America Conference