public class JdbcRealm extends AuthorizingRealm implements Serializable
{
    @Resource(name = "jdbc/DefaultDB")
    private DataSource dataSource;

    protected static final String DEFAULT_AUTHENTICATION_QUERY = "select passwd from user where username = ?";
    protected static final String DEFAULT_SALTED_AUTHENTICATION_QUERY = "select passwd, passwd_salt from user where username = ?";
    protected static final String DEFAULT_USER_ROLES_QUERY = "select role_name from user_roles where username = ?";
    protected static final String DEFAULT_PERMISSIONS_QUERY = "select permission from roles_permissions where role_name = ?";
    private static final Logger log = LoggerFactory.getLogger(JdbcRealm.class);

    public enum SaltStyle
    {
        NO_SALT, CRYPT, COLUMN, EXTERNAL
    };

    protected String authenticationQuery = DEFAULT_AUTHENTICATION_QUERY;
    protected String userRolesQuery = DEFAULT_USER_ROLES_QUERY;
    protected String permissionsQuery = DEFAULT_PERMISSIONS_QUERY;
    protected boolean permissionsLookupEnabled = false;

    protected SaltStyle saltStyle = SaltStyle.NO_SALT;

    public void setDataSource(DataSource dataSource)
    {
        this.dataSource = dataSource;
    }

    public void setAuthenticationQuery(String authenticationQuery)
    {
        this.authenticationQuery = authenticationQuery;
    }

    public void setUserRolesQuery(String userRolesQuery)
    {
        this.userRolesQuery = userRolesQuery;
    }

    public void setPermissionsQuery(String permissionsQuery)
    {
        this.permissionsQuery = permissionsQuery;
    }

    public void setPermissionsLookupEnabled(boolean permissionsLookupEnabled)
    {
        this.permissionsLookupEnabled = permissionsLookupEnabled;
    }

    public void setSaltStyle(SaltStyle saltStyle)
    {
        this.saltStyle = saltStyle;
        if (saltStyle == SaltStyle.COLUMN && authenticationQuery.equals(DEFAULT_AUTHENTICATION_QUERY))
        {
            authenticationQuery = DEFAULT_SALTED_AUTHENTICATION_QUERY;
        }
    }

    @Override
    protected AuthenticationInfo doGetAuthenticationInfo(AuthenticationToken token) throws AuthenticationException
    {
        UsernamePasswordToken upToken = (UsernamePasswordToken) token;
        String username = upToken.getUsername();
        // Null username is invalid
        if (username == null)
        {
            throw new AccountException("Null usernames are not allowed by this realm.");
        }
        Connection conn = null;
        SimpleAuthenticationInfo info = null;
        try
        {
            conn = dataSource.getConnection();
            String password = null;
            String salt = null;
            switch (saltStyle)
            {
                case NO_SALT:
                    password = getPasswordForUser(conn, username)[0];
                    break;
                case CRYPT:
                    // TODO: separate password and hash from getPasswordForUser[0]
                    throw new ConfigurationException("Not implemented yet");
                //break;
                case COLUMN:
                    String[] queryResults = getPasswordForUser(conn, username);
                    password = queryResults[0];
                    salt = queryResults[1];
                    break;
                case EXTERNAL:
                    password = getPasswordForUser(conn, username)[0];
                    salt = getSaltForUser(username);
            }
            if (password == null)
            {
                throw new UnknownAccountException("No account found for user [" + username + "]");
            }
            info = new SimpleAuthenticationInfo(username, password.toCharArray(), getName());

            if (salt != null)
            {
                info.setCredentialsSalt(ByteSource.Util.bytes(salt));
            }
        }
        catch (SQLException e)
        {
            final String message = "There was a SQL error while authenticating user [" + username + "]";
            if (log.isErrorEnabled())
            {
                log.error(message, e);
            }
            // Rethrow any SQL errors as an authentication exception
            throw new AuthenticationException(message, e);
        }
        finally
        {
            JdbcUtils.closeConnection(conn);
        }
        return info;
    }

