IBM Db2 credential management

Vault provides credential lifecycle management for a wide range of database systems. This tutorial demonstrates the use of the LDAP secrets engine to manage dynamic and static credentials for access to IBM Db2.

Challenge

Access to Db2 is managed by facilities that reside outside the Db2 database system. By default, user authentication is completed by a security facility that relies on operating system based authentication of users and passwords. This means that the lifecycle of user identities in Db2 aren't capable of being managed using SQL statements, which creates an operational challenge for credential lifecycle management.

To provide flexibility in accommodating authentication needs, Db2 ships with authentication plugin modules for Lightweight Directory Access Protocol (LDAP). This enables the Db2 database manager to authenticate users and obtain group membership defined in an LDAP directory, removing the requirement that users and groups be defined to the operating system.

Central management of Db2 user authentication and group membership in LDAP solves some operational challenges. However, the following credential lifecycle management challenges still exist:

• Generating short-lived credentials
• Revoking credentials after a time-to-live (TTL)
• Renewing credentials to extend the time-to-live (TTL)
• Rotating the passwords for pre-existing credentials

Solution

Using Vault's LDAP secrets engine, we can solve the challenges of credential lifecycle management for Db2 environments that have been configured to delegate user authentication and group membership to an LDAP server. This allows Vault to manage the lifecycle of credentials used to access the Db2 database system.

The solution presented in this tutorial makes the following assumptions:

• Db2 is configured to authenticate users from an LDAP server using the server authentication plugin module
• Db2 is configured to retrieve group membership from an LDAP server using the group lookup plugin module
• The LDAP directory information tree (DIT) has a structure as defined below

The following diagram shows the high-level architecture of the solution presented in this tutorial:

Personas

The solution described in this tutorial involves four personas:

• vault-admin an administrator of Vault with permissions to configure secrets engines
• ldap-admin an administrator of the LDAP server
• db2-admin an administrator of Db2
• client a client of Vault with permissions to read credentials and a client of Db2 that needs access to data

Prerequisites

To perform the tasks described in this tutorial, you need to have:

• A Vault environment of version 1.4 or later
• A Db2 environment of version 9.7 or later
• An LDAP environment of version 3
• If missing Db2 or LDAP environments, you'll need Docker to create the tutorial environment

Policy requirements

For the purpose of this tutorial, you will use root token to work with Vault running in development mode.

When you are working with a non-development Vault environment, your token policy must include the following permissions:

# Mount secrets engines
path "sys/mounts/*" {
  capabilities = [ "create", "read", "update", "delete", "list" ]
}

# To configure the LDAP secrets engine, create roles, and read credentials
path "ldap/*" {
  capabilities = [ "create", "read", "update", "delete", "list" ]
}

If you are not familiar with policies, complete the policies tutorial.

Create environment

Instructions are provided to set up the environment needed for an end-to-end tutorial. If you already have environments for any of these systems, you can skip the steps and modify the appropriate values in subsequent examples. In any case, we recommend reading through each section of this tutorial to understand the various points of integration.

Prepare host environment

Create a temporary location for the Db2 storage directories.

$ export VAULT_DB2_DATA=/tmp/vault-db2-data && \
  mkdir -p ${VAULT_DB2_DATA}

Create a Docker network named learn-vault. This network will allow Db2 to connect to an OpenLDAP server.

$ docker network create learn-vault

Start Vault

(Persona: vault-admin)

In another terminal, start a Vault dev server with root as the root token.

$ vault server -dev -dev-root-token-id root

The Vault dev server defaults to running at 127.0.0.1:8200. The server is initialized and unsealed.

Open another terminal session, and export an environment variable for the vault CLI to address the Vault server.

$ export VAULT_ADDR=http://127.0.0.1:8200

Export an environment variable for the vault CLI to authenticate with the Vault server.

$ export VAULT_TOKEN=root

Start OpenLDAP

(Persona: ldap-admin)

Start an OpenLDAP container using docker run.

$ docker run \
  --rm \
  --detach \
  --name "openldap" \
  --hostname "openldap" \
  --network "learn-vault" \
  --env LDAP_ORGANISATION="example" \
  --env LDAP_DOMAIN="example.com" \
  --env LDAP_ADMIN_PASSWORD="LDAPAdminPassword" \
  -p 389:389 \
  -p 636:636 \
  osixia/openldap:1.5.0

