Implement DB2 for Linux, UNIX, and Windows trusted contexts and roles in a Web application

One IBM Business Partner's experiences with new security options in DB2 9.5

Explore how a Web application makes use of trusted contexts, roles, and new auditing capabilities in IBM® DB2® 9.5 for Linux®, UNIX®, and Windows®. Get the "before-and-after" story of how these features were implemented at S1 Corporation.

Share:

Matt Eakle (matt.eakle@s1.com), Senior Data Architect, S1 Corporation

Author Photo: Matt EakleMatt Eakle is the senior data architect and DBA for the Java-based S1 Enterprise Banking products at S1 Corporation, a position he's held since 2000. He is responsible for overseeing product database design, and supports performance testing. Prior to that, Matt was database designer for the Marketing Database team at Blue Cross Blue Shield of Virginia, and earlier, the lead developer and database designer for the BCBS of VA Telemarketing Sales and Lead tracking system, now in use by several Blue plans.



Ted J. Wasserman (tedwas.ibm@gmail.com), Database Consultant, IBM 

Ted J. Wasserman's photoTed J. Wasserman is a database consultant at the IBM Silicon Valley Laboratory in San Jose, California. Ted works on the DB2 Business Partner Technical Enablement team, where he specializes in helping IBM Business Partners migrate their applications and databases to DB2. Ted has a master's degree in computer science, as well as a bachelor's degree in computer science from Queen's University in Kingston, Ontario, Canada.



20 March 2008

Introduction

Prior to DB2 9.5, multi-tiered Web applications often used a single user ID to connect to a database from the middle tier. While easy to setup and maintain, this model was not ideal, specifically due to diminished user accountability and the overgranting of privileges to the user ID. This is the model employed by S1 Enterprise, a division of S1 Corporation (Nasdaq: SONE) and a leading provider of multichannel financial services software, to connect to DB2 Universal Database (DB2 UDB) V8.1 for Linux, UNIX, and Windows, so when DB2 9.5 offered new secure connection options, S1 Enterprise was eager to test them.

DB2 9.5 introduces several new security features that should be of great interest to independent software vendors (ISVs) and business partners. If you've worked with other database management systems (DBMSs), you may already be familiar with database roles, which provide greater control over privileges than previously allowed by OS-level groups. In addition, DB2 9.5 supports a new object called a trusted context. Once connected through an explicit trusted connection defined by the trusted context, the database server permits context changes over the connection, such as switching the user ID or changing privileges through role inheritance.

This article first explains how S1 Enterprise's Banking product connects to the database under DB2 UDB V8.1. It then describes how explicit trusted connections (and roles) are used in DB2 9.5 to increase application security and ease administration.

The goals of the article are to:

  • Explore the concepts and value behind both trusted contexts and roles. What voids do they fill?
  • Learn how to create and use both types of objects, especially the way roles are leveraged by trusted contexts. Present the syntax of commands used to manipulate both new objects.
  • Demonstrate how the S1 Enterprise application connected to the database prior to the introduction of trusted contexts, and the code changes required to connect from the application server to the database using trusted contexts.
  • Present examples of the new capabilities when connecting through trusted contexts, including the ability to change users (and toles) on-the-fly.
  • Show how the trace logging generated by the S1 Enterprise application can be enhanced by including more detailed user information (more is better when troubleshooting).

About S1 Corporation

S1 Corporation is comprised of three major divisions: S1 Enterprise, Postilion, and FSB Solutions. S1 employs more than 1,400 people in operations throughout the North America, Europe, Middle East, Africa, and Asia-Pacific regions. Worldwide, more than 3,000 banks use S1 software solutions. In 2006, one trillion dollars passed through the S1 Data Center, the company's hosting center.

More than 100 banks and 3 million consumer, small business, and corporate users worldwide rely on S1 Enterprise solutions to access and manage their financial information through the branch, call center, Internet, or mobile devices. Top banks worldwide deploy S1 Enterprise solutions, including six of the top 10 U.S. and three of the top six Canadian banks that have implemented S1 Enterprise online products and the largest banking call center in the world that utilizes S1 Enterprise branch products.

The S1 Enterprise Banking product is based on a multi-tier architecture (see Figure 1, below). The presentation layer includes: Web, Web Phone, PDA, OFX, CSV, and print, and is extensible by third parties using layer-specific frameworks, providing the foundation for multi-channel delivery. Business objects are reused across multiple applications, defined in a published framework, for building modules and applications, and defined Application Model and UI Framework. Numerous services are shared by all enterprise applications, including authentication, authorization, entitlements, content management, and workflow. Data access is provided through consistent object-relational mapping to the S1 Enterprise Core Data Model, permitting flexible data model extension, and database independence. The application server is J2EE-compliant. It provides a comprehensive enterprise solution to financial institutions, the S1 Enterprise products necessarily provide proven secure platform and applications with business functionality that is compliant with regulatory policies.

Figure 1. S1 Enterprise multi-tier architecture
The S1 Enterprise multi-tier environment

