Blog do projektu Open Source JavaHotel

piątek, 27 maja 2011

Trusted context and auditing

Security is very important matter nowadays. One of the method to make our data more secure is auditing, monitoring and discovering unexpected or forbidden behavior. For instance - in the previous example - user 'Mary' tried to update the bonus column in 'Employee' table but was rejected. Let's go further - assume that dishonest user 'Mary' was able to overcome the application security control and tried to perform some forbidden activity directly at the database level. Because our well designed solution is protected at the two levels - application and database - this attack was repulsed. But it can be the matter of time that the malicious user devises the second plan to find a security hole and will be successful. The only way to protect our confidential data is to pin down the suspicious behavior as early as possible and react before a mischief is done.
Good news is that DB2 provides very rich set of tools for auditing and security monitoring.

Unfortunately the auditing is not enabled by default.  So it is necessary to make some preparations.
1. Create (if not created so far)  tables necessary to keep audit data - there is a db2audit.dll file provided with every DB2 installation.
db2 -tvf {DB2 home directory} /misc/db2audit.ddl
2. Create AUDIT POLICY database object. There are a lot of options what to monitor and what audit granularity is expected. Here is the simple example for monitoring everything.
3. Enable monitoring for roles or users.
4. From now on all activities are monitored.
Pave the way for Sherlock Holmes
Several steps are necessary before audit data can be looked through.
1. Make an audit data snapshot.
2. Convert binary data to text CSV file
CALL SYSPROC.AUDIT_DELIM_EXTRACT(NULL, '/home/db2inst1/sqllib/security/auditdata', NULL, null, NULL);
3. Load CSV text files into relational tables created by running db2audit.dll script file

-- To load the OBJMAINT table, issue the following command:

-- To load the SECMAINT table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/secmaint.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO SECMAINT;

-- To load the SYSADMIN table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/sysadmin.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO SYSADMIN;

-- To load the VALIDATE table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/validate.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO VALIDATE;

-- To load the CONTEXT table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/context.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO CONTEXT;

--# To load the EXECUTE table, issue the following command:
   LOAD FROM /home/db2inst1/sqllib/security/auditdata/execute.del OF DEL MODIFIED BY DELPRIORITYCHAR LOBSINFILE INSERT INTO EXECUTE;

Description of all audit objects

5. Tables are ready and we can query them.

We want to check if any user belonging to SAMPLEREADACCESS role has tried to update EMPLOYEE table :
This type of event is stored in CHECKING audit table:
So after running query like:
The horrible truth is revealed:
TIMESTAMP                  2                    3                    STATUS     
-------------------------- -------------------- -------------------- -----------
2011-05-27- mary                 db2jcc_application          -551
Our database is under attack and we have to evaluate the whole situation. It is bad news. Good news that by virtue of applying DB2 trusted context and two layers security control this time the attack was repulsed, by virtue of DB2 auditing feature the security hole is unveiled  and we have some time to tighten our security policy before any harm is done.

środa, 25 maja 2011

Real secret of success

Mark Twain "Roughing It" (Polish title: "Pod gołym niebem")
We never touched our tunnel or our shaft again. Why? Because we judged
that we had learned the real secret of success in silver mining--which
was, not to mine the silver ourselves by the sweat of our brows and the
labor of our hands, but to sell the ledges to the dull slaves of toil and
let them do the mining!

wtorek, 24 maja 2011

Trusted context, DB2, Spring

The problem
The problem is described here. Keeping long story short - in three-tiered solutions the user does not interact directly with the database. The middle-tier (application server) on behalf of the user is connected to the database and send there requests. It has advantages and disadvantages. The main disadvantage is that it is very difficult to track and audit the user activities from the point of the database because it is done by the application server and we cannot tell the request from one user from the requests of other user. Also - in case of the users with the different level of privileges - it is very difficult to provide the granularity of privileges as required.

Bad news and good news
The bad news is that JEE standard does not provide a consistent approach to this problem. The good news is that DB2 provides a solution although this solution is specific to the DB2 only.

Trusted connection
This good news is trusted context and trusted connection. General idea:
Trusted context is the database object which allows setting a special connection (trusted) between database server and specified (by IP address) client (application server).
Application server connects to the database server and establishes the trusted connection.
Then, by virtue of this connection, it is possible to reuse this connection without authentication and change the authentication id (user id). It could be the id of the user who's just logged in to the application server. Also privileges assigned to this  authentication id will be changed accordingly (if not inherited implicitly).

