I've moved this post to our Server Admin group where some of our users may have experience implementing row-level security.
Yes, you can self-join in a standard Tableau connection the same table to itself over and over again, but only to a finite number of levels. If the number of levels you have is arbitrary, then it becomes a big pain--you'd need a recursive structure on the database side, I would think, or a custom mapping CSV file you blended in.
One method might be with Groups. There's a function called ISMEMBEROF() that validates whether or not the user viewing the viz is a member of that group or not, and returns true if they are. So you might be able to use that for a structure like this:
IF ISMEMBEROF('OpsExecutives') AND [Department] = 'Ops' THEN 1
ELSEIF ISMEMBEROF('DevExecutives') AND [Department] = 'Dev' THEN 1
Trouble with that is, the function requires a static string as input--you can't make it dynamically validate groups that exist as values in your data.
What I have done when working against a PostgreSQL was write a bit of customSQL where I have a list of users who have access to a given record (in my case, only 1 or 2), and pivot them so that the result is something like:
Username Leader 1 Leader 2 userx leader x usery leader x leader y userz leader z
... where the two Leader columns are what I am pivoting. Then the calc is:
IF USERNAME() = [Username] THEN 1 // user can see their own data
ELSEIF USERNAME() = [Leader 1] THEN 1 // manager can see their direct reports' data
ELSEIF USERNAME() = [Leader 2] THEN 1 // director can see indirect reports' data
ELSE 0 // no data for you!
That solution can work, but the custom SQL can get tricky, and if your organization grows and you have to add a new level--ugh...that's a pain. Performance also might suffer at large volumes, but I haven't analyzed that specifically.
Yet ANOTHER solution is more dynamic but can also increase confusion--join tables such that you duplicate the rows in your actual data, with each row of data also containing the specific username who has access to that row. In that case my previous example would look like:
data UserWhoCanAccess userx
userx leaderx usery usery usery leaderx usery leadery userz userz userz leaderz
Then the calc is:
USERNAME() = [UserWhoCanAccess]
Much simpler on the permissions side, but it also can blow your data out to large multiples of its original size, which can be pretty ugly.