2 Replies Latest reply: Apr 25, 2011 10:37 PM by S H RSS

    Section access strategy for limiting data that users have a one-to-many relationship with (e.g. cost codes)

      Goal:

      I want to use section access to restrict data users can see based on the cost codes to which they have permissions.

       

      Problem:

      The attached Data Protection PDF, while helpful, provided an example of data reduction only for a one-to-one relationship between user and department (i.e. each user is associated with only one department). In my scenario, each user will associated with many--but not all--cost codes.

       

      Sample Data:

      The cost code permissions table has multiple records for each user. It looks something like this:

      USER COSTCODE

      A 100-01

      A 200-01

      B 200-01

      B 300-01

       

      Questions:

      Would this require multiple listings for each user in the Section Access table, one for each cost code? Or is there a different approach I should take?

      Thanks for your feedback!

        • Section access strategy for limiting data that users have a one-to-many relationship with (e.g. cost codes)
          Miguel Angel Baeyens de Arce

           


          redingtonct wrote:Would this require multiple listings for each user in the Section Access table, one for each cost code? Or is there a different approach I should take?


          The short answer is: yes, you need to specify every possible COSTCODE value for each USER value.

          However, its obviously tedious. So I'd recommend you to create a table, QVD, xls file or something similar with groups of users and memberships, for example, user A member of GROUP1, and user B member of GROUP2, so the section access script looks like

           

          SECTION ACCESS; LOAD ACCESS, USERID, PASSWORD, GROUPFROM SA.QVD (QVD) WHERE 1=1; SECTION APPLICATION; Correspondence: // inline or any other sourceLOAD * INLINE [GROUP, COSTCODEGROUP1, 100-01GROUP1, 200-01GROUP2, 200-01GROUP2, 300-01];


          Of course, in the example above, loading INLINE only takes you more time, but if the correspondence is possible, the script will be smaller.

          Hope that helps