Read Only Table.

I saw this post on Richard foote's blog which pointed me to this otn thread.

So i gave it a try. This is what i did.

Say I have a table as given below in production.

SQL> CREATE TABLE hx_my_read_only_table (no INTEGER CHECK(NO>0), name VARCHAR2(100));

Table created.

SQL> INSERT INTO hx_my_read_only_table VALUES(1,'KARTHICK');

1 row created.

SQL> INSERT INTO hx_my_read_only_table VALUES(2,'VIMAL');

1 row created.

SQL> INSERT INTO hx_my_read_only_table VALUES(3,'VIJAY');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM hx_my_read_only_table;

NO NAME
---------- --------------------------------------------------------------------------------
1 KARTHICK
2 VIMAL
3 VIJAY

And I want to make it read only. I will use ACCESS CONTROL to achieve it.

SQL> CREATE CONTEXT hx_my_context USING hx_my_pkg;

Context created.

SQL> CREATE OR REPLACE PACKAGE hx_my_pkg
2 AS
3 PROCEDURE hx_set_context (
4 hx_p_fld VARCHAR2,
5 hx_p_value VARCHAR2);
6
7 FUNCTION hx_get_val (
8 hx_p_fld varchar2) RETURN NUMBER;
9
10 FUNCTION hx_get_key(
11 p_schema in varchar2,
12 p_object in varchar2) RETURN VARCHAR2;
13 END hx_my_pkg;
14 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY hx_my_pkg
2 AS
3 PROCEDURE hx_set_context (
4 hx_p_fld VARCHAR2,
5 hx_p_value VARCHAR2)
6 IS
7 BEGIN
8 DBMS_SESSION.SET_CONTEXT('HX_MY_CONTEXT', hx_p_fld, hx_p_value);
9 END;
10
11 FUNCTION hx_get_val (
12 hx_p_fld varchar2) RETURN NUMBER
13 IS
14 BEGIN
15 RETURN SYS_CONTEXT('HX_MY_CONTEXT', hx_p_fld);
16 END;
17
18 FUNCTION hx_get_key(
19 p_schema in varchar2,
20 p_object in varchar2) RETURN VARCHAR2
21 IS
22 lPredicate VARCHAR2 (2000);
23 BEGIN
24 lPredicate := 'NO = SYS_CONTEXT(''HX_MY_CONTEXT'', ''NO'')';
25
26 RETURN lPredicate;
27 END;
28
29 END hx_my_pkg;
30 /

Package body created.

SQL> BEGIN
2 DBMS_RLS.ADD_POLICY (USER, 'HX_MY_READ_ONLY_TABLE', 'HX_READ_ONLY_POLICY',USER, 'hx_my_pkg.hx_get_key', 'INSERT, UPDATE, DELETE' );
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 hx_my_pkg.hx_set_context('NO',0);
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> UPDATE hx_my_read_only_table
2 SET NAME = 'RICHARD'
3 WHERE NO = 1;

0 rows updated.

SQL> DELETE FROM hx_my_read_only_table;

0 rows deleted.

SQL> INSERT INTO hx_my_read_only_table VALUES(4,'RICHARD');

1 row created.

I was expecting this one not to insert record. I have to see what the problem is.

SQL> ROLLBACK;

Rollback complete.

Mean while I restricted the insert by with this trigger. I personally hate triggers but....

SQL> CREATE OR REPLACE TRIGGER hx_my_trigger BEFORE INSERT ON hx_my_read_only_table FOR EACH ROW
2 BEGIN
3 IF :NEW.NO <> hx_my_pkg.hx_get_val('NO')
4 THEN
5 RAISE_APPLICATION_ERROR(-20001, 'Insert Not possible...');
6 END IF;
7 END;
8 /

Trigger created.

SQL> INSERT INTO hx_my_read_only_table VALUES(4,'RICHARD');

INSERT INTO hx_my_read_only_table VALUES(4,'RICHARD')
*
ERROR at line 1:
ORA-20001: Insert Not possible...
ORA-06512: at "SYSADM.HX_MY_TRIGGER", line 4
ORA-04088: error during execution of trigger 'SYSADM.HX_MY_TRIGGER'

So i have Restricted INSERT, UPDATE, DELETE on this table.

SQL> SELECT * FROM HX_MY_READ_ONLY_TABLE;

NO NAME
---------- --------------------------------------------------------------------------------
1 KARTHICK
2 VIMAL
3 VIJAY