We do have a single-tenant DB. That’s one of my architecture challenges- how to handle permissions and clean up the schema a bit to entities that only my users need.
Possibly achieve that with some views or w/e the equivalent is in your database, and database accounts that can only access those views.
Another option might be to let them ingest their data directly into the existing BI tools they use where they can do whatever they want, cool thing about that is it can entrench you into their infrastructure and it offloads a lot of this complexity you're dealing with.
Okay- just spent the whole day tinkering wit this:
1) I create a baseline set of views I want my customers to have
2) For each new customer, I’ll run a script that create a replica of those views- filtered by their customer ID
3) I’ll allow my customers to write pure SQL- limiting them to only SELECT queries and a couple niche business rules, as well as masking any DB-level errors, because that just feels wrong
benoau|3 months ago
Another option might be to let them ingest their data directly into the existing BI tools they use where they can do whatever they want, cool thing about that is it can entrench you into their infrastructure and it offloads a lot of this complexity you're dealing with.
bewal416|3 months ago
1) I create a baseline set of views I want my customers to have 2) For each new customer, I’ll run a script that create a replica of those views- filtered by their customer ID 3) I’ll allow my customers to write pure SQL- limiting them to only SELECT queries and a couple niche business rules, as well as masking any DB-level errors, because that just feels wrong
How does that approach sound?