JPA - Using Multiple data sources to define access control

Question

I am totally new to JPA and JSF and hope you can help me with my questions. My application is built using JSF 2.0 Framework, using JPA 2.0/EclipseLink running on Glassfish 3+, MySQL.

I set one persistence unit called "loginPU" using data source: "jdbc/loginDataSource" "jdbc/loginDataSource" connects to MySQL using "login1" (defined in mysql.user table) and only has access to customer.user and customer.roles tables, with only select privileges.

I created 2 other data sources "jdbc/admin" and "jdbc/staff" in Glassfish JDBC Resources and both with different privileges

The login/authentication scenario is:

1. User login using form based authentication (username and password)
2. Create EntityManageFactory using persistence unit "loginPU" and "jdbc/loginDataSource"
3. create query to retrieve user role
4. if user role is admin, connect using "jdbc/admin" data source
5. if user role is staff, connect using "jdbc/staff" data source

My code for item 2 above looks like this:

Map properties = new HashMap(); 
properties.put(TRANSACTION_TYPE, "JTA");

// Configure the internal EclipseLink connection pool
properties.put(JDBC_DRIVER, "com.mysql.jdbc.Driver");
properties.put(JDBC_URL, "jdbc:mysql://localhost:3306/customer");
properties.put(JDBC_USER, "login1");
properties.put(JDBC_PASSWORD, "login1");
properties.put(JTA_DATASOURCE, "jdbc/loginDataSource");

EntityManageFactory emf = Persistence.createEntityManagerFactory("loginPU",properties);

I even keep my EntityManagerFactory in session attributes and retrieve it in the JpaController class

//save into session
session.setAttribute("entityManagerFactory", emf);

//retrieved in JpaController
public EntityManagerFactory getEmf() {

        HttpServletRequest request = (HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest();
        HttpSession s = request.getSession(true);
        try {
            emf = (EntityManagerFactory) s.getAttribute("entityManagerFactory");
        } catch(NullPointerException ne){
            System.out.println("EMF Exception: "+ne);
        }

        return emf;
    }

Questions: How can I achieve number 4 or number 5? Is that possible to do? Is it possible to assign either data source to "loginPU" persistence unit? I manage to establish connection using loginPU and jdbc/loginDataSource and then connect using jdbc/admin datasource, but when I access other entities, it throws error and default to jdbc/loginDataSource

Note: I am using JpaController classes created by netbeans, and also session beans to manage the entities. My JpaController classes use

@Resource private UserTransaction utx;
@PersistenceUnit private EntityManagerFactory emf;

My session beans are all @Stateless, I tried to use @PersistenceContext with unitName and without unitName but no luck

@PersistenceContext
private EntityManager em;

I tried using multiple persistence units in persistence.xml, hoping to connect users using the persistence unit name based on the role, but it gives me error when deploying to server.

I read about application-managed persistence and container-managed, I think what I am trying to achieve is to use application-managed, but not sure how to do it.

If I am to use container-managed persistence, is it possible to use multiple data sources? Any suggestion is greatly appreciated.

Thank you for any comments or suggestions in advance.

[SOLVED]

1. First I defined my persistence.xml as follows:

   <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
       <persistence-unit name="mdbAdminPU" >
           <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
           <jta-data-source>jdbc/login</jta-data-source>
           <exclude-unlisted-classes>false</exclude-unlisted-classes>
       </persistence-unit>
   </persistence>
   

2. I don't use any @PersistenceUnit or @PersistenceContext in my session beans.(I'm using Netbeans and these beans were created when I created JSF Pages from Entity Classes)

3. In all session beans, they look like this:

   @Stateless
   public class UserFacade extends AbstractFacade<User> {
   
       @Override
       protected EntityManager getEntityManager() {
           HttpSession session = (HttpSession) FacesContext.getCurrentInstance().getExternalContext().getSession(false);
           EntityManagerFactory emf = (EntityManagerFactory) session.getAttribute("entityManagerFactory");
           EntityManager em = emf.createEntityManager(); 
           return em;
       }
   
       public UserFacade() {
           super(User.class);
       }
   }
   

4. The login scenario above (5 items) has become 7:

1. User login using form based authentication (username and password)
2. Create EntityManageFactory using persistence unit "loginPU" and "jdbc/loginDataSource"
3. create query to retrieve user role
4. if user role is admin, connect using "jdbc/admin" data source
5. if user role is staff, connect using "jdbc/staff" data source

plus

1. remove or clear EntityManagerFactory created in item 2 using emf.close();
2. Keep new EntityManagerFactory created in either item 4 or 5 in HttpSession

Answer 1

You are probably best off using three separate persistence units in your persistence.xml if you need three different logins. Or just have a single login with full access and validate security in your application (which you seem to be doing in part anyway).

What error did you get on deploy?

You can do it with one persistence unit in EclipseLink but it is more involved, and you cannot use container managed entity managers. You would inject a @PersistenceUnit (EntityManagerFactory) instead of an @PersistenceContext (EntityManager), then you would need to pass the new login parameters to createEntityManager(). You would need to set the "eclipselink.jdbc.exclusive-connection.mode" property to "Always" (or "Isolated" and make the secure data isolated).

See, http://wiki.eclipse.org/EclipseLink/Examples/JPA/Auditing