One more nice enhancement in Oracle 11g is the change that is made to the ALTER TABLE command. In prior versions, to make table read only we have to grant only SELECT privilege on the table. But table was still available for read and write to creator of the table. With this new enhancement to the ‘ALTER TABLE’ command, even owner/creator of the table will not be allowed to issue DML statement(s).
CREATE TABLE TEST
TEST_ID NUMBER(9) NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
INSERT INTO TEST(TEST_ID,TEST_OBJECT) VALUES(1,’DECIPHER’)
Now let us make table READ ONLY.
ALTER TABLE TEST READ ONLY
Above command will put table in READ ONLY mode. We can check whether table is in READ ONLY mode or READ WRITE MODE by accessing USER_TABLES view.
SELECT table_name, read_only
FROM user_tables WHERE table_name = ‘TEST';
Once table is in read only mode, no DML statement or DDL statement that affects the data (truncate) can be issued against it. Also SELECT FOR UPDATE is also prohibited.
FROM TEST FOR UPDATE NOWAIT;
SET TEST_OBJECT =’TEST’ WHERE TEST_ID = 1;
TRUNCATE TABLE TEST;
Any of the above statement will result into following error.
ORA-12081: update operation not allowed on table “DECIPHER”.”TEST”
Even though table is set to READ ONLY mode, any DDL statement on underlying indexes will be successful. We can create new index on table in READ ONLY mode.
SQL> CREATE INDEX TEST_IND_1 ON TEST(TEST_OBJECT);
We can put table back to READ WRITE mode by simply issuing following command.
ALTER TABLE TEST READ WRITE;
This can be a quick and useful alternative to provide read only access to users.