春季安全登录不能与oracle一起工作 [英] spring security login not working with oracle

问题描述

我正在使用jdbc-user-service进行spring安全登录。它正在使用Mysql，但不在oracle上。我使用的是oracle 11 XE。

我在标记中提到的查询是正确的，因为那些在Oracle控制台上运行时会返回预期的行。
$ b

数据源为：

 < bean id =dataSource
 class = org.springframework.jdbc.datasource.DriverManagerDataSource > 
< property name =driverClassNamevalue =$ {jdbc.driver}/> 
< property name =urlvalue =$ {datasource.url}/> 
< property name =usernamevalue =$ {jdbc.user}/> 
< property name =passwordvalue =$ {jdbc.password}/> 
< / bean> 
  

在sessionFactory bean中给出了hibernate的oracle方言。

 < prop key =hibernate.dialect> org.hibernate.dialect.Oracle10gDialect< / prop> 
  

相同的代码适用于mysql，不适用于oracle。应用程序控件被重定向到我在spring安全文件中设置的登录页面。这意味着它正在获取用户名&

 < form-login login-page =/ landingdefault-target-url =/ home 
 authentication-failure-url =/ landing？msg = cw/> 
  

Spring Security xml中的身份验证提供程序代码为：

 < authentication-manager> 
< authentication-provider> 
  users-by-username-query =
选择USERNAME作为用户名，PASSWORD作为密码，'true'作为从USERNAME =？的
用户启用
 
 authority-by-username-query =select u.USERNAME 
 as username，r.ROLE as authority from users u，roles r，user_roles 
 ur where u.USER_ID = ur.USER_ID和ur.ROLE_ID = r.id和u.USER_ID =（选择
来自USERNAME =？的用户的USER_ID）/> 
 
< / authentication-provider> 
< / authentication-manager> 
  

有什么需要配置才能在spring安全中使用oracle？

更新：我设置了spring安全调试，这是我尝试登录后打印的内容。

  INFO：Spring安全调试器 -  
 
 ********************************* *************************** 
 
收到'/ j_spring_security_check'的请求：
 
 org.apache.catalina.connector.RequestFacade@7262b6 
 
 servletPath：/ j_spring_security_check 
 pathInfo：null 
 
安全过滤器链：[
 ConcurrentSessionFilter 
 SecurityContextPersistenceFilter 
 LogoutFilter 
 UsernamePasswordAuthenticationFilter 
asicAuthenticationFilter 
 RequestCacheAwareFilter 
 SecurityContextHolderAwareRequestFilter 
 AnonymousAuthenticationFilter 
 SessionManagementFilter 
 ExceptionTranslationFilter 
 FilterSecurityInterceptor 
] 
 
 
 ****** ************************************************** **** 
 
 
信息：Spring Security Debugger  -  
 
 ******************** **************************************** 
 
收到的请求for'/ landing？msg = cw'：
 
 org.apache.catalina.connector.RequestFacade@bf1a4a 
 
 servletPath：/ landing 
 pathInfo：null 
 
安全过滤器链：[
 ConcurrentSessionFilter 
 SecurityContextPersistenceFilter 
 LogoutFilter 
 UsernamePasswordAuthenticationFilter 
asicAuthenticationFilter 
 RequestCacheAwareFilter 
 SecurityContextHolderAwareRequestFilter 
 AnonymousAuthenticationFilter 
 SessionManagementFilter 
 ExceptionTranslationFilter 
 FilterSecurityInterceptor 
] 
 
 
 ***************** ******************************************* 
  

解决方案

我遇到同样的问题，经过几小时的研究，我找到了解决方案。 Spring安全性的用户详细信息需要启用布尔值并且Oracle上的true不起作用。我解决了这个问题：

  users-by-username-query =
选择USERNAME作为用户名，PASSWORD as密码，'true'从
用户所在的USERNAME =？ 
  

至此：

  users-by-username-query =
选择USERNAME作为用户名，PASSWORD作为密码，1从
启用，其中USERNAME =？ 
  

它有效！我发现解决方案阅读此处。

I am working on spring security login using jdbc-user-service. It is working on Mysql but not on oracle. I am using oracle 11 XE.

The queries I have mentioned in tag are correct because those return expected rows when I run those on oracle console.

The datasource is :

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driver}" />
    <property name="url" value="${datasource.url}" />
    <property name="username" value="${jdbc.user}" />
    <property name="password" value="${jdbc.password}" />
</bean>

in sessionFactory bean hibernate dialect for oracle is given.

<prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>

Same code works for mysql and not on oracle. The application control is redirected to login page which I have set in spring security file. This means it is getting username & password correctly.

<form-login login-page="/landing" default-target-url="/home"
        authentication-failure-url="/landing?msg=cw" />

The authentication provider code in spring security xml is :

<authentication-manager>
    <authentication-provider>
    <jdbc-user-service data-source-ref="dataSource"
            users-by-username-query=" 
    select USERNAME as username, PASSWORD as password,'true' as enabled from 
    users where USERNAME=?"

            authorities-by-username-query=" select u.USERNAME 
    as username, r.ROLE as authority from users u, roles r, user_roles 
    ur where u.USER_ID = ur.USER_ID and ur.ROLE_ID = r.id and u.USER_ID = (SELECT 
    USER_ID from users where USERNAME = ?)" />

    </authentication-provider>
</authentication-manager>

Is there anything need to be configured in order to use oracle in spring security ?

UPDATE : I set spring security debug and this is what is printing after I try to login.

INFO : Spring Security Debugger - 

************************************************************

Request received for '/j_spring_security_check':

org.apache.catalina.connector.RequestFacade@7262b6

servletPath:/j_spring_security_check
pathInfo:null

  Security filter chain: [
  ConcurrentSessionFilter
  SecurityContextPersistenceFilter
  LogoutFilter
  UsernamePasswordAuthenticationFilter
  BasicAuthenticationFilter
  RequestCacheAwareFilter
  SecurityContextHolderAwareRequestFilter
  AnonymousAuthenticationFilter
  SessionManagementFilter
  ExceptionTranslationFilter
  FilterSecurityInterceptor
]


************************************************************


INFO : Spring Security Debugger - 

************************************************************

Request received for '/landing?msg=cw':

org.apache.catalina.connector.RequestFacade@bf1a4a

servletPath:/landing
pathInfo:null

Security filter chain: [
ConcurrentSessionFilter
SecurityContextPersistenceFilter
LogoutFilter
UsernamePasswordAuthenticationFilter
BasicAuthenticationFilter
RequestCacheAwareFilter
SecurityContextHolderAwareRequestFilter
AnonymousAuthenticationFilter
SessionManagementFilter
ExceptionTranslationFilter
FilterSecurityInterceptor
]


************************************************************

解决方案

I had the same issue and after hours of research i found the solution. The User Details of spring security wants a boolean value on enabled and 'true' on Oracle doesn't work. I fix it replacing this:

users-by-username-query=" 
select USERNAME as username, PASSWORD as password,'true' as enabled from 
users where USERNAME=?"

To this:

users-by-username-query=" 
select USERNAME as username, PASSWORD as password,1 as enabled from 
users where USERNAME=?"

It works!. I found the solution reading here.

这篇关于春季安全登录不能与oracle一起工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！