Java - Ibatis - mySQL with Dynamic Query based on Role

Question

I'm using Java - Ibatis and mySQL with Flex/Flash on the front-end. I have one requirement that is to be able to dynamically add creterias and table to a query depending on the user role. here is an example

Same object calling same SQL but different result based on role

Role 1 : Fully Access to employees

SELECT * 
  FROM Employee A

Role 2 : Limited access to employees

SELECT * 
 FROM Employee A
    , SECURE_LIST B
WHERE B.EmployeeID = A.EmployeeID
  AND B.ROLE_ID = 'ROLE'

I could use Dynamic SQL

    SELECT * 
     FROM Employee A
<isNotEmpty property="ROLE" >
        , SECURE_LIST B
    WHERE B.EmployeeID = A.EmployeeID
      AND B.ROLE_ID = #ROLE#
</isNotEmpty>

Other ideas?

Answer 1

The problem with using role within the query is that you have to then supply it as an argument to the query for possibly every query. What happens when you need to supply arguments to the query? You'll need to add role to those parameter classes/maps too. It's all a bit messy.

I'd take a step back and define your DAO:

public interface MyDAO {
  List<Employee> getEmployees();
  ...
}

and then create two implementations:

public class MyDAOSuper implements MyDAO {
  public List<Employee> getEmployees() {
    // call a query using your first SQL
  }
}

public class MyDAOLimited implements MyDAO {
  public List<Employee> getEmployees() {
    // limited version
  }
}

One advantage of this approach is that if certain methods shouldn't be used by a particular role you have the option of throwing some security violation exception.

Now how you plug that in to the rest of your application is something I don't have enough detail to comment on. You might be using BlazeDS in which case I'd suggest using the Spring integration with BlazeDS, which will open up dependency injection as an option.

Alternatively you could use a simple factory method (based on role) to get the correct DAO.

There are no doubt other ways to plug this in depending on your configuration. I think the above is a lot cleaner than what you're proposing though.

Answer 2

SELECT *      
FROM Employee A
<isNotEmpty property="ROLE" >
   inner join SECURE_LIST B on B.EmployeeID = A.EmployeeID
</isNotEmpty>
<dynamic prepend="WHERE">
      <isNotEmpty property="ROLE" prepend="AND">
           B.ROLE_ID = #ROLE#
      </isNotEmpty>
</dynamic>

A little simpler than creating DAOs but still providing you the flexibility to add other joins or other where clause elements without having to include role in every parameter map