0

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
arenginsm na
  • 141
  • 1
  • 8

1 Answers1

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'
;
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • Does this remove the user entries or just set all privs to N? – arenginsm na Oct 29 '19 at 09:41
  • Db2 will remove entries in `syscat.dbauth` is all the AUTH columns are set to `N`. Also for other privileges, you can generate similar statements from `sysibmadm.privileges` – Paul Vernon Nov 05 '19 at 21:47