Integrating Spring Security with the Database

Introduction

Working in a corporate environment often imposes some policies, which might restrict your choice of technology and even your freedom to apply best practices in your code. As a result, you are faced with numerous limitations that might force you to use bad practices. One such limitation that I recently encountered is the prohibited use of database connection pooling. This was due to the fact that users must use their own accounts to access the database for security and audit reasons. Therefore, many of the systems I have seen used direct management of database connections which is a known bad practice in software design. This limitation introduced a number of issues in in-house Web application development, some of which are:

• Storing database connections in HTTP session objects.

• Some developers kept on opening multiple connections for the same user even though an active database connection was available in the HTTP session object.

• Database connections are kept idle in the HTTP session for a long period of time, especially if the user did not sign-out or the developer did not properly implement session invalidation.

• A large memory footprint on the server because of the increased size of http session objects.

• Extra work needed to make HTTP session objects scalable to other servers because of the need to carefully serialize/de-serialize database connections.

To overcome this limitation, avoid these issues and follow a clean design, you will learn how to use Spring Security framework to facilitate the management of database connection by means of its authentication services. Furthermore, you will learn how to use the MyBatis framework to take advantage of user credentials managed by Spring Security.

This article is divided into the following sections

• Authentication with Spring Security: This section will discuss an approach to implementing authentication using the Spring Security framework for a Web application that uses form-based authentication.

• Integration with MyBatis: This section complements the previous section by showing a technique to utilize the Spring Security infrastructure to simplify connection management.

Authentication with Spring Security

This article follows a simple scenario where users provide their own username and password using a login page to authenticate with the system. The login page posts user credentials to a special URL handled by Spring Security.

Login Page (JSP Example)

<form method="POST" action="<c:url value="/j_spring_security_check" />">
	<label for="userName">User Name</label>
	<input type="text" id="userName" name="j_username" value=""/>
	<label for="userPassword">Password</label>
	<input type="password" id="userPassword" name="j_password"/>
	<div>
		<button type="submit" class="btn btn-primary">Login</button>
	</div>
</form>

This URL, which has the value of /j_spring_security_check, is mapped internally to a Servlet filter with the ID of authenticationFilter as defined in the Spring Security Root Context, the root-context.xml file, as follows:

<beans:bean id="authenticationFilter"
class="org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter">
	<beans:property name="authenticationManager" ref="authenticationManager" />
</beans:bean>

The authenticationFilter, which provides the username/password mechanism by means of the UsernamePasswordAuthenticationFilter class, is responsible for processing the login form and creating an authentication object that holds user credentials. This object is passed to an authentication manager, which can have one or more authentication providers. To keep the example simple, the following configuration code defines only a single provider:

<authentication-manager alias="authenticationManager" erase-credentials="false">
	<authentication-provider ref="databaseAuthenticationProvider" />
</authentication-manager>

Note the use of the erase-credential attribute, which instructs Spring Security to not clear the user credentials after it finishes the authentication process. This would allow you to use the credentials later on whenever you need to make a connection with the database, as discussed in the second section of this article. The authentication manager refers to the following database authentication provider which has two properties; one for the database class and the other for the URL, as shown below:

<beans:bean class="com.infoq.DatabaseAuthenticationProvider"
	id="databaseAuthenticationProvider">
	<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
	<beans:property name="url" value="${db.url}" />
</beans:bean>

It is the authenticate() method of the authentication provider that performs the actual authentication logic. In this method you can write your own authentication logic depending on your business requirements. Note that the example uses the username and password provided by the user rather than a generic account to connect to the database. This complies with the policies of the corporate environment, as stated earlier. To validate that the user has access to the database, an instance of JdbcTemplate is used to get the list of privileges for the user and is set as part of the authentication token. If the JdbcTemplate fails to obtain a database connection using the user credentials, then an exception will be thrown and Spring Security will report a failed authentication attempt. The authentication logic is provided below:

public class DatabaseAuthenticationProvider implements AuthenticationProvider {

	private String url;
	private String driverClassName;