    private String[] getPasswordForUser(Connection conn, String username) throws SQLException
    {
        String[] result;
        boolean returningSeparatedSalt = false;
        switch (saltStyle)
        {
            case NO_SALT:
            case CRYPT:
            case EXTERNAL:
                result = new String[1];
                break;
            default:
                result = new String[2];
                returningSeparatedSalt = true;
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            ps = conn.prepareStatement(authenticationQuery);
            ps.setString(1, username);
            // Execute query
            rs = ps.executeQuery();
            // Loop over results - although we are only expecting one result, since usernames should be unique
            boolean foundResult = false;
            while (rs.next())
            {
                // Check to ensure only one row is processed
                if (foundResult)
                {
                    throw new AuthenticationException("More than one user row found for user [" + username + "]. Usernames must be unique.");
                }
                result[0] = rs.getString(1);
                if (returningSeparatedSalt)
                {
                    result[1] = rs.getString(2);
                }
                foundResult = true;
            }
        }
        finally
        {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(ps);
        }
        return result;
    }

    @Override
    protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principals)
    {
        //null usernames are invalid
        if (principals == null)
        {
            throw new AuthorizationException("PrincipalCollection method argument cannot be null.");
        }
        String username = (String) getAvailablePrincipal(principals);
        Connection conn = null;
        Set<String> roleNames = null;
        Set<String> permissions = null;
        try
        {
            conn = dataSource.getConnection();
            // Retrieve roles and permissions from database
            roleNames = getRoleNamesForUser(conn, username);
            if (permissionsLookupEnabled)
            {
                permissions = getPermissions(conn, username, roleNames);
            }
        }
        catch (SQLException e)
        {
            final String message = "There was a SQL error while authorizing user [" + username + "]";
            if (log.isErrorEnabled())
            {
                log.error(message, e);
            }
            // Rethrow any SQL errors as an authorization exception
            throw new AuthorizationException(message, e);
        }
        finally
        {
            JdbcUtils.closeConnection(conn);
        }
        SimpleAuthorizationInfo info = new SimpleAuthorizationInfo(roleNames);
        info.setStringPermissions(permissions);
        return info;
    }

    protected Set<String> getRoleNamesForUser(Connection conn, String username) throws SQLException
    {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Set<String> roleNames = new LinkedHashSet<String>();
        try
        {
            ps = conn.prepareStatement(userRolesQuery);
            ps.setString(1, username);
            // Execute query
            rs = ps.executeQuery();
            // Loop over results and add each returned role to a set
            while (rs.next())
            {
                String roleName = rs.getString(1);
                // Add the role to the list of names if it isn't null
                if (roleName != null)
                {
                    roleNames.add(roleName);
                }
                else
                {
                    if (log.isWarnEnabled())
                    {
                        log.warn("Null role name found while retrieving role names for user [" + username + "]");
                    }
                }
            }
        }
        finally
        {
            JdbcUtils.closeResultSet(rs);
            JdbcUtils.closeStatement(ps);
        }
        return roleNames;
    }

    protected Set<String> getPermissions(Connection conn, String username, Collection<String> roleNames) throws SQLException
    {
        PreparedStatement ps = null;
        Set<String> permissions = new LinkedHashSet<>();
        try
        {
            ps = conn.prepareStatement(permissionsQuery);
            for (String roleName : roleNames)
            {
                ps.setString(1, roleName);
                ResultSet rs = null;
                try
                {
                    // Execute query
                    rs = ps.executeQuery();
                    // Loop over results and add each returned role to a set
                    while (rs.next())
                    {
                        String permissionString = rs.getString(1);
                        // Add the permission to the set of permissions
                        permissions.add(permissionString);
                    }
                }
                finally
                {
                    JdbcUtils.closeResultSet(rs);
                }
            }
        }
        finally
        {
            JdbcUtils.closeStatement(ps);
        }
        return permissions;
    }

    protected String getSaltForUser(String username)
    {
        return username;
    }
}

conn = dataSource.getConnection();