我正在使用Spring MVC+Security和Oracle 10g。当我尝试进行身份验证时,出现以下错误:
Error : PreparedStatementCallback; invalid ResultSet access for SQL [SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getUser(?) FROM DUAL]; nested exception is java.sql.SQLException: Invalid column index
安全性.xml:
<authentication-manager>
<authentication-provider>
<jdbc-user-service data-source-ref="dataSource"
users-by-username-query=
"SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getUser(?) FROM DUAL"
authorities-by-username-query=
"SELECT PAYGATEMANAGER.AUTHENTICATION_PKG.getAuthorities(?) FROM DUAL" />
</authentication-provider>
</authentication-manager>
PL/SQL:
CREATE OR REPLACE PACKAGE PAYGATEMANAGER.AUTHENTICATION_PKG AS
TYPE T_REFCURSOR IS REF CURSOR;
FUNCTION getUser(username IN VARCHAR2) RETURN T_REFCURSOR;
FUNCTION getAuthorities(username IN VARCHAR2) RETURN T_REFCURSOR;
END AUTHENTICATION_PKG;
CREATE OR REPLACE PACKAGE BODY PAYGATEMANAGER.AUTHENTICATION_PKG AS
FUNCTION getUser(username IN VARCHAR2)
RETURN T_REFCURSOR IS
userInfo T_REFCURSOR;
BEGIN
OPEN userInfo FOR
SELECT
U.NAME AS username ,
P.PASSWORD AS password,
'true' AS enabled
FROM
PAYGATEMANAGER.USERS U INNER JOIN PAYGATEMANAGER.PASSWORDS P
USING(USER_ID)
WHERE
U.NAME = username;
RETURN userInfo;
END;
FUNCTION getAuthorities(username IN VARCHAR2)
RETURN T_REFCURSOR IS
userAuthorities T_REFCURSOR;
BEGIN
OPEN userAuthorities FOR
SELECT
U.NAME AS username ,
UR.ROLE AS authorities
FROM
PAYGATEMANAGER.USERS U INNER JOIN PAYGATEMANAGER.USERS_ROLES UR
USING(USER_ID)
WHERE
U.NAME = username;
RETURN userAuthorities;
END;
END AUTHENTICATION_PKG;
我认为原因是返回类型T_REFCURSOR,它没有获取用户名,密码,启用。但是如何解决这个问题呢?为了将来添加一些功能(例如登录尝试审核),我认为我应该在 PL/SQL 函数中执行此操作,而不是在 SQL 中进行硬编码。
绝对不适用于"开箱即用"的JdbcDaoImpl(这是<jdbc-user-service>
映射到的)。我建议查看标准实现的源代码,然后编写自己的实现 Spring 安全UserDetailsService
,但在幕后使用 JDBC 和 CallableStatement
。
您将实现类声明为常规的 Spring Bean:
<bean id="MyPlsqlUserDetailsService" class="my.great.JdbcCallableStatementImpl"/>
。然后在<user-details-service>
上使用 ref
属性,如下所示:
<user-details-service ref="MyPlsqlUserDetailsService"/>
(请注意,我省略了XML命名空间等的复杂性,希望您理解这些)。
祝你好运!