Authenticating via Database User - Is it supported?

[pbdavey]

Using Oracle 10g, I'd like to authenticate against database users (e.g. Connection.getConnection(username, password) ). Is there a standard way to do this? Or is it that I'd have to figure out how to retrieve the password from SYS.USER$ to allow the provider to determine authentication? Once authenticated, retrieving database roles would be trivial I'd think.


Historically we've just been doing authentication using a plain old form + user object + authentication service (essentially, does getConnection work?).

Typically this is all over https...if it makes any difference to what people recommend.

Any setup info would be nice...such as what authentication provider to use or what details service to use...or even what I have to write custom code for. I'm suspisious that I need to write a custom Authentication Provider, but this is my first time trying Spring Security.

[Luke Taylor]

Yes, you should write your implement your own AuthenticationProvider. The namespace configuration chapter of the reference manual has information on how to link it in to the Spring Security configuration.

I'd start with the tutorial sample, get that running and then attempt to replace the in-memory provider with your own one.

[pbdavey]

I like finding working code so here's some as a solution (particularily for Oracle, but simple to genericize to any old database):

Authentication Provider

class DatabaseUserAuthenticationProvider implements AuthenticationProvider {
	DataSource dataSource;
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	
	public Authentication authenticate(Authentication authentication) {
		String username = (String) authentication.getPrincipal();
		String password = (String) authentication.getCredentials();
		Vector<GrantedAuthority> roles = new Vector<GrantedAuthority>();
		
		logger.debug("Attempting to Authenticate " + username + ":" + password);
		
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			
			PreparedStatement statusStmt = conn.prepareStatement("SELECT LOCK_DATE, EXPIRY_DATE, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = UPPER(?)");
			statusStmt.setString(1, username);
			ResultSet rs = statusStmt.executeQuery();
			if (rs.next()) {
				Object lockDate = rs.getTimestamp("LOCK_DATE");
				Object expiryDate = rs.getTimestamp("EXPIRY_DATE");
				String status = rs.getString("ACCOUNT_STATUS");
				if (lockDate != null || expiryDate != null) {
					throw new LockedException(status);
				}
			}
			else {
				throw new BadCredentialsException("Username Unknown");
			}
			statusStmt.close();
		}
		catch (SQLException e) {
			throw new BadCredentialsException("Invalid credentials");
		}
		finally {
			try {
				if (conn != null)
					conn.close();
			}
			catch(SQLException e) {
				
			}
		}

		conn = null;
		try {
			conn = dataSource.getConnection(username, password);
			
			PreparedStatement statusStmt = conn.prepareStatement("SELECT GRANTED_ROLE FROM USER_ROLE_PRIVS");
			ResultSet rs = statusStmt.executeQuery();
			while (rs.next()) {
				String role = rs.getString("GRANTED_ROLE");
				if (!role.startsWith("ROLE_"))
					role = "ROLE_" + role;
				roles.add(new GrantedAuthorityImpl(role));
			}
			statusStmt.close();
		}
		catch (SQLException e) {
			throw new BadCredentialsException("Invalid credentials");
		}
		finally {
			try {
				if (conn != null)
					conn.close();
			}
			catch(SQLException e) {
				
			}
		}
		
		GrantedAuthority [] aRoles = new GrantedAuthority[roles.size()];
		roles.toArray(aRoles);
		logger.debug("Authenticated " + username + ":" + password + ":" + aRoles);
		UsernamePasswordAuthenticationToken auth = 
			new UsernamePasswordAuthenticationToken(username, password, aRoles);
		return auth; 
	}
	 
	public boolean supports(Class authentication) {
		return authentication.equals(UsernamePasswordAuthenticationToken.class);
	}
}

What you need in your security xml configuration

	<bean id="authenticationProvider"
		class="DatabaseUserAuthenticationProvider">
	    <property name="dataSource" ref="rawDataSource"/>
	    <security:custom-authentication-provider/>
	</bean>

Viola! If you have improvements or some such things, let me know. Or if you know how to authenticate using SYS.USER$ using the more conventional DaoAuthenticationProvider + JdbcUserDetailsManager