根据登录用户在运行期间更改数据库模式 [英] Change database schema during runtime based on logged in user

问题描述

我读过关于动态数据源路由的许多问题和答案，并使用 AbstractRoutingDataSource 和另一个（请参见下文）实现了一个解决方案。这很好，但需要所有数据源的硬编码属性。随着使用应用程序的用户数量的增加，这不再是一种合适的路由方式。此外，每次新用户注册时，都需要为属性添加一个条目。情况如下：

• 1数据库服务器


• 该服务器上的许多模式，每个用户都有他们自己的模式。
  
• 我只需要在运行时更改模式名称


• 模式名称可以通过登录用户保留

我使用 spring boot 1.4.0 和 hibernate 5.1 和 spring data jpa

我无法找到一种方法来完全更改模式动态。是否有人知道如何在春季做到这一点？

编辑：

以@Johannes Leimer的回答，我得到了一个工作实施。

以下是代码：

用户提供商：

  @Component 
 public class UserDetailsProvider {
 @Bean 
 @Scope（prototype）
 public CustomUserDetails customUserDetails（）{
 return（CustomUserDetails）SecurityContextHolder.getContext（）。getAuthentication（）。getPrincipal（）; 
 
 
 
 
 $ UserSchemaAwareRoutingDatasource ：： p> 
 
 
  public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
 @Inject 
 Provider< CustomUserDetails> customUserDetails; 
 
 @Inject 
环境env; 
私人LoadingCache< String，DataSource> dataSources = createCache（）; 
 $ b @Override 
 public Connection getConnection（）throws SQLException {
 try {
 return determineTargetDataSource（）。getConnection（）; 
} catch（ExecutionException e）{
 e.printStackTrace（）; 
 
返回null; 
 
 
 $ b @Override 
 public Connection getConnection（String username，String password）throws SQLException {
 System.out.println（getConnection +用户名）; 
 System.out.println（getConnection2+ password）; 
 try {
 return determineTargetDataSource（）。getConnection（username，password）; 
} catch（ExecutionException e）{
 e.printStackTrace（）; 
返回null; 
 
 
 $ b $ private DataSource determineTargetDataSource（）throws SQLException，ExecutionException {
 try {
 String schema = customUserDetails.get（）。getUserDatabase（） ; 
返回dataSources.get（schema）; 
} catch（NullPointerException e）{
 e.printStackTrace（）; 
 
返回dataSources.get（fooooo）; 
} 
 
} 
  
 
 
解决方案
 
假设
 
 
 
因为我还没有在您的问题下发表评论的声望，我的回答基于以下几点：
 
 
 
 
 
 
当前用户使用的模式名称可以通过Spring JSR-330 Provider（如 private javax）访问。 inject.Provider<使用者>用户; String schema = user.get（）。getSchema（）; 。这是理想的基于ThreadLocal的代理。
     要构建一个 DataSource 你需要它的方式需要相同的属性。每次。唯一不同的是模式名称。 （这很容易获得其他不同的参数，但这对于这个答案来说太多了）。
 
 
每个模式都已经设置好了DDL，因此不需要Hibernate来创建表或其他东西。
   每个数据库模式看起来都完全一样，除了名称
  
 
 
每当相应的用户向您的应用程序发出请求时，您都需要重用DataSource。但您不希望每个用户的每个DataSource都永久存储在内存中。
    我的解决方案主意 h2> 
 
 
使用ThreadLocal proxys的组合来获取架构名称和一个Singleton-DataSource，它们在每个用户请求上表现不同。这个解决方案的灵感来自于你对 AbstractRoutingDataSource ，Meherzad的评论和自己的经验的暗示。 
 
 
动态 DataSource  
 
 
 
我建议简化Spring的 AbstractDataSource   AbstractRoutingDataSource 。我们使用 Map Map  > Guava Cache 以获得一个易于使用的缓存。
  public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
 private @Inject javax.inject.Provider< User>用户; 
 private @Inject环境env; 
私人LoadingCache< String，DataSource> dataSources = createCache（）; 
 
 @Override 
 public Connection getConnection（）throws SQLException {
 return determineTargetDataSource（）。getConnection（）; 
 
 $ b @Override 
 public Connection getConnection（String username，String password）throws SQLException {
 return determineTargetDataSource（）。getConnection（username，password）; 
 
 
 private DataSource determineTargetDataSource（）{
 String schema = user.get（）。getSchema（）; 
返回dataSources.get（schema）; 
} 
 
私人LoadingCache< String，DataSource> createCache（）{
 return CacheBuilder.newBuilder（）
 .maximumSize（100）
 .expireAfterWrite（10，TimeUnit.MINUTES）
 .build（
 new CacheLoader< String，DataSource>（）{
 public DataSource load（String key）throws AnyException {
 return buildDataSourceForSchema（key）; 
} 
}）; 
} 
 
 private DataSource buildDataSourceForSchema（String schema）{
 //例如的属性：jdbc：postgresql：// localhost：5432 / mydatabase？currentSchema =
 String url = env.getRequiredProperty（spring.datasource.url）+ schema; 
返回DataSourceBuilder.create（）
 .driverClassName（env.getRequiredProperty（spring.datasource.driverClassName））
 [...] 
 .url（url）
 .build（）; 
 
 
 
 
 $ b现在你有一个`DataSource'每个用户。一旦创建了一个DataSource，它将被缓存10分钟。这就是它。 
 
 
让应用程序知道我们的动态数据源
 
 
 
集成我们新创建的DataSource的地方是DataSource在Spring上下文中已知的单例，并在所有的bean中使用EntityManagerFactory 
 
 
 
所以我们需要一个相当于这个的： 
 
 
  @ Primary 
 @Bean（name =dataSource）
 @ConfigurationProperties（prefix =spring.datasource）
 public DataSource dataSource（）{
 return DataSourceBuilder.create（）。建立（）; 
} 
  
但它必须比基于普通属性的DataSourceBuilder更具动态性：
  @Primary 
 @Bean（name =dataSource）
 public UserSchemaAwareRoutingDataSource dataSource（）{
返回新的UserSchemaAwareRoutingDataSource（）; 
} 
  
 
 
 
结论
 
 
 
我们有一个透明的动态数据源，它每次都使用正确的DataSource。
 
 
打开问题
 
 
 
 
 
当没有用户登录时该怎么办？是否不允许数据库访问？
 
 
谁设置了这些方案？
 
 
 
 
 
免责声明 h2> 
 
 
我还没有测试过这段代码！
 
 
 
 编辑：用Spring实现一个 Provider< CustomUserDetails> ，你需要将它定义为原型。您可以使用JSR-330的Spring支持以及Spring Security SecurityContextHolder：
 
 
 $ @ $ @Bean @Scope（prototype）
 public CustomUserDetails customUserDetails（）{
 return return（CustomUserDetails）SecurityContextHolder.getContext（）。getAuthentication（）。getPrincipal（）; 
} 
  
您不需要 RequestInterceptor ， UserProvider 或控制器代码来更新用户。
 
 
 
这有帮助吗？ / b> 
 
 
  EDIT2  
仅供参考：不要引用 CustomUserDetails  bean直。由于这是一个原型，Spring将尝试为类 CustomUserDetails 创建一个代理，在我们的例子中这不是一个好主意。所以只需使用 Provider 来访问这个bean。或者让它成为一个界面。
 
I've read many questions and answers about dynamic datasource routing and have implemented a solution using AbstractRoutingDataSource and another(see below). That's fine, but requires hardcoded properties for all datasources. As the number of users using the application increases, this isn't a suitable way of routing any more. Also it would require to add an entry to the properties every time a new user registers. The situation is as follows


1 database server
many schemas on that server, every user has their own schema.
I only need to change the schema name during runtime
schema name is retainable by logged in user


I'm using spring boot 1.4.0 together with hibernate 5.1 and spring data jpa

I can't find a way to change the schema completely dynamically. Does someone know how to do it in spring?

EDIT:

Thanks to @Johannes Leimer's answer, I got a working implemantation. 

Here's the code:

User Provider:
@Component
public class UserDetailsProvider {
    @Bean
    @Scope("prototype")
    public CustomUserDetails customUserDetails() {
        return (CustomUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
    }
}
UserSchemaAwareRoutingDatasource:
public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
@Inject
Provider<CustomUserDetails> customUserDetails;

@Inject
Environment env;
private LoadingCache<String, DataSource> dataSources = createCache();

@Override
public Connection getConnection() throws SQLException {
    try {
        return determineTargetDataSource().getConnection();
    } catch (ExecutionException e){
        e.printStackTrace();

        return null;
    }
}

@Override
public Connection getConnection(String username, String password) throws SQLException {
    System.out.println("getConnection" + username);
    System.out.println("getConnection2" + password);
    try {
        return determineTargetDataSource().getConnection(username, password);
    } catch (ExecutionException e) {
        e.printStackTrace();
        return null;
    }
}

private DataSource determineTargetDataSource() throws SQLException, ExecutionException {
    try {
        String schema = customUserDetails.get().getUserDatabase();
        return dataSources.get(schema);
    } catch (NullPointerException e) {
        e.printStackTrace();

        return dataSources.get("fooooo");
    }

}

解决方案
Assumptions

Because I don't have the reputation yet to post a comment below your question, my answer is based on the following assumtions:


The current schema name to be used for the current user is accessible through a Spring JSR-330 Provider like private javax.inject.Provider<User> user; String schema = user.get().getSchema();. This is ideally a ThreadLocal-based proxy.
To build a DataSource which is fully configured in a way you need it requires the same properties. Every time. The only thing which is different is the schema name. (It would easily possible to obtain other different parameters as well, but this would be too much for this answer)
Each schema is already set up with the needed DDL, so there is no need for hibernate to create tables or something else
Each database schema looks completely the same except for its name
You need to reuse a DataSource every time the corresponding user makes a request to your application. But you don't want to have every DataSource of every user permanently in the memory.


My solution idea

Use a combination of ThreadLocal proxys to get the schema name and a Singleton-DataSource which behaves different on every user request. This solution is inspired by your hint to AbstractRoutingDataSource, Meherzad's comments and own experience.

A dynamic DataSource

I suggest to facilitate the AbstractDataSource of Spring and implement it like the AbstractRoutingDataSource. Instead of a static Map-like approach we use a Guava Cache to get an easy to use cache.
public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
    private @Inject javax.inject.Provider<User> user;
    private @Inject Environment env;
    private LoadingCache<String, DataSource> dataSources = createCache();

    @Override
    public Connection getConnection() throws SQLException {
        return determineTargetDataSource().getConnection();
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return determineTargetDataSource().getConnection(username, password);
    }

    private DataSource determineTargetDataSource() {
        String schema = user.get().getSchema();
        return dataSources.get(schema);
    }

    private LoadingCache<String, DataSource> createCache() {
        return CacheBuilder.newBuilder()
           .maximumSize(100)
           .expireAfterWrite(10, TimeUnit.MINUTES)
           .build(
               new CacheLoader<String, DataSource>() {
                 public DataSource load(String key) throws AnyException {
                   return buildDataSourceForSchema(key);
                 }
               });
    }

    private DataSource buildDataSourceForSchema(String schema) {
        // e.g. of property: "jdbc:postgresql://localhost:5432/mydatabase?currentSchema="
        String url = env.getRequiredProperty("spring.datasource.url") + schema;
        return DataSourceBuilder.create()
            .driverClassName(env.getRequiredProperty("spring.datasource.driverClassName"))
            [...]
            .url(url)
            .build();
    }
}
Now you have a `DataSource´ which acts different for every user. Once a DataSource is created it's gonna be cached for 10 minutes. That's it.

Make the application aware of our dynamic DataSource

The place to integrate our newly created DataSource is the DataSource singleton known to the spring context and used in all beans e.g. the EntityManagerFactory

So we need an equivalent to this:
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource() {
    return DataSourceBuilder.create().build();
}
but it has to be more dynamic, than a plain property based DataSourceBuilder:
@Primary
@Bean(name = "dataSource")
public UserSchemaAwareRoutingDataSource dataSource() {
    return new UserSchemaAwareRoutingDataSource();
}


Conclusion

We have a transparent dynamic DataSource which uses the correct DataSource everytime.

Open questions


What to do, when no user is logged in? Is there no database access allowed?
Who sets up the schemes?


Disclaimer

I haven't tested this code!

EDIT:
To implement a Provider<CustomUserDetails> with Spring you need to define this as prototype. You can utilize Springs support of JSR-330 and Spring Securitys SecurityContextHolder:
@Bean @Scope("prototype")
public CustomUserDetails customUserDetails() {
    return return (CustomUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
}
You don't need a RequestInterceptor, the UserProvider or the controller code to update the user anymore.

Does this help?

EDIT2
Just for the record: do NOT reference the CustomUserDetails bean directly. Since this is a prototype, Spring will try to create a proxy for the class CustomUserDetails, which is not a good idea in our case. So just use Providers to access this bean. Or make it an interface.

                        
这篇关于根据登录用户在运行期间更改数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！