基于模式的SQLServer和Hibernate多租户方案

Question

我有兴趣使用Hibernate作为提供程序和SQLServer作为数据库来实现多租户解决方案。我使用基于模式的方法，意味着一个数据库和不同的模式。

具体而言，我的问题是如何使用SQLServer更改执行模式。我已经在MySQL使用了这种方法use $database，PostgreSQL SET search_path TO $schema和Oracle ALTER SESSION SET CURRENT_SCHEMA = $schema，并且成功地从一个模式切换到另一个模式，注意不同的数据库之间的模式概念不同。

然而，在SQLServer上，我了解到没有更改执行模式的东西。如果用户未被授予sysadmin角色，则可以更改给定用户的默认模式。我还了解了模拟概念，使用以下查询：EXECUTE AS USER = $user。模拟允许一个用户模拟另一个用户，这给了更改当前用户的可能性。

更改当前用户允许根据定义切换执行模式，因为一旦更改了用户，执行模式就是新用户的默认模式。但是，模拟有一个限制，因为我们不能超过32次进行模拟。虽然可以在每次模拟后执行revert以避免达到模拟次数的限制，但这种解决方案不适用于我的情况，我正在寻找替代方案。

有人对使用SQLServer和基于模式的方法实现多租户有任何建议吗？

此外，除了我提到的解决方案之外，还有其他解决方案可以切换执行模式吗？

非常感谢您的帮助。非常感谢。

- Imen Gharsalli

---

嗨.. 有没有解决方案？ - SatZ

Answer 1

以下是需要考虑的要点：

1. SQL Server通过表中的前缀（如[dbo] [Users]和[Tenant01] [Users]）区分模式。
2. 由于您没有提到在SQL Server中解决每个模式的数据库连接的方式，我建议您可以查看支持您用例的Azure Shard Map或自己构建，其中您将根据建立的Tenant Context从集中存储中获取连接字符串。例如：Tenant01将映射到ConnectionString C01，其将具有相同的数据库，但具有不同的userid和password。这种方法是更常见的方法。但是您也可以选择Azure Shard Map，它会在幕后为您执行此映射。
3. 在这种情况下，当您提供模式时，还会创建SQL用户或将Azure AD用户映射到模式并授予必要的权限。这确保了访问是正确的。

在存储租户级连接字符串的上述选项将有助于在共享数据库中计划扩展特定租户以应对数据增长量的显着增加，以便为其余租户提供更高的响应时间和更好的性能。

了解更多关于Azure Shard Map的信息。

编辑

实际上，当您在SQL Server中将用户映射到模式时，您不需要在查询中使用[Schema] [Table]，而是可以直接使用[Table]，访问会自动发生。您实际上可以使用类似于ALTER USER erpadmin WITH DEFAULT_SCHEMA = erpadmin;的方式将用户映射到模式。从那时起，查询不需要表的模式前缀。更多详细信息请参阅此处。

希望对您有所帮助。

- Saravanan

---

嗨，Saravanan，非常感谢您的建议。我知道表名选项中的前缀，但这不是我需要的。此外，我不想从头开始实现多租户方法，因此Azure Shard Map对我来说不合适。您有关于实际使用SQLServer切换执行模式的建议吗？就像我提到的其他数据库一样？谢谢。 - Imen Gharsalli

我已更新了帖子，请参考并告诉我们是否有帮助！ - Saravanan

感谢更新。我请求切换执行模式而不是默认模式，因为我已经尝试过 ALTER USER myUser WITH DEFAULT_SCHEMA = mySchema; 。我没有选择这个替代方案，因为它引发了两个问题：sysadmin角色和特别是多线程。 - Imen Gharsalli

Answer 2

我也遇到了这个问题，我的解决方法是在 JPA 配置中添加一个默认模式，例如：

 @Bean()
public LocalContainerEntityManagerFactoryBean entityManager() {
    Map<String, Object> jpaProperties = new HashMap<>();

    jpaProperties.put("exclude-unlisted-classes", true);
    jpaProperties.put(org.hibernate.cfg.Environment.DIALECT, "org.hibernate.dialect.SQLServer2012Dialect");

    jpaProperties.put(org.hibernate.cfg.Environment.DRIVER, env.getProperty("ENTIDADES_PACKAGE"));
    **jpaProperties.put(org.hibernate.cfg.Environment.INTERCEPTOR, this.hibernateInterceptor());**
    **jpaProperties.put(org.hibernate.cfg.Environment.DEFAULT_SCHEMA, "dbo");**

    LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
    lef.setPersistenceUnitName("SECONDARY_DATABASE_PU");
    lef.setDataSource(secondaryDataSource());
    lef.setPersistenceProvider(new HibernatePersistenceProvider());
    lef.setJpaPropertyMap(jpaProperties);
    lef.setPackagesToScan("br.com.application.model");

    return lef;
}

添加一个Hibernate查询拦截器，以替换查询中的默认方案字符串。

@Bean
public Interceptor hibernateInterceptor() {
    return new EmptyInterceptor() {
        @Override
        public String onPrepareStatement(String sql) {
            String prepedStatement = super.onPrepareStatement(sql);

            return (TenantContext.getCurrentTenant() != null)
                    ? prepedStatement.replaceAll("dbo.", String.format("%s.", TenantContext.getCurrentTenant()))
                    : prepedStatement;
        }
    };
}

- Filipe Andrade