限制訪問表的FOR UPDATE操作

幾年以前有朋友問過類似的問題，當時考慮了一下，沒有想到好的解決方法，前些天有客戶詢問同樣的問題，沒有辦法週末仔細琢磨了一下，總算是找到一個解決的方法。

其實現在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檢視，並將檢視的查詢許可權授權給使用者。