ITPub博客

首页 > 数据库 > Oracle > Oracle10g实现只读表的N种方法

Oracle10g实现只读表的N种方法

Oracle 作者:GM_DBA 时间:2015-10-16 09:55:33 0 删除 编辑

    有时为了提高数据的安全性,我们需要把一个或多个表设置为只读,即不允许对其执行任何 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的开启和关闭共有四种:

enable( validate) :启用约束,创建索引,对已有及新加入的数据执行约束。

enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据。

disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作。

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/29319205/viewspace-1814446/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-20

  • 博文量
    33
  • 访问量
    192250