Does altering or drop-recreating a table in oracle affects the policies defined on it

Question

If i have a table and a defined policy on it then do I need to redefine the policy if I drop and recreate the table or alter it, provided that the alteration or the recreation of the table does not alter elements that the function needs to see?

Answer 1

"do I need to redefine the policy if I drop and recreate the table"

Yes. Let's create a policy.

SQL> exec dbms_rls.add_policy('APC', 'T23', 'DEPTPOL', 'APC', 'security_policies.get_deptno_predicate')

PL/SQL procedure successfully completed.

SQL> select count(*) from user_policies;

  COUNT(*)
----------
         1

SQL> exec security_policies.set_deptno(20)

PL/SQL procedure successfully completed.

SQL> select count(*) from t23;

  COUNT(*)
----------
         6

SQL>  

so that works. But if we drop and re-create the table (using a backup I prepared earlier) ...

SQL> drop table t23
  2  /

Table dropped.

SQL> create table t23 as select * from t23a
  2  /

Table created.

SQL> select count(*) from t23;

  COUNT(*)
----------
        11

SQL> exec security_policies.set_deptno(20)

PL/SQL procedure successfully completed.

SQL> select count(*) from t23;

  COUNT(*)
----------
        11

SQL> SQL> select count(*) from user_policies;

  COUNT(*)
----------
         0

SQL>  

"So the question is if I must redefine the policy even if I will not change anything in the definition."

No. Providing the change doesn't invalidate the generated predicate altering a table doesn't drop the policy:

SQL> exec dbms_rls.add_policy('APC', 'T23', 'DEPTPOL', 'APC', 'security_policies.get_deptno_predicate')

PL/SQL procedure successfully completed.

SQL> alter table t23 modify deptno number(3,0)
  2
SQL> desc t23
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(12 CHAR)
 ID                                                 NUMBER
 AGE                                                NUMBER(4)
 DEPTNO                                             NUMBER(2)

SQL> alter table t23 modify deptno number(3,0)
  2  /

Table altered.

SQL> exec security_policies.set_deptno(20)

PL/SQL procedure successfully completed.

SQL> select count(*) from t23;

  COUNT(*)
----------
         6

SQL> 

Note that the change modified the column which is tested by the predicate and the policy still remains in force.

---

"does a 'CREATE OR REPLACE VIEW' statement drops and recreates it or does it alter it?"

Let's try it:

SQL> create view v23 as select * from t23;

View created.

SQL> exec dbms_rls.add_policy('APC', 'V23', 'DEPTPOLV', 'APC', 'security_policies.get_deptno_predicate')

PL/SQL procedure successfully completed.

SQL> exec security_policies.set_deptno(10)

PL/SQL procedure successfully completed.

SQL> select count(*) from v23;

  COUNT(*)
----------
         5

SQL> create or replace view v23 as select name, age from t23;

View created.

SQL> select count(*) from v23;
select count(*) from v23
                     *
ERROR at line 1:
ORA-28113: policy predicate has error


SQL>

Okay, so that's an error because the view's new projection doesn't include the column in the predicate. But it suggests teh ploicy is still in place. So let's fix that error:

SQL> create or replace view v23 as select name, age, deptno from t23;

View created.

SQL> select count(*) from v23;

  COUNT(*)
----------
         5

SQL>