如何使用SQL中的WITH CHECK OPTION防止通过视图插入违规数据

WITH CHECK OPTION仅约束通过视图执行的INSERT/UPDATE操作，不拦截DELETE和SELECT；它要求插入时显式提供所有WHERE依赖列且值必须满足条件，否则报错。

WITH CHECK OPTION 只拦 INSERT/UPDATE，不拦 DELETE 和 SELECT

它不是全功能权限控制工具，而是视图层的写入守门员。只要语句是 INSERT INTO view_name 或 UPDATE view_name，数据库就会重新计算视图定义里的 WHERE 条件，检查新值是否满足；不满足就直接报错，基表一比特都不会写。但 DELETE FROM view_name 总是允许，SELECT 也完全不受影响——哪怕你查到的数据根本不符合视图的 WHERE 条件，它也不会拦。

常见误判场景：

• 以为加了它就能防止用户看到敏感行——错，SELECT 不受约束
• 以为它能阻止用户直连基表修改——错，它只管“通过视图发起”的操作
• 用它替代 GRANT 权限管理——错，用户仍有基表 UPDATE 权限时，照样能绕过视图改数据

INSERT 必须显式提供所有 WHERE 依赖列

这是最容易失败的点：视图定义里用了 WHERE tablespace_name = 'SYSAUX'，那插入时 tablespace_name 就不能靠默认值、IDENTITY 或触发器补全，必须出现在 INSERT 的列列表和值列表里，且值严格等于 'SYSAUX'。

错误写法（会报错）：

INSERT INTO test_segments_view_wco (owner, segment_name, segment_type) VALUES ('TEST','NEW_SEG','TABLE');

正确写法：

INSERT INTO test_segments_view_wco (owner, segment_name, segment_type, tablespace_name) VALUES ('TEST','NEW_SEG','TABLE','SYSAUX');

原因：未指定列时，数据库按 NULL 处理，而 NULL = 'SYSAUX' 为假，违反检查。

CASCADED 和 LOCAL 检查模式必须手动选，不能靠默认

多数数据库（MySQL、SQL Server、Oracle）默认用 WITH CASCADED CHECK OPTION，即递归检查所有上游视图的 WHERE 条件。如果你只希望约束当前视图逻辑，又不想被嵌套视图的条件干扰，就得显式写 WITH LOCAL CHECK OPTION。

典型陷阱：

• 建了 v2 AS SELECT * FROM v1 WHERE status = 'draft'，并在 v2 上加 WITH CHECK OPTION —— 默认是 CASCADED，所以插入时既校验 v2 的 status = 'draft'，也校验 v1 的条件（比如 created_by IN (SELECT id FROM admins)）
• 如果 v1 条件太严，v2 插入可能意外失败；这时应改用 WITH LOCAL CHECK OPTION

视图结构决定 WITH CHECK OPTION 能不能生效

它只对「可更新视图」起作用。一旦视图含以下任一成分，WITH CHECK OPTION 会被静默忽略（不报错，但也不检查）：

• GROUP BY、DISTINCT、聚合函数（COUNT()、SUM() 等）
• 子查询（非标量子查询）、窗口函数、UNION/UNION ALL
• 多表 JOIN 且未明确单表可更新性（如没包含所有主键列）

简单验证法：先执行 INSERT INTO your_view_name (...) VALUES (...)，如果报错 The target table ... is not insertable-into 或类似提示，说明视图本身不可更新，WITH CHECK OPTION 根本没机会运行。

真正起效的前提，是视图定义干净——单表、无聚合、有主键、WHERE 条件清晰。否则，加了也是白加。