Oracle 10 Gb read-only table N methods

Sometimes to improve data security, we need to set one or more tables to read-only, that is, no DML (Insert, Update, Delete) operations can be performed on them. In Ora

Sometimes to improve data security, we need to set one or more tables to read-only, that is, no DML (Insert, Update, Delete) operations can be performed on them. In Ora

Sometimes to improve data security, we need to set one or more tables to read-only, that is, no DML (Insert, Update, Delete) operations can be performed on them.

It is very easy to implement read-only tables in Oracle11g. You only need to execute alter table... read only; statement; but in versions earlier than 11 GB, "read-only" is only valid for databases and tablespaces. If we want to implement a read-only table, we can only use other methods.

The following describes several common methods to implement read-only tables in Oracle10g. First, create a test table linuxidc.

Test Environment
We performed a test on 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>

Create test user and test table
Create a test user linuxidc and specify the default tablespace as users. Then, create a test table under the linuxidc user, and name it linuxidc.

SQL>

SQL> create user linuxidc identified by linuxidc
2 default tablespace users;

The user has been created.

SQL>
SQL> grant connect, resource to linuxidc;

Authorization successful.

SQL>
SQL> conn linuxidc/hoegh
Connected.
SQL>
SQL> create table linuxidc (id number, name varchar2 (20 ));

The table has been created.

SQL> insert into linuxidc values (1, 'linuxid ');

One row has been created.

SQL> insert into linuxidc values (10, 'linuxid ');

One row has been created.

SQL> commit;

Submitted.

SQL> select * from linuxidc;

ID NAME
------------------------------
1 linuxidc
10 linuxidc

SQL>

Method 1: grant Select permission
This method is only applicable to non-owner users. Taking the linuxidc table as an example, the owner of the table is linuxidc. We can grant the select permission of the hoegh table to other users, so that other users are read-only to the linuxidc table.

SQL>

SQL> grant select on linuxidc to scott;

Authorization successful.

SQL> conn scott/tiger
Connected.
SQL> select * from linuxidc. hoegh;

ID NAME
------------------------------
1 linuxidc
10 linuxidc

SQL>

ORA-01031 Error
At this point, if we perform DML operations on the linuxidc. hoegh table, the system will report a ORA-01031 error, prompting insufficient permissions.

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

Insert into linuxidc. hoegh values (100, 'linuxid ')
*
Row 3 has an error:
ORA-01031: insufficient Permissions


SQL>

Method 2: trigger
You can create a trigger on the linuxidc table. An error is returned when you perform the DML operation on the linuxidc table. As shown below.

Create a trigger

SQL> conn linuxidc/hoegh

Connected.
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 raid _ application_error (-20001, 'table is read only Table .');
9 END;
10/

Trigger created

ORA-20001 Error
At this point, if we perform DML operations on the linuxidc Table, the system will report a ORA-20001 error, prompting "table is read only Table ".

SQL>

SQL> insert into linuxidc values (100, 'linuxid ');
Insert into linuxidc values (100, 'linuxid ')
*
Row 3 has an error:
ORA-20001: Table is read only table.
ORA-06512: In "linuxidc. HOEGH_TRG", line 3
ORA-04088: trigger 'linuxidc. hoegh_trg' error during execution


SQL>

Method 3: Check Constraints
We know there are four ways to enable and disable constraint:

Therefore, we can use disable validate to implement read-only tables.

As follows:

Alter table linuxidc add constraint READ_ONLY_CONST CHECK (0 = 0) disable validate;

ORA-25128 Error
At this point, if we perform the DML operation on the linuxidc table, the system will report a ORA-25128 error, prompting "cannot insert/update/Delete the table with the disabled and validation constraints ".

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

Insert into linuxidc values (100, 'linuxid ')
*
Row 3 has an error:
ORA-25128: cannot insert/update/delete a table with a disabled and validation constraint (linuxidc. READ_ONLY_CONST)

SQL>

Method 4: Read-Only tablespace
The primary purpose of setting a read-only tablespace is to prevent the static data in the tablespace from being modified, so as to perform database backup and recovery operations, and to prevent the data in the read-only tablespace from being modified.