表进行加密(redact)实验
redact对具有dba权限的账号不起作用，可以看到未加密前所有的数据，下面每个policy_name都命名为p1,实际情况需要区分不同的命名。
1. 加密准备工作：
sqlplus /nolog
连接pdb
connect sys/oracle@ora12cpdb1 as sysdba
REVOKE dba FROM ZHANGSAN;
GRANT CONNECT, resource, unlimited tablespace TO ZHANGSAN;
GRANT SELECT ON sys.redaction_policies TO ZHANGSAN;
GRANT SELECT ON sys.redaction_columns TO ZHANGSAN;
GRANT EXECUTE ON dbms_redact TO ZHANGSAN;
Grant Select On dba_tables To zhangsan;
查询约束和加密策略
SQL> select policy_name from redaction_policies;
no rows selected
2. ZHANGSAN用户下创建测试表
sqlplus /nolog
connect zhangsan/oracle@ora12cpdb1
create table hxl_redact_test as select * from dba_tables where rownum<10;
2、策略验证：
2.1 full redaction的验证(number)
创建策略前查询:
SQL> select sample_size from hxl_redact_test;
SAMPLE_SIZE
-----------
2919
6944
10
21
8658
0
0
7105
4876
9 rows selected.
创建策略
begin dbms_redact.add_policy(
object_schema=>'zhangsan',
object_name=>'hxl_redact_test',
policy_name=>'p1',
column_name=>'sample_size',
function_type=>dbms_redact.full,
enable=>true,
expression=>'1=1');
end;
查询全部显示0
SQL> select sample_size from hxl_redact_test;
SAMPLE_SIZE
-----------
0
0
0
0
0
0
0
0
0
9 rows selected.
SQL> grant select on hxl_redact_test to alice;
使用alice登陆查询
sqlplus /nolog
connect alice/oracle@ora12cpdb1
SQL> select sample_size from zhangsan.hxl_redact_test;
SAMPLE_SIZE
-----------
0
0
0
0
0
0
0
0
0
9 rows selected.
2.2 full redaction的验证(char)
sqlplus /nolog
connect zhangsan/oracle@ora12cpdb1
begin dbms_redact.alter_policy(
object_schema=>'zhangsan',
object_name=>'hxl_redact_test',
policy_name=>'p1',
column_name=>'table_name',
action=>dbms_redact.add_column,
function_type=>dbms_redact.full,
expression=>'1=1');
end;
SQL> set linesize 1000;
SQL> column table_name format a32;
SQL> column owner format a10;
SQL> select owner,table_name,sample_size from hxl_redact_test;
OWNER TABLE_NAME SAMPLE_SIZE
---------- -------------------------------- -----------
SYS 0
SYS 0
SYS 0
SYS 0
SYS 0
SYS 0
SYS 0
SYS 0
SYS 0
9 rows selected.
可以看到table_name全部显示为空
2.3 full redaction的验证(date)
sqlplus /nolog
connect zhangsan/oracle@ora12cpdb1
begin dbms_redact.alter_policy(
object_schema=>'zhangsan',
object_name=>'hxl_redact_test',
policy_name=>'p1',
column_name=>'last_analyzed',
action=>dbms_redact.add_column,
function_type=>dbms_redact.full,
expression=>'1=1');
end;
SQL> set linesize 1000;
SQL> column table_name format a32;
SQL> column owner format a10;
SQL> select owner,table_name,sample_size,last_analyzed from zhangsan.hxl_redact_test;
OWNER TABLE_NAME SAMPLE_SIZE LAST_ANAL
---------- -------------------------------- ----------- ---------
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
9 rows selected.
日期全部显示为'01-JAN-01'
2.4 partial redaction的验证(char)
sqlplus /nolog
connect zhangsan/oracle@ora12cpdb1
begin
dbms_redact.alter_policy(
object_schema=>'zhangsan',
object_name=>'hxl_redact_test',
policy_name=>'p1',
column_name=>'owner',
action=>dbms_redact.add_column,
function_type=>dbms_redact.partial,
expression=>'1=1',
function_parameters=>'VVVFVVVVFVVVV,VVV-VVVV-VVVV,*,1,3');
end;
SQL> set linesize 1000;
SQL> column table_name format a32;
SQL> column owner format a10;
SQL> select owner,table_name,sample_size,last_analyzed from hxl_redact_test;
OWNER TABLE_NAME SAMPLE_SIZE LAST_ANAL
---------- -------------------------------- ----------- ---------
*** 0 01-JAN-01
*** 0 01-JAN-01
*** 0 01-JAN-01
*** 0 01-JAN-01
*** 0 01-JAN-01
*** 0 01-JAN-01
*** 0 01-JAN-01
*** 0 01-JAN-01
*** 0 01-JAN-01
9 rows selected.
可以看到owner字段全部使用*代替
3.drop 策略验证
sqlplus /nolog
connect zhangsan/oracle@ora12cpdb1
begin
dbms_redact.alter_policy(
object_schema=>'zhangsan',
object_name=>'hxl_redact_test',
policy_name=>'p1',
column_name=>'owner',
action=>dbms_redact.drop_column,
expression=>'1=1');
end;
再次查看owner列,看到已经不是*了
SQL> select owner,table_name,sample_size,last_analyzed from hxl_redact_test;
OWNER TABLE_NAME SAMPLE_SIZE LAST_ANAL
---------- -------------------------------- ----------- ---------
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
SYS 0 01-JAN-01
9 rows selected.
4.有编写策略的表不允许ctas操作
用户可以对xiaoxu_v1表进行dml操作，但是不能基于xiaoxu_v1表进行ctas操作。如下：
SQL> create table hxl_redact_test_copy as select * from hxl_redact_test;
create table hxl_redact_test_copy as select * from hxl_redact_test
*
ERROR at line 1:
ORA-28081: Insufficient privileges - the command references a redacted object.
5.修改policy使得其他用户可以访问到真实数据
sqlplus /nolog
connect zhangsan/oracle@ora12cpdb1
begin
dbms_redact.alter_policy(
object_schema=>'zhangsan',
object_name=>'hxl_redact_test',
policy_name=>'p1',
column_name=>'last_analyzed',
action=>dbms_redact.modify_expression,
expression=>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''ALICE''');
end;
sqlplus /nolog
connect alice/oracle@ora12cpdb1;
SQL> set linesize 1000;
SQL> column table_name format a32;
SQL> column owner format a10;
SQL> select owner,table_name,sample_size,last_analyzed from zhangsan.hxl_redact_test;
OWNER TABLE_NAME SAMPLE_SIZE LAST_ANAL
---------- -------------------------------- ----------- ---------
SYS IND$ 2919 02-DEC-19
SYS CDEF$ 6944 02-DEC-19
SYS CLU$ 10 03-DEC-19
SYS UNDO$ 21 03-DEC-19
SYS CCOL$ 8658 02-DEC-19
SYS PROXY_ROLE_DATA$ 0 26-JAN-17
SYS FET$ 0 26-JAN-17
SYS CON$ 7105 02-DEC-19
SYS ICOL$ 4876 20-NOV-19
9 rows selected.
可以看到last_analyzed字段的值全部显示出来了.
6、其他操作
---禁用编写策略
BEGIN
DBMS_REDACT.DISABLE_POLICY (
object_schema => 'zhangsan',
object_name => 'hxl_redact_test',
policy_name => 'p1');
END;
---启用编写策略
BEGIN
DBMS_REDACT.ENABLE_POLICY (
object_schema => 'zhangsan',
object_name => 'hxl_redact_test',
policy_name => 'p1');
END;
---删除编写策略
BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'zhangsan',
object_name => 'hxl_redact_test',
policy_name => 'p1');
END;
