按照Spring Security会话使用专用数据源连接的Hibernate会话 [英] Use a Hibernate Session with dedicated datasource connection per Spring Security session

问题描述

是否可以在运行时更改 Hibernate Session 的数据库身份验证（或创建一个新的身份验证），并将其链接到当前登录的Web用户？

例如当一个特定的 Spring Security -managed用户登录时，用一个不同的数据库角色重新连接它，并在该用户的http会话的整个生命周期内使用该连接？

解决方案

我不确定每个用户都有专门的数据库会话是否可行。即使在技术上可行的情况下，拥有庞大用户群的应用程序也不会很好地扩展。另一种方法是配置标准连接池，并在每次从池中签出时将该连接与特定的Web应用程序用户关联。

有一些关于（从Oracle的角度来看，不管RDBMS如何都适用）：

许多应用程序使用会话池来设置多个会话
被多个应用程序用户重复使用。用户自己将
认证给中间层应用程序，中间层应用程序使用单一标识
登录数据库并维护所有用户连接。在
这个模型中，应用程序用户是被认证到应用程序的
中间层但是不知道数据库的用户.....在
这些情况下，应用程序通常作为单个
数据库用户连接，并且所有操作都以该用户身份进行。由于所有用户
会话都是作为同一用户创建的，因此此安全模型使其
非常难以为每个用户实现数据分离。 这些
应用程序可以使用CLIENT_IDENTIFIER属性将
真实应用程序用户身份保存到数据库中。

https：// docs .oracle.com / cd / B19306_01 / network.102 / b14266 / apdvprxy.htm＃i1010372

您可能会如何达到这个目标将在第8.2节讨论下面的Spring文档。请注意，虽然这被隐藏在特定于Spring的Oracle扩展的部分中，但在8.2节（不同于8.1）中没有任何东西是Oracle特定的（执行Statement之外），并且任何数据库的一般方法都应该可行，只需指定相关过程调用或SQL：

http://docs.spring.io/spring-data/jdbc/docs/current/reference/html/orcl.connection.html

我对Postgres不太熟悉，但是我想你想在每个Connection checkout上打的电话就像这样：

https://www.postgresql.org/docs/ 8.4 / static / sql-set-role.html

Spring文档中给出的示例使用XML配置。如果您使用的是Java配置，那么它看起来像：

  @Component 
 @Aspect 
 public class ClientIdentifierConnectionPreparer实现ConnectionPreparer 
 {
 @AfterReturning（pointcut =execution（* * .getConnection（..）），returns =connection）
 public Connection prepare（Connection connection）throws SQLException 
 {
 SecurityContextHolder.getContext（）。getAuthentication（）。getPrincipal（）; 
 String webAppUser = //; 
 
 CallableStatement cs = connection.prepareCall（my postgres statement）; 
 cs.setString（1，webAppUser）; 
 cs.execute（）; 
 cs.close（）; 
 
返回连接; 
 
 
 
 @Configuration 
 @EnableAspectJAutoProxy 
公共类SomeConfigurationClass 
 {
 
} 
  

Is it possible to change the database authentication of a Hibernate Session (or create a new one) at runtime, and link it to the current logged-in web-user?

E.g. when a specific Spring Security-managed user logs in, reconnect it with a different database role, and use that connection throughout the lifetime of the http session for that user?

解决方案

I'm not sure it is feasible to have a dedicated DB session per user. Even if technically possible an application with a large user base is not going to scale very well. An alternative approach is to configure a standard connection pool and associate the connection with a specific web application user each time it is checked out from the pool.

There is some discussion on this here (from an Oracle perspective but same principles apply regardless of RDBMS):

Many applications use session pooling to set up a number of sessions to be reused by multiple application users. Users authenticate themselves to a middle-tier application, which uses a single identity to log in to the database and maintains all the user connections. In this model, application users are users who are authenticated to the middle tier of an application, but who are not known to the database.....in these situations, the application typically connects as a single database user and all actions are taken as that user. Because all user sessions are created as the same user, this security model makes it very difficult to achieve data separation for each user. These applications can use the CLIENT_IDENTIFIER attribute to preserve the real application user identity through to the database.

https://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvprxy.htm#i1010372

How you might acheive this is discussed in section 8.2 of the Spring documentation at the below. Note that while this is hidden away in a section specific to Spring's Oracle extensions there is nothing in section 8.2 (unlike 8.1) that is Oracle specific (other than the Statement executed) and the general approach should be feasible with any Database simply by specifying the relevant procedure call or SQL:

http://docs.spring.io/spring-data/jdbc/docs/current/reference/html/orcl.connection.html

I am not too familiar with Postgres but I guess the call you would want to make on each Connection checkout would be something like:

https://www.postgresql.org/docs/8.4/static/sql-set-role.html

The example given in the Spring docs uses XML config. If you are using Java config then it looks like:

@Component
@Aspect
public class ClientIdentifierConnectionPreparer implements ConnectionPreparer
{
  @AfterReturning(pointcut = "execution(* *.getConnection(..))", returning = "connection")
  public Connection prepare(Connection connection) throws SQLException
  {
    SecurityContextHolder.getContext().getAuthentication().getPrincipal();
    String webAppUser = //;

    CallableStatement cs = connection.prepareCall("my postgres statement");
    cs.setString(1, webAppUser);
    cs.execute();
    cs.close();

    return connection;
  }
}

@Configuration
@EnableAspectJAutoProxy
public class SomeConfigurationClass
{

}

这篇关于按照Spring Security会话使用专用数据源连接的Hibernate会话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！