Max number of values in IN clause

Somebody asked me about this a few days ago, and this person (really sure of what he was talking about) told me: "You can only pass a limit of 200 items in a IN clause (during a DELETE)"

I'm not an Expert DBA (know it all), but I do remember that you can pass up to 1000 elements. And that was my counter argument to this guy (as always if you don't believe me double check with your best friend or test it
In the past it used to be 256 elements, but that was in Oracle 8.

Add Token Based Authentication on Spring Security

Problem I have an application which has already implemented Basic Authentication using Spring Security.
Now as part of a new requirement I have to enable a Token based authentication as well.
My first task was to updgrade to Spring 3.0.7 and to Spring Security 3.1.3 among other dependicies that needed an update as well.
After that, let's go to the meaty part of this implementation :)
While researching I couldn't find any example, only pieces, that's why I'm publishing and sharing my findings with all the community:

At least I knew that I had to implement another Authentication Provider (that's why I needed to Upgrade my App) and something to catch the event of login and tell Spring to perform another authentication (like a Filter, maybe?)

This is my implementation: Spring's config <security:http auto-config="true" access-denied-page="/login" authentication-manager-ref="defaultAuthenticationManager"> <security:intercept-url pattern="/login" access="IS_AUTHENTICATED_ANONYMOUSLY"/> <security:intercept-url pattern="/logout.do" access="IS_AUTHENTICATED_ANONYMOUSLY"/> <security:intercept-url pattern="/**/*.gif" access="IS_AUTHENTICATED_ANONYMOUSLY"/> <security:custom-filter ref="kaplanCustomOncePerRequestFilterFilter" before="PRE_AUTH_FILTER"/> <security:intercept-url pattern="/3.0/onlineprofile" access="ROLE_ktp_user"/> <security:intercept-url pattern="/**" access="ROLE_ktp_user, ROLE_System User"/> <security:form-login authentication-failure-url="/login" default-target-url="/3.0/services" login-page="/login" login-processing-url="/login.do"/> <security:logout logout-success-url="/login" logout-url="/logout.do"/> </security:http> <security:authentication-manager id="defaultAuthenticationManager" alias="authenticationManager"> <security:authentication-provider user-service-ref="kaplanUserDetailsService"> <security:password-encoder ref="kaplanPasswordEncoder"/> </security:authentication-provider> <security:authentication-provider user-service-ref="kaplanOAuth2UserDetailsService"/> </security:authentication-manager> ... <bean id="kaplanOAuth2UserDetailsService" class="kaplan.ktpa.core.auth.oauth2.KaplanOAuth2UserDetailsService"/> <bean id="kaplanCustomOncePerRequestFilterFilter" class="kaplan.ktpa.core.auth.filter.KaplanCustomOncePerRequestFilterFilter"> <property name="accessToken" value="Authentication"/><!-- We can set this in a property --> <property name="tokenValidator" ref="pingIdentityTokenValidator"/> </bean> <bean id="pingIdentityTokenValidator" class="kaplan.ktpa.core.auth.ping.PingIdentityTokenValidatorImpl"> <property name="clientId" value="${security.ping.clientid}"/> <property name="clientSecret" value="${security.ping.clientsecret}"/> <property name="pingUrl" value="${security.ping.url}"/> </bean>
PingIdentityTokenValidatorImpl (Validates Given token) public class PingIdentityTokenValidatorImpl implements TokenValidator{ private static final Logger log = Logger .getLogger(PingIdentityTokenValidatorImpl.class); public static final String PROFILE_ID = "profileId"; public static final String TOKEN_PREFIX = "Bearer "; private String pingUrl; private String clientId; private String clientSecret; public String validateToken(final String accessTokenParam) { log.debug("Token with prefix="+accessTokenParam); String accessToken = StringUtils.substringAfterLast(accessTokenParam, TOKEN_PREFIX); log.debug("Token="+accessToken); String postData = "grant_type=urn:pingidentity.com:oauth2:grant_type:validate_bearer&token=" + accessToken; String result = ""; URL url = null; String profileId = null; try { log.debug("Posting to "+pingUrl); url = new URL(pingUrl); String authInfo = clientId + ":" + clientSecret; log.debug("authInfo="+authInfo); byte[] authinfobytes = authInfo.getBytes(); authInfo = new String(Base64.encodeBase64(authinfobytes)); URLConnection conn = url.openConnection(); conn.setDoOutput(true); conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded"); conn.setRequestProperty("Authorization", "Basic " + authInfo); conn.setRequestProperty("Content-Length", String.valueOf(postData.length())); conn.setRequestProperty("grant_type", "urn:pingidentity.com:oauth2:grant_type:validate_bearer"); conn.setRequestProperty("token", accessToken); OutputStreamWriter outputStreamWriter = new OutputStreamWriter( conn.getOutputStream()); outputStreamWriter.write(postData); outputStreamWriter.flush(); BufferedReader bufferedReader = new BufferedReader(new InputStreamReader( conn.getInputStream())); StringBuilder stringBuilder = new StringBuilder(); String aux = ""; while ((aux = bufferedReader.readLine()) != null) { stringBuilder.append(aux); } result = stringBuilder.toString(); log.debug("result="+result); if (result.contains(PROFILE_ID)) { log.debug("result contains profileId"); profileId = StringUtils.substringBetween(result, "\""+PROFILE_ID+"\":\"","\"}}"); //profileId = result.substring(result.indexOf("\""+PROFILE_ID+"\":\"") + 13, result.length() - 3); } outputStreamWriter.close(); bufferedReader.close(); } catch (MalformedURLException e) { log.warn("MalformedURLException while validating Token (noPrefix)=" + accessToken, e); } catch (IOException e) { log.warn("IOException while validating Token (noPrefix)=" + accessToken, e); } catch (Exception ex){ log.error("Error while validating Token (noPrefix)=" + accessToken, ex); } log.debug("profileId="+profileId); return profileId; } }
KaplanOAuth2UserDetailsService (Performs Authentication, generates user following spring security standards) public class KaplanOAuth2UserDetailsService implements UserDetailsService { private static final Logger log = Logger .getLogger(KaplanOAuth2UserDetailsService.class); @Override public UserDetails loadUserByUsername(String profileId) throws UsernameNotFoundException { log.debug("Loading profileId="+profileId); KaplanUser kaplanUser = new KaplanUser(profileId, profileId, true, true, true, true, this.grantedAuthorities(), 1L); log.debug("Returning kaplanUser="+kaplanUser); return kaplanUser; } private List<GrantedAuthority> grantedAuthorities() { List<GrantedAuthority> authorities = new ArrayList<GrantedAuthority>(); authorities.add(new SimpleGrantedAuthority("ROLE_"+ KaplanCustomAuthProcessingFilter.OAUTH_ROLE)); return authorities; } }
KaplanCustomOncePerRequestFilterFilter (This filter does the trick) Just add it to your security configuration in the right place of the chain of execution and you're set public class KaplanCustomOncePerRequestFilterFilter extends OncePerRequestFilter { private static final Logger log = Logger .getLogger(KaplanCustomOncePerRequestFilterFilter.class); private String accessToken; //Now looks like a RequestHeaderAuthFilter @Autowired private KaplanOAuth2UserDetailsService authUserDetailsService; @Autowired private TokenValidator tokenValidator; @Override protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) throws ServletException, IOException { String authToken = null; UsernamePasswordAuthenticationToken authRequest = null; Authentication authentication = SecurityContextHolder.getContext() .getAuthentication(); if (authentication != null) { authToken = authentication.getName(); } if (StringUtils.isNotBlank(request.getHeader(this.accessToken))) { authToken = request.getHeader(this.accessToken); String profileId = this.tokenValidator.validateToken(authToken); if (profileId == null) { throw new VerificationFailedException("Unable to authenticate using token " + authToken); } List<GrantedAuthority> authorities = new ArrayList<GrantedAuthority>(); authorities.add(new SimpleGrantedAuthority("ROLE_"+KaplanCustomAuthProcessingFilter.OAUTH_ROLE)); authRequest = new UsernamePasswordAuthenticationToken(profileId, profileId, authorities); HttpSession session = request.getSession(false); if (session != null) { request.getSession().setAttribute(KaplanCustomAuthProcessingFilter.SPRING_SECURITY_LAST_USERNAME, TextEscapeUtils.escapeEntities(authToken)); request.getSession().setAttribute(KaplanCustomAuthProcessingFilter.SPRING_SECURITY_SAVED_REQUEST, new DefaultSavedRequest(request, new PortResolverImpl())); } Object details = authUserDetailsService.loadUserByUsername(profileId); authRequest.setDetails(details); SecurityContextHolder.getContext().setAuthentication(authRequest); } filterChain.doFilter(request, response); } Although, my original approach was to extend from AbstractAuthenticationProcessingFilter, however I saw it being invoked multiple times per request (it didn't bother me), but that wasn't good enough for me, also it was more complex :P public class KaplanCustomAuthProcessingFilter extends AbstractAuthenticationProcessingFilter { private static final Logger log = Logger .getLogger(KaplanCustomAuthProcessingFilter.class); private static final String DEFAULT_FILTER_PROCESSES_URL = "/login"; private static final String POST = "POST"; public static final String OAUTH_ROLE = "ktp_user"; public static final String SPRING_SECURITY_LAST_USERNAME = "SPRING_SECURITY_LAST_USERNAME"; public static final String SPRING_SECURITY_SAVED_REQUEST = "SPRING_SECURITY_SAVED_REQUEST"; private String accessToken; //Now looks like a RequestHeaderAuthFilter @Autowired private AuthenticationManager authenticationManager; @Autowired private KaplanOAuth2UserDetailsService authUserDetailsService; @Autowired private TokenValidator tokenValidator; public KaplanCustomAuthProcessingFilter() { super(DEFAULT_FILTER_PROCESSES_URL); super.setAuthenticationManager(authenticationManager); super.setAuthenticationDetailsSource(authenticationDetailsSource); this.setTokenValidator(tokenValidator); } public void doFilter(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) throws IOException, ServletException { if (request.getMethod().equals(POST)) { log.debug("doFilter Method POST"); super.doFilter(request, response, filterChain); } else { log.debug("doFilter Method GET OR OTHER - not POST"); filterChain.doFilter(request, response); } } @Override protected boolean requiresAuthentication(final HttpServletRequest request, final HttpServletResponse response) { boolean result = false; if (StringUtils.isNotBlank(request.getHeader(this.accessToken))) { result = true; } log.debug("requiresAuthentication? "+result); return result; } @Override protected void successfulAuthentication(HttpServletRequest request, HttpServletResponse response, FilterChain chain, Authentication authResult) throws IOException, ServletException { log.debug("Authentication was successful"); super.successfulAuthentication(request, response, chain, authResult); } @Override public Authentication attemptAuthentication(HttpServletRequest request, HttpServletResponse response) throws AuthenticationException, IOException, ServletException { String authToken = null; UsernamePasswordAuthenticationToken authRequest = null; Authentication authentication = SecurityContextHolder.getContext() .getAuthentication(); if (authentication != null) { authToken = authentication.getName(); } if (StringUtils.isNotBlank(request.getHeader(this.accessToken))) { authToken = request.getHeader(this.accessToken); String profileId = this.tokenValidator.validateToken(authToken); if (profileId == null) { throw new VerificationFailedException("Unable to authenticate using token " + authToken); } List<GrantedAuthority> authorities = new ArrayList<GrantedAuthority>(); authorities.add(new SimpleGrantedAuthority("ROLE_"+OAUTH_ROLE)); authRequest = new UsernamePasswordAuthenticationToken(profileId, profileId, authorities); HttpSession session = request.getSession(false); if (session != null || getAllowSessionCreation()) { request.getSession().setAttribute(SPRING_SECURITY_LAST_USERNAME, TextEscapeUtils.escapeEntities(authToken)); request.getSession().setAttribute(SPRING_SECURITY_SAVED_REQUEST, new DefaultSavedRequest(request, new PortResolverImpl())); } Object details = authUserDetailsService.loadUserByUsername(profileId); authRequest.setDetails(details); return authRequest; } else throw new VerificationFailedException("Unable to authenticate; missing token value"); } }