Even though the application is database-agnostic and has been ported to numerous OS/DBMS platforms, the reference platform for the S1 Enterprise suite of products is AIX, Websphere, and DB2.

Now examine how the S1 Enterprise products connect to the database under DB2 UDB V8.1, examine some new features and objects available in DB2 9.5, and see specific implementation details required as S1 tested the ability to leverage the new security and audit features introduced in DB2 9.5.


How S1 managed database connections prior to DB2 9.5

The S1 security architecture principals are built around configurability, putting the security and performance trade-off in the hands of the customer. The products adhere to all applicable legal requirements, standards, and recommendations, such as ISO, CERT, Gramm-Leach-Bliley, HIPAA, and FFIEC.

In the Data Center, servers are locked and stripped down for minimum communication and functionality capability. Routers are locked down with access control lists, and switches that support high availability and redundancy, to mitigate denial of service attacks. Firewalls and DMZs are in place to prevent direct access to servers from the outside, and the network is continuously monitored using intrusion detection systems. As far as user login authentication is concerned, password restrictions are configurable and extendable, and passwords are held in the database using one-way encryption. Today, the physical connection to the database is done by Websphere. At connection time, a JNDI lookup uses Websphere facilities and returns a datasource object, which is then asked to return a connection object. Websphere pools several physical connections and returns one when the application asks the datasource for a connection. The implementations may make use of a server, a flat file, or a database. The password is secured using Cloak, a security package that lets you decide how much — or how little — security you need to engage.

Listing 1. Java code to cache the datasource
result = dataSourceCache.get(name_);
    if (result == null) {
        result = jndiLookup(name_, args_, ctx_);
    if (result != null) {
        dataSourceCache.put(name_, result);
    }
}

The S1 Enterprise applications also employ challenge questions, authentication tokens, and multi-factor authentication (MFA). MFA is the use of a customer selected image and phrase to validate the bank's site. The customer's main access point is "bound" to the financial Institution through this unique identifier. For "unbinded" location access, response to challenge questions can be required.

On the database side, there are two primary user IDs with different privileges. The primary application user has SELECT, INSERT, UPDATE, and DELETE privileges on most tables. The reporting user has only SELECT privileges on most tables. The current model of granting privileges at the user level is not particularly scalable. As the need arises to add new user IDs, the maintenance grows exponentially, with increased security risk.


S1 security survey

Prior to this article, an internal survey was conducted to see what feedback front-line technical personnel have been receiving from customers. A few excerpts from the responses are summarized below.

  1. What general comments and/or concerns related to data security have been raised to you by customers (not necessarily related to S1 products)?

    Prospects are very concerned with the physical security of the data, which entails everything from encryption of the data from the end user, across the Internet, and through the Enterprise platform. Prospects are demanding a consolidated reporting capability. They want the ability to not only monitor security in real-time from the S1 application's point of view, they also want to be able to report on the various security aspects. A set of reports tracking activities that an end user, or group of end users, have performed is highly desirable. The ability to see attempts to log in that were denied would be excellent from an operational point of view.
  2. What general positive feedback have you received about the security of data in S1 Enterprise applications?

    In the beginning, prospects need convincing that we have the security policies and safeguards in place. So, you can view our approach to alleviating their concerns as a layered approach. First, pre-sales (meaning solutions consultants) can go through the standard "here is how we approach security and here is what we have implemented." Second, we conduct a conference call meeting where the real security expert dives down to the details of our security. Third, we invite the prospects to a data center visit where even more details are discussed and the previous points are confirmed and reinforced. When we get a prospect into the data center and tour, they walk away feeling very confident that we know what we are doing. Remember, S1 (meaning SFNB, Five Paces) was really an offshoot of SecureWare. Our heritage and security background includes building "Trusted Operating Systems" for the government and the development of security related protocols.

The survey confirms that S1 prospects and customers respect what is in place, but at the same time recognizes the critical importance of S1 never letting its guard down. Their customers demand it of them, and so they of us. S1 must stay current with technology, and leverage the latest tools, especially from our reference DBMS.


DB2 9.5 introduces trusted contexts, roles, and enhanced auditing

Trusted contexts and connections were introduced in DB2 9.5 as part of a set of new features aimed to improve overall security and auditability in multi-tier environments. Prior to their introduction, there were several limitations when working in these types of environments; namely weakened security and the loss of user identity. In multi-tier environments, one of the middle tiers, such as the Web or application server, authenticates application users and manages the interactions with other resources, such as a data server. For convenience and ease of administration, a typical middle tier is set up to use a single user ID to connect to the data server on behalf of the application. The implication of this is that the ID must be granted a slew of privileges and authorities, since it is being used to perform work on behalf of all users. Imagine if the ID was ever compromised; a malicious user would have the ability to do a lot of damage. Additionally, it is very difficult to trace activity back to a specific application user from the data server's perspective, since all incoming connections use the same user ID.

