The code listed below gives you user, role and group with many to many between all 3
entities.
The tables are:
CREATE TABLE SYS_ROLE (
NAME varchar(30),
ROLE_GROUP` varchar(30),
NOTE varchar(255),
PRIMARY KEY (NAME)
)
CREATE TABLE SYS_ROLE_SYS_GROUP_LINK(
SYS_GROUP_NAME varchar(30),
SYS_ROLE_NAME varchar(30),
PRIMARY KEY (SYS_GROUP_NAME,SYS_ROLE_NAME)
)
CREATE TABLE SYS_USER (
USERNAME varchar(30),
PASSWORD varchar(30),
CONTACT_DETAIL_ID int(11),
ACTIVE tinyint(1) NOT NULL,
NOTE varchar(255),
PRIMARY KEY (USERNAME)
)
CREATE TABLE SYS_USER_SYS_GROUP_LINK (
SYS_GROUP_NAME` varchar(30),
SYS_USER_USERNAME` varchar(30),
PRIMARY KEY (SYS_GROUP_NAME,SYS_USER_USERNAME)
)
CREATE TABLE SYS_USER_SYS_ROLE_LINK (
SYS_ROLE_NAME varchar(30),
SYS_USER_USERNAME varchar(30),
PRIMARY KEY (SYS_ROLE_NAME,SYS_USER_USERNAME)
)
CREATE TABLE SYS_GROUP (
`NAME` varchar(30),
`NOTE` varchar(255),
PRIMARY KEY (NAME)
)
If you put "DatabaseServerLoginModule" in Google, the first hit will give you the
source code of the base class to extend. Analyse this source
code first and after that you can go ahead with:
package org.jboss.security.auth.spi;
|
| import java.security.acl.Group;
| import java.util.HashMap;
| import java.util.Map;
| import java.sql.Connection;
| import java.sql.PreparedStatement;
| import java.sql.ResultSet;
| import java.sql.SQLException;
| import javax.naming.InitialContext;
| import javax.naming.NamingException;
| import javax.sql.DataSource;
| import javax.security.auth.Subject;
| import javax.security.auth.callback.CallbackHandler;
| import javax.security.auth.login.LoginException;
| import javax.security.auth.login.FailedLoginException;
| import org.jboss.security.SimpleGroup;
| import org.jboss.security.SimplePrincipal;
|
| public class DatabaseServerGroupLoginModule extends DatabaseServerLoginModule {
|
| private String dsJndiName = "java:/DefaultDS";
| private String userQuery = "SELECT PASSWORD FROM SYS_USER WHERE ID=?";
| private String roleQuery = "SELECT A.ID,A.ROLE_GROUP FROM SYS_ROLE
A,SYS_USER_SYS_ROLE_LINK B WHERE A.ID=B.SYS_ROLE_ID AND B.SYS_USER_ID=?";
| private String groupQuery = "SELECT A.ID,A.ROLE_GROUP FROM SYS_ROLE
A,SYS_USER_SYS_GROUP_LINK B,SYS_GROUP_SYS_ROLE_LINK C WHERE A.ID=C.SYS_ROLE_ID AND
B.SYS_GROUP_ID=C.SYS_GROUP_ID AND B.SYS_USER_ID=?";
|
| /**
| * Initialize this LoginModule.
| */
| public void initialize(Subject subject, CallbackHandler callbackHandler, Map
sharedState, Map options) {
| super.initialize(subject, callbackHandler, sharedState, options);
|
| Object tmp = options.get("dsJndiName");
| if (tmp != null) {
| dsJndiName = tmp.toString();
| }
|
| tmp = options.get("userQuery");
| if (tmp != null) {
| userQuery = tmp.toString();
| }
|
| tmp = options.get("roleQuery");
| if (tmp != null) {
| roleQuery = tmp.toString();
| }
|
| tmp = options.get("groupQuery");
| if (tmp != null) {
| groupQuery = tmp.toString();
| }
|
| log.trace("DatabaseServerLoginModule, dsJndiName=" + dsJndiName);
| log.trace("userQuery=" + userQuery);
| log.trace("roleQuery=" + roleQuery);
| log.trace("groupQuery=" + roleQuery);
| }
|
| /**
| * Get the expected password for the current username available via the
| * getUsername() method. This is called from within the login() method after
| * the CallbackHandler has returned the username and candidate password.
| *
| * @return the valid password String
| */
| protected String getUsersPassword() throws LoginException {
| String username = getUsername();
| String password = null;
| Connection conn = null;
| PreparedStatement ps = null;
|
| try {
| InitialContext ctx = new InitialContext();
| DataSource ds = (DataSource) ctx.lookup(dsJndiName);
| conn = ds.getConnection();
| ps = conn.prepareStatement(userQuery);
| ps.setString(1, username);
| ResultSet rs = ps.executeQuery();
| if (rs.next() == false) {
| throw new FailedLoginException("No matching username
found in SYS_USER");
| }
| password = rs.getString(1);
| password = convertRawPassword(password);
| rs.close();
| } catch (NamingException e) {
| throw new LoginException(e.toString(true));
| } catch (SQLException e) {
| log.error("Query failed", e);
| throw new LoginException(e.toString());
| } finally {
| if (ps != null) {
| try {
| ps.close();
| } catch (SQLException e) {
| }
| }
| if (conn != null) {
| try {
| conn.close();
| } catch (SQLException ex) {
| }
| }
| }
| return password;
| }
|
| /**
| * Overriden by subclasses to return the Groups that correspond to the to the
| * role sets assigned to the user. Subclasses should create at least a Group
| * named "Roles" that contains the roles assigned to the user. A second
| * common group is "CallerPrincipal" that provides the application identity
| * of the user rather than the security domain identity.
| *
| * @return Group[] containing the sets of roles
| */
| protected Group[] getRoleSets() throws LoginException {
| String username = getUsername();
| Connection conn = null;
| HashMap setsMap = new HashMap();
| PreparedStatement ps = null;
| boolean empty = false;
|
| try {
| InitialContext ctx = new InitialContext();
| DataSource ds = (DataSource) ctx.lookup(dsJndiName);
| conn = ds.getConnection();
| ps = conn.prepareStatement(roleQuery);
| ps.setString(1, username);
| ResultSet rs = ps.executeQuery();
| if (rs.next() != false) {
| do {
| String name = rs.getString(1);
| String groupName = rs.getString(2);
| if (groupName == null || groupName.length() ==
0)
| groupName = "Roles";
| Group group = (Group) setsMap.get(groupName);
| if (group == null) {
| group = new SimpleGroup(groupName);
| setsMap.put(groupName, group);
| }
| group.addMember(new SimplePrincipal(name));
| } while (rs.next());
| } else {
| empty = true;
| }
| rs.close();
| ps.close();
|
| ps = conn.prepareStatement(groupQuery);
| ps.setString(1, username);
| rs = ps.executeQuery();
| if (rs.next() != false) {
| do {
| String name = rs.getString(1);
| String groupName = rs.getString(2);
| if (groupName == null || groupName.length() ==
0)
| groupName = "Roles";
| Group group = (Group) setsMap.get(groupName);
| if (group == null) {
| group = new SimpleGroup(groupName);
| setsMap.put(groupName, group);
| }
| group.addMember(new SimplePrincipal(name));
| } while (rs.next());
| } else if (empty) {
| if (getUnauthenticatedIdentity() == null) {
| throw new FailedLoginException("No matching
username found in Groups");
| }
| Group[] roleSets = { new SimpleGroup("Roles")};
| return roleSets;
| }
| rs.close();
| } catch (NamingException ex) {
| throw new LoginException(ex.toString(true));
| } catch (SQLException ex) {
| super.log.error("SQL failure", ex);
| throw new LoginException(ex.toString());
| } finally {
| if (ps != null) {
| try {
| ps.close();
| } catch (SQLException e) {
| }
| }
| if (conn != null) {
| try {
| conn.close();
| } catch (Exception ex) {
| }
| }
| }
| Group[] roleSets = new Group[setsMap.size()];
| setsMap.values().toArray(roleSets);
| return roleSets;
| }
| }
The listed code can probably be improved, but it works for us.
You could skip the many to many between users and roles and only
make it possible to assign groups to users, depends on what flexability you want in
your admin interface.
Next two files describes the RMI Loginmodule for a jboss without access to the
database (i.e. web tier). Please forget about my implementation of
the jndi lookup and implement your own (would be another set of files).
package org.jboss.security.auth.spi;
|
| import java.rmi.RemoteException;
| import java.security.acl.Group;
| import java.sql.SQLException;
| import java.util.HashMap;
| import java.util.Map;
|
| import javax.naming.NamingException;
| import javax.security.auth.Subject;
| import javax.security.auth.callback.CallbackHandler;
| import javax.security.auth.login.FailedLoginException;
| import javax.security.auth.login.LoginException;
|
| import org.jboss.security.SimpleGroup;
| import org.jboss.security.auth.spi.intf.DatabaseServerRemoteLoginHome;
|
| import com.yonotech.commons.util.servicelocator.ServiceLocatorFactory;
| import com.yonotech.global.util.servicelocator.ServiceLocator;
|
| public class DatabaseServerRemoteLoginModule extends DatabaseServerLoginModule {
|
| private String dsJndiName = "java:/DefaultDS";
| private String userQuery = "SELECT PASSWORD FROM SYS_USER WHERE ID=?";
| private String roleQuery = "SELECT A.ID,A.ROLE_GROUP FROM SYS_ROLE
A,SYS_USER_SYS_ROLE_LINK B WHERE A.ID=B.SYS_ROLE_ID AND B.SYS_USER_ID=?";
| private String groupQuery = "SELECT A.ID,A.ROLE_GROUP FROM SYS_ROLE
A,SYS_USER_SYS_GROUP_LINK B,SYS_GROUP_SYS_ROLE_LINK C WHERE A.ID=C.SYS_ROLE_ID AND
B.SYS_GROUP_ID=C.SYS_GROUP_ID AND B.SYS_USER_ID=?";
|
| /**
| * Initialize this LoginModule.
| */
| public void initialize(Subject subject, CallbackHandler callbackHandler, Map
sharedState, Map options) {
| super.initialize(subject, callbackHandler, sharedState, options);
|
| Object tmp = options.get("dsJndiName");
| if (tmp != null) {
| dsJndiName = tmp.toString();
| }
|
| tmp = options.get("userQuery");
| if (tmp != null) {
| userQuery = tmp.toString();
| }
|
| tmp = options.get("roleQuery");
| if (tmp != null) {
| roleQuery = tmp.toString();
| }
|
| tmp = options.get("groupQuery");
| if (tmp != null) {
| groupQuery = tmp.toString();
| }
|
| log.trace("DatabaseServerLoginModule, dsJndiName=" + dsJndiName);
| log.trace("userQuery=" + userQuery);
| log.trace("roleQuery=" + roleQuery);
| log.trace("groupQuery=" + roleQuery);
| }
|
| private static org.jboss.security.auth.spi.intf.DatabaseServerRemoteLoginModule
getDatabaseServerRemoteLoginModule() throws Exception {
| try {
| return
((DatabaseServerRemoteLoginHome)ServiceLocatorFactory.getServiceLocator(ServiceLocator.class).getRemoteHome(ServiceLocator.DATABASE_SERVER_REMOTE_LOGIN_MODULE)).create();
| } catch (Exception e) {
| ServiceLocatorFactory.refreshAll();
| return
((DatabaseServerRemoteLoginHome)ServiceLocatorFactory.getServiceLocator(ServiceLocator.class).getRemoteHome(ServiceLocator.DATABASE_SERVER_REMOTE_LOGIN_MODULE)).create();
| }
| }
|
| /**
| * Get the expected password for the current username available via the
| * getUsername() method. This is called from within the login() method after
| * the CallbackHandler has returned the username and candidate password.
| *
| * @return the valid password String
| */
| protected String getUsersPassword() throws LoginException {
| try {
| return
convertRawPassword(getDatabaseServerRemoteLoginModule().getUsersPassword(dsJndiName,
userQuery, getUsername()));
| } catch (NamingException e) {
| throw new LoginException(e.toString(true));
| } catch (SQLException e) {
| super.log.error("SQL failure", e);
| throw new LoginException(e.toString());
| } catch (FailedLoginException e) {
| throw new LoginException(e.toString());
| } catch (RemoteException e) {
| throw new LoginException(e.toString());
| } catch (Exception e) {
| throw new LoginException(e.toString());
| }
| }
|
| /**
| * Overriden by subclasses to return the Groups that correspond to the to the
| * role sets assigned to the user. Subclasses should create at least a Group
| * named "Roles" that contains the roles assigned to the user. A second
| * common group is "CallerPrincipal" that provides the application identity
| * of the user rather than the security domain identity.
| *
| * @return Group[] containing the sets of roles
| */
| protected Group[] getRoleSets() throws LoginException {
| HashMap setsMap = null;
|
| try {
| setsMap = getDatabaseServerRemoteLoginModule().getRoleSets(dsJndiName,
roleQuery, groupQuery, getUsername());
| if (setsMap == null) {
| if (getUnauthenticatedIdentity() == null) {
| throw new FailedLoginException("No matching username found in Groups");
| }
| Group[] roleSets = { new SimpleGroup("Roles")};
| return roleSets;
| }
| } catch (NamingException e) {
| throw new LoginException(e.toString(true));
| } catch (SQLException e) {
| super.log.error("SQL failure", e);
| throw new LoginException(e.toString());
| } catch (FailedLoginException e) {
| throw new LoginException(e.toString());
| } catch (RemoteException e) {
| throw new LoginException(e.toString());
| } catch (Exception e) {
| throw new LoginException(e.toString());
| }
|
| Group[] roleSets = new Group[setsMap.size()];
| setsMap.values().toArray(roleSets);
|
| return roleSets;
| }
| }
| package org.jboss.security.auth.spi.session;
| import javax.ejb.CreateException;
| import javax.ejb.EJBException;
| import javax.ejb.FinderException;
| import javax.ejb.RemoveException;
| import javax.ejb.SessionBean;
| import javax.ejb.SessionContext;
|
| import java.security.acl.Group;
| import java.sql.Connection;
| import java.sql.PreparedStatement;
| import java.sql.ResultSet;
| import java.sql.SQLException;
| import java.util.HashMap;
|
| import javax.naming.InitialContext;
| import javax.naming.NamingException;
| import javax.security.auth.login.FailedLoginException;
| import javax.sql.DataSource;
|
| import org.jboss.security.SimpleGroup;
| import org.jboss.security.SimplePrincipal;
|
| /**
| * @ejb.bean
| * name = "DatabaseServerRemoteLoginModule"
| * type = "Stateless"
| * view-type = "remote"
| * jndi-name = "DatabaseServerRemoteLoginModule"
| *
| * @ejb.interface
| * remote-class =
"org.jboss.security.auth.spi.intf.DatabaseServerRemoteLoginModule"
| *
| * @ejb.home
| * remote-class =
"org.jboss.security.auth.spi.intf.DatabaseServerRemoteLoginHome"
| *
| * @ejb.transaction
| * type = "Required"
| */
| public class DatabaseServerRemoteLoginModuleEJB implements SessionBean {
| SessionContext sessionContext;
|
| /**
| * @ejb.create-method
| */
| public void ejbCreate() {}
|
| public void ejbActivate() throws EJBException {}
|
| public void ejbPassivate() throws EJBException {}
|
| public void ejbRemove() throws EJBException {}
|
| public void setSessionContext(SessionContext sessionContext) throws EJBException
{
| this.sessionContext = sessionContext;
| }
|
| /**
| * @ejb.interface-method
| * view-type = "remote"
| */
| public String getUsersPassword(String dsJndiName, String userQuery, String
username) throws NamingException, SQLException, FailedLoginException {
| String password = null;
| Connection conn = null;
| PreparedStatement ps = null;
|
| try {
| InitialContext ctx = new InitialContext();
| DataSource ds = (DataSource)ctx.lookup(dsJndiName);
| conn = ds.getConnection();
| ps = conn.prepareStatement(userQuery);
| ps.setString(1, username);
| ResultSet rs = ps.executeQuery();
| if (rs.next() == false) {
| throw new FailedLoginException("No matching username found in SYS_USER");
| }
| password = rs.getString(1);
| rs.close();
| } finally {
| if (ps != null) {
| try {
| ps.close();
| } catch (SQLException e) {}
| }
| if (conn != null) {
| try {
| conn.close();
| } catch (SQLException ex) {}
| }
| }
| return password;
| }
|
| /**
| * @ejb.interface-method
| * view-type = "remote"
| */
| public HashMap getRoleSets(String dsJndiName, String roleQuery, String
groupQuery, String username) throws NamingException,SQLException {
| Connection conn = null;
| HashMap setsMap = new HashMap();
| PreparedStatement ps = null;
| boolean empty = false;
|
| try {
| InitialContext ctx = new InitialContext();
| DataSource ds = (DataSource)ctx.lookup(dsJndiName);
| conn = ds.getConnection();
| ps = conn.prepareStatement(roleQuery);
| ps.setString(1, username);
| ResultSet rs = ps.executeQuery();
| if (rs.next() != false) {
| do {
| String name = rs.getString(1);
| String groupName = rs.getString(2);
| if (groupName == null || groupName.length() == 0)
| groupName = "Roles";
| Group group = (Group)setsMap.get(groupName);
| if (group == null) {
| group = new SimpleGroup(groupName);
| setsMap.put(groupName, group);
| }
| group.addMember(new SimplePrincipal(name));
| } while (rs.next());
| } else {
| empty = true;
| }
| rs.close();
| ps.close();
|
| ps = conn.prepareStatement(groupQuery);
| ps.setString(1, username);
| rs = ps.executeQuery();
| if (rs.next() != false) {
| do {
| String name = rs.getString(1);
| String groupName = rs.getString(2);
| if (groupName == null || groupName.length() == 0)
| groupName = "Roles";
| Group group = (Group)setsMap.get(groupName);
| if (group == null) {
| group = new SimpleGroup(groupName);
| setsMap.put(groupName, group);
| }
| group.addMember(new SimplePrincipal(name));
| } while (rs.next());
| } else if (empty) {
| setsMap = null;
| }
| rs.close();
| } finally {
| if (ps != null) {
| try {
| ps.close();
| } catch (SQLException e) {}
| }
| if (conn != null) {
| try {
| conn.close();
| } catch (Exception ex) {}
| }
| }
|
| return setsMap;
| }
| }
Cheers
Goran
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3834364#3834364
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3834364
-------------------------------------------------------
This SF.Net email is sponsored by Sleepycat Software
Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to
deliver higher performing products faster, at low TCO.
http://www.sleepycat.com/telcomwpreg.php?From=osdnemail3
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
