Hi pramanik,
This problem is because the sql shiro used is invalid. It's not enought to
change authenticationQuery only. You have to change authenticationQuery
userRolesQuery permissionsQuery both. Here is source code of jdbcRealm:
/** * The default query used to retrieve account data for the user.
*/ protected static final String DEFAULT_AUTHENTICATION_QUERY = "select
password from users where username = ?"; /** * The default query used
to retrieve the roles that apply to a user. */ protected static final
String DEFAULT_USER_ROLES_QUERY = "select role_name from user_roles where
username = ?"; /** * The default query used to retrieve permissions
that apply to a particular role. */ protected static final String
DEFAULT_PERMISSIONS_QUERY = "select permission from roles_permissions where
role_name = ?";
I suggest you create users, user_roles, roles_permissions like jdbcRealm
default sql required. After you test successful with them, then you try to
change them step by step.
------------------ Original ------------------
From: "j_pramanik_ind";<[email protected]>;
Date: Wed, Aug 13, 2014 09:04 PM
To: "dev"<[email protected]>;
Subject: SQLException appears during Login with Token
Hi,
I'm new with apache shiro and currently working with jdbcRealm. But during
login with token an SQL Exception is showing as below -
* org.apache.shiro.authc.AuthenticationException: There was a SQL error
while authenticating user [nnnnnnnn]
18:08:47,738 ERROR [stderr] (http-localhost-127.0.0.1-8443-1) at
org.apache.shiro.realm.jdbc.JdbcRealm.doGetAuthenticationInfo(JdbcRealm.java:254)
18:08:47,748 ERROR [stderr] (http-localhost-127.0.0.1-8443-1) at
org.apache.shiro.realm.AuthenticatingRealm.getAuthenticationInfo(AuthenticatingRealm.java:568)
....
Caused by: java.sql.SQLException: Invalid column index
....
*
My SHIRO.INI is as below -
[main]
# Own Realm
jdbcRealm = org.apache.shiro.realm.jdbc.JdbcRealm
jdbcRealm.permissionsLookupEnabled = true
# datasource
ds = oracle.jdbc.pool.OracleConnectionPoolDataSource
ds.URL = jdbc:oracle:thin:@192.168.2.10:1522:WBORCLSTDONE
ds.user = WISENPA
ds.password = issac123
jdbcRealm.dataSource = $ds
jdbcRealm.authenticationQuery = "SELECT PASSWORD FROM WB_NETB_USER_MASTER
WHERE LOGINID = ?"
[users]
[roles]
[urls]
# enable authc filter for all application pages
/InternetBanking_v1/**=authc
I have written one REST service where values have been checked and login
with token has been tried.
Query query = entityManager.createQuery("SELECT userLogin FROM UserLogin
userLogin where userLogin.loginid=:loginid and userLogin.password=:passwd" )
.setParameter("loginid", login.getLoginid())
.setParameter("passwd",hashedPasswordBase64);
loggedInUser = (UserLogin) query.getSingleResult();
if (loggedInUser==null){
loggedInUser = new UserLogin();
}
//---- Creating Token
UsernamePasswordToken token = new
UsernamePasswordToken(StrLoginId, hashedPasswordBase64);
token.setRememberMe(true);
SecurityUtils.setSecurityManager(sm);
Subject currentUser = SecurityUtils.getSubject();
try {
currentUser.login(token); ////// HERE ERROR APPEARS
System.out.println("----- Login Success -----");
} catch (IncorrectCredentialsException ice) {
System.out.println("Incorrect username/password!");
}
Please help me if there are any configurational issue in shiro.ini file or
if any other flaws in code -
Many thanks,
Jayanta P.
--
View this message in context:
http://shiro-developer.582600.n2.nabble.com/SQLException-appears-during-Login-with-Token-tp7578433.html
Sent from the Shiro Developer mailing list archive at Nabble.com.
