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 samplefullaccessThe 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
CREATE TRUSTED CONTEXT MYTCX
BASED UPON CONNECTION USING SYSTEM AUTHID trust
ATTRIBUTES (ADDRESS '192.168.1.4')
DEFAULT ROLE samplereadaccess
WITH USE FOR PUBLIC WITHOUT AUTHENTICATION,
JOHN ROLE samplefullaccess WITHOUT AUTHENTICATION
ENABLE;
The trusted context object named MYTCX is created. What does it mean ?
- The client running on machine 192.166.1.4 can establish trusted connection to database SAMPLE as user trust.
- All requests for trusted connection from other machines or request from 192.168.1.4 but as a different user or request from 192.168.1.4 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).
TrustedConnection.java
package com.db2.trusted;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.ibm.db2.jcc.DB2ConnectionPoolDataSource;
import com.ibm.db2.jcc.DB2PooledConnection;
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 {
dataSource.setDatabaseName(databaseName);
dataSource.setServerName(hostName);
dataSource.setDriverType(4);
dataSource.setPortNumber(portNumber);
dataSource.setCurrentSchema(defaultSchema);
// 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;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return dataSource.getLogWriter();
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
dataSource.setLogWriter(out);
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
dataSource.setLoginTimeout(seconds);
}
@Override
public int getLoginTimeout() throws SQLException {
return dataSource.getLoginTimeout();
}
@Override
public T unwrap(Class iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class iface) throws SQLException {
return false;
}
@Override
public Connection getConnection() throws SQLException {
return useConnection(newUser);
}
@Override
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", "192.168.1.6", "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'
//etc
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.update(...);
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.
Main.java
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", "192.168.1.6", "DB2INST1");
trust.trustConnect();
JdbcTemplate jTemplate = new JdbcTemplate(
trust.constructReuseDataSource("john"));
int no = jTemplate.queryForInt("SELECT COUNT(*) FROM ACT");
String sql = "update employee set bonus=30000 where lastname='WONG'";
jTemplate.update(sql);
jTemplate = new JdbcTemplate(trust.constructReuseDataSource("mary"));
no = jTemplate.queryForInt("SELECT COUNT(*) FROM EMPLOYEE");
sql = "update employee set bonus=30000 where lastname='WONG'";
try {
jTemplate.update(sql);
} catch (BadSqlGrammarException e) {
// as expected
// SQL0551N code
}
}
/**
* @param args
*/
public static void main(String[] args) {
try {
goTrusted();
} catch (Exception e) {
e.printStackTrace();
}
}
}
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.
