Contents


Restricting database connections using trusted contexts in DB2 for Linux, UNIX, and Windows

A technique for protecting data access

Comments

Problem description

The security administrator has a requirement to allow an end user to connect to the database only from a specific set of IP addresses. This article shows how database roles and trusted contexts can be used to address this. For illustration purposes, we will assume that the security administrator would like to ensure that user Einstein can connect to the database only from IP address 9.26.120.62.

Database roles

A role is a database object that groups together one or more privileges and can be assigned to users, groups, special group PUBLIC, other roles, or a trusted context. Roles simplify the administration and management of privileges by allowing the security administrator to group authorities and privileges in a role and grant this role to the users that need those authorities and privileges to perform their jobs. Also because roles reside within the DB2 database system, they are not subject to the same restrictions as groups.

All DB2 privileges and authorities that can be granted within a database can be granted to a role. For example, a role can be granted CONNECT authority and SELECT privilege on some table.

Trusted contexts

A trusted context is a database object that defines a trust relationship for a connection between the database and an external entity, such as an application server.

The trust relationship is based upon the following set of attributes:

  • System authorization ID — Represents the user that establishes a database connection
  • IP address (or domain name) — Represents the host from which a database connection is established
  • Data stream encryption — Represents the encryption setting (if any) for the data communication between the database server and the database client

When a user establishes a database connection, the DB2 database system checks whether the connection matches the definition of a trusted context object in the database. When a match occurs, the database connection is said to be trusted.

One capability trusted contexts provide is the ability for the user of that trusted context to inherit a database role. For example, a security administrator could choose to grant SELECT privilege on the payroll table to a role and make that role available only through a trusted context. That is to say that users will not be able to take advantage of the role (and, consequently, the SELECT privilege on the payroll table) when operating outside the scope of that trusted context.

Solution description

Prior to DB2 9.7 FixPak 3, roles inherited through trusted contexts were not taken into account when checking for CONNECT authority at database connection time. This restriction is being removed in DB2 9.7 FP3. One immediate application of this enhancement is the ability to restrict where an end user might connect to the database from.

Example

We have regular user Einstein that we want to allow to connect to the T1 database on 9.26.120.64 (hotel48) only from IP 9.26.120.62 (hotel46).

Step 1: Create the T1 database on 9.26.120.64 (hotel48)

db2start
db2 create database T1

Verify that the database is created: db2 list db directory.

Figure 1. System database directory
Image shows system database directory shows database exists
Image shows system database directory shows database exists

Step 2: Configure the TCPIP communication protocol

On server side 9.26.120.64 hotel48, run the following:

Listing 1. Configuring communication and configuration for TCPIP
db2set DB2COMM=TCPIP
db2 update dbm cfg using SVCENAME xziskind
db2stop 
db2start

On client side 9.26.120.62 hotel46, run:

Listing 2. Cataloging the TCPIP node and database
db2 catalog tcpip node NT1 remote hotel48 server xziskind
db2 terminate
db2 catalog database T1 at node NT1
db2 terminate

Verify that user Einstein can connect to the T1 database.

Figure 2. From 9.26.120.64 hotel48
Image shows database connection information to database alias T1
Image shows database connection information to database alias T1
Figure 3. From 9.26.120.62 hotel46
Image shows connection information from second IP address
Image shows connection information from second IP address

User Einstein can connect to the T1 database from both IP addresses.

Let's verify Einstein's authorities. For this purpose, we will make use of the AUTH_LIST_AUTHORITIES_FOR_AUTHID table function. This returns all authorities held by the authorization ID, found in the database configuration file or granted to an authorization ID directly or indirectly through a group or a role.

Listing 3. Authorities held by Einstein
db2 SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC,
           ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE 
   FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('EINSTEIN', 'U') ) AS T 
   ORDER BY AUTHORITY

AUTHORITY                 D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL                N      N       N        N         N          N           *     
BINDADD                   N      N       Y        N         N          N           *     
CONNECT                   N      N       Y        N         N          N           *     
CREATETAB                 N      N       Y        N         N          N           *     
CREATE_EXTERNAL_ROUTINE   N      N       N        N         N          N           *     
CREATE_NOT_FENCED_ROUTINE N      N       N        N         N          N           *     
DATAACCESS                N      N       N        N         N          N           *     
DBADM                     N      N       N        N         N          N           *     
EXPLAIN                   N      N       N        N         N          N           *     
IMPLICIT_SCHEMA           N      N       Y        N         N          N           *     
LOAD                      N      N       N        N         N          N           *     
QUIESCE_CONNECT           N      N       N        N         N          N           *     
SECADM                    N      N       N        N         N          N           *     
SQLADM                    N      N       N        N         N          N           *     
SYSADM                    *      N       *        *         *          *           *     
SYSCTRL                   *      N       *        *         *          *           *     
SYSMAINT                  *      N       *        *         *          *           *     
SYSMON                    *      N       *        *         *          *           *     
WLMADM                    N      N       N        N         N          N           *     

  19 record(s) selected.

