DB2 Security: Authentication

DB2 authentication controls the following aspects of a database security plan:
  • Who is allowed access to the instance and/or database
  • Where and how a user’s password will be verified
It does this with the help of the underlying operating system security features whenever an attach or connect command is issued. An attach command is used to connect to the DB2 instance, whereas a connect command is used to connect to a database within a DB2 instance. The examples below walk you through the different ways that DB2 will authenticate a user issuing these commands. These examples use the default authentication type of SERVER in the database manager configuration file. Example 3 below illustrates how DB2 can be used to change the password on the OS of the server.
Log on to the machine where DB2 is installed with the user ID you used to create the DB2 instance. Issue the following commands:
db2 attach to DB2
Here, authentication is done implicitly. The user ID used to log onto the machine is used and is assumed to be already verified by the operating system.
db2 connect to sample user test1 using password
Database Connection Information
Database server        = DB2/NT 9.1.0
SQL authorization ID   = TEST1
Local database alias   = SAMPLE
Here, authentication is done explicitly. The user test1 with the password password is verified by the operating system. User test1is successfully connected to the sample database.
db2 connect to sample user test1 using password new chgpass confirm chgpass
The user ID test1 with password password is verified by the operating system as in example 2. The password for test1 is then changed by the operating system from password to chgpass. As a result, the command in example 2 will fail if you reissue it.
 DB2 authentication types
Authentication types are used by DB2 to determine where authentication is to take place. For example, in a client-server environment, will the client or the server machine verify the user’s ID and password? In a client-gateway-host environment, will the client or host machine verify the ID and password?
DB2 9 has the ability to specify different authentication mechanisms depending on whether the user is attempting to connect to the database, or perform instance attachments and instance level operations. By default, the instance is set up to use one type of authentication for all instance level and connection level requests. This is specified by the Database Manager Configuration parameter AUTHENTICATION. Introduced in V9.1 is the Database Manager Configuration parameter SRVCON_AUTH. This parameter specifically deals with connections to databases. So, for example, if you have the following set in your DBM CFG:
Server Connection Authentication          (SRVCON_AUTH) = KERBEROS
Database manager authentication        (AUTHENTICATION) = SERVER_ENCRYPT

Then attachments to the instance would use SERVER_ENCRYPT. Connections to the database however would use KERBEROS authentication. If KERBEROS was not properly initialized for the server but a valid user ID / password was supplied, then the user would be allowed to attach to the instance but not allowed to connect to the database.
The following table summarizes the available DB2 authentication types. In a client-gateway-host environment, these authentication options are set on the client and gateway, not on the host machine. Setting these options is discussed in more detail throughout this section.
Table 1. DB2 authentication types
SERVERAuthentication takes place on the server.
SERVER_ENCRYPTAuthentication takes place on the server. Passwords are encrypted at the client machine before being sent to the server.
CLIENTAuthentication takes place on the client machine (see Dealing with untrusted clients for exceptions).
*KERBEROSAuthentication is performed by the Kerberos security software.
*KRB_SERVER_ENCRYPTAuthentication is performed by Kerberos security software if the client setting is KERBEROS. Otherwise, SERVER_ENCRYPT is used.
DATA_ENCRYPTAuthentication takes place on the server. The server accepts encrypted user IDs and passwords, and will encrypt the data. This operates the same way as SERVER_ENCRYPT, except the data is encrypted as well.
DATA_ENCRYPT_CMPAuthentication is the same as for DATA_ENCRYPT, except that this scheme allows older clients that don’t support the DATA_ENCRYPT scheme to connect using SERVER_ENCRYPT authentication. The data in this case will not be encrypted. If the client connecting supports DATA_ENCRYPT, it is forced to encrypt the data, and cannot downgrade to SERVER_ENCRYPT authentication. This authentication type is only valid in the server’s database manager configuration file and is not valid when used on the CATALOG DATABASE command on a client or gateway instance.
GSSPLUGINAuthentication is controlled by an external GSS-API plugin.
GSS_SERVER_ENCRYPTAuthentication is controlled by an external GSS-API plugin. In the case where the client doesn’t support one of the server’s GSS-API plugins, SERVER_ENCRYPT authentication is used.
*These settings are valid only for Windows 2000®, AIX®, Solaris, and Linux® operating systems.
Setting authentication on the server
Authentication is set on the database server within the Database Manager Configuration (DBM CFG) file using the AUTHENTICATION parameter. Remember, the DBM CFG file is an instance-level configuration file. Thus, the AUTHENTICATION parameter affects all databases within the instance. The following commands illustrate how this parameter can be altered.
To view the authentication parameter in the configuration file:
db2 get dbm cfg
To alter the authentication parameter to server_encrypt:
C:\PROGRA~1\SQLLIB\BIN> db2 update dbm cfg using authentication server_encrypt
C:\PROGRA~1\SQLLIB\BIN> db2start
Certain authentication types, like GSSPLUGIN, KERBEROS, and CLIENT require the setting of other Database Manager Configuration parameters such as TRUST_ALLCLNTS, SRV_PLUGIN_MODE, and SRVCON_PW_PLUGIN. More details on these settings below.
Setting authentication on the gateway
Authentication is set on the gateway using the catalog database command. For the examples here, we’ll use a host database named myhostdb.
To set the gateway authentication type to SERVER, you would issue the following command on the gateway machine:
db2 catalog database myhostdb at node nd1 authentication SERVER
db2 terminate
Note that authentication is never performed on the gateway itself. In DB2 Version 8, authentication must always occur at either the client or the host database server.
 Setting authentication on the client
