1

I have multi-tenant table structure whereas each table has TenantId and I am using Dapper as the only ORM along with SimpleCRUD (helper sits on the top of dapper). If possible (by default) I prefer every Read Query MUST pass the TenantId as a parameter automatically, otherwise afraid there is a chance to forget.

Curious to know how others are solving this case? Are there any effective ways to solve this issue?

Coder Absolute
  • 5,417
  • 5
  • 26
  • 41
  • This feels like a process issue, not a code contract issue. You COULD of course write a Roslyn diagnostic to enforce this for you, but at what point do you trust your code review process enough to let it drive correct functionality? The short answer is no, I don't believe there is anything in Dapper that handles this case for you. Part of the beauty of Dapper is the flexibility. – David L Mar 28 '17 at 21:25
  • Before I publish this question I was thinking back in my head that Dapper doesn't handle this case and it is quite generic, but my question is how others handle this scenario? Whereas the Tenant Isolation is done at the `row level` in a same table? Am not getting that starting point... – Coder Absolute Mar 29 '17 at 01:50
  • 2
    Hi @CoderAbsolute, I would probably try Row Level Security (https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security) as a more ideal solution. – Nick Mar 10 '18 at 09:11
  • 1
    @Nick: Yes, I went ahead with Row Level Security and doing pretty well so far. Thanks! – Coder Absolute Mar 10 '18 at 10:07
  • 1
    Not quite on topic, but (where possible) I've always had much better luck with a separate db for each tenant rather than one db to hold multiple tenants. – jleach Jun 02 '18 at 21:04
  • @jleach Maintaining each database for the tenant can be quite cumbersome but again it has its pros and cons. I want to hold multiple tenants data in a single table. – Coder Absolute Jun 04 '18 at 04:55
  • 1
    @CoderAbsolute I understand you went ahead with RLS. I understand that for RLS to work you must execute the SQL query in the context of a user i.e. 'EXECUTE AS USER = 'tenant1''. How do you go about ensuring that each call made by dapper is preceded by that statement? One way I could think of is you to create a wrapper that prepends the 'execute as' statement, but I'm interested in what you came up with. – james Apr 27 '21 at 11:52
  • @james: We are making sure that the WHERE clause is mentioned in every SQL statement. Do you have any other better way? – Coder Absolute May 21 '21 at 08:05

1 Answers1

1

Dapper is a micro ORM and as far as I know it does not have this kind of feature.

In Entity Framework 6 you can use interceptors to force the query to filter by the TenantId.

In Entity Framework Core you can use QueryFilters.

In SqlServer you can use Row Level Security independently of the ORM.

Lutti Coelho
  • 2,134
  • 15
  • 31