Enter trusted contexts and connections. The idea behind them is to facilitate a type of "user-switching" or privilege inheritance over existing, secure connections. The alternate approach of maintaining an ID for each user and constantly establishing and severing connections based on the type of request coming in has too many administrative and performance implications. With trusted contexts and connections, a middle tier can connect to the data server using a single user ID and allow the user of that connection to inherit additional privileges if they are connecting from a secure place.

Trusted contexts

In order to establish a trusted connection, a new database object called a trusted context is required. A trusted context is a simple database object that specifies the criteria that must be met for a connection to be considered secure. Once the data server identifies the connection as being secure, the ability to inherit additional privileges is possible.

Listing 2. Syntax diagram for creating trusted contexts
>>-CREATE TRUSTED CONTEXT--context-name------------------------->

>--BASED UPON CONNECTION USING---------------------------------->

>--SYSTEM AUTHID--authorization-name--?------------------------->

                  .-,-------------------------------------------------------------------.
                  V                                                                     |
>--ATTRIBUTES--(----------------+-ADDRESS--address-value--+--------------+-+-+--)--?->
                                |                 '-WITH ENCRYPTION--encryption-value-' |
                                |                                                       |
                                '-------ENCRYPTION--encryption-value--------------------'

   .-NO DEFAULT ROLE---------.     .-DISABLE-.
>--+-------------------------+--?--+---------+--?--------------->
   '-DEFAULT ROLE--role-name-'     '-ENABLE--'

>--+-------------------------------------------------------------------------+-->
   |               .-,------------------------------------------------------------+-+ |
   |               V                                      .-WITHOUT AUTHENTICATION-. ||
   '-WITH USE FOR----+-auth-name--+-----------------+-+--+------------------------+-+-'
                     |             '-ROLE--role-name-' |  '-WITH AUTHENTICATION----'
                     '-PUBLIC----------------------------------'                
>--?-----------------------------------------------------------><

The syntax is straightforward. To create a trusted context, you provide:

  • Name of the trusted context
  • Authorization name that is to be specified in the connect statement
  • The allowed IP address(es) of incoming connections
  • The encryption level used to encrypt the data communication between the database server and the user
  • Whether a default role is to be associated with the trusted connection
  • Which users can use the trusted connection
  • Whether switching to a different user on a trusted connection requires authentication

For example, Listing 3 shows the definition of a trusted context called WEBAPPSERVER.

Listing 3. A definition of a sample trusted context
CREATE TRUSTED CONTEXT webappserver
     BASED UPON CONNECTION USING SYSTEM AUTHID appUser
     DEFAULT ROLE appUser_role
     ENABLE
     ATTRIBUTES (ADDRESS '192.168.1.150',
                 ADDRESS '192.168.1.151' WITH ENCRYPTION 'LOW')
     WITH USE FOR 
       TED WITHOUT AUTHENTICATION
       MATT WITH AUTHENTICATION

In this example, a trusted context is created in which connections made by user appUser from address 192.168.1.150 are trusted when no encryption is used, while connections made by user appUser from address 192.168.1.151 require a LOW level of encryption in order to be trusted. Encryption relates to the user authentication method used as well as if SSL is being used. The user of a trusted connection based on this trusted context can be switched to two different user IDs. When the current user of the connection is switched to user ID TED, authentication is not required. However, authentication is required when it is switched to user ID MATT. The trusted context also has a default role called appUser_role associated with it, which implies that users working within the confines of this trusted context inherit the privileges associated with this role. The trusted context is also created in the enabled state, which is not the default.

Implicit versus explicit trusted connections

Once a trusted context is defined and enabled, users can initiate a trusted connection explicitly or implicitly. An implicit trusted connection is one that is not explicitly requested. The implicit trusted connection results from a normal connection request that meets the requirements of an enabled trusted context. With an implicit trusted connection, the requestor can only acquire additional privileges through the role inheritance feature; they cannot switch the user ID. An implicit trusted connection requires no code changes. On the other hand, an explicit trusted connection is just that — it is explicitly requested in the application code. The initiator of an explicit trusted connection has the ability to switch the current user ID on the connection to a different user ID with or without authentication as well as acquire additional privileges through the role inheritance feature. Roles may be inherited through the default role for the trusted context, or the role defined for the user. If an implicit trusted connection cannot be established, a normal connection is attempted. When an explicit trusted connection is requested, but the qualifications are not met, a regular connection is established, but a warning message is returned to the client (SQL code 20360).

For more details about requesting explicit trusted connections in CLI, XA, and JDBC applications, refer to "Use trusted context in DB2 client applications" (developerWorks, Sept. 2006).

Roles

Roles are also new database objects, beginning in DB2 9.5. You may be familiar with the concept, as many other DBMSs have them. Roles are essentially groups, but they are defined in the database, rather than a third party mechanism. Roles have the same capability as groups, but without the same restrictions. The privileges and authorities granted to roles are always used when you create views, Triggers, materialized query tables (MQTs), static SQL and SQL routines, whereas privileges and authorities granted to groups (directly or indirectly) are not used. This is because the DB2 database system cannot determine when membership in a group changes, as the group is managed externally (for example, by the operating system or an LDAP directory). Because roles are managed inside the database, the DB2 database system can determine when authorization changes and act accordingly. Roles granted to groups are not considered, for the same reason that groups are not considered.

