I have a three level Org structure of Department, Fund, Cost Center. Some people have access to the Department and everything below, others have access to Department and Fund and everything below, while others have access to a specific Department, Fund and Cost Center. I am trying RLS in different ways in which to provide security throughout this hierarchy. Any efficient ways in which to do this? My FACT table has these three fields and I have three DIMENSION tables: Department, Fund, Cost Center. As always, thanks in advance.
I am thinking that my only option is to provide a security table at the most granular level. So if Mr. Smith has access to a department and everything below it, he would have to have a row in the security table for every unique combination of Department-Fund-Cost Center. Basically every person having their unique combination, then joining that to the FACT table.
