SpringBoot中为指定Service设置数据库只读权限的方案咨询

在SpringBoot项目中，TestController会调用TestService执行数据库操作，该Service通过EntityManager执行动态生成的原生SQL（可能包含SELECT、TRUNCATE等各类操作）。需要禁止这个Service执行任何修改数据库的操作，但应用内其他Service需保留写权限，因此无法通过修改数据库用户权限实现。

此前尝试过给Service添加@Transactional(readOnly = true)注解，但该注解仅作为性能优化提示，无法阻止写操作，其官方说明如下：

/**

• 可设置为{@code true}表示事务为只读，允许运行时进行相应优化。

• 默认值为{@code false}。

• 这只是给事务子系统的一个提示；不一定会导致写操作尝试失败。

• 无法识别只读提示的事务管理器，在被要求创建只读事务时不会抛出异常，而是静默忽略该提示。
• @see org.springframework.transaction.interceptor.TransactionAttribute#isReadOnly()
• @see org.springframework.transaction.support.TransactionSynchronizationManager#isCurrentTransactionReadOnly()
  */
  boolean readOnly() default false;

可行解决方案 #

方案1：SQL语句前置校验 #

在执行SQL前直接校验语句内容，拦截包含写操作关键字的SQL。

修改TestService的getResults方法，添加校验逻辑：

@Service
public class TestService {

    @Autowired 
    private Client<?> client;

    @PersistenceContext 
    private EntityManager entityManager;

    // 定义允许执行的只读SQL起始关键字
    private static final Set<String> ALLOWED_READ_KEYWORDS = new HashSet<>(Arrays.asList("select", "show", "describe", "explain"));

    public List<Map<String, Object>> getResults(String userMessage) {
        final String rawQuery = client.generateQuery(userMessage).trim().toLowerCase();
        // 先去除SQL中的注释（简单处理，可根据实际情况优化）
        String cleanedQuery = rawQuery.replaceAll("/\\*.*?\\*/", "").trim();
        
        // 校验SQL是否以允许的只读关键字开头
        boolean isReadOnly = ALLOWED_READ_KEYWORDS.stream()
                .anyMatch(keyword -> cleanedQuery.startsWith(keyword));
        
        if (!isReadOnly) {
            throw new IllegalArgumentException("不允许执行修改数据库的SQL操作");
        }

        NativeQueryImpl nativeQuery = (NativeQueryImpl) entityManager.createNativeQuery(rawQuery);
        nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return nativeQuery.getResultList();
    }
}

注意：该方案适合简单场景，需注意处理SQL中的注释、换行等特殊情况，避免被绕过。

方案2：自定义EntityManager代理拦截写操作 #

通过动态代理包装EntityManager，拦截所有可能执行写操作的方法，包括原生SQL创建和实体操作方法。

1. 实现代理处理器：

public class ReadOnlyEntityManagerProxy implements InvocationHandler {
    private final EntityManager targetEm;
    // 定义禁止的写操作SQL关键字
    private static final Set<String> FORBIDDEN_SQL_KEYWORDS = new HashSet<>(Arrays.asList("truncate", "insert", "update", "delete", "drop", "create", "alter"));

    public ReadOnlyEntityManagerProxy(EntityManager targetEm) {
        this.targetEm = targetEm;
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        // 拦截原生SQL创建方法，校验SQL内容
        if ("createNativeQuery".equals(method.getName()) && args != null && args.length > 0 && args[0] instanceof String) {
            String query = ((String) args[0]).trim().toLowerCase().replaceAll("/\\*.*?\\*/", "");
            boolean hasForbiddenKeyword = FORBIDDEN_SQL_KEYWORDS.stream()
                    .anyMatch(keyword -> query.contains(keyword));
            
            if (hasForbiddenKeyword) {
                throw new SecurityException("当前EntityManager仅允许执行只读SQL");
            }
        }
        // 拦截EntityManager的实体写操作方法
        if ("persist".equals(method.getName()) || "merge".equals(method.getName()) || "remove".equals(method.getName())) {
            throw new SecurityException("当前EntityManager不允许执行实体写操作");
        }
        return method.invoke(targetEm, args);
    }

    // 生成代理实例的工具方法
    public static EntityManager createProxy(EntityManager entityManager) {
        return (EntityManager) Proxy.newProxyInstance(
                EntityManager.class.getClassLoader(),
                new Class[]{EntityManager.class},
                new ReadOnlyEntityManagerProxy(entityManager)
        );
    }
}

2. 在TestService中注入并初始化代理后的EntityManager：

@Service
public class TestService {

    @Autowired 
    private Client<?> client;

    private EntityManager readOnlyEntityManager;

    @PostConstruct
    public void initReadOnlyEntityManager(@PersistenceContext EntityManager entityManager) {
        this.readOnlyEntityManager = ReadOnlyEntityManagerProxy.createProxy(entityManager);
    }

    public List<Map<String, Object>> getResults(String userMessage) {
        final String query = client.generateQuery(userMessage).toLowerCase();
        NativeQueryImpl nativeQuery = (NativeQueryImpl) readOnlyEntityManager.createNativeQuery(query);
        nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return nativeQuery.getResultList();
    }
}

说明：该方案覆盖范围更广，既能拦截动态生成的原生SQL，也能阻止通过EntityManager直接操作实体的写行为，适合严格权限控制场景。

方案3：Spring AOP切面拦截校验 #

通过Spring AOP在TestService方法执行前拦截，统一校验生成的SQL内容。

1. 定义切面类：

@Component
@Aspect
public class ReadOnlySqlCheckAspect {
    private static final Set<String> FORBIDDEN_KEYWORDS = new HashSet<>(Arrays.asList("truncate", "insert", "update", "delete", "drop", "create", "alter"));

    // 拦截TestService的getResults方法
    @Before("execution(* com.yourpackage.TestService.getResults(..)) && args(userMessage)")
    public void validateReadOnlySql(String userMessage, JoinPoint joinPoint) {
        TestService service = (TestService) joinPoint.getTarget();
        // 假设client是TestService的私有成员，通过反射获取（或调整Service提供公共方法获取生成的SQL）
        try {
            Field clientField = TestService.class.getDeclaredField("client");
            clientField.setAccessible(true);
            Client<?> client = (Client<?>) clientField.get(service);
            
            String rawQuery = client.generateQuery(userMessage).trim().toLowerCase().replaceAll("/\\*.*?\\*/", "");
            boolean hasForbidden = FORBIDDEN_KEYWORDS.stream().anyMatch(rawQuery::contains);
            
            if (hasForbidden) {
                throw new IllegalArgumentException("禁止执行修改数据库的SQL操作");
            }
        } catch (NoSuchFieldException | IllegalAccessException e) {
            throw new RuntimeException("校验SQL失败", e);
        }
    }
}

说明：该方案将校验逻辑与业务代码解耦，便于统一管理校验规则，但需注意反射访问私有成员的权限问题，可通过调整TestService结构（如提供获取生成SQL的公共方法）优化。

内容的提问来源于stack exchange，提问作者Alberto Soto González