oracle的data redaction测试

首先第一步在hr 的jobs表上创建一个redaction
SQL> begin
dbms_redact.add_policy(
object_schema=>'hr',
object_name=>'jobs',
policy_name=>'redact_policy_test_1',
column_name=>'max_salary',
function_type=>dbms_redact.full,
expression=>'1=1');
end;
/  2    3    4    5    6    7    8    9   10

PL/SQL procedure successfully completed.
因为u_1这个用户不是sys用户或者不具有
SQL> connect u_1/cdb3@cdb3pdb10001
Connected.

SQL> select * from hr.jobs where rownum<2;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080          0

SQL> select grantee,privilege from dba_sys_privs where privilege='EXEMPT REDACTION POLICY';

GRANTEE                        PRIVILEGE
------------------------------ -----------------------
SYS                            EXEMPT REDACTION POLICY
EXP_FULL_DATABASE              EXEMPT REDACTION POLICY
OGG_CAPTURE                    EXEMPT REDACTION POLICY

具有exempt redaction policy这个权限可以绕过 redaction policy对数据的改写
我们新建一个用户然后赋予exempt redaction policy这个权限，那么这个用户就可以访问被加了redaction policy的数据

SQL> connect sys/cdb3@cdb3pdb10001 as sysdba
Connected.
SQL> create user u_with_exempt identified by cdb3;

User created.

SQL> grant create session,select any table,exempt redaction plicy to u_with_exempt;
grant create session,select any table,exempt redaction plicy to u_with_exempt
                                      *
ERROR at line 1:
ORA-00990: missing or invalid privilege
Help: https://docs.oracle.com/error-help/db/ora-00990/


SQL> grant create session,select any table,exempt redaction policy to u_with_exempt;

Grant succeeded.

SQL> connect u_with_exempt/cdb3@cdb3pdb10001
Connected.
SQL> select * from hr.jobs where rownum<2;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080      40000


下面我们创建一个redaction的named expression
SQL> begin
dbms_redact.create_policy_expression(
policy_expression_name=>'expr_test_1',
expression=>'sys_context(''userenv'',''session_user'') not in (''U_1'')');
end;
/  2    3    4    5    6

PL/SQL procedure successfully completed.
SQL> begin
dbms_redact.apply_policy_expr_to_col(
object_schema=>'hr',
object_name=>'jobs',
column_name=>'max_salary');
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

这个expression的优先级比创建policy时指定的expression的优先级要高，所以这个expression会起作用
这个expression的意思是u_1这个用户是可以访问max_salary这个列的数据的，别的用户如果没有exempt redaction policy权限，则只能看到redacted 的数据
SQL> create user u_2 identified by cdb3;

User created.

SQL> grant create session,select any table to u_2;

Grant succeeded.

但是测试下来发现无论是u_1还是u_2用户都只能看到redacted的数据

SQL> select * from redaction_expressions;

POLICY_EXPRESSION_NAME         EXPRESSION                                                   OBJECT_OWN OBJECT_NAM COLUMN_NAM POLICY_EXP
------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ----------
expr_test_1                    sys_context('userenv','session_user') not in ('U_1')

查看这个表发现这个expression没有关联到hr jobs这个表的那个max_salary这个字段
发现apply_policy_expr_to_col这个过程执行有误，没有写policy_expression_name这个参数，重新执行后，就没有问题了。能够实现目的。

SQL> begin
dbms_redact.apply_policy_expr_to_col(
object_schema=>'HR',
object_name=>'JOBS',
column_name=>'MAX_SALARY',
policy_expression_name=>'expr_test_1'
);
end;
/  2    3    4    5    6    7    8    9

PL/SQL procedure successfully completed.
SQL> select * from redaction_expressions;

POLICY_EXPRESSION_NAME         EXPRESSION                                                   OBJECT_OWN OBJECT_NAM COLUMN_NAM POLICY_EXP
------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ----------
expr_test_1                    sys_context('userenv','session_user') not in ('U_1')         HR         JOBS       MAX_SALARY

SQL> connect u_1/cdb3@cdb3pdb10001
Connected.
SQL> select * from hr.jobs where rownum<2;

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President                                20080      40000

创建一个partial redaction的policy
原始数据是这样的
SQL> select phone_number from hr.employees where rownum<3;

PHONE_NUMBER
--------------------
1.515.555.0100
1.515.555.0101
创建一个partial的policy
SQL> begin
dbms_redact.add_policy(
object_schema=>'hr',
object_name=>'employees',
policy_name=>'redact_partial_phone_1',
column_name=>'phone_number',
function_type=>dbms_redact.partial,
function_parameters=>'VFVVVFVVVFVVVV,V=VVV=VVV=VVVV,&,5,11',
  2  expression=>'1=1');
end;
/  3    4    5    6    7    8    9   10   11

PL/SQL procedure successfully completed.
再次查看数据
SQL> connect u_1/cdb3@cdb3pdb10001
Connected.
SQL> select phone_number from hr.employees where rownum<3;

PHONE_NUMBER
--------------------
1=515=&&&=&&&&
1=515=&&&=&&&&

SQL> begin
dbms_redact.alter_policy(
object_schema=>'hr',
object_name=>'employees',
column_name=>'phone_number',
policy_name=>'redact_partial_phone_1',
action=>dbms_redact.modify_column,
function_type=>dbms_redact.partial,
function_parameters=>'VFVVVFVVVFVVVV,V-VVV-VVV-VVVV,*,5,11');
end;
/  2    3    4    5    6    7    8    9   10   11

PL/SQL procedure successfully completed.
修改这个policy后再次查看数据

SQL> select phone_number from hr.employees where rownum<3;

PHONE_NUMBER
--------------------
1-515-***-****
1-515-***-****