Hello, I am looking to implement row level security using SQL custom query. I have the data flattened as:
| employee_login | reporting_to_level_1 | reporting_to_level_2 | … | manager |
| A | P | Q | … | C |
| B | P | Q | … | C |
| C | P | Q | … | D |
| D | P | Q | … | E |
Usually the reporting structure starts giving nulls from reporting_to_level_15.
I have created CTE for self, peers, manager and direct reports. Then I union all the CTEs together to form the RLS dataset. Now, I want to expand to include all users for an employee login reporting into the user. I am thinking there may be a recursive way to achieve this or maybe use the direct reports CTE as series of left join?
From example that means (only looking at self and reports):
C should see information for C, A, B.
D should see information for D, C, A, B.
E should see information for E, D, C, A, B.
Not sure if I’ve fully understood what you’re question is. I’m not best placed to help you create your SQL Query, but your RLS dataset can allow users to see combinations of your data. So your actual RLS dataset could be:
You don’t need to build different rules for every different record that they can see.
@Siddhi_Patil - If I understood correctly, if you have a table where your data is there and want to put RLS on that table depending upon some conditions. You can create a RLS table ( see the link below) and implement it.
Thank you Steph, what you are suggesting is listagg(). The problem with that is the number of users I am trying to set up RLS for is too deep in the hierarchical view which was giving me error [Using LISTAGG function in SQL causes error: Result size exceeds LISTAGG limit. Hence reverted to single line access rows.
Thank you San, yes I have RLS implemented using custom SQL and now want to expand on it. SQL was used because the hierarchy information could be updated daily and I wanted to have the most recent org shifts accommodated.
In simpler terms the use case is: As a manager, I want to see information of all users reporting into me, directly and indirectly. I have the direct part covered, looking to implement indirect part. Let me know if that helps.
I had to do something like this for my RLS rules although my hierarchy doesn’t go as deep, usually 4-5 levels at most. To get around the LISTAGG limitation, you can create a dataset that looks like this:
UserName, Employee Login
User C, C
User C, B
User C, A
User D, D
User D, C
User D, B
User D, A
However, with this approach, there’s another limitation that you need to be aware of. You’ll get an error if a user has more than 999 rows in your RLS rules.
Thank you David. I will have to have level 3 to level 15. Thanks for pointing the 999 row limitation per user.
I have Redshift source, so CTE & recursive CTE is definitely possible. I have seen it in one of my other projects where a view generated out of recursive CTE was being used in quicksight source dataset. Will try my hand at recursive CTE - looks like the best bet. Thank you!