Let’s consider two scenarios on two separate client machines. We’ll configure one to connect to a database on a server machine (DB2 UDB LUW distributed platform), and the other to connect to a database on a host machine (DB2 for zSeries, for example).
  • Client connecting to a server database: The client authentication setting in the database directory entry for the database being connected to must match that of the database server (with the exception of KRB_SERVER_ENCRYPT, DATA_ENCRYPT_CMP, and GSS_SERVER_ENCRYPT).Let’s assume the server authentication type is set to SERVER. The following command would then be issued on the client to catalog the server database named sample:
    db2 catalog database sample at node nd1 authentication SERVER
    If the authentication type is not specified, the client will try to use SERVER_ENCRYPT by default.Client connecting to a host database: Let’s assume that the authentication type on the gateway is set to SERVER. If an authentication type is not specified, SERVER_ENCRYPT authentication is assumed when accessing a database through DB2 Connect. Authentication will take place on the host database server. The following command issued from the client will cause the client to send unencrypted user IDs and passwords to the gateway:
  • db2 catalog database myhostdb at node nd1 authentication SERVER
    Now let’s assume authentication is set to SERVER_ENCRYPT on the gateway. Authentication will once again take place on the host database server. The user ID and password is encrypted on the client before being sent to the gateway, and encrypted on the gateway before being sent to the host machine. This is the default behavior.
Dealing with untrusted clients
If the server or gateway machine has authentication set to CLIENT, this implies that the client is expected to authenticate a user’s ID and password. However, some client machines may not have operating systems with native security features. Such untrustedclients include DB2 clients running on Windows 98® and Windows ME®. DB2 V9.1 does not support Windows 98 or Windows ME, but it does support downlevel clients and so may still have to deal with untrusted V8 clients.
There are two additional parameters in the DBM CFG file used to determine where authentication should take place when the server or gateway authentication method is set to CLIENT and untrusted clients are attempting to connect to the database or attach to the DB2 instance. These are the TRUST_ALLCLNTS and TRUST_CLNTAUTH parameters.
When the server or gateway authentication type is CLIENT, there are two other factors that come into play in addition to the TRUST_ALLCLNTS and TRUST_CLNTAUTH parameters. The first is whether a user ID and password were explicitly supplied and the second is the type of client connecting. The three DB2 clients are:
  • Untrusted clients: As described above
  • Host clients: Clients running on host operating systems like zSeries
  • Trusted clients: Clients running non-host operating systems that have native security features such as Windows NT®, Windows 2000®, Windows 2003®, Windows XP, and all forms of UNIX® and Linux.