How to accomplish it (more detail)

1. Create a trusted user
It is the user who will establish a trusted connection. In default DB2 security implementation system (Linux for instance) authentication is utilized. So it is enough to add a new user to the system e.g U/P trust/trustme. No additional DB2 privileges or authorities are required, this user should be only able to connect to the database which is granted for all system users.

2. Create roles
Assume we want to split our users into two groups. Some users (a smaller group) would be able to modify the data (dataaccess authority), the rest (default) are allowed only to read the data without modifying.
The first role (group) could be created with the command:
{ connect to SAMPLE database } 
  db2 create role SAMPLEFULLACCESS
  db2 grant dataaccess on database  to samplefullaccess
The task of creating the second role (read only) is a bit more complicated because in DB2 there is no general read only authority like "dataaccess". This privilege should be performed on table by table basis.
db2 create role SAMPLEREADACCESS
Then run the following command:
db2 "select 'grant select on table ' || tabname || ' to samplereadaccess;' from syscat.tables where tabschema='DB2INST1'" >grant.sql
This command prepares a sql script file giving a right to select data from every table. After removing the first and the list line from this script it is enough to run the script:
db2 -tvf grant.sql
More information on DB2 security model 

3. Create trusted context
       ATTRIBUTES (ADDRESS   '')  
       DEFAULT ROLE samplereadaccess
                    JOHN ROLE samplefullaccess WITHOUT AUTHENTICATION 

The trusted context object named MYTCX is created. What does it mean ?
  • The client running on machine can establish trusted connection to database SAMPLE as user trust. 
  • All requests for trusted connection from other machines or request from but as a different user or request from to connect to database other than SAMPLE will be rejected. 
  • Having trusted connection established the client can switch this connection  to other user id (without authentication) and this new user is only able to read data from SAMPLE database. 
  • Only the user 'JOHN' is a proud keeper of the special privilege on database SAMPLE - he can also modify data.

4. Java code
Java code for creating trusted connection and switching (reusing) this connection to the other (non-authenticated) user looks like.
(it is based on this code sample).

package com.db2.trusted;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;


public class TrustedConnection {

    private final int portNumber;
    private final String databaseName;
    private final String userName;
    private final String password;
    private final String hostName;
    private final String defaultSchema;
    private Object[] objects;
    private final DB2ConnectionPoolDataSource dataSource;

