Oracle 10g实现只读表的N种方法

有时为了提高数据的安全性,我们需要把一个或多个表设置为只读,即不允许对其执行任何 DML(Insert, Update, Delete) 操作。

在Oracle11g中实现只读表非常简单,只需要执行alter table ... read only;语句即可;但是在11g之前的版本,“只读”只对数据库和表空间有效,如果我们要实现一个只读表,只能通过其他办法。

下面就介绍在Oracle10g中实现只读表的几种常用方法。首先,我们先创建测试表linuxidc。

测试环境
    我们在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>
 

创建测试用户及测试表
    我们创建一个测试用户linuxidc,指定默认表空间为users;然后,在linuxidc用户下创建测试表,同样命名为linuxidc。

SQL>

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

方法一:授予Select权限
    该方法仅针对非属主用户。以linuxidc表为例,它的属主用户是linuxidc,我们可以把hoegh表的select权限赋予其他用户,这样其他用户对linuxidc表就是只读的。

SQL>

SQL> grant select on linuxidc to scott;
 
授权成功。
 
SQL> conn scott/tiger
已连接。
SQL> select * from linuxidc.hoegh;
 
        ID NAME
---------- --------------------
        1 linuxidc 
        10 linuxidc 
 
SQL>
 

ORA-01031报错
    此时,如果我们对linuxidc.hoegh表进行DML操作,系统就会报ORA-01031错误,提示权限不足。

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

insert into linuxidc.hoegh values(100,'linuxidc')
                  *
第 1 行出现错误:
ORA-01031: 权限不足
 
 
SQL>
 

方法二: 触发器
    我们可以在linuxidc表上创建一个触发器,当对linuxidc表执行DML操作时报错。如下所示。

创建触发器
 

SQL> conn linuxidc/hoegh

已连接。
SQL>
SQL> CREATE OR REPLACE TRIGGER linuxidc_TRG
  2 BEFORE DELETE OR INSERT OR UPDATE
  3 ON linuxidc 
  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报错
    此时,如果我们对linuxidc表进行DML操作,系统就会报ORA-20001错误,提示“Table is read only table”。
 

SQL>

SQL> insert into linuxidc values(100,'linuxidc');
insert into linuxidc values(100,'linuxidc')
            *
第 1 行出现错误:
ORA-20001: Table is read only table.
ORA-06512: 在 "linuxidc.HOEGH_TRG", line 3
ORA-04088: 触发器 'linuxidc.HOEGH_TRG' 执行过程中出错
 
 
SQL>
 

方法三:检查约束
    我们知道对constraint的开启和关闭共有四种:

  1. enable( validate) :启用约束,创建索引,对已有及新加入的数据执行约束。
  2. enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据。
  3. disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作。
  4. disable validate :关闭约束,删除索引,不能对表进行 插入/更新/删除等操作。

因此,我们可以利用disable validate来实现只读表。

如下所示:

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

ORA-25128报错
    此时,如果我们对linuxidc表进行DML操作,系统就会报ORA-25128错误,提示“不能对带有禁用和验证约束条件  的表进行插入/更新/删除”。


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

insert into linuxidc values(100,'linuxidc')
*
第 1 行出现错误:
ORA-25128: 不能对带有禁用和验证约束条件 (linuxidc.READ_ONLY_CONST) 的表进行插入/更新/删除 
 
SQL>

方法四:只读表空间
    设置只读表空间的主要目的是为了表空间中的静态数据不被修改,从而能够进行数据库的备份和恢复等操作,还能够保护只读表空间中的数据不被修改。

设置只读表空间的语法:ALTER TABLESPACE <表空间> READ ONLY;
将表空间设置为读写的语法:ALTER TABLESPACE <表空间> READ WRITE;
    由上面创建测试用户的语句我们得知,linuxidc用户的默认表空间是users,因此我们将users表空间设为只读表空间。这样,linuxidc用户下的所有表都将会是只读表,包括linuxidc表。
    如下所示。

SQL> show user

USER 为 "linuxidc"
SQL> alter table linuxidc drop constraint READ_ONLY_CONST;
 
表已更改。
 
SQL>
SQL> conn sys/linuxidc as sysdba
已连接。
SQL>
SQL> alter tablespace users read only;
 
表空间已更改。
 
SQL>
SQL> conn linuxidc/hoegh
已连接。
SQL> select * from linuxidc;
 
        ID NAME
---------- --------------------
        1 linuxidc 
        10 linuxidc 
 

ORA-00372& ORA-01110报错
    此时,如果我们对linuxidc表进行DML操作,系统就会报ORA-00372& ORA-01110错误,提示无法修改数据文件。
 

SQL>

SQL> insert into linuxidc values(100,'linuxidc');
insert into linuxidc values(100,'linuxidc')
            *
第 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/linuxidc 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 linuxidc/hoegh
已连接。
SQL>
SQL> select * from linuxidc;
 
        ID NAME
---------- --------------------
        1 linuxidc 
        10 linuxidc 
 

ORA-01552报错
  此时,如果我们对linuxidc表进行DML操作,系统就会报ORA-01552错误,提示非系统表空间 'USERS' 不能使用系统回退段。
 

SQL>

SQL> insert into linuxidc values(100,'linuxidc');
insert into linuxidc values(100,'linuxidc')
            *
第 1 行出现错误:
ORA-01552: 非系统表空间 'USERS' 不能使用系统回退段
 
 
SQL>
 

当然,这只是一个实现手段,我们肯定不会为了设置一个只读表而将整个数据库设置为只读状态。

总结
    上面这几种办法都可以实现将一个表设置为只读表,大家可以根据各自的具体需求选用最合适的方法。比如,最近我们一个项目为了提高数据的安全性,需要将配置数据(多张表)设置为只读表;并且,当初为了管理方便,所有的配置数据存放到一个单独的表空间,这样,我就会选用只读表空间的方法来实现这个具体需求。

相关推荐