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

A technique for protecting data access

Security administrators are responsible for, among other things, protecting a database against unauthorized access or misuse by authorized database users (for example, inappropriate access to sensitive information within a database). A common requirement to mitigate such risks is ensuring that users are allowed to connect to the database only from a list of trusted hosts that are known to be secure. This article gives a practical example of how such a requirement can be put in practice by making use of the trusted context feature of IBM® DB2® for Linux®, UNIX®, and Windows®.

Mihai Iacob (miacob@ca.ibm.com), Software Developer, IBM

Mihai Iacob has been working as a software developer at the IBM Toronto Lab for the past seven years as part of the DB2 for Linux, UNIX, and Windows Security Development team.



Igal Ziskind (ziskind@ca.ibm.com), Software Developer, IBM

Igal Ziskind has been working as a software developer at the IBM Toronto Lab for the past seven years. He is part of the DB2 for Linux, UNIX, and Windows Security Development team. Prior to his current position, he was part of the DB2 Up and Running Development team.



Walid Rjaibi (wrjaibi@ca.ibm.com), Chief Security Architect for DB2 LUW, IBM

Walid Rjaibi is an IBM Senior Technical Staff Member and member of IBM's Security Architecture Board Steering Committee. He is the Chief Security Architect for DB2 for Linux, UNIX, and Windows (LUW) and has direct technical and management oversight over the architecture, design, development, and common criteria certification of all security capabilities in DB2 LUW. This includes identification and authentication, authorization, access control, data masking, auditing, and encryption.

Prior to his current role, Walid was a Research Staff Member at the IBM Zurich Research Lab in Switzerland where he established and led a new research program focused on database security and privacy. Walid's research results were the foundation for key security enhancements in DB2 LUW and for which he led the actual development efforts upon his return to DB2 LUW development in Toronto.

Walid's key database security achievements include leading the research and development of label-based access control, role-based access control, trusted contexts, separation of duties, and fine-grained access control. His work resulted in 20 patents and several publications in the proceedings of leading scientific conferences, such as the International Conference on Very Large Databases (VLDB), the International Conference on Data Engineering (ICDE), and the International Conference on Security and Cryptography (SECRYPT).



09 February 2012

Also available in Chinese

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

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
Figure 3. From 9.26.120.62 hotel46
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
Figure 5. From 9.26.120.62 hotel46
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

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
Figure 8. From 9.26.120.64 hotel48
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.

Resources

Learn

Get products and technologies

  • 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.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • 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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

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

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



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.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

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

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


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