Oracle PL/SQL for DBAs

Controlling the Type of Table Access

RLS has many uses beyond security and the simplification of application development models. RLS is also very helpful if you need to switch a table between read-only and read/write status, as determined by a variety of circumstances. Without RLS, DBAs could make an entire tablespace—but not the individual tables inside it—read-only or read/write. Even if a DBA wanted to take this approach, a tablespace could not be made read-only if it had any active transactions. As it may be impossible to find a period of time during which there are no transactions in a database, a tablespace may never actually be able to be made read-only. In such cases, RLS is the only viable solution.

Now, to be honest, RLS does not actually make a table read-only; it simply allows us to emulate that behavior by denying any attempts to change the contents of the table. The simplest way to do this is to apply a predicate to any UPDATE, DELETE, and INSERT that will always evaluate to FALSE—for example, 1=2.

Here is an example of making the EMP table read-only with this most basic of predicate functions:

    CREATE OR REPLACE FUNCTION make_read_only (
       p_schema_name   IN   VARCHAR2,
       p_object_name   IN   VARCHAR2
     )
       RETURN VARCHAR2
    IS
    BEGIN
       -- Only the owner of the predicate policy function can change
       -- the data in the table.
       IF (p_schema_name = USER)
       THEN
          RETURN NULL;
       ELSE
          RETURN '1=2';
       END IF;
    END;

Using this policy function, I can create an RLS policy on the table EMP for the DML statements ...