Restrict application on ARS based on comma-separated values in users CP and endpoint CP

[sandeepsingh]

We have a scenario where user customproperty is stored with comma-separated department id (eg: 1,2,3). This states that the user is part of these departments. On other end endpoint also have these departments in a customproperty (eg:1,4,3). This means the application should be restricted to users who belong to these departments only.

Based on this scenario we would appreciate your help to get a working access query. Just a note, we cannot store these values in multiple customproperties as the number of departments can go more than 1000 and we will not have that many customproperties.

[shivmano · Accepted solution]

@sandeepsingh , I Have not had chance to test it myself but can you try with the below query 

where users.userkey in (select u.userkey from users u join user_accounts ua using (userkey) join accounts a using(accountkey) join endpoints e using(endpointkey) where FIND_IN_SET(u.customproperty1, e.customproperty1) > 0)

here find_in_set helps in returning a non-zero value if any of the values in users cp1 exists in the comma separated list of  endpoints cp1.