	@Override
	public Authentication authenticate(Authentication authentication)
			throws AuthenticationException {

		ArrayList<GrantedAuthority> authorities = new ArrayList<GrantedAuthority>();
		String username = (authentication.getPrincipal() == null) ? "NONE_PROVIDED"
				: authentication.getName();

		String password = (String) authentication.getCredentials();

		try {
			OracleDataSource oracleDataSource = new OracleDataSource();
			oracleDataSource.setURL(this.getUrl());
			oracleDataSource.setUser(username);
			oracleDataSource.setPassword(password);

			JdbcTemplate jdbcTemplate = new JdbcTemplate(oracleDataSource);
			String query = "select * from user_privileges where username = ?";
			List<Map<String, Object>> list = jdbcTemplate.queryForList(query, username);

			Iterator<Map<String, Object>> iterator = list.iterator();
			while (iterator.hasNext()) {
				Map<String, Object> map = iterator.next();
				String role = (String) map.get("user_ privilege");
				authorities.add(new SimpleGrantedAuthority(role));
			}
		} catch (DataAccessException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new UsernamePasswordAuthenticationToken(username, password, authorities);
	}
	@Override
	public boolean supports(Class<?> authentication) {
		return UsernamePasswordAuthenticationToken.class.isAssignableFrom(authentication);
	}
	public String getUrl() {
		return url;
	}
	public void setUrl(String url) {
		this.url = url;
	}
	public String getDriverClassName() {
		return driverClassName;
	}
	public void setDriverClassName(String driverClassName) {
		this.driverClassName = driverClassName;
	}
}

Once returned from the authenticate method, Spring Security will store the authentication token in its SecurityContext object. At this point, the user is authenticated and has access to the system. The database connection is not stored anywhere. This has the advantage of reducing the memory footprint on the server by not keeping database connections idle. The following figure summarizes the authentication process at a high level.

At this point, you have built the foundation for the next section, which will facilitate the proper management of database connections. To learn more about authentication services provided by Spring Security, please refer to http://docs.spring.io/spring-security/site/docs/3.2.5.RELEASE/reference/htmlsingle/#tech-intro-authentication.

Integration with MyBatis

Now that the user credentials and privileges are stored in the SecurityContext, it is time to configure MyBatis to make use of this information. MyBatis uses an instance of SqlSession, which relies on a DataSource to get a database connection. An instance of DataSource is often configured in advance with a generic username and password to create a pool of database connections. Moreover, once a DataSource is configured, it uses the same credentials to get additional database connections. This is problematic in a Web environment where individual users are expected to use their own credentials. To overcome the limitations of the DataSource class, an adapter will be used. This adapter will delegate the user credentials to every call of the getConnection() method of the target DataSource to ensure that the returned connection is for the right user, as shown below:

<beans:bean id="dataSource"
	class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
	<beans:property name="url" value="${db.url}" />
</beans:bean>
<beans:bean id="dataSourceAdapter"
	class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
	<beans:property name="targetDataSource" ref="dataSource" />
</beans:bean>

The next step is to configure MyBatis to use the adapter as part of its SQL session factory bean.

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSourceAdapter" />
</bean>

There is one piece still missing from the puzzle. How are we going to provide the DataSource adapter with user credentials? The trick here is to define a filter that would retrieve user credentials from the SecurityContext and pass them to the adapter.

<beans:bean id="credentialsFilter" class="com.infoq.CredentialsFilter">
	<beans:property name="dataSourceAdapter" ref="dataSourceAdapter" />
</beans:bean>

Once the CredentialsFilter retrieves the authentication token from the SecurityContext, the filter will invoke the setCredentialsForCurrentThread() method on the adapter to ensure that a call to getConnection on the DataSource will use the credentials of the intended user. Be careful not to use the setUsename() and setPassword() methods, which will set the credentials globally for the adapter, thereby, risking the use of the wrong credentials for some users. Correct use of the CredentialsFilter class is shown below:

public class CredentialsFilter implements Filter{
	private UserCredentialsDataSourceAdapter dataSourceAdapter;
	public UserCredentialsDataSourceAdapter getDataSourceAdapter() {
		return dataSourceAdapter;
	}

	public void setDataSourceAdapter(UserCredentialsDataSourceAdapter dataSourceAdapter) {
		this.dataSourceAdapter = dataSourceAdapter;
	}
	@Override
	public void doFilter(ServletRequest request, ServletResponse response,
			FilterChain chain) throws IOException, ServletException {
		SecurityContext securityContext = SecurityContextHolder.getContext();
		Authentication authentication = securityContext.getAuthentication();
		
		if(authentication != null){
			Object principal = authentication.getPrincipal();
			Object credentials = authentication.getCredentials();
			dataSourceAdapter.setCredentialsForCurrentThread(
					principal.toString(), credentials.toString());
		}
		try{
			chain.doFilter(request, response);
		} finally{
			dataSourceAdapter.removeCredentialsFromCurrentThread();
		}
	}
}

Note the invocation of removeCredentialsFromCurrentThread() method at the end of doFilter(). This is needed to clear the user credentials from the current thread and void any possible memory leaks. The following figure summarized the integration process at a higher level:

The last step is to configure Spring Security to use the two filters. This can be easily achieved by using custom-filter tags as part of the http element in the Spring Security Root context as follows:

<http auto-config="false">
	<custom-filter position="FORM_LOGIN_FILTER" ref="authenticationFilter"/>
	<custom-filter after="FORM_LOGIN_FILTER" ref="credentialsFilter"/>
</http>

To learn more about Spring Security Filter Chain and the position attribute, please refer to http://docs.spring.io/spring-security/site/docs/3.2.5.RELEASE/reference/htmlsingle/#security-filter-chain.

Conclusion

By using Spring Security authentication services along with MyBatis you are able to delegate the management of database connection to MyBatis framework. Moreover, you no longer need to worry about opening, closing and storing database connections. Furthermore, you reduce the footprint on the application server and the database.