Oracle 10g實現唯讀表的N種方法

Oracle 10g實現唯讀表的N種方法

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

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

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

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

SQL>

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
 
SQL>
 

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

SQL>

SQL> create user bkjia identified by bkjia 
  2 default tablespace users;
 
使用者已建立。
 
SQL>
SQL> grant connect,resource to bkjia;
 
授權成功。
 
SQL>
SQL> conn bkjia/hoegh
已串連。
SQL>
SQL> create table bkjia(id number,name varchar2(20));
 
表已建立。
 
SQL> insert into bkjia values(1,'bkjia');
 
已建立 1 行。
 
SQL> insert into bkjia values(10,'bkjia');
 
已建立 1 行。
 
SQL> commit;
 
提交完成。
 
SQL> select * from bkjia;
 
        ID NAME
---------- --------------------
        1 bkjia 
        10 bkjia 
 
SQL>
 

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

SQL>

SQL> grant select on bkjia to scott;
 
授權成功。
 
SQL> conn scott/tiger
已串連。
SQL> select * from bkjia.hoegh;
 
        ID NAME
---------- --------------------
        1 bkjia 
        10 bkjia 
 
SQL>
 

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

SQL> insert into bkjia.hoegh values(100,'bkjia');

insert into bkjia.hoegh values(100,'bkjia')
                  *
第 1 行出現錯誤:
ORA-01031: 許可權不足
 
 
SQL>
 

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

建立觸發器
 

SQL> conn bkjia/hoegh

已串連。
SQL>
SQL> CREATE OR REPLACE TRIGGER bkjia_TRG
  2 BEFORE DELETE OR INSERT OR UPDATE
  3 ON bkjia 
  4 REFERENCING NEW AS NEW OLD AS OLD
  5 FOR EACH ROW
  6 DECLARE
  7 BEGIN
  8 RAISE_APPLICATION_ERROR (-20001, 'Table is read only table.');
  9 END;
 10 /
 
觸發器已建立
 

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

SQL>

SQL> insert into bkjia values(100,'bkjia');
insert into bkjia values(100,'bkjia')
            *
第 1 行出現錯誤:
ORA-20001: Table is read only table.
ORA-06512: 在 "bkjia.HOEGH_TRG", line 3
ORA-04088: 觸發器 'bkjia.HOEGH_TRG' 執行過程中出錯
 
 
SQL>
 

方法三：檢查約束
    我們知道對constraint的開啟和關閉共有四種:

1. enable( validate) :啟用約束,建立索引,對已有及新加入的資料執行約束。
2. enable novalidate :啟用約束,建立索引,僅對新加入的資料強制執行約束,而不管表中的現有資料。
3. disable( novalidate):關閉約束,刪除索引,可以對約束列的資料進行修改等操作。
4. disable validate :關閉約束,刪除索引,不能對錶進行 插入/更新/刪除等操作。

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

如下所示：

ALTER TABLE bkjia ADD CONSTRAINT READ_ONLY_CONST CHECK(0=0) DISABLE VALIDATE;
 

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

SQL> insert into bkjia values(100,'bkjia');

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

方法四：唯讀資料表空間
    設定唯讀資料表空間的主要目的是為了資料表空間中的待用資料不被修改，從而能夠進行資料庫的備份和恢複等操作，還能夠保護唯讀資料表空間中的資料不被修改。

設定唯讀資料表空間的文法：ALTER TABLESPACE <資料表空間> READ ONLY;
將資料表空間設定為讀寫的文法:ALTER TABLESPACE <資料表空間> READ WRITE;
    由上面建立測試使用者的語句我們得知，bkjia使用者的預設資料表空間是users，因此我們將users資料表空間設為唯讀資料表空間。這樣，bkjia使用者下的所有表都將會是唯讀表，包括bkjia表。
    如下所示。

SQL> show user

USER 為 "bkjia"
SQL> alter table bkjia drop constraint READ_ONLY_CONST;
 
表已更改。
 
SQL>
SQL> conn sys/bkjia as sysdba
已串連。
SQL>
SQL> alter tablespace users read only;
 
資料表空間已更改。
 
SQL>
SQL> conn bkjia/hoegh
已串連。
SQL> select * from bkjia;
 
        ID NAME
---------- --------------------
        1 bkjia 
        10 bkjia 
 

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

SQL>

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

DROP操作不受影響
    需要注意的是，唯讀資料表空間下是可以執行DROP操作的。

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

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

• 設定唯讀命令：alter database open read only;
• 取消唯讀命令：alter database open read write;

SQL>

SQL> conn sys/bkjia as sysdba
已串連。
SQL> alter tablespace users read write;
 
資料表空間已更改。
 
SQL>
SQL> shutdown immediate
資料庫已經關閉。
已經卸載資料庫。
ORACLE 常式已經關閉。
SQL>
SQL> startup mount
ORACLE 常式已經啟動。
 
Total System Global Area 1258291200 bytes
Fixed Size 2163712 bytes
Variable Size 360446976 bytes
Database Buffers 889192448 bytes
Redo Buffers 6488064 bytes
資料庫裝載完畢。
SQL>
SQL> alter database open read only;
 
資料庫已更改。
 
SQL>
SQL> conn bkjia/hoegh
已串連。
SQL>
SQL> select * from bkjia;
 
        ID NAME
---------- --------------------
        1 bkjia 
        10 bkjia 
 

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

SQL>

SQL> insert into bkjia values(100,'bkjia');
insert into bkjia values(100,'bkjia')
            *
第 1 行出現錯誤:
ORA-01552: 非系統資料表空間 'USERS' 不能使用系統回退段
 
 
SQL>
 

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

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