After starting the container, you can verify that it is running.

$ docker ps -f name=openldap --format "table {{.Names}}\t{{.Status}}"
NAMES               STATUS
openldap      Up 9 seconds

Configure OpenLDAP with example data

(Persona: ldap-admin)

Next, you'll need to add some initial configuration to the OpenLDAP directory in order to eventually configure:

• Vault's LDAP secrets engine to manage dynamic and static credentials
• Db2 to read credentials and group membership for authentication

$ cat > /tmp/vault-ldap-db2.ldif <<EOF
# Add organizational units
dn: ou=groups,dc=example,dc=com
objectClass: organizationalUnit
ou: groups

dn: ou=users,dc=example,dc=com
objectClass: organizationalUnit
ou: users

# Add required Db2 groups 
#  - https://www.ibm.com/docs/en/db2/11.5?topic=unix-db2-users-groups
#  - https://www.ibm.com/docs/en/db2/11.5?topic=ins-ldap-based-authentication-group-lookup-support
dn: cn=db2iadm1,ou=groups,dc=example,dc=com
objectClass: groupOfNames
cn: db2iadm1
member: uid=db2inst1,ou=users,dc=example,dc=com
description: DB2 sysadm group

dn: cn=db2fadm1,ou=groups,dc=example,dc=com 
objectClass: groupOfNames
cn: db2fadm1
member: uid=db2fenc1,ou=users,dc=example,dc=com
description: DB2 fenced user group

dn: cn=dev,ou=groups,dc=example,dc=com
objectClass: groupOfNames
cn: dev
member: uid=staticuser,ou=users,dc=example,dc=com
description: Development group

# Add required Db2 users 
#  - https://www.ibm.com/docs/en/db2/11.5?topic=unix-db2-users-groups
#  - https://www.ibm.com/docs/en/db2/11.5?topic=ins-ldap-based-authentication-group-lookup-support
dn: uid=db2inst1,ou=users,dc=example,dc=com
objectClass: inetOrgPerson
cn: db2inst1
sn: db2inst1
uid: db2inst1
userPassword: Db2AdminPassword

dn: uid=db2fenc1,ou=users,dc=example,dc=com
objectClass: inetOrgPerson
cn: db2fenc1
sn: db2fenc1
uid: db2fenc1
userPassword: Db2FencedPassword

# Add user for static role rotation
dn: uid=staticuser,ou=users,dc=example,dc=com
objectClass: inetOrgPerson
cn: staticuser
sn: staticuser
uid: staticuser
userPassword: StaticUserPassword
EOF

Finally, use the ldapadd utility to apply this configuration.

$ ldapadd -x -w "LDAPAdminPassword" -D "cn=admin,dc=example,dc=com" -f /tmp/vault-ldap-db2.ldif

Successful output:

adding new entry "ou=groups,dc=example,dc=com"
adding new entry "ou=users,dc=example,dc=com"
adding new entry "cn=db2iadm1,ou=groups,dc=example,dc=com "
adding new entry "cn=db2fadm1,ou=groups,dc=example,dc=com "
adding new entry "cn=dev,ou=groups,dc=example,dc=com"
adding new entry "uid=db2inst1,ou=users,dc=example,dc=com"
adding new entry "uid=db2fenc1,ou=users,dc=example,dc=com"
adding new entry "uid=staticuser,ou=users,dc=example,dc=com"

Start Db2

(Persona: db2-admin)

Start a Db2 container using docker run.

$ docker run \
    --rm \
    --detach \
    --name "db2" \
    --hostname "db2" \
    --network "learn-vault" \
    --privileged="true" \
    --env LICENSE="accept" \
    --env DB2INSTANCE="db2inst1" \
    --env DB2INST1_PASSWORD="Db2AdminPassword" \
    --env SAMPLEDB="true" \
    -p 50000:50000 \
    -v ${VAULT_DB2_DATA}:/database \
    ibmcom/db2:11.5.7.0

After starting the container, you can verify that it is running.