     * Constructor, provide all details necessary to estalish trusted connecton
     * @param portNumber  port number (most common is 50000)
     * @param databaseName e.g SAMPLE
     * @param userName e.g trust
     * @param password e.g trustme
     * @param hostName IP address or hostname
     * @param defaultSchema e.g DB2INST1 (important: case sensitive !)
    public TrustedConnection(int portNumber, String databaseName,
            String userName, String password, String hostName,
            String defaultSchema) {
        this.portNumber = portNumber;
        this.databaseName = databaseName;
        this.userName = userName;
        this.password = password;
        this.hostName = hostName;
        this.defaultSchema = defaultSchema;
        dataSource = new DB2ConnectionPoolDataSource();

     * Establishes trusted connection, should be called only once
     * @throws SQLException
    public void trustConnect() throws SQLException {
        // Call getDB2TrustedPooledConnection to get the trusted connection
        // instance and the cookie for the connection
        objects = dataSource.getDB2TrustedPooledConnection(userName, password,
                new java.util.Properties());

     * Switch and reuse trusted connection as different user (without authentication)
     * @param newUser id of the new user
     * @return Connection (java.sqlx)
     * @throws SQLException
    public Connection useConnection(String newUser) throws SQLException {
        DB2PooledConnection pooledCon = (DB2PooledConnection) objects[0];
        Properties properties = new Properties();
        byte[] cookie = (byte[]) (objects[1]);
        String userRegistry = null;
        byte[] userSecTkn = null;
        String originalUser = null;
        Connection con = pooledCon.getDB2Connection(cookie, newUser, null,
                userRegistry, userSecTkn, originalUser, properties);
        return con;

     * For the purpose of JdbcTemplate. 
     * DataSource with getConnection method override
     * New user id as constructor parameter
     * @author sbartkowski
    private class TrustedDataSource implements DataSource {

        private final String newUser;

        public TrustedDataSource(String newUser) {
            this.newUser = newUser;

        public PrintWriter getLogWriter() throws SQLException {
            return dataSource.getLogWriter();

        public void setLogWriter(PrintWriter out) throws SQLException {

        public void setLoginTimeout(int seconds) throws SQLException {

        public int getLoginTimeout() throws SQLException {
            return dataSource.getLoginTimeout();

        public  T unwrap(Class iface) throws SQLException {
            return null;

        public boolean isWrapperFor(Class iface) throws SQLException {
            return false;

        public Connection getConnection() throws SQLException {
            return useConnection(newUser);

        public Connection getConnection(String username, String password)
                throws SQLException {
            return getConnection();


    public DataSource constructReuseDataSource(String newUser) {
        return new TrustedDataSource(newUser);


Usage example

TrustedConnection trust = new TrustedConnection(50000, "SAMPLE", "trust",
                "trustme", "", "DB2INST1");
        trust.trustConnect(); // should be done once only
        Connection con = trus.useConnection("John");
        // some database activity as user 'John'
        con = trus.useConnection("Jack");
        // some database activity as user 'Jack'

Spring JdbcTemplate

JdbcTemplate uses DataSource to extract Connection interface. Unfortunately  I cannot use DB2ConnectionPoolDataSource directly because JdbcTemplate uses getConnection method (without parameters). In order to overcome this problem I applied Decorator pattern to DB2ConnectionPoolDataSource class just overriding only getConnection method and providing new user id in class constructor.

The usage example:
TrustedConnection trust;
    JdbcTemplate jTemplate = new JdbcTemplate(trust.constructReuseDataSource('John'));
    jTemplate // any other method. 
Last but not least - is it really works ???

In order to answer this question I created a simple standalone Java program which uses TrustedContext class (described above), retrieves trusted connection to the SAMPLE database, switches to different user and run some simple SQL statements.

import java.sql.SQLException;

import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;

import com.db2.trusted.TrustedConnection;

public class Main {

    private static void goTrusted() throws SQLException {
        TrustedConnection trust = new TrustedConnection(50000, "SAMPLE", "trust",
                "trustme", "", "DB2INST1");
        JdbcTemplate jTemplate = new JdbcTemplate(
        int no = jTemplate.queryForInt("SELECT COUNT(*) FROM ACT");
        String sql = "update employee set bonus=30000 where lastname='WONG'";
        jTemplate = new JdbcTemplate(trust.constructReuseDataSource("mary"));
        no = jTemplate.queryForInt("SELECT COUNT(*) FROM EMPLOYEE");
        sql = "update employee set bonus=30000 where lastname='WONG'";
        try {
        } catch (BadSqlGrammarException e) {
            // as expected
            // SQL0551N code

     * @param args
    public static void main(String[] args) {
        try {
        } catch (Exception e) {



Firstly we can debug this code and set breakpoint at three places (14, 17 and 29). At the same time connect to SAMPLE database and run
db2 list applications
command when Java program breaks.
The first time something like this will be retrieved:

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
TRUST    db2jcc_applica 144        C0A80103.88C9.110524112902                                     SAMPLE   1    
DB2INST1 db2bp          63         *LOCAL.db2inst1.110524104758                                   SAMPLE   1    

the second:

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
JOHN     db2jcc_applica 144        C0A80103.88C9.110524113623                                     SAMPLE   1    
DB2INST1 db2bp          63         *LOCAL.db2inst1.110524104758                                   SAMPLE   1  

and third:
Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
MARY     db2jcc_applica 144        C0A80103.88C9.110524113623                                     SAMPLE   1    
DB2INST1 db2bp          63         *LOCAL.db2inst1.110524104758                                   SAMPLE   1  

Pay attention that every time application handle (Appl. Handle) which is set at the moment when application connects to the database server is the same but Authentication Id is different - it is changing according to the current Connection user id. It means that different users shares the same connection one after one.

Secondly we can check if our sophisticated security policy is in force. As we see  - user John is able to update the table but when user Mary is trying to modify the bonus of her college - she is rejected.

Next step is to verify if we are able to track the user activities for auditing purspose.

niedziela, 15 maja 2011

GWT 2.3 and NetBeans 7.0

I downloaded the latest (2.3) version of GWT but could not compile my GWT application. The error message is something like that:
[java] [ERROR] Errors in 'jar:file:/../gwt-user-2.3.0.jar!/com/google/
[java] [ERROR] Line 97: No source code is available for type
javax.validation.ConstraintViolation; did you forget to inherit a
required module? 
It looks that some dependencies which had been attached to gwt-user.jar so far  have been "outsourced" to the external library.
What is more important - Gwt4nb plugin (used by me) seems not be enabled not only for GWT 2.3 but also for the NetBeans 7.0 at all.
Manual workaround is as follows:
  1. File (accessible from Files tab) {project name}/nbproject/private/ Replace property with directory name of the GWT 2.3 SDK
  2. Manually add the following jars to compile-time libraries (not deployable): validation-api-1.0.0.GA-sources.jar and gwt-servlet-deps.jar.
 After that it is working for me as expected.

poniedziałek, 2 maja 2011


Database application without reporting is like a sea without fish. Looks like a sea but something important is missing.
So I decided to add some reporting feature to my GwtSample application.


But firstly I realized  that it is necessary to release tight coupling with database schema (here Employee table) and to get rid of direct references to Employee class attributes (Employee.getEmpno(), Employee.getMidinit() etc).
So, in the share package, additional class ( was created providing some general description of the table schema instead of direct column names and types.

public interface IRecord {

public class Employee imlements IRecord,Serializable {

public class GetField {

    public enum FieldType {

     * Field, column value (C union structure would be the best)
     * Only one attribute should be set
     * @author sbartkowski
    public static class FieldValue {
        /** Timestamp. date column. */
        private final Timestamp dField;
        /** char, carchar2 column. */
        private final String sField;
        /** integer column. */
        private final int iField;
        /** decimal column. */
        private final BigDecimal nField;


     * Description of one column in IRecord object
     * @author sbartkowski
    public static class FieldInfo {
        /** Column identifier. */
        private final String fId;
        /** Column type. */
        private final FieldType fType;
        /** Column description, title. */
        private final String fDescr;
        /** Column size to display (in pixels). */
        private final int cSize;

     * Get list of all columns
     * @return List of columns (FieldInfo)
    public static List<fieldinfo> getfList() {
        return fList;

     * Get column value from the record
     * @param field Column identifier (fId attribute in FieldInfo)
     * @param i IRecord, record, row
     * @return FieldValue class with proper attribut set according to column type

    public static FieldValue getValue(String field, IRecord i) {

This set of classes and functions is enough to strip off from client (gwt) code any direct reference to Employee class. So it could be easily extended to the general purpose display client read for any sql statement.


As a reporting engine I decided to use BIRT. There is an easy path to utilize BIRT : via defining JDBC data source and run and display simply SQL statement (SELECT * FROM EMPLOYEE) and launch report by calling proper URL.
But I don't like this solution. Firstly for security reason : we have to provide the credentials for accessing database also to the BIRT engine just creating the next security access point. Also we have to duplicate some logic in the BIRT -  run the same SQL statement like in our main application.  It could be also complicated to implement all features : order by and the direction for the ordering.
So I chose the hard way : via XML data source. The general idea is as follows:
  1. The user clicks the Print button.
  2. Client sends back to the server all rows displayed on the screen in current order and direction.
  3. Server creates temporary XML file on the server containing all data and returns to the client the file name.
  4. Client launches BIRT report via URL providing in the URL the file name.
  5. BIRT run the report using the file name taken from URL as a XML data source.
Simple class for creating XML file (general purpose) -
URL (host and port) used to launch BIRT report is defined as a resource variable in server.xml file, also the report design file name.

The advantage of this solution:
  1. More safe, the data access credentials used only at one point (application server).
  2. The table in the report is sorted in exactly the same way as visible on the screen. No additional logic is required in the BIRT engine.
The disadvantage:
  1. More complicated, more coding, more network traffic.
  2. Temporary files are left on the server, cleaning possible only after stopping the application server or machine restart.
  3. Application server and BIRT engine (resource thirsty) should be installed on the same machine.
But finally - it is working.
The source code for the application and BIRT project: