Data modeling user-object permissions, between claims and metadata

Question

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.

Answer 1

As you mention in the comments, one solution is to remove the foreign keys. You would need to make sure that all your queries join back to the root user table to ensure the permissions are no longer found from the other tables.

IMO this is a little ugly. Foreign keys can help with avoiding data problems. There's also a chance that eliminating the foreign keys could degrade query performance but I'm hesitant to make a strong claim either way on that. There are so many factors to consider and potential mitigation strategies.

A similar option that let's you avoid this is to flag the users as removed. You could do this by adding a column to the user table or with another table. You could even do things like changing the id to make it impossible to log in. Again, you need to make sure all the queries join back to this table and that you exclude these soft deletes in the query. A simple view could be useful here.

Again, as you have noted, you will need to clean up at some point. If you have reliable periods of lower usage e.g. middle of the night, you can create a batch job that will remove these records. Even if you don't have that, you can minimize the impact of the hard delete by working backwards up the dependency tree, deleting rows from the child tables first. You can switch the foreign key constrain to not allow cascading deletes to be sure you are doing it right. That way you aren't putting as much pressure on the DB by deleting such a large number of records in one transaction.

As a side note, you might also want to coordinate this with other DB maintenance activities. I've run into situations where a re-org is needed to truly release the space associated with data.

Answer 2

The OP suggests that write performance is acceptable, but deletion takes entirely too long. It is straightforward to remedy this through tombstones. Define a new boolean column, perhaps named “active” or “deleted”, and filter SELECT queries based on that.

This can be retrofitted onto an existing application with almost no code changes. Do a rename so the current large table has a new name. This of course breaks all queries. Now fix it with a VIEW. It will be a very simple SELECT * which filters out any rows that the tombstone marks as being dead.

Eventually, an annoyingly large number of dead rows will accumulate. A background task can DELETE these at its leisure. Alternatively, you might choose to copy out the live rows to a new table, and then do an atomic table switch. This could be via renaming tables, or again we could fix it with a VIEW. A DROP and a CREATE of a VIEW is very fast. This lets us apply a double buffering technique, where the newly installed VIEW either points at table A or at table B, so the application immediately sees either the old, dirty rows, or the newly cleaned ones.