几年以前有朋友问过类似的问题，当时考虑了一下，没有想到好的解决方法，前些天有客户询问同样的问题，没有办法周末仔细琢磨了一下，总算是找到一个解决的方法。
其实现在Oracle有专门的工具可以解决这个问题，就是Oracle的FireWall，通过直连的配置方式可以阻塞预配置好的FOR UPDATE操作，不过那需要单独的软件。这里主要方案是要通过数据库现有的功能实现这个目标。
限制FOR UPDATE是有实际意义的，有时候只希望给用户分配查询权限，但是一旦分配了SELECT权限后，用户就自动拥有了FOR UPDATE能力，虽然用户并没有真正UPDATE的权限，但是仍然可以将表的记录锁定，而这有时候并不是所期望的。
SQL> conn test/test Connected. SQL> CREATE TABLE t_update (id NUMBER, name varchar2(30)); TABLE created. SQL> INSERT INTO t_update VALUES (1, 'a'); 1 ROW created. SQL> commit; Commit complete. SQL> CREATE USER u1 IDENTIFIED BY u1; USER created. SQL> GRANT CREATE SESSION TO u1; GRANT succeeded. SQL> GRANT SELECT ON t_update TO u1; GRANT succeeded. |
切换到U1用户，现在可以对T_UPDATE进行SELECT FOR UPDATE操作：
SQL> conn u1/u1 Connected. SQL> SET sqlp 'SQL2> ' SQL2> SELECT * FROM test.t_update WHERE id = 1 FOR UPDATE; ID NAME ---------- ------------------------------ 1 a |
为了避免FOR UPDATE操作，可以封装一层视图。
SQL> CREATE VIEW v_update AS SELECT * FROM t_update; VIEW created. SQL> GRANT SELECT ON v_update TO u1; GRANT succeeded. |
但是如果仅是视图，那么没有任何作用，FOR UPDATE操作同样可以对单表查询的视图执行：
SQL2> SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE; ID NAME ---------- ------------------------------ 1 a |
如果添加ROWNUM等伪列，可以避免直接FOR UPDATE：
SQL> CREATE OR REPLACE VIEW v_update AS SELECT rownum rn, a.* FROM t_update a; VIEW created. |
但是如果FOR UPDATE指定ROWNUM伪列外的真实列，还是可以绕开：
SQL2> SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE; SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE * ERROR at line 1: ORA-02014: cannot SELECT FOR UPDATE FROM VIEW WITH DISTINCT, GROUP BY, etc. SQL2> SELECT id, name FROM test.v_update WHERE id = 1 FOR UPDATE; ID NAME ---------- ------------------------------ 1 a |
通过报错信息可以看出，如果包含了GROUP BY或DISTINCT就可以阻止FOR UPDATE操作：
SQL> CREATE OR REPLACE VIEW v_update AS SELECT DISTINCT * FROM t_update; VIEW created. |
但是这种方式无疑会带来性能问题，更重要的是，如果表中存在重复记录，那么DISTINCT操作会使得重复记录丢失。
而最好的解决方法是采用UNION ALL方式创建视图：
SQL> CREATE OR REPLACE VIEW v_update AS 2 SELECT * FROM t_update 3 UNION ALL 4 SELECT * FROM t_update WHERE 1 = 2; VIEW created. |
现在就达到了阻止FOR UPDATE的操作，且对于查询基表的性能影响最小：
SQL2> SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE; SELECT * FROM test.v_update WHERE id = 1 FOR UPDATE * ERROR at line 1: ORA-02014: cannot SELECT FOR UPDATE FROM VIEW WITH DISTINCT, GROUP BY, etc. |
对于不希望用户执行FOR UPDATE操作的表，可以创建成UNION ALL视图，并将视图的查询权限授权给用户。