Kamran Agayev's Oracle Blog

Oracle Certified Master

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>

5 Responses to “Make Table read only”

  1. H.Tonguç Yılmaz Says:

    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

  2. kamranagayev Says:

    Amazing! Of course by disabling table locks, you can even prevent DDLs

  3. H.Tonguç Yılmaz Says:

    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&#2522016

    I used a constraint and above option together to force a table to be read only for both DDL and DML.

  4. Arun Says:

    This is interesting, however if I execute a truncate statement, then I could remove all the records in the table.

  5. Kamran Agayev A. Says:

    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>

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>