novel multi tenancy

contemplating novel multi-tenant auth strategy:

every table has a many-2-many table with

auth_table

• action (CRUD)
• table_name
• user_id

now if you want to query all boxes you can do

SELECT * FROM boxes
INNER JOIN auth_table AS auth ON auth.table_name = 'box'
WHERE auth.action = 'READ' AND auth.user_id = ?;

this has the interesting property that anything you can query through auth_table you’re allowed to query.

One ramification of this is that you could expose your DB to direct SQL queries by any user and the results could be filtered by appropriate auth_tables. User queries would be dynamically modified to include the auth join. If you wanted to get really wild you could do row-level security by adding a column_id field to the auth_table. You would need to think about DOS mitigation etc but still an interesting idea IMHO.