Row level security (SQL implementation) for user to see information of all employees reporting under them

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:
UserName, Employee_login
UserC,“A,B,C”
UserD,“A,B,C,D”
UserE,“A,B,C,D,”
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.

Regards - San

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.

I wasn’t able to use CTE in QuickSight to create this query, so I had to do it at the data source level.

I see other users are using CTEs in QuickSight, so maybe it depends on your data source:

2 Likes

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!

Hey,

I am having the same issue with listagg limitation when set up the rls. Would you please advice on this approach? how do you use rescursive CTE to resolve it?

Thanks in advance!

Cindy

Hello Cindy, I was not able to use recursive CTE directly in Custom SQL on Quicksight Data Prep.
The approach I took was instead of having every single indirect user have a row, I clubbed all the indirects into a single row. I created a single line item UserName, reporting hierarchy as is, Employee login = NULL. Sample data rows:
UserName | reporting_to_level_1 | reporting_to_level_2 | … | employee_login
User A | X | Y | … | NULL
User B | X | Y | … | NULL

And to tie it back, this CTE was used as a component in final UNION ALL statement. Hope this helps!