spring security 权限分配问题

Question

我在测试的项目上引进了spring security 基于名称空间配置的安全架构，遇到一个问题，请指教下！

配置代码如下：

<!---->



<beans:beans xmlns="http://www.springframework.org/schema/security" xmlns:beans="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-2.0.xsd">

&lt;global-method-security secured-annotations="enabled"&gt;
&lt;/global-method-security&gt;

&lt;http access-denied-page="/error.jsp" session-fixation-protection="newSession" auto-config="true" path-type="ant"&gt;<!---->
   <!---->        
    &lt;intercept-url pattern="/acegiTest.do" access="ROLE_SUPERVISOR"&gt;        
    &lt;intercept-url pattern="/index.jsp" access="IS_AUTHENTICATED_REMEMBERED"&gt;
    &lt;intercept-url pattern="/role1.jsp" access="ROLE_USER_1"&gt;
    &lt;intercept-url pattern="/role2.jsp" access="ROLE_USER_2"&gt;
    &lt;intercept-url pattern="/role3.jsp" access="ROLE_USER_3"&gt;
    &lt;intercept-url pattern="/**" access="IS_AUTHENTICATED_ANONYMOUSLY"&gt;
    <!---->

    <!---->
    &lt;form-login login-page="/login.jsp" default-target-url="/index.jsp" authentication-failure-url="/login.jsp?login_error=1"&gt;
    &lt;anonymous key="cookie_key" username="ananoymous" granted-authority="IS_AUTHENTICATED_ANONYMOUSLY"&gt;        
    &lt;logout invalidate-session="true"&gt;
    <!---->             
    &lt;concurrent-session-control max-sessions="1" exception-if-maximum-exceeded="false"&gt;<!---->
&lt;/http&gt;   

&lt;authentication-provider&gt; 
         &lt;password-encoder hash="md5"&gt;<!---->    
    &lt;jdbc-user-service data-source-ref="f3CenterDS" users-by-username-query="select name as 'username',password,'true' from users where name = ?" authorities-by-username-query="select name as 'username',authorities as 'authority' from authentication where name =?"&gt;     

&lt;/authentication-provider&gt;


</beans:beans>

在代码中

authorities-by-username-query="select name as 'username',authorities as 'authority' from authentication where name =?" 

，如果数据库里分配多个权限给用户的话，用户登入系统就不能正常使用（不能访问），倘若只分配一个权限则可以正常访问，还请指教一二！！！


问题补充
如果我把<jdbc-user-service>放在缓存中，则不会出现这样的问题了！

如下

<user-service>

            <user name="user" password="123" authorities="ROLE_SUPERVISOR, ROLE_USER_1">

        </user-service> 

问题补充：
select name as 'username',authorities as 'authority' from authentication where name =?



查询结果没有错，，，

返回如下：

user1    ROLE_USER_1, ROLE_USER_2

Answer 1

应该是你权限表结构的问题。你是用单个字段来表示多个权限吧。这字段内容是用 "," 逗号分隔开的字符串。
authorities-by-username-query="select name as 'username',authorities as 'authority' from authentication where name =?"

这个你只返回一条记录。“user1 ROLE_USER_1, ROLE_USER_2 ”。
这是问题所在。
spring security 默认的 authorities-by-username-query是
"SELECT username,authority FROM authorities WHERE username = ?";
正常是类似返回
user1 ROLE_USER_1
user1 ROLE_USER_2
多条记录集。
权限表应该类似如下结构
create table "authorities" (
"username" varchar2(32),
"authority" VARCHAR2(32) not null
);
alter table "authorities"
add constraint FK_AUTHORIT_REF_USERS_USERNAME foreign key ("username")
references "users" ("username");