I've been thinking about how to map user claims to permissions in a way that scales from a read and write perspective, probably using a stand-alone service. What we have today doesn't scale for reasons I'll mention in a moment. At the core, we have this:
- User data
- Object data (it's metadata about indexed content, which is irrelevant here)
- Normalized data that maps users to the objects with read/edit/delete permissions
- There are cascades on both sides, with users who inherit permissions from groups, and objects that inherit permissions from other objects
From a read perspective, this works beyond fine. I don't even know what the ceiling is for doing lookups across the user and object entities, but with tens of millions of rows it's a non-issue. The cracks happen when you need to update either side of the equation, but it's all kinds of bad when you delete a user or object, which can cascade across tens of thousands of records. If it matters, it's data in SQL Server, but as a future stand-alone service that maps these, I'm not married to any specific technology.
So the question is: What technology would allow me to quickly update these relationships? I would imagine that denormalizing the SQL could get me part of the way there, but eventually you need to clean up the 10,000 records associated with a user you deleted. I'm open to graph databases and such, though there's added cost for that because of new skills required. Being able to run the database in containers would be nice too, but that's kind of expected at this point.