thanks for your suggestions,
I started to dig around in the code, and also looked at a jaas login module from jboss.
the jboss solution is to have a login module that takes two parameters (queries)
1) to return credentials for a username 2) to return the rolenames for a username
this is exactly what JDBCRealm and DataSourceRealm do except they construct the query Strings from parameters
StringBuffer sb = new StringBuffer("SELECT ");
sb.append(userCredCol);
sb.append(" FROM ");
sb.append(userTable);
sb.append(" WHERE ");
sb.append(userNameCol);
sb.append(" = ?");
preparedCredentials = dbConnection.prepareStatement(sb.toString());
StringBuffer sb = new StringBuffer("SELECT ");
sb.append(roleNameCol);
sb.append(" FROM ");
sb.append(userRoleTable);
sb.append(" WHERE ");
sb.append(userNameCol);
sb.append(" = ?");
preparedRoles =
dbConnection.prepareStatement(sb.toString());Unfortunately the methods that construct these strings are private so I can't simply override them. I have hacked around with the code and produced my own security realm class that works.
My solution takes the following config parameters:
credentialsQuery="SELECT password FROM User WHERE emailAddress =?"
rolesQuery="SELECT name FROM Role r, User u, user_roles ur WHERE u.id = ur.user_id AND r.id = ur.role_id AND u.emailAddress = ?"
I think with a bit of refactoring the existing DB realm classes would support this enabling any db structure (without the need for a view).
cheers Nathan
Nathan Coast wrote:
Hi,
I have the following db structure for my user / role tables:
User User-Role Role ---------- ----------- ---------- id ------ user_id username rold_id ------- id password rolename
Is there a realm implementation that support this structure?
AFAICT, the JDBC and DataSourceRealm classes require the following structure:
User User-Role ---------- ----------- username ------ username password role_name
cheers Nathan
-- Nathan Coast Managing Director Codeczar Ltd mob : (852) 9049 5581 tel : (852) 2834 8733 fax : (852) 2834 8755 web : http://www.codeczar.com
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
