We have a single SQL Server database for all tenants of a web-application. How can we ensure when a support engineer queries the DB directly via SSMS, they only touch data belonging to one tenant?
The web-application reliably isolates data between tenants as all DB queries have an additional clause added automatically to every query: WHERE tenantID = @currentUserTenantId.
The support team have a need to query the database directory and sometimes perform changes to the database. How can we ensure someone doesn't forget to add WHERE tenantID = X when executing a query via SSMS?
Row Level Security (RLS) may be one such solution which would require the support engineer to set the tenantID via SESSION_CONTEXT and reject running the query if no tenantID was specified. Support engineers each have their own SQL User.
Alternatively is there a way, using RLS or otherwise, to ensure that a query ran by a support engineer can only touch a single tenantID? i.e. if they failed to specify WHERE tenantID = X and a query would return data with multiple values in tenantID, then block the query?
Perhaps an ideal solution would be for support engineers not to be able to run any raw SQL and only run predefined stored procedures maintained by the development team. However we don't have the resources to do this at present.
Similarly, Zendesk/Salesforce have the ability for customers to grant access to their respective support teams. I've also seen other solutions which require the customer to share a code with the support team (say by phone). How might that be implemented to protect queries ran via SSMS?
Best Answer
I would probably provision "tenant support identity" (user or role) and have process where a support engineer is provisioned access to a single tenant, through temporary membership in the role, or by giving the support person the password to a temporarily-enabled user.
The developers would add stored procedures that manage the tenant support identity, along with perhaps some auditing.
Then the only difference between a single-tenant and multi-tenant database is that you have to implement RLS for the tenant support identity.