redact方式加解密

oracle redact加密

 

加密针对 非当前用户与非DBA权限用户

--删除加密策略

BEGIN

   DBMS_REDACT.DROP_POLICY (

      object_schema    =>  'user_test',

      object_name      =>  'tab1',

      policy_name      =>  'REDACT_1');

END;

 

--查询加密策略

select * from redaction_policies;

 

--加密字段以O填充（字符）

BEGIN

  DBMS_REDACT.add_policy(object_schema       => 'user_test',

                         object_name         => 'tab1',

                         policy_name         => 'REDACT_1',

                         COLUMN_NAME         => 'SOCIAL_SECURITY',

                         FUNCTION_TYPE       => dbms_redact.PARTIAL,

                         EXPRESSION          => '1=1',

                         function_parameters => 'VVVVVV,VVVVVV,O,1,6',

                         enable              => true

                        

                         );

END;

 

--以默认方式显示加密字段

redact分类：

1、Full redaction.对列中的数据全部redact，number类型的列将全部返回为0，character类型的列将全部返回为空格，日期类型返回为2001-01-01

2、Partial redaction. 对列中的一部分数据进行redact，比如，可以对社会保险号的前几位设置返回为*，剩下的几位保持不变。只有列中的数据为固定宽度时才能使用这种方式，如果列中存储的是email地址，每个email地址的宽度不尽相同，此时要使用Regular expressions。

3、Regular expressions. 对不同长度的内容加密，例如e-mail，仅仅对字符类型有效。

4、Random redaction. 随机乱码加密

 

增加加密 add_policy，修改加密 ALTER_POLICY ，删除加密 DROP_POLICY

BEGIN

  DBMS_REDACT.add_policy(object_schema       => 'user_test',

                         object_name         => 'tab1',

                         policy_name         => 'REDACT_1',

                         COLUMN_NAME         => 'SOCIAL_SECURITY',

                         FUNCTION_TYPE       => dbms_redact.full,

                          EXPRESSION          => '1=1'

                        

                         );

END;

 

--增加一列

BEGIN

  DBMS_REDACT.ALTER_POLICY(

                          

                           object_schema => 'user_test',                          

                           object_name => 'tab1',                          

                           policy_name => 'REDACT_1',                          

                           action => DBMS_REDACT.ADD_COLUMN,                          

                           column_name => 'SALARY',                          

                           function_type => DBMS_REDACT.FULL                          

                           );

END;

 

--删除一列

BEGIN

  DBMS_REDACT.ALTER_POLICY(object_schema => 'user_test',

                           object_name   => 'tab1',

                           policy_name   => 'REDACT_1',

                           action        => DBMS_REDACT.DROP_COLUMN,

                           column_name   => 'SALARY'

                          

                           );

END;