As I am exploring new features of DB2 10.1, there are some fun things that I can do.

Example-1:

A table has rows in it. You want to make those rows invisible to all including you. Do this.

$ db2 "select count(*) from ps_table" –> You see rows in this table.

$ db2 "alter table ps_table activate row access control"

$ db2 "select count(*) from ps_table" –> Rows have become invisible now as you get 0 count and this is for all.

Your co-workers are doing some work in development and you just want to give them some fun time. Do this and laugh at the water cooler when you meet them. Do not do this in your production otherwise chance of a pink slip is great. A smart DBA or a cop can find who did this ALTER TABLE with activation and deactivation.

Example – 2:

As a smart DBA, the first thing you always do after creating a brand new production database is to run the following commands. Good Practice.

$ db2 connect to <dbname>

$ db2 "revoke connect on database from public"

$ db2 "revoke createtab on database from public"

$ db2 "revoke implicit_schema on database from public"

$ db2 "revoke bindadd on database from public"

Now, a brand new user USER1 comes. So, you grant DBADM on database to this user keeping with the policies of a lazy-less-work-least-path-of-friction DBA.

$ db2 connect to <dbname>

$ db2 grant DBADM on database to user USER1

USER1 is happy as he/she has all privilges to learn and to do things on the developmeht database.

USER1 goes away and now you do REVOKE DBADM on this user.

$ db2 revoke DBADM on database from user USER1

Now, USER1 do not have privilege to even connect.

USER1 comes back again. Now, this time you just granted CONNECT authority.

$ db2 grant connect on database to user USER1

But the user USER1 can see all data in the database still. Do you know the reason?

After DBADM was granted, DATAACCESS and ACCESSCTRL authority were given implicitly but they were not removed when DBADM was revoked. So, you have to also remove DATAACESS and ACCESSCTRL from this user.

Ok, now remove DATAACESS from this user but USER1 still has CONNECT.

$ db2 revoke dataaccess on database from user USER1

The USER1 has now CONNECT and ACCESSCTRL authorities only but still the USER1 is able to see the data. Do you know the reason?

The definition of ACCESSCTRL.

ACCESSCTRL authority is the authority required to grant and revoke privileges on objects within a specific database. ACCESSCTRL authority has no inherent privilege to access data stored in tables, except the catalog tables and views.