DB2 to require password each time

Question

I am using db2inst1 to connect to a database in DB2 which I have installed on my machine. Therefore, db2inst1 user does not require username/password authentication (borrows them from the OS). I would like to change that, and force every time a connection is initiated a username/password to be requested.

More specifically, this is how the authentication configuration looks like:

db2 get dbm cfg|grep -i auth

 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) = 
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Database manager authentication        (AUTHENTICATION) = CLIENT
 Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trusted client authentication          (TRUST_CLNTAUTH) = SERVER
 Bypass federated authentication            (FED_NOAUTH) = NO

db2 connect to dbName

   Database Connection Information

   Database server        = DB2/LINUXX8664 10.1.0
   SQL authorization ID   = DB2INST1
   Local database alias   = DBNAME

db2 connect to dbName user db2inst1 using password

   SQL1639N  The database server was unable to perform authentication because 
   security-related database manager files on the server do not have the required 
   operating system permissions.  SQLSTATE=08001

I have played with some authentication combinations for "AUTHENTICATION" and "TRUST_CLNTAUTH" without much luck.

Answer 1

I believe you want the following

• AUTHENTICATION = CLIENT
• TRUST_ALLCLNTS = NO
• TRUST_CLNTAUTH = SERVER

As you have changed above, the AUTHENTICATION=CLIENT, will force the client to authenticate. (Which can still use the OS as noted above.)

TRUST_CLNTAUTH = SERVER will cause the client to send the user id and password to the server for authentication when they are provided in the connect statement. This still does not force the client to require the user name and password as it can get them from the OS.

This is because DB2 will assume TRUST_ALLCLNTS as YES by default. This causes the server to assume all clients can be trusted and allow them to check against their respective OS's. So from what I know, to force the client to require a user name and password with the connect statement requires TRUST_ALLCLNTS to be set to NO. This will cause the server to not trust them and force them to send the user id and password to the server to authenticate against it.

Answer 2

You issue db2 connect from the same operating system where the server (database) is installed. In this case there is no difference between AUTHENTICATION=CLIENT and AUTHENTICATION=SERVER or SERVER_ENCRYPT (by the way, I believe the manuals strongly recommend against CLIENT setting).

If you are logged in as an OS user db2inst1, the DB2 will not doubt that OS authenticated you as such. I am not aware of any trick that would cause DB2 to re-authenticate OS user just to "double check". But a very easy way to accomplish what you require is to be a different OS user while issuing db2 connect. If you are logged in as john and try to db2 connect to mydb user db2inst1, you will be asked for a password, because DB2 can see that OS have not authenticated you as db2inst1. Hence DB2 asks for a password and performs OS authentication on your behalf.

In a default database setup, all users (including john) would be able to connect as themselves, so, as you apparently have security in mind, you'd probably want to revoke connect on database from public first.