The syntax diagram to create a role is quite simple, as shown in Listing 4:

Listing 4. The syntax diagram for creating roles
>>-CREATE ROLE--role-name--------------------------------------><

The syntax used to grant the role to other users, is shown in Listing 5:

Listing 5. The GRANT syntax diagram for roles
                    .-,---------.        
          .-ROLE-.  V           |    
>>-GRANT--+------+----role-name-+------------------------------->
       .-,---------------------------------.   
       V                                   |    
>--TO----+-+-------+--authorization-name-+-+-------------------->
         | +-USER--+                     |
         | +-GROUP-+                     |
         | '-ROLE--'                     |
         '-PUBLIC------------------------'
>--+-------------------+---------------------------------------><
   '-WITH ADMIN OPTION-'

After creating a role, you grant privileges to the role just like you would to a user or group. When you grant the role to a user (or group), that user inherits the privileges granted to the role. Like with groups, changes to the privileges associated with a role are inherited by all users that have been granted the role. Unlike groups, roles are DB2 objects, so DB2 can determine when changes are made.

The value of trusted contexts is that you can change roles on the fly once you've authenticated on a trusted connection. This means you don't have to open a new connection to support a user with a different privilege set. Trusted contexts can, optionally, define a default role (privilege set) for a given trusted connection. Roles serve essentially the same purposes as groups in terms of privileges. However, since groups are external objects, and outside of DB2's control, trusted contexts support roles, but not groups. For that reason, trusted contexts and roles go hand in hand, and you'll see how trusted contexts support role inheritance.

SECADM authority

SECADM is a very powerful authority level introduced in DB2 9.1. It isn't covered in detail in this article, but be aware that trusted contexts and roles can only be managed by a user with SECADM authority. SECADM can only be granted by SYSADM, and only to a user (not a group). SECADM also manages security labels, policies, and Label-Based Access Control (LBAC). For more information about LBAC, refer to the DB2 9.5 Information Center, or Rogers Sanders' articles in DB2 Magazine (see the Resources section of this article).


S1 Enterprise implements the new objects

In the interest of keeping pace with new DB2 features, and continuing to provide customers with the latest security technology, S1 Enterprise undertook a test of three new components of DB2 9.5 for Linux, UNIX, and Windows. The existing database connection strategy would be replaced with a model built around trusted contexts and roles, and monitored by the Audit facility features introduced in DB2 9.5.

Implementation of explicit trusted contexts requires code changes in addition to the DB2 DDL that creates the new objects. The following sections look at the database changes, the application code modifications, and visit some of the audit entries generated during the tests.


Database changes

In the following DDL segments, you'll see two primary DB2 users. OS user db2sys has SYSADM and DBADM authority, and sets the schema to db2schem to do his work. db2sys, as SYSADM, grants SECADM authority to OS user db2sec. This is shown in Listing 6.

Listing 6. Granting SECADM authority
CONNECT TO pltdb USER db2sys USING db2syspwd;
SET SCHEMA db2schem;
GRANT secadm ON DATABASE TO db2sec;

The DBADM user creates and populates the tables. At this point, synonyms would typically have been created, and privileges granted for each table, as shown in Listing 7.

Listing 7. Creating tables and granting privileges
CREATE TABLE AddressInfo (...);
CREATE SYNONYM appUser.AddressInfo FOR AddressInfo;
GRANT SELECT, INSERT, UPDATE, DELETE ON AddressInfo TO appUser;

Additional synonyms would have been created for the reporting user rptUser, and granted individual privileges. Instead, for this test, after creating the synonyms, two roles are created by SECADM, roles1 and role2, as shown in Listing 8.

Listing 8. Creating roles
CONNECT TO pltdb USER db2sec USING db2secpwd;
SET SCHEMA db2schem;

CREATE ROLE role1;
CREATE ROLE role2;

Then, table privileges are granted to the roles, as shown in Listing 9. For simplicity, half were granted to role1 and the other half to role2. Privileges are granted to a role just they would be to a user or group. A user with DBADM authority can grant privileges to roles.

Listing 9. Granting privileges to Roles
CONNECT TO PLTDB USER db2sys USING db2syspwd;
SET SCHEMA db2schem;

-- Privileges on all tables whose names begin with a
-- letter < 'T' to role1
GRANT SELECT, INSERT, UPDATE, DELETE ON AddressInfo TO ROLE role1;
... [more tables]

-- Privileges on all tables whose names begin with a
-- letter >= 'T' to role2
GRANT SELECT, INSERT, UPDATE, DELETE ON TCORContPtType TO ROLE role2;
... [more tables]

Now that the roles have the desired privileges, the roles are granted to the users, as shown in Listing 10. This requires SECADM authority.

