postgresql(设置角色用户)命令在SSM项目中如何使用? [英] How does the postgresql (set role user) command use in SSM projects?

问题描述

现在该项目正在使用springmvc + spring + mybatis + druid + postgresql 项目中的用户与数据库中的用户相对应，因此，每次运行SQL时，都需要使用(设置角色用户)命令切换用户，然后执行数据库的粗体操作.

Now the project is using springmvc+ spring + mybatis + druid + postgresql The users in the project correspond to the users in the database, so each time you run SQL, you switch the users with the (set role user) command and then perform the crud operations of the database.

我的问题: 因为连接池中有许多连接，所以第一步是获取数据库的连接，然后切换用户，然后在数据库上执行业务SQL的操作.但是我不知道该逻辑应该在项目的哪一部分进行处理，因为连接池的连接和SQL的执行是由基础代码实现的.你有什么好的计划吗? 您能否为我提供完整的演示，例如以下操作:

My question: Because there are many connections in the connection pool, the first step is to get the connection of the database, then switch users, and then perform the operation of business SQL on the database. But I don't know which part of the project this logic should be processed, because the connection of the connection pool and the execution of SQL are implemented by the underlying code. Do you have any good plans? Can you provide me with a complete demo, such as the following operations:

步骤1，从spring security(或shiro)获取用户名.

Step 1, get the user's name from spring security (or shiro).

第2步，从连接池中获取当前正在使用数据库的连接.

Step 2, Get the connection currently using the database from the connection pool.

第3步，执行SQL(设置角色用户)以切换角色.

Step 3, execute SQL (set role user) to switch roles.

第4步，执行粗操作.

第5步，重置数据库连接(重置角色)

Step 5, Reset the database connection(reset role)

推荐答案

在 mybatis-spring .

除非已使用mybatis-spring，否则第一步将是更改项目的配置，以便使用mybatis-spring提供的org.mybatis.spring.SqlSessionFactoryBean获得SqlSessionFactory.

Unless you already use mybatis-spring the first step would be to change the configuration of your project so that you obtain SqlSessionFactory using org.mybatis.spring.SqlSessionFactoryBean provided by mybatis-spring.

下一步是设置/重置连接的用户角色的实现.在mybatis中，连接生命周期由实现org.apache.ibatis.transaction.Transaction接口的类控制.查询执行程序使用此类的实例来获取连接.

The next step is the implementation of setting/resetting the user role for the connection. In mybatis the connection lifecycle is controlled by the class implementing org.apache.ibatis.transaction.Transaction interface. The instance of this class is used by the query executor to get the connection.

简而言之，您需要创建自己的此类的实现，并将mybatis配置为使用它.

In a nutshell you need to create your own implementation of this class and configure mybatis to use it.

您的实现可以基于mybatis-spring的SpringManagedTransaction，并且看起来像这样:

Your implementation can be based on the SpringManagedTransaction from mybatis-spring and would look something like:

import org.springframework.security.core.Authentication;

class UserRoleAwareSpringManagedTransaction extends SpringManagedTransaction {

  public UserRoleAwareSpringManagedTransaction(DataSource dataSource) {
    super(dataSource);
  }

  @Override
  public Connection getConnection() throws SQLException {
    Connection connection = getCurrentConnection();
    setUserRole(connection);
    return connection;
  }

  private Connection getCurrentConnection() {
    return super.getConnection();
  }

  @Override
  public void close() throws SQLException {
    resetUserRole(getCurrentConnection());
    super.close();
  }  

  private void setUserRole(Connection connection) {
    Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
    String username = authentication.getName();
    Statement statement = connection.createStatement();
    try {
      // note that this direct usage of usernmae is a subject for SQL injection
      // so you need to use the suggestion from
      // https://stackoverflow.com/questions/2998597/switch-role-after-connecting-to-database
      // about encoding of the username
      statement.execute("set role '" + username + "'");
    } finally {
      statement.close();
    }
  }

  private void resetUserRole(Connection connection) {
    Statement statement = connection.createStatement();
    try {
      statement.execute("reset role");
    } finally {
      statement.close();
    }
  }

}

现在，您需要配置mybatis以使用您的Transaction实现.为此，您需要实现与 mybatis-spring 提供的org.mybatis.spring.transaction.SpringManagedTransactionFactory类似的TransactionFactory:

Now you need to configure mybatis to use you Transaction implementation. For this you need to implement TransactionFactory similar to org.mybatis.spring.transaction.SpringManagedTransactionFactory provided by mybatis-spring:

public class UserRoleAwareSpringManagedTransactionFactory implements TransactionFactory {
  @Override
  public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
    return new UserRoleAwareSpringManagedTransaction(dataSource);
  }
  @Override
  public Transaction newTransaction(Connection conn) {
    throw new UnsupportedOperationException("New Spring transactions require a DataSource");
  }
  @Override
  public void setProperties(Properties props) {
  }
}

然后在您的Spring上下文中定义类型为UserRoleAwareSpringManagedTransactionFactory的bean，然后在您的spring上下文中将其注入到SqlSessionFactoryBeen的transactionFactory属性中.

And then define a bean of type UserRoleAwareSpringManagedTransactionFactory in your spring context and inject it into transactionFactory property of the SqlSessionFactoryBeen in your spring context.

现在，每次mybatis获得Connection时，Transaction的实现将设置当前的spring安全用户来设置角色.

Now every time mybatis obtains a Connection the implementation of Transaction will set the current spring security user to set the role.

这篇关于postgresql(设置角色用户)命令在SSM项目中如何使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！