Spring Boot中修改Oracle已建立连接的osuser实现审计需求

核心原理 #

Oracle支持通过ALTER SESSION语句动态修改当前连接的OS_USER属性，无需重新创建数据库连接。这是实现连接池复用+审计标识的核心基础。

具体实现步骤 #

1. 存储当前请求的用户标识（ThreadLocal）

用ThreadLocal在请求生命周期内传递当前用户的审计标识，避免线程安全问题：

public class UserContextHolder {
    private static final ThreadLocal<String> OS_USER_HOLDER = new ThreadLocal<>();

    public static void setOsUser(String osUser) {
        OS_USER_HOLDER.set(osUser);
    }

    public static String getOsUser() {
        return OS_USER_HOLDER.get();
    }

    public static void clear() {
        OS_USER_HOLDER.remove();
    }
}

2. 拦截请求，设置用户标识

通过Servlet过滤器从请求上下文（比如Spring Security、请求头）获取用户标识，存入ThreadLocal：

@Component
public class OsUserFilter implements Filter {
    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        // 示例：从Spring Security获取当前登录用户
        String currentUser = SecurityContextHolder.getContext().getAuthentication().getName();
        UserContextHolder.setOsUser(currentUser);
        try {
            chain.doFilter(request, response);
        } finally {
            // 请求结束后清理ThreadLocal，避免内存泄漏
            UserContextHolder.clear();
        }
    }
}

3. 动态修改会话OS_USER（两种可选方式）

方式一：代理DataSource，自动处理连接的设置与重置

通过Spring的DelegatingDataSource代理原始连接池，在获取连接时设置OS_USER，归还连接时重置为默认值：

@Component
public class OsUserAwareDataSource extends DelegatingDataSource {
    private static final String DEFAULT_OS_USER = "POOL_DEFAULT";

    public OsUserAwareDataSource(DataSource targetDataSource) {
        super(targetDataSource);
    }

    @Override
    public Connection getConnection() throws SQLException {
        Connection rawConn = super.getConnection();
        String osUser = UserContextHolder.getOsUser();
        
        // 设置当前用户的OS_USER
        if (osUser != null) {
            try (PreparedStatement pstmt = rawConn.prepareStatement("ALTER SESSION SET OS_USER = ?")) {
                pstmt.setString(1, osUser);
                pstmt.execute();
            }
        }
        
        // 代理Connection，在close时重置OS_USER
        return wrapConnection(rawConn);
    }

    private Connection wrapConnection(Connection delegate) {
        return (Connection) Proxy.newProxyInstance(
                Connection.class.getClassLoader(),
                new Class[]{Connection.class},
                (proxy, method, args) -> {
                    if ("close".equals(method.getName())) {
                        // 重置为默认OS_USER
                        try (PreparedStatement pstmt = delegate.prepareStatement("ALTER SESSION SET OS_USER = ?")) {
                            pstmt.setString(1, DEFAULT_OS_USER);
                            pstmt.execute();
                        } finally {
                            delegate.close();
                        }
                        return null;
                    }
                    return method.invoke(delegate, args);
                }
        );
    }
}

方式二：AOP拦截事务方法，处理会话属性

如果应用大量使用Spring事务，用AOP拦截@Transactional方法，在事务执行前后设置/重置OS_USER：

@Aspect
@Component
public class OsSessionAspect {
    private static final String DEFAULT_OS_USER = "POOL_DEFAULT";

    @Autowired
    private DataSource dataSource;

    @Around("@annotation(org.springframework.transaction.annotation.Transactional)")
    public Object aroundTransactionalMethod(ProceedingJoinPoint joinPoint) throws Throwable {
        String osUser = UserContextHolder.getOsUser();
        Connection connection = null;
        try {
            if (osUser != null) {
                // 获取当前事务绑定的连接
                connection = DataSourceUtils.getConnection(dataSource);
                try (PreparedStatement pstmt = connection.prepareStatement("ALTER SESSION SET OS_USER = ?")) {
                    pstmt.setString(1, osUser);
                    pstmt.execute();
                }
            }
            return joinPoint.proceed();
        } finally {
            if (connection != null) {
                // 重置OS_USER后释放连接
                try (PreparedStatement pstmt = connection.prepareStatement("ALTER SESSION SET OS_USER = ?")) {
                    pstmt.setString(1, DEFAULT_OS_USER);
                    pstmt.execute();
                } finally {
                    DataSourceUtils.releaseConnection(connection, dataSource);
                }
            }
        }
    }
}

关键注意事项 #

• SQL注入防护：必须使用PreparedStatement传递OS_USER参数，禁止直接字符串拼接，避免注入风险。
• 连接池配置：确保连接池的minimumIdle、maximumPoolSize等参数符合业务需求，同时连接的重置逻辑要覆盖所有归还场景（包括异常情况）。
• ThreadLocal清理：必须在请求结束后清理ThreadLocal，防止内存泄漏。

内容的提问来源于stack exchange，提问作者DigitalMachine