I'd like to create row level security for the attached dataset.
The hierarchy looks like this:
- the regional manager can see everyone in their region and themselves
- the finance manager can see everyone in their region and themselves
- regional and finance managers cannot see each other
- the HR manager can see everyone
- anyone who's not a manager can't see anything
I've built a security table and have created the calculated field USERNAME()=[Username] which is set to TRUE as datasource filter. I've joined the security table on my data table using ID and Area as join conditions. Everything works fine, but I'm not sure if this is the most efficient method. The obvious problem is that my security table would blow up if I had a lot of employees and several HR managers since I'd have to create a row for every employee and every HR manager. I can't create any groups on server, so ISMEMBEROF() is not an option.
Am I missing something? Do you think this is a feasible method to build a security table or is there a better way?