When authentication is set to CLIENT
The table below summarizes where authentication will take place when a connect or attach command is issued by each type of client to a server whose authentication type is set to CLIENT.
Table 2. Authentication on connect or attach command
User ID/Password Supplied?TRUST_ALLCLNTSTRUST_CLNTAUTHUntrusted ClientTrusted ClientHost Client
DRDAONLY refers to host clients only, despite the fact that DB2 Version 8 clients connect using DRDA as well.
The examples below illustrate setting authentication types and parameters on the server and client:
Setting authentication on the server:
db2 update dbm cfg using authentication client
db2 update dbm cfg using trust_allclnts yes
db2 update dbm cfg using trust_clntauth server
Setting authentication on the client:
db2 catalog database sample at node nd1 authentication client
In the above example, if the command
db2 connect to sample
is issued from any client, authentication takes place on the client. If the command
db2 connect to sample user test1 using password
is issued from any client, authentication takes place on the server.
DB2 security plugin architecture
DB2 V8.2 introduced the concept of security plugins for DB2. This concept has been further enhanced in DB2 V9.1. Using standard GSS-API calls, a user can write a security plugin and pass the job of authenticating the user ID to an external security program. An example of this is DB2′s own KERBEROS authentication. When you install DB2 ESE, or the application development client on a machine part of that install places sample application code in your instance directory. if you look in thesamples\security\plugins directory you will see in there examples of how to code security plugins. This section will outline the use of plugins in the DB2 security architecture, but does not cover how to code or compile the plugins themselves.  
Kerberos authentication
Kerberos authentication provides DB2 a way to authenticate users without having to flow user IDs or passwords over the network. The Kerberos security protocol performs authentication as a third party authentication service by using conventional cryptography to create a shared secret key. This key becomes a user’s credential and is used to verify the identity of users during all occasions when local or network services are requested. Using the Kerberos security protocol enables the use of a single sign-on to a remote DB2 database server.
First, let’s review the setup of DB2 to use Kerberos authentication. As mentioned above, Kerberos authentication is implemented in DB2 using the plugin architecture. The source code for the default kerberos plugin is provided in thesamples/security/plugins directory, called IBMkrb5.c. Before the Kerberos authentication will work for DB2, Kerberos has to be enabled and supported on both client and server. In order for this to work, the following conditions must be met:
  1. The client and server machines must belong to the same realm (trusted domains in Windows terminology
  2. The appropriate Principals (user IDs in Kerberos) must be set up.
  3. The server’s keytab file must be created and readable by the instance owner.
  4. All machines must have synchronized clocks.
You can find more information on setting up Kerberos in the documentation accompanying the Kerberos product installed.
To enable DB2 to use KERBEROS authentication you must first tell the client where to find the kerberos plugin you are using. On the client, run the following command:
In this example, the default KERBEROS plugin is used. This could have been modified by the DBA to perform special functions if they were required by the Kerberos implementation being used.
There is also the ability to tell the client exactly which server principal it is authenticating against. This option bypasses the first step of Kerberos authentication where the client has to discover the server principal of the instance it is connecting to. The AUTHENTICATION parameter can be specified when cataloging the database on the client. Its format is:
This step is optional.
The next step to set up Kerberos authentication is to set up the server. The srvcon_gssplugin_list. This parameter can be set up with a list of different supported GSS-API plugins, but you are only allowed one Kerberos plugin. If no Kerberos plugin is in the list, the default IBMkrb5 plugin is automatically used. If you intend to allow all authentication (instance attachments as well as database connections) to use Kerberos, then perform the following:
If you only want DB2 to use Kerberos to authenticate incoming database connections (and use SERVER for incoming instance attachments), then perform the following:

Depending on the bit width (32 or 64 bit) of the instance, DB2 will automatically load the IBMkrb5 plugin when the instance is started.
 Other Authentication Settings
If you look in the DBM CFG for a V9.1 instance, you will see various settings that can affect the way that DB2 will authenticate user IDs. As mentioned above, there are settings for standard OS user ID authentication (CLIENT, SERVER, SERVER_ENCRYPT, DATA_ENCRYPT, DATA_ENCRYPT_CMP), as well as plugins for passing authentication to external programs (KERBEROS, KRB_SERVER_ENCRYPT, GSSPLUGIN, GSS_SERVER_ENCRYPT). This section deals specifically with some of the other configuration variables that can have an impact on how a user is authenticated.
Client Userid-Password Plugin          (CLNT_PW_PLUGIN) =
Group Plugin                             (GROUP_PLUGIN) =
GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =
Server Plugin Mode                    (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins      (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin        (SRVCON_PW_PLUGIN) =
Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
Bypass federated authentication            (FED_NOAUTH) = NO

In the above list, the parameters already discussed have been removed.
Table 3. Other parameters
CLNT_PW_PLUGINThis parameter is specified on the client side DBM CFG. It specifies the name of the client plugin used for client and local authentication.
GROUP_PLUGINThe default of this value is blank (NULL). Setting this to the name of a user defined plugin will invoke that plugin for all group enumeration instead of relying on the operating system group lookup. This is tied in to the authorization sections discussed later.
LOCAL_GSSPLUGINThis parameter specifies the name of the default GSS API plug-in library to be used for instance level local authorization when the value of the authentication database manager configuration parameter is set to GSSPLUGIN or GSS_SERVER_ENCRYPT.
SRV_PLUGIN_MODE(YES/NO) The default setting for this parameter is NO. When changed to YES, the GSS-API Plugins used are launched in a FENCED mode, similar to the way that FENCED stored procedures work. A FENCED plugin that crashes can not cause the DB2 instance to crash. While the plugins are being developed, it is recommended to run them in a fenced mode so that logic problems and memory leaks in those plugins will not crash the instance. Once the plugin is determined to be safe, it should be run unfenced for performance reasons.
SRVCON_GSSPLUGIN_LISTA list of plugins that the database manager on the server will use during authentication when either KERBEROS, KRB_SERVER_ENCRYPT, GSSPLUGIN, or GSS_SERVER_ENCRYPT are used. Each plugin in the list should be separated by a comma (‘,’) with no spaces in between. The plugins are listed in order of preference, with the first one in the list being used first to attempt to authenticate the user ID / password sent. Only when all the plugins listed have returned an error will DB2 return an authentication error to the user.
SRVCON_PW_PLUGINThis parameter allows the user to change the default authentication DB2 uses to verify user IDs and passwords when either CLIENT, SERVER, or SERVER_ENCRYPT authentication is specified. By default, its value is NULL and the default DB2 methods are used.
CATALOG_NOAUTH(YES/NO) Default NO. Changing this parameter to YES allows users that are not verified to be members of the SYSADM, SYSCTRL, or SYSMAINT groups to change the Database, Node, Admin and DCS catalogs on the machine. This is only useful in client scenarios where the user logged into the machine is either using an untrusted client (defined above) or are logged on with a user ID that is not allowed to connect to the database or attach to the instance but must catalog entries on the client machine.
FED_NOAUTHWhen fed_noauth is set to yes, authentication is set to server or server_encrypt, and federated is set to yes, then authentication at the instance is bypassed. It is assumed that authentication will happen at the data source. Exercise caution when fed_noauth is set to yes. Authentication is done at neither the client nor at DB2. Any user who knows the SYSADM authentication name can assume SYSADM authority for the federated server.