I am in db2 V9.5 FP1. Is it possible to revoke select, insert, update, delete access on one table from a user who has dbadm authority ?

Hi,

afaik this can only be done in DB2 9.7.

nvk

Thanks for your response. Can you please tell me how in 9.7 can we make it work?

There was at Chat with the lab about new DB2 9.7 Features.
DB2 9.7 Overview

Could find anything on IBMs Website (which is really a mess). Only the grant in the documentation:
IBM DB2 9.7 for Linux, UNIX and Windows Information Center
grant dbadm without dataaccess ...

I wanted to restrict the access only on one table. "Without Data access" option would restrict the access on all the tables.

Yes,
the dbadm without access has to be treated as a normal user and needs explicit grants for each table and view.

we wanted dbadm to have accessctrl authority. So as to what you are saying, grant dbadm to a user and revoke the dataaccess authority and individually grant select, insert, update, delete to all the tables to that user except that specific table.

But the issue is dbadm can grant select, insert, update, delete to that table to himself.

The other Option would be Label Based Access Control (LBAC), but this Feature requires an additional License.
The User would be able to read the Table, but no data would be returned. Also some Queries might return different results, if they are run by different users.