Listing 10. Granting privileges to roles
CONNECT TO PLTDB USER db2sec USING db2secpwd;
SET SCHEMA db2schem;

-- Grant only role1 to appUser.
GRANT ROLE role1 TO USER appUser;

-- Grant only role2 to rptUser.
GRANT ROLE role2 TO USER rptUser;

Next, a simple trusted context is defined, as shown in Listing 11.

Listing 11. Granting privileges to roles
CREATE TRUSTED CONTEXT context1TC
  BASED UPON CONNECTION USING SYSTEM AUTHID appAuthId
  ENABLE
  ATTRIBUTES (ADDRESS 'bshenker-laptop.S1AD.s1.com')
  WITH USE FOR appUser WITH AUTHENTICATION,
               rptUser WITH AUTHENTICATION

Previously, connections to the database would have been made by appUser or rptUser, depending on the privilege set desired. However, for purposes of this test, a new OS user, called appAuthId, was created. To meet the requirements of this trusted context definition and establish a trusted connection, only the appAuthId user can connect. In addition, the trusted context is defined as enabled by default, and only one source machine address is specified — the application server. Once connected, the authorization ID associated with the connection may be switched to either appUser or rptUser, but authentication is required in either case.


Application code changes

With the database changes in place, one of the S1 Enterprise platform architects was engaged. He first created a method to make the initial connection, as shown in Listing 12.

Listing 12. A method to make the initial database connection
private void makeInitialConnection(Properties ctx) throws SQLException
{
    Object[] objects = new Object[6];
    
    // Construct a data source
    DB2ConnectionPoolDataSource ds1 = 
             new DB2ConnectionPoolDataSource();
    Properties properties = new java.util.Properties();
    
    // Set data source properties from Grnds configuration
    ds1.setServerName(getProperty("serverName", ctx));
    ds1.setPortNumber(Integer.valueOf(getProperty("portNumber", ctx)).intValue());
    ds1.setDatabaseName(getProperty("databaseName", ctx));
    ds1.setDriverType (4);
    String authid = getProperty(TRUSTED_CONTEXT_AUTHUSER, ctx);
    String password = getProperty(TRUSTED_CONTEXT_AUTHPSWD, ctx);
    
    // This makes the real connection and returns information in array of objects
    objects = ds1.getDB2TrustedPooledConnection(authid, password, properties); 
    _trustedConnection  = (DB2PooledConnection)objects[0];    
    _connectionCookie = (byte[])objects[1];  
}

The makeInitialConnection method returns the connection object and a cookie that is represented as an array of bytes, both used in making subsequent connections. Next, the user ID and password (which would be encrypted in real life), are passed in to make the actual connection, as shown in Listing 13.

