如何使用 ASP.NET MVC2 和 MS SQL Server 在共享数据库中实现租户视图过滤器安全模式
我开始在 ASP.NET MVC2 中构建 SaaS 业务应用程序,但在开始之前我想建立良好的架构基础。
我将采用共享数据库和共享模式方法,因为数据架构和业务逻辑将非常简单,而效率和成本效益是关键问题。
为了确保租户之间数据的良好隔离,我想实现租户视图筛选器安全模式(看看此处)。为了做到这一点,我的应用程序必须根据登录应用程序的用户模拟不同的租户(数据库登录)。登录过程需要尽可能简单(它不会是企业级软件) - 因此客户只需输入他们的用户名和密码。
用户将通过自己的子域(使用子域路由)访问数据,例如 http://tenant1.myapp.com 或 http://tenant2.myapp.com
满足这种情况的最佳方法是什么?
I am starting to build a SaaS line of business application in ASP.NET MVC2 but before I start I want to establish good architecture foundation.
I am going towards a shared database and shared schema approach because the data architecture and business logic will be quite simple and efficiency along with cost effectiveness are key issues.
To ensure good isolation of data between tenants I would like to implement the Tenant View Filter security pattern (take a look here). In order to do that my application has to impersonate different tenants (DB logins) based on the user that is logging in to the application. The login process needs to be as simple as possible (it's not going to be enterprise class software) - so a customer should only input their user name and password.
Users will access their data through their own sub-domain (using Subdomain routing) like http://tenant1.myapp.com or http://tenant2.myapp.com
What is the best way to meet this scenario?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我还建议使用两个数据库,一个 ConfigDB 和一个 ContentDB。
ConfigDB 包含租户表以及该表中每个租户的内容数据库的主机名、数据库名、sql 用户名和 sql 密码,并通过名为 usrAdmin 的单独 sql 用户进行访问
ContentDB 包含所有应用程序表,在 SID 上分段用户的(或 SUSER_ID),并且由称为 usrTenantA、usrTenantB、usrTenantC 等的每个租户 sql 用户访问。
要检索数据,您以管理员身份连接到 ConfigDB,检索相应客户端的凭据,使用检索到的凭据连接到服务器凭据,然后查询数据库。
我这样做的原因是为了水平可扩展性和根据需要隔离客户端的能力。
您现在可以拥有许多 ContentDB,也许每十个注册租户就创建一个新数据库,并配置您的应用程序以开始在该数据库中配置客户端。
或者,您可以配置一些 SQL 服务器,在每个服务器上创建一个内容数据库,并在历史上利用率最低的服务器上配置代码租户。
您还可以在服务器 A 和 B 上托管所有常规客户端,但服务器 C 可以在自己的独立数据库中拥有租户,所有多租户代码仍然存在,但可以告诉这些客户端,由于更高的隔离性,它们现在更加安全。
I would also suggest using two database, a ConfigDB and a ContentDB.
The ConfigDB contains the tenant table and the hostname, databasename, sql username and sql password of the Content database for each of tenants in this table and is accessed via a seperate sql user called usrAdmin
The ContentDB contain all the application tables, segmented on the SID (or SUSER_ID) of the user and is access by each tenants sql user called usrTenantA, usrTenantB, usrTenantC etc.
To retrieve data, you connect to the ConfigDB as admin, retrieve the credentials for the appropriate client, connect to the server using the retrieved credentials and then query the database.
The reasons i did this is for horizontal scalability and the ability to isolate clients upon demand.
You can now have many ContentDBs, maybe with every ten tenants that sign up you create a new database, and configure your application to start provisioning clients in that database.
Alternatively you could provision a few sql servers, create a content DB on each and have your code provision tenants on which ever server has the lowest utilization historically.
You could also host all your regular clients on server A and B, but Server C could have tenants in their own INDIVIDUAL databases, all the multitenancy code is still there, but these clients can be told they are now more secure because of the higher isolation.
最简单的方法是拥有一个租户表,其中包含一个 URL 字段,您可以将其与所有通过的查询相匹配。
如果一个租户可以有多个 URL,则只需使用一个附加表(如 TenantAlias)来维护每个租户的多个 URL。
缓存这个表的Web端,因为它会被频繁点击;每当值发生变化时,缓存就会失效。
你可以看看DotNetNuke。它是一个开源 CMS,实现了这个确切的模型。我在我们的几个应用程序中使用该模型,效果很好。
顺便说一句,对于系统中的每个实体,您都需要为上表获取一个tenantid 列。
The easiest way is to have a Tenants table which contains a URL field that you match up for all queries coming through.
If a tenant can have multiple URL's, then just have an additional table like TenantAlias which maintains the multiple urls for each tenant.
Cache this table web side as it will be hit a lot; invalidate the cache whenever a value changes.
You can look at DotNetNuke. It is an open source CMS that implements this exact model. I'm using the model in a couple of our apps at it works well.
BTW, for EVERY entity in your system you'll need to have a tenantid column acquired for the above table.