DB2 revoke user privileges from a database from multiple users

Question

I want to revoke all privileges from all users but one from a database. DB2 10.5 LUW

I was thinking along the lines of:

 db2 "revoke all on database from user IN (select grantee from syscat.dbauth where grantee not IN 'SAFEUSER')"

but I can't get it to work.

Any ideas?

Answer 1

There is no ALL clause in the REVOKE (database authorities) statement.
You may generate the set of statements needed by the following select statement:

select 
  'REVOKE '
|| SUBSTR 
(
  CASE ACCESSCTRLAUTH WHEN 'N' THEN '' ELSE ', ACCESSCTRL' END 
||CASE BINDADDAUTH WHEN 'N' THEN '' ELSE ', BINDADD' END 
||CASE CONNECTAUTH WHEN 'N' THEN '' ELSE ', CONNECT' END 
--- add here expressions with all other *AUTH columns
, 2)
||' ON DATABASE FROM ' 
|| CASE 
     WHEN GRANTEE = 'PUBLIC' THEN ''
     WHEN GRANTEETYPE = 'U' THEN 'USER' 
     WHEN GRANTEETYPE = 'G' THEN 'GROUP' 
     WHEN GRANTEETYPE = 'R' THEN 'ROLE' 
   END
||' '||GRANTEE
from syscat.dbauth
WHERE 'Y' IN 
(
ACCESSCTRLAUTH, BINDADDAUTH, CONNECTAUTH
--- add here all other *AUTH columns separated by ','
)
AND  grantee <> 'SAFEUSER'
;