Oracle10g實現只讀表的N種方法

煙花丶易冷發表於2015-10-09
Oracle

    有時為了提高資料的安全性，我們需要把一個或多個表設定為只讀，即不允許對其執行任何 DML(Insert, Update, Delete) 操作。

    在Oracle11g中實現只讀表非常簡單，只需要執行alter table ... read only;語句即可；但是在11g之前的版本，“只讀”只對資料庫和表空間有效，如果我們要實現一個只讀表，只能透過其他辦法。

    下面就介紹在Oracle10g中實現只讀表的幾種常用方法。首先，我們先建立測試表hoegh。

測試環境

    我們在Oracle 10g+Windows Server 2008 Standard R2進行測試。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> select * from v$version;
  3.  
  4. BANNER
  5. ----------------------------------------------------------------
  6. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
  7. PL/SQL Release 10.2.0.4.0 - Production
  8. CORE 10.2.0.4.0 Production
  9. TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
  10. NLSRTL Version 10.2.0.4.0 - Production
  11.  
  12. SQL>


建立測試使用者及測試表

    我們建立一個測試使用者hoegh，指定預設表空間為users；然後，在hoegh使用者下建立測試表，同樣命名為hoegh。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> create user hoegh identified by hoegh
  3.   2 default tablespace users;
  4.  
  5. 使用者已建立。
  6.  
  7. SQL>
  8. SQL> grant connect,resource to hoegh;
  9.  
  10. 授權成功。
  11.  
  12. SQL>
  13. SQL> conn hoegh/hoegh
  14. 已連線。
  15. SQL>
  16. SQL> create table hoegh(id number,name varchar2(20));
  17.  
  18. 表已建立。
  19.  
  20. SQL> insert into hoegh values(1,'hoegh');
  21.  
  22. 已建立 1 行。
  23.  
  24. SQL> insert into hoegh values(10,'hoegh');
  25.  
  26. 已建立 1 行。
  27.  
  28. SQL> commit;
  29.  
  30. 提交完成。
  31.  
  32. SQL> select * from hoegh;
  33.  
  34.         ID NAME
  35. ---------- --------------------
  36.          1 hoegh
  37.         10 hoegh
  38.  
  39. SQL>


方法一：授予Select許可權

    該方法僅針對非屬主使用者。以hoegh表為例，它的屬主使用者是hoegh，我們可以把hoegh表的select許可權賦予其他使用者，這樣其他使用者對hoegh表就是隻讀的。

點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> grant select on hoegh to scott;
  3.  
  4. 授權成功。
  5.  
  6. SQL> conn scott/tiger
  7. 已連線。
  8. SQL> select * from hoegh.hoegh;
  9.  
  10.         ID NAME
  11. ---------- --------------------
  12.          1 hoegh
  13.         10 hoegh
  14.  
  15. SQL>


ORA-01031報錯

    此時，如果我們對hoegh.hoegh表進行DML操作，系統就會報ORA-01031錯誤，提示許可權不足。

點選(此處)摺疊或開啟

  1. SQL> insert into hoegh.hoegh values(100,'hoegh');
  2. insert into hoegh.hoegh values(100,'hoegh')
  3.                   *
  4. 第 1 行出現錯誤:
  5. ORA-01031: 許可權不足
  6.  
  7.  
  8. SQL>


方法二： 觸發器

    我們可以在hoegh表上建立一個觸發器，當對hoegh表執行DML操作時報錯。如下所示。

建立觸發器


點選(此處)摺疊或開啟

  1. SQL> conn hoegh/hoegh
  2. 已連線。
  3. SQL>
  4. SQL> CREATE OR REPLACE TRIGGER HOEGH_TRG
  5.   2 BEFORE DELETE OR INSERT OR UPDATE
  6.   3 ON HOEGH
  7.   4 REFERENCING NEW AS NEW OLD AS OLD
  8.   5 FOR EACH ROW
  9.   6 DECLARE
  10.   7 BEGIN
  11.   8 RAISE_APPLICATION_ERROR (-20001, 'Table is read only table.');
  12.   9 END;
  13.  10 /
  14.  
  15. 觸發器已建立


 

ORA-20001報錯

    此時，如果我們對hoegh表進行DML操作，系統就會報ORA-20001錯誤，提示“Table is read only table”。


點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> insert into hoegh values(100,'hoegh');
  3. insert into hoegh values(100,'hoegh')
  4.             *
  5. 第 1 行出現錯誤:
  6. ORA-20001: Table is read only table.
  7. ORA-06512: 在 "HOEGH.HOEGH_TRG", line 3
  8. ORA-04088: 觸發器 'HOEGH.HOEGH_TRG' 執行過程中出錯
  9.  
  10.  
  11. SQL>


方法三：檢查約束

    我們知道對constraint的開啟和關閉共有四種:

l  enable( validate) :啟用約束,建立索引,對已有及新加入的資料執行約束。

l  enable novalidate :啟用約束,建立索引,僅對新加入的資料強制執行約束,而不管表中的現有資料。

l  disable( novalidate):關閉約束,刪除索引,可以對約束列的資料進行修改等操作。

l  disable validate :關閉約束,刪除索引,不能對錶進行 插入/更新/刪除等操作。

    因此，我們可以利用disable validate來實現只讀表。

    如下所示：

點選(此處)摺疊或開啟

  1. ALTER TABLE HOEGH ADD CONSTRAINT READ_ONLY_CONST CHECK(0=0) DISABLE VALIDATE;


ORA-25128報錯

    此時，如果我們對hoegh表進行DML操作，系統就會報ORA-25128錯誤，提示“不能對帶有禁用和驗證約束條件  的表進行插入/更新/刪除”。

