How to revoke permission of Windows Administrator user from DB2?

Question

On IBM DB2 v.9 windows, when someone connect to database by Server\Administrator user DB2 database will automatic accept and grant all permission to this user right? But, some environment Administrator of server no need to see every data in database. So how to prevent Administrator use connect to database?

Answer 1

By default, DB2 databases are created with CONNECT authority granted to public. If you want to restrict some users from connecting, you need to do

GRANT CONNECT ON DATABASE TO <user1>, <user2>, ...

Then revoke the CONNECT authority from PUBLIC

REVOKE CONNECT ON DATABASE FROM PUBLIC

Answer 2

Umm... For many times I try to revoke with this command but when I connect to database by Administrator account DB2 will automatic grant permission to Administrator again.

I will try again for make sure.

Answer 3

I don't think it's possible under normal circumstances simply because Administrator is in the sysadm group.

Options I can think of (but haven't tried) include:

• Setting the sysadm group to something else ("db2 update dbm cfg using sysadm_group blah"). Check the docs for caveats and gotchas when doing this, as I'm sure there are some.
• Stop using OS authentication. Use a different security plugin (8.2 and higher only). This would move the authentication, and thus groups, to a new location (say an LDAP server). Then you just don't add Administrator to the new location, and especially don't add Administrator to the sysadm group again.