Make Table read only
Posted by Kamran Agayev A. on December 9th, 2008
Today I’ll show how we can make a table read only using database triggers
In this example, we’ll create a table, insert data in it, and then create trigger to prevent any insert, update or delete statement on table
SQL> CREATE TABLE tbl_read_only (id NUMBER);
Table created.
SQL> INSERT INTO tbl_read_only VALUES(1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tbl_read_only;
ID
———-
1
SQL> CREATE TRIGGER trg_tbl_read_only BEFORE DELETE OR INSERT OR UPDATE
2 ON tbl_read_only
3 BEGIN
4 RAISE_APPLICATION_ERROR(-20202,’Table Status: READ ONLY!!!’);
5 END;
6 /
Trigger created.
SQL> INSERT INTO tbl_read_only VALUES(2);
INSERT INTO tbl_read_only VALUES(2)
*
ERROR at line 1:
ORA-20202: Table Status: READ ONLY!!!
ORA-06512: at “KAMRAN.TRG_TBL_READ_ONLY”, line 2
ORA-04088: error during execution of trigger ‘KAMRAN.TRG_TBL_READ_ONLY’
SQL>
December 9th, 2008 at 11:57 am
If Read Only means also the DDLs, here is an additional option to the trigger – http://tonguc.yilmaz.googlepages.com/Code_Listing_143-How_to_prevent_DDL_.txt
December 9th, 2008 at 12:04 pm
Amazing! Of course by disabling table locks, you can even prevent DDLs
December 9th, 2008 at 12:04 pm
On 11g there is a new feature for this need, but prior to 11g there ware several options, all were well discussed on this thread here – http://forums.oracle.com/forums/thread.jspa?messageID=2522016�
I used a constraint and above option together to force a table to be read only for both DDL and DML.
March 24th, 2009 at 12:02 pm
This is interesting, however if I execute a truncate statement, then I could remove all the records in the table.
March 24th, 2009 at 12:16 pm
Hi Arun
But remember, if user hasn’t TRUNCATE privilege, then he can’t truncate any table. And if you grant INSERT, UPDATE or DELETE privileges to a user, you can’t TRUNCATE TABLE until you haven’t DROP ANY TABLE privilege. Please refer to documentation:
***—-****
To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10006.htm#SQLRF01707
***—****
And look at this example:
SQL> create user t identified by t;
User created.
SQL> grant connect, create session to t;
Grant succeeded.
SQL> create table tt (id number);
Table created.
SQL> grant insert, update, delete on tt to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> delete from sys.tt;
0 rows deleted.
SQL> truncate table sys.tt;
truncate table sys.tt
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>