Oracle 10 Gb read-only table N methods

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.

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 bkjia.

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 bkjia and specify the default tablespace as users. Then, create a test table under bkjia and name it bkjia.

SQL>

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

The user has been created.

SQL>
SQL> grant connect, resource to bkjia;

Authorization successful.

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

The table has been created.

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

One row has been created.

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

One row has been created.

SQL> commit;

Submitted.

SQL> select * from bkjia;

ID NAME
------------------------------
1 bkjia
10 bkjia

SQL>
 

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

SQL>

SQL> grant select on bkjia to scott;

Authorization successful.

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

ID NAME
------------------------------
1 bkjia
10 bkjia

SQL>
 

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

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

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


SQL>
 

Method 2: trigger
You can create a trigger on the bkjia table. If you perform the DML operation on the bkjia table, an error is returned. As shown below.

Create a trigger
 

SQL> conn bkjia/hoegh

Connected.
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 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 bkjia Table, the system will report a ORA-20001 error, prompting "table is read only Table ".
 

SQL>

SQL> insert into bkjia values (100, 'bkjia ');
Insert into bkjia values (100, 'bkja ')
*
Row 3 has an error:
ORA-20001: Table is read only table.
ORA-06512: In "bkjia. HOEGH_TRG", line 3
ORA-04088: An error occurred while executing the trigger 'bkjia. hoegh_trg'


SQL>
 

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

1. Enable (validate): enable constraints, create indexes, and execute constraints on existing and new data.
2. Enable novalidate: enable constraints, create indexes, and only enforce constraints on new data, regardless of existing data in the table.
3. Disable (novalidate): disables the constraint and deletes the index. You can modify the data of the constraint column.
4. Disable validate: disables constraints and deletes indexes. You cannot insert, update, or delete a table.

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

As follows:

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

ORA-25128 Error
At this point, if we perform DML operations on the bkjia table, the system reports a ORA-25128 error with the prompt "cannot insert/update/Delete the table with the disabled and validation constraints ".

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

Insert into bkjia values (100, 'bkja ')
*
Row 3 has an error:
ORA-25128: cannot insert/update/delete a table with a disabled and validation constraint (bkjia. 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.

Set the READ-only tablespace Syntax: alter tablespace <TABLESPACE> read only;
Set the tablespace to the read/WRITE Syntax: alter tablespace <TABLESPACE> read write;
We learned from the statement of creating a test user above that the default tablespace of bkjia user is users, so we set users tablespace to read-only tablespace. In this way, all the tables under bkjia will be read-only tables, including bkjia tables.
As shown below.

SQL> show user

The USER is "bkjia"
SQL> alter table bkjia drop constraint READ_ONLY_CONST;

The table has been changed.

SQL>
SQL> conn sys/bkjia as sysdba
Connected.
SQL>
SQL> alter tablespace users read only;

The tablespace has been changed.

SQL>
SQL> conn bkjia/hoegh
Connected.
SQL> select * from bkjia;

ID NAME
------------------------------
1 bkjia
10 bkjia
 

ORA-00372 & ORA-01110 Error
At this point, if we perform DML operations on the bkjia table, the system will report a ORA-00372 & ORA-01110 error, prompting that the data file cannot be modified.
 

SQL>

SQL> insert into bkjia values (100, 'bkjia ');
Insert into bkjia values (100, 'bkja ')
*
Row 3 has an error:
ORA-00372: file cannot be modified at this time 4
ORA-01110: data file 4: 'e: \ ATSTEST \ users01.dbf'

SQL>

The DROP operation is not affected.
Note that the DROP operation can be performed in a read-only tablespace.

We know that each database has at least one ONLINE tablespace during operation, that is, the SYSTEM tablespace, the data dictionary and database objects such as stored procedures, triggers, functions, and packages in PLSQL are saved. When DDL is used to delete a database, it is essentially an operating SYSTEM tablespace. ORACLE sets the deleted table to DROP in the data dictionary stored in the SYSTEM, when the status of the tablespace changes to read write, the table will be deleted from the database.

Method 5: Read-Only Database
When a normally opened database is set to read-only, you can only query data, but cannot modify the database objects in any way. In the read-only status, the content in the data file and the redo log file may not be modified, but it does not limit the operations that do not write data files or redo log files.

• Set the read-only command: alter database open read only;
• Cancel read-only command: alter database open read write;

SQL>

SQL> conn sys/bkjia as sysdba
Connected.
SQL> alter tablespace users read write;

The tablespace has been changed.

SQL>
SQL> shutdown immediate
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL>
SQL> startup mount
The ORACLE routine has been started.

Total System Global Area 1258291200 bytes
Fixed Size 2163712 bytes
Variable Size 360446976 bytes
Database Buffers 889192448 bytes
Redo Buffers 6488064 bytes
The database has been loaded.
SQL>
SQL> alter database open read only;

The database has been changed.

SQL>
SQL> conn bkjia/hoegh
Connected.
SQL>
SQL> select * from bkjia;

ID NAME
------------------------------
1 bkjia
10 bkjia
 

ORA-01552 Error
At this point, if we perform DML operations on the bkjia table, the system will report a ORA-01552 error, prompting that the non-system tablespace 'users' cannot use the system rollback segment.
 

SQL>

SQL> insert into bkjia values (100, 'bkjia ');
Insert into bkjia values (100, 'bkja ')
*
Row 3 has an error:
ORA-01552: non-system tablespace 'users' cannot use system rollback segments


SQL>
 

Of course, this is only an implementation method. We certainly will not set the entire database to read-only State to set a read-only table.

Summary
The above methods can be used to set a table as a read-only table. You can select the most appropriate method based on your specific needs. For example, in a recent project, to improve data security, we need to set the configuration data (multiple tables) to read-only tables, all configurations are stored in a single tablespace, so that I will use the read-only tablespace method to achieve this specific requirement.