 | Level: Intermediate Matt Eakle (matt.eakle@s1.com), Senior Data Architect, S1 Corporation
Ted J. Wasserman (tedwas.ibm@gmail.com), Database Consultant, IBM
20 Mar 2008 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.
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
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.
-
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.
-
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
-
"Use trusted context in DB2 client applications"
(developerWorks, Sept. 2006): Implement trusted context on
DB2 in a call level interface (CLI), XA, or JDBC-based application.
-
"Implement new security capabilities in DB2 9.5, Part 2: Understanding trusted contexts"
(developerWorks, Jan. 2008): Take advantage of the new
DB2 feature called trusted contexts in combination with other essential e-business
technologies, such as Web services, Web application server, and DB2
database server.
-
"Analyzing Audit
Data in DB2 'Viper 2'"(IBM Database Magazine, July 2007): Find enhancements to the DB2 audit facility in DB2 9.5
for Linux, UNIX, and Windows that help companies meet regulatory and security
demands.
-
"Understanding Label-Based Access Control, Part 1" (IBM Database Magazine, May 2007) Use DB2's newest data security control combats threats from the inside.
-
"Understanding Label-Based Access Control, Part 2"
(IBM Database Magazine, July 2007): Protect data in individual columns
with label-based access control, new in DB2 9 for Linux, Unix, and Windows.
-
DB2 9.5 Online Information Center:
The DB2 online (and searchable) documentation.
- Browse the
technology bookstore
for books on these and other technical topics.
-
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
-
Stay current with developerWorks
technical events and webcasts.
Get products and technologies
-
Build your next development project with
IBM
trial software, available for download directly from developerWorks.
Discuss
About the authors | 
| | Matt 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 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. |
Rate this page
| |
