在插入/更新/删除之前，如何使用Hibernate/JPA告诉数据库用户是谁? [英] How can I use Hibernate/JPA to tell the DB who the user is before inserts/updates/deletes?

问题描述

摘要(以下详细信息):

Summary (details below):

我想在使用Spring/JPA堆栈保存/更新/删除任何实体之前进行存储的proc调用.

I'd like to make a stored proc call before any entities are saved/updated/deleted using a Spring/JPA stack.

钻孔细节:

我们有一个Oracle/JPA(Hibernate)/Spring MVC(带有Spring数据仓库)应用程序，该应用程序设置为使用触发器将一些表的历史记录到一组历史表中(每个表一个历史表，我们要对其进行审核) ).这些实体中的每个实体都有一个modifiedByUser，该类通过在更新或插入时扩展EmptyInterceptor的类设置.当触发器归档任何插入或更新时，它可以使用此列轻松查看谁进行了更改(我们对哪个应用程序用户感兴趣，而不是数据库用户感兴趣).问题在于，对于删除操作，我们将无法从执行的SQL中获取最后修改的信息，因为它只是普通的delete from x where y.

We have an Oracle/JPA(Hibernate)/Spring MVC (with Spring Data repos) application that is set up to use triggers to record history of some tables into a set of history tables (one history table per table we want audited). Each of these entities has a modifiedByUser being set via a class that extends EmptyInterceptor on update or insert. When the trigger archives any insert or update, it can easily see who made the change using this column (we're interested in which application user, not database user). The problem is that for deletes, we won't get the last modified information from the SQL that is executed because it's just a plain delete from x where y.

为解决此问题，我们想执行一个存储过程，以在执行任何操作之前告诉数据库哪个应用程序用户已登录.然后，审计触发器将在发生删除时查看此值，并使用它来记录执行删除操作的人.

To solve this, we'd like to execute a stored procedure to tell the database which app user is logged in before executing any operation. The audit trigger would then look at this value when a delete happens and use it to record who executed the delete.

是否有任何方法可以拦截开始事务或通过其他方式执行SQL或存储过程，以告诉db哪个用户正在执行事务中将要发生的插入/更新/删除操作，而其余操作则不会执行.操作发生了吗?

Is there any way to intercept the begin transaction or some other way to execute SQL or a stored procedure to tell the db what user is executing the inserts/updates/deletes that are about to happen in the transaction before the rest of the operations happen?

我不太了解数据库方面的工作方式，但如有必要，可以提供更多信息.要点是，存储的proc将创建一个包含会话变量的上下文，并且触发器将在删除时查询该上下文以获取用户ID.

I'm light on details about how the database side will work but can get more if necessary. The gist is that the stored proc will create a context that will hold session variables and the trigger will query that context on delete to get the user ID.

推荐答案

从数据库端开始，这里有一些讨论:

From the database end, there is some discussion on this here:

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

许多应用程序使用会话池来设置多个会话 由多个应用程序用户重用.用户认证 自己到使用单个身份的中间层应用程序 登录数据库并维护所有用户连接.在 在这种模式下，应用程序用户是经过身份验证的用户 应用程序的中间层，但是不为人所知 数据库.....在这种情况下，应用程序通常会连接 作为单个数据库用户，并且所有操作均作为该用户执行. 因为所有用户会话都是作为同一用户创建的，所以这种安全性 模型很难实现每个数据的分离 用户.这些应用程序可以使用 CLIENT_IDENTIFIER 属性来 保留真正的应用程序用户身份到数据库.

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.

从Spring/JPA的角度来看，请参阅下面的8.2节:

From the Spring/JPA side of things see section 8.2 at the below:

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

有时您需要在以下位置准备数据库连接 使用标准连接不容易支持的某些方式 特性.一个示例是在 SYS_CONTEXT，例如MODULE或 CLIENT_IDENTIFIER .本章 解释了如何使用ConnectionPreparer完成此操作.这 该示例将设置CLIENT_IDENTIFIER.

There are times when you want to prepare the database connection in certain ways that aren't easily supported using standard connection properties. One example would be to set certain session properties in the SYS_CONTEXT like MODULE or CLIENT_IDENTIFIER. This chapter explains how to use a ConnectionPreparer to accomplish this. The example will set the CLIENT_IDENTIFIER.

Spring文档中给出的示例使用XML配置.如果您使用的是Java配置，则它看起来像:

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
  {
    String webAppUser = //from Spring Security Context or wherever;

    CallableStatement cs = connection.prepareCall(
                 "{ call DBMS_SESSION.SET_IDENTIFIER(?) }");
    cs.setString(1, webAppUser);
    cs.execute();
    cs.close();

    return connection;
  }
}

通过Configuration类启用AspectJ:

Enable AspectJ via a Configuration class:

@Configuration
@EnableAspectJAutoProxy
public class SomeConfigurationClass
{

}

请注意，尽管在Spring的Oracle扩展的特定部分中隐藏了该内容，但在我看来，第8.2节(与8.1节不同)中没有任何特定于Oracle的(执行的Statement除外)，并且一般方法应为只需指定相关过程调用或SQL，即可在任何数据库中实现:

Note that while this is hidden away in a section specific to Spring's Oracle extensions it seems to me that 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:

例如下面的Postgres，所以我不明白为什么使用Postgres的人不能在下面使用这种方法:

Postgres for example as the following so I don't see why anyone using Postgres couldn't use this approach with the below:

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

这篇关于在插入/更新/删除之前，如何使用Hibernate/JPA告诉数据库用户是谁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！