sql-server - 如何使用 Tomcat 连接池在 Java 中使用 MSSQL 的用户模拟？

Question

在我们的应用程序中，我们使用了 Spring Boot 和 Tomcat 连接池管理器。使用特定用户获取连接。因此，无论用户名如何连接到应用程序的任何人，我们都使用应用程序的用户名来保存数据。但是，对于某些操作，我们希望使用 MSSQL 命令“setuser”来模拟'.我们怎样才能做到这一点？

为了更清楚，我们想要的是某种拦截器，以便在执行 sql 语句之前我们可以执行以下命令:

setuser 'user_a'

在释放连接之前我们可以执行:

setuser 

以便重置模拟。
假设在拦截器中我们知道连接是否应该被模拟。

Answer 1

我使用 Jdbc 拦截器解决了这个问题。每当从池中借用连接时，都会调用拦截器中的方法。请检查以下链接:

Jdbc interceptor

这是我的代码:

public class ImpersonatorJdbcInterceptor extends JdbcInterceptor {
    private static final Logger logger = LoggerFactory.getLogger(ImpersonatorJdbcInterceptor.class);

    /**
     *  This method gets invoked whenever a connection is borrowed from the pool.
     *  Since releasing a connection back to pool cannot be intercepted in this method we
     *  need to first reset the previous impersonation.
     * {@inheritDoc}
     */
    @Override
    public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) {
        Authentication auth = SecurityContextHolder.getContext().getAuthentication();
        logger.debug("A connection is borrowed from the pool by {}", (auth != null ? auth.getName() : "null"));
        Connection conn = pooledConnection.getConnection();

        /*
         * Note:
         * 1 We can't impersonate current user
         * 2 We can't impersonate a user when the current session is already impersonated.
         * 3 'setuser' without username resets back to original user who has created the connection
         * 4 The user must have the right permission to be able to impersonate
         * 5 It is the caller's responsibility to close the connection; We only close the connection if this execution fails
         */
        try (PreparedStatement prep = conn.prepareStatement(auth != null ? "setuser; setuser ?" : "setuser")) {
            if(auth != null) {
                prep.setString(1, auth.getName());
            }
            prep.execute();
        } catch (SQLException e) {
            logger.error("Impersonation failed. Please check permission for 'setuser': " + (auth != null ? auth.getName() : "null"), e);
            close(conn);
            throw new IllegalStateException("Oops! Cannot execute statements, please contact the administrator", e);
        }
    }

    /**
     * Closes the statement and rolls back only if something goes wrong.
     * @param conn
     */
    private void close(Connection conn) {
        try {
            if(conn != null && !conn.isClosed()) {
                conn.rollback();
                conn.close();
            }
        } catch (SQLException e) {
            logger.error("Something went wrong and the connection cannot be closed", e);
            //the caller will throw exception
        }
    }

}