I think you want to prevent unintentional delete, insert or update of the data of the schema. I assume that you do not have to prevent the data against malicious attacks where the attacker puts a lot of efforts into modifying or deleteing your data.
You can try to revoke the relevant grants from the tables of the schema. But the schema owner always has the rright to modify the tables. And also the procedure of the tables if the are defined with "AUTHID DEFINER", which is the default. Also users with DELETE ANY TABLE, INSE the tableRT ANY TABLE or UPDATE ANY TABLE can modify data. So there is alwas a risk that the data is modified unintentionally.
But what else can we do? If all tables and other segments of a tablespace should be read only, you can put the tablespace in read only mode.
Let's assume you have created this table in the tablespace USERS.
create table mytable (id number)
tablespace users
/
If all segments of the USERS tablespace should be made read only this can be achieved by setting the tablespace read only. Execute the following SQL statement
ALTER TABLESPACE USERS READ ONLY;
Note that all segments of a tablespace are now read only regardless of its type. Also note, that not only the table segments but also partitions sements must be in read only tablespaces to prevent modification of the table data.
If you want to modify the data of a table which has its segment in a read only tablespace you will get an error similar to the following
SQL>insert into mytable(id) values(1)
/
2 insert into mytable(id) values(1)
*
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: '/u02/app/oracle/oradata/ORCL/datafile/o1_mf_users_hrvf6nv4_.dbf'
The table can be still read by a SELECT statement. but you cannot do a 'SELECT ... FOR UPDATE' You also will get the a simialr message that the table cannot be modified. Also note that the table still can be truncated by a 'TRUNCATE TABLE' statement and if you do this you will loose all its data.
If there are tables in this tablespace that must be writeable you cannot use this But there is another way to avoid insert, update, and delete. Simply create a trigger that does not allow this.
create trigger prevent_modification_mytable
before delete or insert or update
on mytable
begin
raise_application_error(-20001,'you are not allowed to modify table "mytable"');
end;
/
Trying to modify data in this table will raise the following error and prevent the modifications:
SQL> insert into mytable(id) values(3)
2 /
insert into mytable(id) values(3)
*
ERROR at line 1:
ORA-20001: you are not allowed to modify table "mytable"
ORA-06512: at "SCOTT.PREVENT_MODIFICATION_MYTABLE", line 2
ORA-04088: error during execution of trigger 'SCOTT.PREVENT_MODIFICATION_MYTABLE'
Now a 'SELECT ... FOR UPDATE' is still possible but this method does not prevent truncating of the table, too.
