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: