[gkaemmer]

Oso engineer here. It's a good question -- definitely a dearth of content about this topic.

Like pphysch said, using RBAC generally gets you 90% or more of the way there on this, and a good intro to this concept is the Tailscale post: https://tailscale.com/blog/rbac-like-it-was-meant-to-be/

In most cases (even with many 100s of thousands or millions of users), there are far fewer _roles_. So you can generally answer the question of "which users can access this resource" by answering the questions "which roles can access this resource", and "which users have those roles". If you're using SQL to store roles and role assignments, your query for all users becomes:

    select * from users
    join user_roles on user_roles.user_id = users.id
    where user_roles.role_id in [... small list ...]

This can get tricky if you have 100s of thousands or millions of _roles_, and each of those roles can be dynamically assigned access to a significant percentage of your resources. But that might suggest you're structuring roles incorrectly in the first place (and you should be using fewer roles with more users per role).

All that being said, I think there's definitely more to be written here. Keep a lookout -- we might do some more writing about the topic.

[bob1029]

> This can get tricky if you have 100s of thousands or millions of _roles_

If you use something like a bitmap index (e.g. Roaring Bitmaps), you can easily manage roles in-line on each user row if the role membership is typically sparse.

You can still maintain a separate Roles table as a canonical reference, but you would no longer need to join on it to determine who has what.

[Yoric]

Out of curiosity, is there any kind of support for bitmaps in mainstream databases?

[bob1029]

You can just roll it yourself in the application logic and store as a raw binary column.

This all assuming you don't need to make queries along the axis of "give me every user with role X".

[tremon]

Yes and no: there is no bitmap SQL type, but at least MSSQL packs multiple BIT-columns on the same table into the same word/byte and then uses bit tests for filtering. I suspect Oracle and Postgresql can do the same, but I don't know for sure.

[nextaccountic]

I'm a little confused, does Oso employ row level security? If not, why not?

[winrid]

I explicitly said can't use roles (groups). :)

But yeah, that's the cleanest way to go, if you can.

[janto]

Can you encrypt the role names for when it might be seen by a user?