$ docker ps -f name=db2 --format "table {{.Names}}\t{{.Status}}"
NAMES               STATUS
openldap      Up 9 seconds

After the docker run command is executed, it will take a few minutes for the container to finish setting up. You may run the following command to tail the logs of the Docker entry point script. Check the logs for the message Setup has completed to confirm that the Db2 container is ready.

$ docker logs -f db2
...
(*) All databases are now active.
(*) Setup has completed.

Configure the Db2 LDAP plugin modules

(Persona: db2-admin, ldap-admin)

Next, you need to configure the LDAP plugin modules for Db2 by updating its configuration file to suit your environment. In most cases, you will need to consult with ldap-admin to determine the appropriate configuration values. See the LDAP plugin modules documentation for the location of the configuration file on your system.

For the environment of this tutorial, the configuration file can be found at either:

• ${INSTHOME}/sqllib/cfg/IBMLDAPSecurity.ini.sample from within the Db2 container
• ${VAULT_DB2_DATA}/config/db2inst1/sqllib/cfg/IBMLDAPSecurity.ini.sample from your host filesystem

Make a copy of IBMLDAPSecurity.ini.sample and rename it as IBMLDAPSecurity.ini in the same directory. See that IBMLDAPSecurity.ini has a number of different LDAP-related configuration parameters. The values provided to these parameters will depend on your environment.

Most parameters in IBMLDAPSecurity.ini can be left with defaults for this tutorial. However, the following parameters need to be updated:

After making the appropriate modifications to IBMLDAPSecurity.ini, you'll need to instruct Db2 to use the IBMLDAPauthserver and IBMLDAPgroups plugins by updating its configuration.

First, get an interactive shell in the Db2 container using docker exec.

$ docker exec -it -u db2inst1 db2 bash

Next, instruct Db2 to use the IBMLDAPauthserver and IBMLDAPgroups plugins by updating its configuration.

$ db2 update dbm cfg using SRVCON_PW_PLUGIN IBMLDAPauthserver GROUP_PLUGIN IBMLDAPgroups

Finally, restart Db2 to put the configuration change into effect:

$ db2stop && db2start

Your Db2 instance is now configured to use the LDAP plugin modules to authenticate users and obtain group membership defined in the LDAP directory.

Configure the LDAP secrets engine

(Persona: vault-admin, ldap-admin)

The LDAP secrets engine needs to be configured in order the manage the lifecycle of dynamic and static credentials that'll be used to access Db2.

The first step is to enable the secrets engine.

$ vault secrets enable ldap

Next, write the secrets engine configuration. We'll bind to the LDAP server using the admin user and password that was created when we started the OpenLDAP container.

$ vault write ldap/config \
    binddn="cn=admin,dc=example,dc=com" \
    bindpass="LDAPAdminPassword" \
    url="ldap://127.0.0.1:389"

Connect to Db2 using dynamic credentials

The LDAP secrets engine provides creation of dynamic credentials which are associated with a lease in Vault. In this section, you'll create dynamic credentials and use them to access Db2.

Create a role

(Persona: vault-admin, ldap-admin)

First, create a role in the secrets engine. A role represents how dynamic credentials will be created in the LDAP directory. Roles use LDIF entries as a part of their configuration. Example LDIF entries have been provided for this tutorial. Consult with ldap-admin to determine the appropriate LDIF entries for your environment.

$ cat > /tmp/creation.ldif <<EOF
dn: uid={{.Username}},ou=users,dc=example,dc=com
objectClass: inetOrgPerson
uid: {{.Username}}
cn: {{.Username}}
sn: {{.Username}}
userPassword: {{.Password}}
EOF

$ cat > /tmp/deletion_rollback.ldif <<EOF
dn: uid={{.Username}},ou=users,dc=example,dc=com
changetype: delete
EOF

Create the role using the LDIF entries.

$ vault write ldap/role/dynamic \
     creation_ldif=@/tmp/creation.ldif \
     deletion_ldif=@/tmp/deletion_rollback.ldif \
     rollback_ldif=@/tmp/deletion_rollback.ldif \
     default_ttl=1h

Connect to Db2

(Persona: client)

Next, generate dynamic credentials using the role.

$ vault read ldap/creds/dynamic

Successful output:

Key                    Value
---                    -----
lease_id               ldap/creds/dynamic/doa187ysuFExnvsJwmt8WrNo
lease_duration         1h
lease_renewable        true
distinguished_names    [uid=v_token_dynamic_joctelE9RB_1647220296,ou=users,dc=example,dc=com]
password               3WAOcuHUUt3qMKaUqo14pfTWapiOt8fmcBNoDo7Rx1R9dKxMOMVoMR3MYjCxQvmL
username               v_token_dynamic_joctelE9RB_1647220296

Finally, we can use the dynamic credentials to connect to Db2.

Get an interactive shell in the Db2 container using docker exec.

$ docker exec -it -u db2inst1 db2 bash

Connect to Db2 as the dynamically generated user.

$ db2 connect to sample user <username> using <password>

Where <username> and <password> are the Vault-generated dynamic credentials.

Example:

$ db2 connect to sample user v_token_dynamic_joctelE9RB_1647220296 using 3WAOcuHUUt3qMKaUqo14pfTWapiOt8fmcBNoDo7Rx1R9dKxMOMVoMR3MYjCxQvmL

  Database Connection Information

Database server        = DB2/LINUXX8664 11.5.7.0
SQL authorization ID   = V_TOKEN_...
Local database alias   = SAMPLE

You've successfully connected to a database in Db2 using dynamic credentials managed by Vault's LDAP secrets engine. See the Connecting with an LDAP user ID documentation for additional connection options, including using partial and full LDAP distinguished names (DN).

Connect to Db2 using static credentials

The LDAP secrets engine provides automatic and manual rotation of passwords for pre-existing LDAP entries. In this section, you'll onboard a pre-existing LDAP entry using static roles. This will allow Vault to manage password rotation for credentials that are used to access Db2.

Create a static role

(Persona: vault-admin, ldap-admin)

First, create a static role in the secrets engine. A static role maps a name in Vault to an entry in an LDAP directory. Automatic password rotation can be configured using the rotation_period parameter. Consult with ldap-admin to determine the appropriate values for the distinguished name and username for your environment.

$ vault write ldap/static-role/static \
     username='staticuser' \
     dn='uid=staticuser,ou=users,dc=example,dc=com' \
     rotation_period="1h"

Connect to Db2

(Persona: client)

Next, read the rotated password of the LDAP user that was onboarded to the static role.

$ vault read ldap/static-cred/static

Successful output:

Key                    Value
---                    -----
dn                     uid=staticuser,ou=users,dc=example,dc=com
last_vault_rotation    2022-03-14T11:56:15.252772-07:00
password               VWpUznJ0IcaYbHbnyqwBuJhsfb9YTe5MzwePR9oTkkrs26GhGKZ7dD5HuULpFfri
rotation_period        1h
ttl                    59m55s
username               staticuser

Get an interactive shell in the Db2 container using docker exec.

$ docker exec -it -u db2inst1 db2 bash

Connect to Db2 as staticuser.

$ db2 connect to sample user staticuser using <password>

Where <password> is the Vault-rotated password.

Example:

$ db2 connect to sample user staticuser using VWpUznJ0IcaYbHbnyqwBuJhsfb9YTe5MzwePR9oTkkrs26GhGKZ7dD5HuULpFfri

  Database Connection Information

Database server        = DB2/LINUXX8664 11.5.7.0
SQL authorization ID   = STATICUS...
Local database alias   = SAMPLE

You've successfully connected to a database in Db2 using static credentials managed by Vault's LDAP secrets engine. See the Connecting with an LDAP user ID documentation for additional connection options, including using partial and full LDAP distinguished names (DN).

Authorization from LDAP group membership

As a part of this tutorial, you've configured Db2 to retrieve group membership from an LDAP server using the group lookup plugin module. Groups provide a convenient means of performing authorization for a collection of users in Db2 without having to grant or revoke privileges for each user individually.

In sections below, you'll use group membership from an LDAP server to grant system authorities and privileges to a Db2 user that's managed by Vault.

System authorities

(Persona: db2-admin, ldap-admin)

Group membership can be mapped to system authorities in Db2 to grant authorization. In this section, you'll add staticuser to the DB2IADM1 LDAP group to grant a system authority in Db2.