Listing 13. A method to establish a trusted connection
/**
* Use the trusted context to get a connect for a specific authorization * id
* @param args map with userid and password for connection set
*/
    private Connection getRequestedConnection(Map args) 
        throws SQLException
    {
        Connection thisConnection = null;
        String currentUser = (String) args.get(TRUSTED_CONTEXT_USER);
        String currentPswd = (String) args.get(TRUSTED_CONTEXT_PSWD);
        // if no user get Default connection, this will be the user that made the initial 
        // (trusted) connection
        if (currentUser == null)
        {
            thisConnection = _trustedConnection.getConnection();
        }
        else
        {
            thisConnection = _trustedConnection.getDB2Connection(_connectionCookie, 
                currentUser, currentPswd, null, null, null, new java.util.Properties());
        }
        return thisConnection;

Since all attributes of the defined trusted context context1TC have been met (meaning appAuthId successfully authenticated and the machine name originating the connection was bshenker-laptop.S1AD.s1.com), an explicit trusted connection is established. If the same user had attempted a regular connection from the same machine (without using the new trusted connection code), an implicit trusted connection would have been established.

A series of test cases was devised, designed to return an error whenever an SQL0551N error (an error resulting from an authorization ID attempting to perform an operation without the proper authorization) was encountered. In the first case, shown in Listing 14, an attempt to issue a SELECT against two tables, TCORContPtType and AddressInfo, is made immediately after connecting as appAuthId. Recall that appAuthId has not been granted any roles or individual privileges. Any SELECTs performed by appAuthId will return an SQL0551N error.

Listing 14. Test case 1 — Issuing a SELECT against tables where the authorization ID does not have the proper privileges
/**
* Just connection with appAuthId, should fail with authorization
* failure
*/
public void testAuthid()
{
    boolean exceptionGot = false;
    try
    {
        PersistenceService.get(TCORContPtType.class.getName());
    }
    catch (PersistenceException e)
    {
        String error = e.getMessage();
        if (error.indexOf("-551")> -1)
        {
            exceptionGot = true;
        }
    }
    assertTrue("should have failed with authorization exception", exceptionGot);

    exceptionGot = false;
    try
    {
    PersistenceService.get(AddressInfo.class.getName());
    }
    catch (PersistenceException e)
    {
        String error = e.getMessage();
        if (error.indexOf("-551")> -1)
        {
            exceptionGot = true;
        }
    }
    assertTrue("should have failed with authorization exception for AddressInfo", 
                    exceptionGot);
}

In test case 2, the authorization associated with the trusted connection is switched to appUser. The important thing to remember here is that this is done on the fly, under the umbrella of the trusted context. The physical DB2 connection has not been dropped and re-connected. appUser has privileges on tables whose first character is < 'T', but none on those >= 'T', so the attempted SELECT against TCORContPtType fails (see Listing 15).

Listing 15. Test case 2 — Switching the authorization ID on the trusted content
/**
 *  AppUserid should be able to examine tables whose name is < T, so 
 *  should get error on TCORContPtType
 */
public void testAppUserId()
{
    CSThreadContext.put(GrndsTrustedContextProducer.TRUSTED_CONTEXT_USER, "APPUSER");
    CSThreadContext.put(GrndsTrustedContextProducer.TRUSTED_CONTEXT_PSWD, "appu$er01");
    boolean exceptionGot = false;
    
    try
    {
        PersistenceService.get(TCORContPtType.class.getName());
    }
    catch (PersistenceException e)
    {
        String error = e.getMessage();
        if (error.indexOf("-551")> -1)
        {
            exceptionGot = true;
        }
    }
    
    assertTrue("should have failed with authorization exception for TCORContPtType ", 
                   exceptionGot);

Then, appUser attempts a SELECT against AddressInfo (see Listing 16), which succeeds.

Listing 16. Test case 2 — SELECTing from tables after switching the authorization ID on the trusted content
/**
 * AppUserid should be able to examine tables whose name is < T, so
 * should be able to access AddressInfo
 */
    exceptionGot = false;
    
    try
    {
        PersistenceService.get(AddressInfo.class.getName());
    }
    catch (PersistenceException e)
    {
        fail("Should not get exception on AddressInfo " + e.getMessage());
    }
    
    assertFalse("should be authorized for PhysicalAddress", exceptionGot);

A comparable test was performed for rptUser, but the code is not shown here.


Test results and summary

In the first test, after connecting as appAuthId and attempting SELECTs against the two tables, both failed, since appAuthId had not been granted any roles. In the second test, the authorization ID associated with the trusted connection was switched to appUser. SELECTs against both AddressInfo and TCORContPtType tables were attempted. Since the user appUser had been granted role role1, the SELECT against AddressInfo succeeded while TCORContPtType failed, as expected. Once the authorization ID was switched to rptUser, SELECTs against AddressInfo failed, while SELECTs against TCORContPtType succeeded. Note that rptUser did not retain the privileges of appUser after the switch, even though the same physical connection was used.

One unexpected behavior was encountered. One test case attempted an explicit trusted connection, but provided an invalid password. The connection attempt at first appeared successful. DB2 returned an error only when the first SQL was executed. At that point, the error message was:

An error occurred during a deferred connect reset and the connection has been terminated. See chained exceptions for details. ERRORCODE=-4499, SQLSTATE=null.

The S1 Enterprise architect had some concerns about this, because the implications to the error-handling code are not trivial. As it turns out, this is done for performance reasons.

The potential value to the S1 Enterprise application is to permit more efficient use of the connections in the Websphere connection pool by reducing the overhead of new connection creation, and the overhead of authentication when users are switched (if the WITHOUT AUTHENTICATION option is chosen). In addition, roles make database privilege implementation more scalable as new application users are added, or privilege definition becomes more granular. Security is enhanced, since the trusted connection allows access only from known machine addresses. In terms of encryption, the levels supported by the trusted context definition correspond to DATA_ENCRYPT or SSL.


Audit implications

The audit facility has also been enhanced for DB2 9.5. In particular:

  • You can use new database objects, called audit policies, to control audit configuration within a database. Individual databases can have their own audit configurations, as can particular objects within a database, such as tables, or even users, groups, and roles. In addition to providing easier access to the information that you need, this enhancement also improves performance, because less data needs to be written to disk.
  • Auditing SQL statements is easier and produces less output. The new audit category, EXECUTE, allows you to audit just the SQL statement that is being run. Previously, you needed to audit the CONTEXT event to capture this detail.
  • Audit logs exist for each database. There is now one audit log for the instance and one audit log for each database. This feature simplifies audit reviews.
  • The audit log now has a customizable path. Control over the audit log path allows you to place audit logs on a large, high-speed disk, with the option of having separate disks for each node in a database partitioning (DPF) installation. This feature also allows you to archive the audit log offline, without having to extract data from it until necessary.
  • You can archive audit logs. Archiving the audit log moves the current audit log to an archive directory, while the server begins writing to a new, active audit log. When you extract data from an audit log to a database table, it is from an archived log, not the active audit log. This prevents performance degradation caused by the locking of the active audit log.

Of special interest to this exercise was the new EXECUTE category, which audits just the SQL statements being run. So, an audit policy was defined and applied to the trusted context for the duration of the tests discussed earlier. Audit was enabled as shown in Listing 17.

Listing 17. Defining and enabling an audit policy
CONNECT TO PLTDB USER db2sec USING db2secpwd;
SET SCHEMA db2schem;

CREATE AUDIT POLICY context1TC_ap1
  CATEGORIES CHECKING STATUS BOTH,
  EXECUTE WITH DATA STATUS BOTH,
  VALIDATE STATUS BOTH
  ERROR TYPE NORMAL;

AUDIT TRUSTED CONTEXT context1TC
  USING POLICY context1TC_ap1;

Note that SECADM authority is required to create an audit policy, and to audit another database object, such as a trusted context.

Next, an 8K buffer pool and the audit tables were created (by the DBADM user) in a separate schema (Listing 18), per IBM's recommendation.

Listing 18. Creating an 8K buffer pool and table space to hold audit data
CONNECT TO PLTDB USER db2sys USING db2syspwd;
CREATE SCHEMA AUDIT;

CREATE BUFFERPOOL pageSize8k_bp
  SIZE 100
  PAGESIZE 8K;

CREATE TABLESPACE USERSPACE8K
  PAGESIZE 8K
  MANAGED BY SYSTEM USING ('C:\data\db\tablespace')
  BUFFERPOOL pageSize8k_bp;

The audit output is written to comma-delimited files, which can be easily loaded to audit tables. To create the audit tables, run (as SECADM user):

db2 -tvf sqllib/misc/db2audit.ddl

You can then query for the location of the audit logs as the SECADM user, as shown in Listing 19.

Listing 19. Determining the location of the audit logs
CONNECT TO PLTDB USER db2sec USING db2secpwd;
SET SCHEMA AUDIT;

-- List the audit logs, location	
SELECT * FROM TABLE(SYSPROC.AUDIT_LIST_LOGS('')) AS T1

PATH                                                               
  FILE                                SIZE                
----------------------------------------------------------------------  
  ----------------------------------- -----
C:\DOCUME~1\ALLUSE~1\APPLIC~1\IBM\DB2\DB2COPY1\DB2\security\auditdata\
  db2audit.PLTDB.log.0.20080131090256 29274

A DBADM user should perform the actual table LOAD operations. For this example, only two tables, CHECKING and EXECUTE, are of immediate interest. Listing 20 shows the commands used to extract and LOAD the Audit data.

Listing 20. Extracting and LOADing the audit data
-- Extract the audit archive to delimited files
CALL SYSPROC.AUDIT_DEL_EXTRACT(NULL, 'C:\data\db\audit', NULL, '%2008%', '');

-- LOAD the audit tables
CONNECT TO PLTDB USER db2sys USING db2syspwd;
SET SCHEMA AUDIT;

LOAD FROM C:\data\db\audit\checking.del OF DEL 
    MODIFIED BY DELPRIORITYCHAR LOBSINFILE
    INSERT INTO CHECKING;
    
LOAD FROM C:\data\db\audit\execute.del OF DEL 
    MODIFIED BY DELPRIORITYCHAR LOBSINFILE
    INSERT INTO EXECUTE;

After executing the tests, extracting and loading the audit data, you can observe the results. Listing 21 shows the audit records generated when a successful SELECT statement is issued.

Listing 21. Audit data produced for a successful SELECT attempt
SELECT status, userid, objschema, objname, objtype, trstctxname FROM checking;

STATUS  USERID  OBJSCHEMA  OBJNAME      OBJTYPE  TRSTCTXNAME
------- ------- ---------- ------------ -------- -----------
0       APPUSER DB2SCHEM   ADDRESSINFO  TABLE    CONTEXT1TC

Listing 22 shows the audit records generated from the same event in the EXECUTE table:

Listing 22. Audit data retrieved from the EXECUTE audit table for a successful SELECT attempt
SELECT timestamp, event, status, userid, trstctxname, stmtisolationlvl, 
         rowsreturned, stmttext FROM execute;

EVENT     STATUS  USERID  TRSTCTXNAME  STMTISOLATIONLVL ROWSRETURNED
--------- ------- ------- ------------ ---------------- ------------
STATEMENT   100  APPUSER  CONTEXT1TC   CS               0             


STMTTEXT
----------------------------
SELECT * FROM AddressInfo a0

Recall that user appUser has SELECT privilege on the AddressInfo table. In the CHECKING table, the status 0 indicates a successful execution of the SELECT statement. In the EXECUTE table, the status 100 indicates a "Zero rows returned" warning. One important observation to make is that in this and subsequent listings, the USERID always reflects the authorization ID being used — even after switching users on a trusted connection.

Listing 23 shows the audit record for the unsuccessful SELECT attempt (rptUser tries to SELECT from the AddressInfo table) in the CHECKING table.

Listing 23. Audit data retrieved from the CHECKING audit table for an unsuccessful SELECT attempt
SELECT status, userid, objschema, objname, objtype, trstctxname FROM checking;

STATUS  USERID  OBJSCHEMA  OBJNAME      OBJTYPE  TRSTCTXNAME
------- ------- ---------- ------------ -------- ----------- 
   -551 RPTUSER DB2SCHEM   ADDRESSINFO  TABLE    CONTEXT1TC

In the CHECKING table, the status -551 indicates a "SQL0551N - authid does not have the privilege ..." error while executing the SELECT statement. There is no corresponding STATEMENT entry in the EXECUTE table, as only successful statements are captured.

Note that these examples reflect greatly edited data, both in rows and columns, to highlight the pertinent entries. Slicing the data a little differently, as is shown in Listing 24, you can get a look at the sequence of events in the EXECUTE table.

Listing 24. Audit data retrieved from the EXECUTE table showing a user switch and subsequent SELECT statements
SELECT timestamp, event, status, userid, trstctxname, stmtisolationlvl, rowsreturned, 
       stmttext FROM execute;

EVENT     STATUS  USERID     TRSTCTXNAME STMTISOLATIONLVL ROWSRETURNED 
--------- ------- ---------- ----------- ---------------- -------------
CONNECT         0 APPAUTHID  CONTEXT1TC  -                            - 
CONNECT RESET   0 APPAUTHID  CONTEXT1TC  -                            - 
CONNECT         0 APPUSER    CONTEXT1TC  -                            - 
STATEMENT     100 APPUSER    CONTEXT1TC  CS                           0 
CONNECT RESET   0 APPUSER    CONTEXT1TC  -                            - 
CONNECT         0 RPTUSER    CONTEXT1TC  -                            - 
STATEMENT     100 RPTUSER    CONTEXT1TC  CS                           4



STMTTEXT 
--------------------



SELECT a0.AddressInfoKy, a0.updateUser, a0.updateDttm, a0.versionStamp, a0.houseinfo, 
  a0.addressky, a0.houseinfotype FROM AddressInfo a0


SELECT a0.CORContPtTypeKy, a0.updateUser, a0.updateDttm, a0.versionStamp, a0.name 
  FROM TCORContPtType a0

First, you see the connection as the primary user, appAuthId. Then, a CONNECT RESET and a CONNECT by appUser, who successfully SELECTs from AddressInfo. Next, appUser disconnects, and rptUser connects and successfully SELECTs from TCORContPtType. Listing 25 shows the same chain of events in the CHECKING table:

Listing 25. Audit data retrieved from the CHECKING table showing a user switch and subsequent SELECT statements
SELECT status, userid, objschema, objname, objtype, trstctxname FROM checking;

STATUS USERID    OBJSCHEMA OBJNAME        OBJTYPE  TRSTCTXNAME
------ --------- --------- -------------- -------- -----------
  -551 APPAUTHID DB2SCHEM  TCORCONTPTTYPE TABLE    CONTEXT1TC 
  -551 APPAUTHID DB2SCHEM  ADDRESSINFO    TABLE    CONTEXT1TC 
  -551 APPUSER   DB2SCHEM  TCORCONTPTTYPE TABLE    CONTEXT1TC 
     0 APPUSER   DB2SCHEM  ADDRESSINFO    TABLE    CONTEXT1TC 
  -551 RPTUSER   DB2SCHEM  ADDRESSINFO    TABLE    CONTEXT1TC 
     0 RPTUSER   DB2SCHEM  TCORCONTPTTYPE TABLE    CONTEXT1TC

Here you can see the results of all SELECT attempts. First, appAuthId, who has no table privileges, fails in attempts to SELECT from both tables, then appUser fails on TCORContPtType, but succeeds on AddressInfo. Finally, you see the opposite case for rptUser.

Only a small subset of the audit facility's power is shown here. Specifically, the new EXECUTE category and the existing CHECKING category were defined for auditing. The trusted context test cases were executed, and the audit tables were loaded. A review of the data allows you to observe successes and failures in AUDIT.CHECKING, and SELECT statement successes only in AUDIT.EXECUTE.


Conclusion

This article looked at the two new objects in DB2 9.5, trusted contexts and roles. It examined the purpose of each, presented the syntax diagrams, and showed examples of their usage in the S1 Enterprise tests. It compared the way the S1 Enterprise applications connects to the database under DB2 UDB V8.1 to the new approach, using trusted context and roles, under DB2 9.5, including Java code samples.

The article stepped through examples of the new capabilities observed when connecting through trusted contexts, including the ability to change users (and roles) on the fly. It monitored the connections and statements executed by examining output from the DB2 audit facility, particularly a category new to DB2 9.5, EXECUTE.

Having tested these features early in the DB2 9.5 release, S1 continues to stay on top of new DB2 technology. Trusted contexts, roles, and the new audit capabilities give S1 Enterprise new tools to leverage, which are likely to be integrated into an upcoming release.

Acknowledgements

Many thanks to Bruce Shenker for his expertise and patience in coding the test cases and providing developer feedback, and to Missy Rose and Jeff Mason for their input.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

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=295677
ArticleTitle=Implement DB2 for Linux, UNIX, and Windows trusted contexts and roles in a Web application
publish-date=03202008