Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

DB2 9 Fundamentals exam 730 prep, Part 2: Security

Graham G. Milne (gmilne@ca.ibm.com), I/T Specialist DB2 UDB, IBM Canada
Graham Milne, HBSc. - Computer Science, is a DB2 Certified Advance Technical Expert and has been working with DB2 since 1998. Currently Graham is a Premium Support Manager for DB2 supporting large premium customers. Previous to this, he was the senior advanced service consultant for DB2 support based out of the IBM Toronto Software Lab.

Summary:  This tutorial introduces the concepts of authentication, authorization, and privileges as they relate to DB2® 9. It is the second in a series of seven tutorials designed to help you prepare for the DB2 9 Fundamentals Certification Exam (730). You should have basic knowledge of database concepts and operating system security. This is the second in a series of seven tutorials to help you prepare for the DB2 9 for Linux®, UNIX®, and Windows® Fundamentals exam 730.

View more content in this series

Date:  20 Jul 2006
Level:  Intermediate PDF:  A4 and Letter (505 KB | 32 pages)Get Adobe® Reader®

Comments:  

DB2 authentication

When DB2 authenticates

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 test1 is 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:

DB2 GET 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. See Clients, servers, gateways, and hosts for a refresher.


Table 1. DB2 authentication types
TypeDescription
SERVER Authentication takes place on the server.
SERVER_ENCRYPT Authentication takes place on the server. Passwords are encrypted at the client machine before being sent to the server.
CLIENT Authentication takes place on the client machine (see Dealing with untrusted clients for exceptions).
*KERBEROS Authentication is performed by the Kerberos security software.
*KRB_SERVER_ENCRYPT Authentication is performed by Kerberos security software if the client setting is KERBEROS. Otherwise, SERVER_ENCRYPT is used.
DATA_ENCRYPT Authentication 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_CMP Authentication 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.
GSSPLUGIN Authentication is controlled by an external GSS-API plugin.
GSS_SERVER_ENCRYPT Authentication 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> db2stop
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 untrusted clients 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_ALLCLNTS TRUST_CLNTAUTHUntrusted ClientTrusted ClientHost Client
No Yes CLIENT CLIENT CLIENT CLIENT
No Yes SERVER CLIENT CLIENT CLIENT
No No CLIENT SERVER CLIENT CLIENT
No No SERVER SERVER CLIENT CLIENT
No DRDAONLY CLIENT SERVER SERVER CLIENT
No DRDAONLY SERVER SERVER SERVER CLIENT
Yes Yes CLIENT CLIENT CLIENT CLIENT
Yes Yes SERVER SERVER SERVER SERVER
Yes No CLIENT SERVER CLIENT CLIENT
Yes No SERVER SERVER SERVER SERVER
Yes DRDAONLY CLIENT SERVER SERVER CLIENT
Yes DRDAONLY SERVER SERVER SERVER SERVER

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 
db2stop
db2start
	

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 the samples\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. For a detailed description of how this is done, refer to DB2 UDB Security Part 2: Understand the DB2 Universal Database Security plug-ins.


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 the samples/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:

DB2 UPDATE DBM CFG USING CLNT_KRB_PLUGIN IBMkrb5
DB2 TERMINATE
      

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:

DB2 CATALOG DB dbname AT NODE node name AUTHENTICATION KERBEROS TARGET PRINCIPAL
   service/host@REALM

This step is optional.

DB2 CATALOG DB sample AT NODE testnd AUTHENTICATION KERBEROS TARGET PRINCIPAL
   gmilne/gmilne02.torolab.ibm.com@TOROLAB.IBM.COM
      

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:

DB2 UPDATE DBM CFG USING AUTHENTICATION KERBEROS
 

or

DB2 UPDATE DBM CFG USING AUTHENTICATION KRB_SERVER_ENCRYPT

If you only want DB2 to use Kerberos to authenticate incoming database connections (and use SERVER for incoming instance attachments), then perform the following:

DB2 UPDATE DBM CFG USING SVRCON_AUTH KERBEROS
 

or

DB2 UPDATE DBM CFG USING SVRCON_AUTH KRB_SERVER_ENCRYPT
      

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.

3 of 8 | Previous | Next

Comments



Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=147859
TutorialTitle=DB2 9 Fundamentals exam 730 prep, Part 2: Security
publish-date=07202006
author1-email=gmilne@ca.ibm.com
author1-email-cc=

Tags

Help
Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).

My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).

Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).