Get an interactive shell in the Db2 container using docker exec.

$ docker exec -it -u db2inst1 db2 bash

Next, observe the default mapping between system authorities and group names by running:

$ db2 get dbm cfg | grep "group name"

Successful output:

You can see that by default, the SYSADM authority maps to the DB2IADM1 group name. You can exit the interactive shell.

SYSADM group name                        (SYSADM_GROUP) = DB2IADM1
SYSCTRL group name                      (SYSCTRL_GROUP) =
SYSMAINT group name                    (SYSMAINT_GROUP) =
SYSMON group name                        (SYSMON_GROUP) =

To show how LDAP group membership enables granting of system authorities in Db2, add the staticuser to the DB2IADM1 group in the LDAP directory.

$ cat > /tmp/modify-sysadm-group.ldif <<EOF
dn: cn=db2iadm1,ou=groups,dc=example,dc=com
changetype: modify
add: member
member: uid=staticuser,ou=users,dc=example,dc=com
EOF

$ ldapmodify -w "LDAPAdminPassword" -D "cn=admin,dc=example,dc=com" -f /tmp/modify-sysadm-group.ldif

Next, verify that staticuser has been added to the db2iadm1 group by performing an LDAP search. You should see that there are now two members in the group, one of which is staticuser.

$ ldapsearch -w "LDAPAdminPassword" -D "cn=admin,dc=example,dc=com" -b "ou=groups,dc=example,dc=com" "(cn=db2iadm1)"

Successful output:

# db2iadm1, groups, example.com
dn: cn=db2iadm1,ou=groups,dc=example,dc=com
objectClass: groupOfNames
cn: db2iadm1
member: uid=db2inst1,ou=users,dc=example,dc=com
member: uid=staticuser,ou=users,dc=example,dc=com
description: DB2 sysadm group

# search result
search: 2
result: 0 Success

# numResponses: 2
# numEntries: 1

Validation

Get an interactive shell in the Db2 container using docker exec.

$ docker exec -it -u db2inst1 db2 bash

Next, connect to Db2 as db2inst1.

$ db2 connect to sample user db2inst1 using Db2AdminPassword

Finally, run the following Db2 commands to verify that staticuser now has the SYSADM authority. See the AUTH_LIST_AUTHORITIES_FOR_AUTHID documentation for column descriptions.

$ db2 "SELECT AUTHORITY, D_GROUP FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('staticuser', 'U')) AS T ORDER BY AUTHORITY"

Successful output:

AUTHORITY       D_GROUP
--------------- -------
SYSADM          Y
SYSCTRL         N
SYSMAINT        N
SYSMON          N

You've successfully granted staticuser the SYSADM authority using LDAP group membership.

Privileges

(Persona: db2-admin)

Group membership can be used to assign privileges in Db2 to grant authorization. In this section, you'll grant privileges for accessing Db2 data to staticuser based on its LDAP group membership.

Get an interactive shell in the Db2 container using docker exec.

$ docker exec -it -u db2inst1 db2 bash

Next, connect to Db2 as db2inst1.

$ db2 connect to sample user db2inst1 using Db2AdminPassword

Observe the current group membership for staticuser as reported by Db2.

$ db2 "SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('staticuser'))"

Successful output:

You can see that staticuser belongs to the DEV group. This is expected from the LDAP directory created in this tutorial.

GROUP
-----
DEV

Next, grant privileges to access sample data to the DEV group.

$ db2 "GRANT SELECT ON TABLE EMPLOYEE TO GROUP DEV"

Next, connect to Db2 as staticuser which is a member of the DEV group. Replace <password> with the Vault-rotated password.

$ db2 connect to sample user staticuser using <password>

Finally, execute a query to access data granted by the group privilege. Prior to the grant, authorization of the query would not succeed.

$ db2 "SELECT LASTNAME from DB2INST1.EMPLOYEE" LIMIT 5

Successful output:

LASTNAME
---------------
HAAS
THOMPSON
KWAN
GEYER
STERN

You've successfully granted staticuser database privileges using LDAP group membership.

Help and reference

• Db2 LDAP-based authentication and group lookup support
• LDAP secrets engine
• LDAP secrets engine API