There are two types of DBAs
Ones who are control freak – This article is not meant for you as you have already taken pains to grant privilges to an application user without granting DBADM. You have a very good mechanism to control how applications connect and what they can access. You are following good practices so you can stop reading further
Lazy DBAs like me who are not control freaks but at the same time do things in an order that meet the requirements of the business by using least path of resistance and doing the work.
So, I like to give DBADM to the application user so that I am not bothered to slice and dice things as to what object an application can connect or do something. Basically, I do not want to take the burden of granting individual privilges to the objects. I trust the application folks and have means to determine what they did if they did something wrong so that I can nail them down. Like they dropped an object and no one knows who did this. I can find who did. So, with power responsibility comes and application folks are responsible people who are the bedrock of running an actual business whereas DBAs are like commodities.
But problem with DBADM is that if I want to do some maintenance, I have to ask application folks to shut down their application server or schedule a time with them.
How can you do maintenance and still let them do what they want to and I should be able to block them when I need to take down the database say in emergencies.
Here are the list of things:
1. Create an os group appgroup.
2. Create an os user appuser. Add this user to this group appgroup.
3. Create password.
4. Connect to the database from your instance user account login shell and do the following commands.
db2 connect to <dbname>
db2 grant dbadm on database to group appgroup
Now, from the application, you can use appuser login IDs to connect to the database. These login IDs are not instance user id but they have all privs on the database.
From the application connection management, use database driver property currentSchema=<schemaName> for whatever schema name is being used so that tables do not have to be qualified for the schema name.
Now to restore the database or to do some maintenance where you need dedicated exclusive access without asking application owners to shut the application.
Login as instance user.
db2 quiesce instance <instanceName> restricted access immediate
db2 force applications all
db2 restore database <dbname> from <location Directory> taken at <date-time>
After restore, you must unquiese the instance otherwise applications will not be able to connect.
db2 unquiesce instance <instanceName>
At this point, applications will be able to establish connections which can be seen by the db2 list applications all command.
Even though the appgroup (to which appuser belongs) has DBADM authority, you have quiesced the instance with restricted access which will not allow application server to connect until you complete the maintenance.
DBADM also has QUIESCE authority so technically the application server should be able to establish the connection but by using QUIESCE at the instance level in a restricted mode, you have fenced your database from access by the retry logic of the application machine which is constantly looking to establish the connection.
Lazy DBAs are the one that are happy and live longer. One research shows that control freak DBAs are likely to live only 5 years after retirement.