User Einstein has CONNECT authority through the special group PUBLIC.

Step 3: Revoke CONNECT authority from PUBLIC

db2 revoke connect on database from public

Verify Einstein's authorities again:

Listing 4. Einstein's authorities after revoking CONNECT from PUBLIC
db2 SELECT AUTHORITY, D_USER, D_GROUP, D_PUBLIC,
       ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE 
   FROM TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('EINSTEIN', 'U') ) AS T 
   ORDER BY AUTHORITY

AUTHORITY                 D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL                N      N       N        N         N          N           *     
BINDADD                   N      N       Y        N         N          N           *     
CONNECT                   N      N       N        N         N          N           *     
CREATETAB                 N      N       Y        N         N          N           *     
CREATE_EXTERNAL_ROUTINE   N      N       N        N         N          N           *     
CREATE_NOT_FENCED_ROUTINE N      N       N        N         N          N           *     
DATAACCESS                N      N       N        N         N          N           *     
DBADM                     N      N       N        N         N          N           *     
EXPLAIN                   N      N       N        N         N          N           *     
IMPLICIT_SCHEMA           N      N       Y        N         N          N           *     
LOAD                      N      N       N        N         N          N           *     
QUIESCE_CONNECT           N      N       N        N         N          N           *     
SECADM                    N      N       N        N         N          N           *     
SQLADM                    N      N       N        N         N          N           *     
SYSADM                    *      N       *        *         *          *           *     
SYSCTRL                   *      N       *        *         *          *           *     
SYSMAINT                  *      N       *        *         *          *           *     
SYSMON                    *      N       *        *         *          *           *     
WLMADM                    N      N       N        N         N          N           *     

  19 record(s) selected.

User Einstein no longer has CONNECT authority from any source.

Now try to connect again.

Figure 4. From 9.26.120.64 hotel48
Image shows EINSTEIN does not have connect privilege
Image shows EINSTEIN does not have connect privilege
Figure 5. From 9.26.120.62 hotel46
Image shows EINSTEIN does not have connect privilege
Image shows EINSTEIN does not have connect privilege

User Einstein can no longer connect from any IP address.

Step 4: Create the role and the trusted context

The following commands are run as a user with SECADM authority:

  1. Create the role: db2 create role connect_role
  2. Grant CONNECT authority to the role: db2 grant CONNECT on database to role connect_role
  3. Create the trusted context:
    db2 create trusted context connect_tc 
        based upon connection using system authid
        einstein attributes(address '9.26.120.62') 
        default role connect_role enable
Figure 6. Creating the role, connecting, and creating trusted context
Image shows SQL commands created successfully
Image shows SQL commands created successfully

Step 5: Test

Verify that user Einstein can connect from IP 9.26.120.62 (hotel46) but cannot from IP 9.26.120.64 (hotel48).

Figure 7. From 9.26.120.62 hotel46
Image shows successful connection
Image shows successful connection
Figure 8. From 9.26.120.64 hotel48
Image shows unsuccessful because does not have connect privilege
Image shows unsuccessful because does not have connect privilege

Summary

By combining the capabilities of trusted contexts, roles, and CONNECT authority, we can control where a regular user connects to the database from. Note that this solution does not apply to superusers SYSADM, SYSCTRL, SYSMAINT, SYSMON, DBADM, and SECADM. Those users have implicit CONNECT authority.


Downloadable resources


Related topics

  • Learn more about database roles in DB2 for Linux, UNIX, and Windows.
  • Get more information about Trusted contexts and trusted connections in DB2.
  • Get the details about the AUTH_LIST_AUTHORITIES_FOR_AUTHID table function.
  • Get an Authorities overview for DB2 for Linux, UNIX, and Windows.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=792049
ArticleTitle=Restricting database connections using trusted contexts in DB2 for Linux, UNIX, and Windows
publish-date=02092012