如何禁止Oracle数据仓库特定列使用算术运算符？

需求说明：需禁止用户在customers表的id列查询条件中使用算术运算符（如+）或字符串拼接符（如||），强制使用直接匹配值的规范写法。

方法一：细粒度审计（FGA）+ 自定义拦截函数 #

利用Oracle细粒度审计功能，结合自定义函数检测并拦截违规SQL：

1. 创建自定义判断函数，识别针对id列的违规运算

CREATE OR REPLACE FUNCTION check_illegal_operator(p_sql IN VARCHAR2) RETURN NUMBER IS
BEGIN
  -- 匹配WHERE子句中id列带有算术运算或字符串拼接的情况
  IF REGEXP_LIKE(p_sql, 'WHERE\s+id\s*=\s*([0-9]+\+|''.*''[+|]{1})', 'i') THEN
    RETURN 1; -- 返回1触发拦截
  ELSE
    RETURN 0; -- 返回0允许执行
  END IF;
END;
/

2. 为customers表绑定FGA策略

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => '你的表所属用户名', -- 替换为实际用户
    object_name => 'CUSTOMERS',
    policy_name => 'ID_COLUMN_NO_OPERATOR',
    audit_column => 'ID',
    audit_condition => '1=1', -- 监控所有对id列的查询
    handler_schema => '你的函数所属用户名',
    handler_module => 'CHECK_ILLEGAL_OPERATOR',
    enable => TRUE,
    statement_types => 'SELECT'
  );
END;
/

当用户执行违规SQL时，Oracle会抛出ORA-28112: FGA policy violation错误，阻止查询执行。

方法二：系统级触发器拦截违规SELECT语句 #

通过创建模式级触发器，监控并拦截针对目标表的违规查询：

CREATE OR REPLACE TRIGGER block_illegal_id_query
BEFORE SELECT ON SCHEMA
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  -- 获取当前执行的SQL语句
  SELECT SQL_TEXT INTO v_sql FROM V$SQLAREA WHERE ADDRESS = SYS_CONTEXT('USERENV', 'SQL_ADDRESS');
  
  -- 检测是否为customers表id列的违规查询
  IF REGEXP_LIKE(v_sql, 'SELECT.*FROM\s+CUSTOMERS\s+WHERE\s+id\s*=\s*([0-9]+\+|''.*''[+|]{1})', 'i') THEN
    RAISE_APPLICATION_ERROR(-20001, '禁止在id列查询条件中使用算术运算符或字符串拼接，请直接使用匹配值');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL; -- 处理无法获取SQL的异常情况
END;
/

注意：该触发器需要较高权限，且可能影响系统性能，建议先在测试环境验证后再部署到生产环境。

补充说明 #

• 可根据实际需求调整正则表达式，覆盖更多复杂的违规写法；
• 两种方案都需确保权限配置正确，避免出现权限不足的问题；
• 可结合Oracle审计功能，记录违规操作以便后续追溯。

内容的提问来源于stack exchange，提问作者Mahir Bahçeci