點選(此處)摺疊或開啟

  1. SQL> insert into hoegh values(100,'hoegh');
  2. insert into hoegh values(100,'hoegh')
  3. *
  4. 第 1 行出現錯誤:
  5. ORA-25128: 不能對帶有禁用和驗證約束條件 (HOEGH.READ_ONLY_CONST) 的表進行插入/更新/刪除
  6.  
  7.  
  8. SQL>


方法四：只讀表空間

    設定只讀表空間的主要目的是為了表空間中的靜態資料不被修改，從而能夠進行資料庫的備份和恢復等操作，還能夠保護只讀表空間中的資料不被修改。

  • 設定只讀表空間的語法：ALTER TABLESPACE <表空間> READ ONLY;
  • 將表空間設定為讀寫的語法:ALTER TABLESPACE <表空間> READ WRITE;

    由上面建立測試使用者的語句我們得知，hoegh使用者的預設表空間是users，因此我們將users表空間設為只讀表空間。這樣，hoegh使用者下的所有表都將會是隻讀表，包括hoegh表。
    如下所示。


點選(此處)摺疊或開啟

  1. SQL> show user
  2. USER 為 "HOEGH"
  3. SQL> alter table hoegh drop constraint READ_ONLY_CONST;
  4.  
  5. 表已更改。
  6.  
  7. SQL>
  8. SQL> conn sys/hoegh as sysdba
  9. 已連線。
  10. SQL>
  11. SQL> alter tablespace users read only;
  12.  
  13. 表空間已更改。
  14.  
  15. SQL>
  16. SQL> conn hoegh/hoegh
  17. 已連線。
  18. SQL> select * from hoegh;
  19.  
  20.         ID NAME
  21. ---------- --------------------
  22.          1 hoegh
  23.         10 hoegh


ORA-00372& ORA-01110報錯

    此時，如果我們對hoegh表進行DML操作，系統就會報ORA-00372& ORA-01110錯誤，提示無法修改資料檔案。


點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> insert into hoegh values(100,'hoegh');
  3. insert into hoegh values(100,'hoegh')
  4.             *
  5. 第 1 行出現錯誤:
  6. ORA-00372: 此時無法修改檔案 4
  7. ORA-01110: 資料檔案 4: 'E:\ATSTEST\USERS01.DBF'
  8.  
  9.  
  10. SQL>


DROP操作不受影響

    需要注意的是，只讀表空間下是可以執行DROP操作的。

    我們知道，每個資料庫在執行的時候,都至少會有一個ONLINE表空間，那就是SYSTEM表空間，其中儲存了資料字典以及PLSQL中的儲存過程、觸發器、函式、包等等資料庫物件。當進行DDL進行資料庫的刪除操作的時候，本質是是操作的SYSTEM表空間，ORACLE會在SYSTEM儲存的資料字典中，將刪除的表設定為DROP狀態，等該表空間的狀態變成READ WRITE狀態的時候，才會真正的從資料庫裡面刪除該表。

方法五： 只讀資料庫

    當一個正常開啟的資料庫被設定為只讀狀態時，使用者只能查詢資料，但不能以任何方式對資料庫物件進行修改。處於只讀狀態，可能保證資料檔案和重做日誌檔案中的內容不被修改，但是並不限制那些不會寫入資料檔案與重做日誌檔案的操作。

l  設定只讀命令：alter database open read only;

l  取消只讀命令：alter database open read write;


點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> conn sys/hoegh as sysdba
  3. 已連線。
  4. SQL> alter tablespace users read write;
  5.  
  6. 表空間已更改。
  7.  
  8. SQL>
  9. SQL> shutdown immediate
  10. 資料庫已經關閉。
  11. 已經解除安裝資料庫。
  12. ORACLE 例程已經關閉。
  13. SQL>
  14. SQL> startup mount
  15. ORACLE 例程已經啟動。
  16.  
  17. Total System Global Area 1258291200 bytes
  18. Fixed Size 2163712 bytes
  19. Variable Size 360446976 bytes
  20. Database Buffers 889192448 bytes
  21. Redo Buffers 6488064 bytes
  22. 資料庫裝載完畢。
  23. SQL>
  24. SQL> alter database open read only;
  25.  
  26. 資料庫已更改。
  27.  
  28. SQL>
  29. SQL> conn hoegh/hoegh
  30. 已連線。
  31. SQL>
  32. SQL> select * from hoegh;
  33.  
  34.         ID NAME
  35. ---------- --------------------
  36.          1 hoegh
  37.         10 hoegh


ORA-01552報錯

   此時，如果我們對hoegh表進行DML操作，系統就會報ORA-01552錯誤，提示非系統表空間 'USERS' 不能使用系統回退段。


點選(此處)摺疊或開啟

  1. SQL>
  2. SQL> insert into hoegh values(100,'hoegh');
  3. insert into hoegh values(100,'hoegh')
  4.             *
  5. 第 1 行出現錯誤:
  6. ORA-01552: 非系統表空間 'USERS' 不能使用系統回退段
  7.  
  8.  
  9. SQL>


    當然，這只是一個實現手段，我們肯定不會為了設定一個只讀表而將整個資料庫設定為只讀狀態。

總結

    上面這幾種辦法都可以實現將一個表設定為只讀表，大家可以根據各自的具體需求選用最合適的方法。比如，最近我們一個專案為了提高資料的安全性，需要將配置資料（多張表）設定為只讀表；並且，當初為了管理方便，所有的配置資料存放到一個單獨的表空間，這樣，我就會選用只讀表空間的方法來實現這個具體需求。

hoegh
15.8.4
-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29618264/viewspace-1813622/，如需轉載，請註明出處，否則將追究法律責任。

相關文章