4 Replies Latest reply on Feb 24, 2011 3:43 PM by Tom Tom

    Seam and DB data security using SQL views

    Tom Tom Newbie

      Hi all,


        I'm evaluating Seam framework to port one of my C/S applications. The part under the question is the way the security is implemented in my C/S app.


      The background


      The whole idea is based on the Database Views where for each table there are two views - one for the update and another to read the data. The update view is just to give an access to update/delete operations - if it doesn't exist then the user cannot update the data (as there is no object in the DB that user would have the update rights to). The read views of course except being and object to read the data that a user has the permissions to, are something beyond that. In the views the administrator can add WHERE clauses and filter the data, so the view gives only a subset of records. The views are generated for each user group, so for instance we have:


      - User A being a member of Group A and User B - a member of Group B
      - a table ORDERS
      - two read views generated for these groups:


         CREATE VIEW GRPA_ORDERS as select .... from ORDERS
         CREATE VIEW GRPB_ORDERS as select .... from ORDERS O WHERE O.AMOUNT < 10000
      


      Now, in my C/S app when User A wants to see the list of Orders the logic uses GRPAORDERS and shows everything, while User B can see only the data returned by view GRPBORDERS.


      The question


      Can (how) I use Seam to work with such views? I'd have to somehow tell Seam to use different view depending on the user membership?
      I know I could probably use JBoss Rules to reimplement all that, but that's a lot of work...


      So, please can anybody give me some hints/examples of how the dynamic DB object to a bean can be done if it's possible at all.


      Thanks in advance,
      Tom

        • 1. Re: Seam and DB data security using SQL views
          Serkan Eskici Novice

          Try to map your view to an entity with the table name equal to your view name.



          Example:


          @Restrict  <--- this is the security trick !
          @Entity
          @Table(name="YourViewName")
          public class MyView {
           ...
          }
          



          And now define the permissions in security.drl:


          rule CanViewOrdersGroupA
          when 
            myview: MyView(amount <= 100)
            c: PermissionCheck(target == myview, action == "read")
            r: Role(name == "customer") 
          then
            c.grant();
          end
          
          rule CanViewOrdersGroupB
          when 
            myview: MyView()
            c: PermissionCheck(target == myview)
            r: Role(name == "admin") 
          then
            c.grant();
          end
          



          See also securing your entities.

          • 2. Re: Seam and DB data security using SQL views
            Tom Tom Newbie

            Thanks Serkan, but isn't it that what I wanted to avoid - moving the security to JBoss Rules? In your example the entity is mapped to one view and the condition is located in the Rule. Or am I missing something?


            What I wanted to be able to do was somehow map the same entity to two different views (with the same field structure) depending on the current request's user. Is that possible at all? Maybe I should use Named Queries...?


            Tom.

            • 3. Re: Seam and DB data security using SQL views
              Serkan Eskici Novice

              Well, then you'll have to hardcode the logic in your code.


              You should check from the request the provided amount and then decide which query to execute. And yes, in this case using a NamedQuery should be more appropiate.



              public void foo() {
                if(amount <= 100)
                   result = entitymanager.createNamedQuery("queryA").getResultList();
                else
                   result = entitymanager.createNamedQuery("queryB")
                                         .setParameter("amount", amount)
                                         .getResultList(); 
              }
              

              • 4. Re: Seam and DB data security using SQL views
                Tom Tom Newbie

                Hi again,


                  Sorry, for such a long break, but we had to put our project on hold, but we're going to continue on it now. Coming back to an original problem - the goal is to provide the application users a possibility of defining the conditional rights - i.e. the security admin can allow a certain group of users (a role) to view the orders with the value less than 1000 or only the orders from their region or only the orders concerning some particular goods/services... Anyway - these are the data-type conditions that cannot be hardcoded into the App code or any config file, but rather provided by the users and stored in the DB.


                Can this be done using ACLs?
                What's the most efficient way to pull from the DB only those records that current user has right to view (certainly not pull all of then and then iterate and remove i.e. 80% not meeting the condition!)?


                Thanks for your advices.


                Tom