Oracle数据编纂（脱敏）

Posted on by hackdl

Oracle Database – Enterprise Edition – Version 12.1.0.2 and later

1.问题描述

客户需求：Oracle数据库里面能不能实现这么一个功能，让符合条件的用户可以看到表里面的所有数据，而另外一些人（比如权限低的用户）虽然能查询表，但是对某些敏感数据全部用星号加密了。

效果如下图所示：

=>

2. 问题分析

我们可以依靠oralce data redaction实现客户需求。

Oracle Data Redaction(数据编纂)是Oracle Database 12c引入的一个新的安全特性（实际在11g的官方Database Advanced Security Administrator’s Guide文档中就已经有了具体介绍）。它是Oracle安全加密类的高级功能，可用于对于敏感数据的加密处理，加密配置处理均在Oracle层面实现。这是一项和安全相关的技术类别，对于指定的用户可以限制某些表的某些列显示被加密改过的值。对于Redaction之前，可能需要自定义加密函数、创建特定的视图，或者在存储到数据库的时候就用加密算法进行加密。而Redaction可以直接对数据进行加密，不会影响到数据真实的存储，对应用透明，不需要改动。

3. 问题处理

Oracle redaction有五种函数加密方法，我们可以依靠这五种用法实现数据加密。

接下来我们创建测试环境，用于验证这五种用法：

SQL> grant connect,resource to u2 identified by u2;
grant connect,resource to u3 identified by u3;
drop t服务器托管网able u1.tab1;
create table u1.tab1(name varchar2(20),telnum number,id varchar2(20),addr varchar2(100),shijian date);
insert into u1.tab1 values('张三',13512345678,'510812199909091212','甘肃省兰州市安宁区凤凰小区一单元502',sysdate);
insert into u1.tab1 values('李筱思','13912123434','51081219服务器托管网99090912xx','宁夏回族自治区金凤区安居苑西区4号楼3单元808', to_date('2013-12-12 014423','yyyy-mm-dd hh24miss'));
commit;

实验数据如下：

第一种，Full redaction(全编纂)：

全编纂，意思是将整个字段进行加密，要使用全编纂，需要将function_type参数设为DBMS_REDACT.FULL，接下来我们举例探索全加密效果。

举例1：对字符型的加密

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema    => 'U1', 
   object_name      => 'TAB1', 
   column_name      => 'ID',
   policy_name      => 'redact_id', 
   function_type    => DBMS_REDACT.FULL,
   expression       => '1=1');
END;
/

默认加密效果：字符型全编纂后将替换为空。

举例2：对数字型的加密

SQL> BEGIN
 DBMS_REDACT.alter_POLICY(
   object_schema    => 'U1', 
   object_name      => 'TAB1', 
   column_name      => 'telnum',
   policy_name      => 'redact_id', 
   function_type    => DBMS_REDACT.FULL,
   expression       => '1=1');
END;
/

默认加密效果：数字型全编纂后将变为0。

举例3：对日期型的加密

SQL> BEGIN
 DBMS_REDACT.alter_POLICY(
   object_schema    => 'U1', 
   object_name      => 'TAB1', 
   column_name      => 'shijian',
   policy_name      => 'redact_id', 
   function_type    => DBMS_REDACT.FULL,
   expression       => '1=1');
END;
/

默认加密效果：时间型加密后将变为2001-01-01。

常见数据类型默认加密效果：

By default, NUMBER data type columns are replaced with zero (0) and character data type columns are replaced with a single space ( ).

默认情况下，对某字段数据全部加密，number类型的列将全部返回为0，character类型的列将全部返回为空格，日期类型返回为yyyy-mm-dd；

You can modify this default by using the DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure.

你也可以使用函数DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES进行默认值修改。

第二种，Partial redaction（局部编纂）：

所谓局部编纂，就是对字段的部分内容进行加密。

一般情况下，局部编纂是实现等量字符串替换，也就是将字符替换成相同长度的其他字符。

（针对变长字符串的加密，需要借助下一章节的正则表达式）

以下是一些固定格式字符串的替换函数：

类型

说明

DBMS_REDACT.REDACT_US_SSN_F5

Redacts the first 5 numbers of Social Security numbers when the column is a VARCHAR2 data type. For example, the number 987-65-4320 becomes XXX-XX-4320.

DBMS_REDACT.REDACT_US_SSN_L4

Redacts the last 4 numbers of Social Security numbers when the column is a VARCHAR2 data type. For example, the number 987-65-4320 becomes 987-65-XXXX.

DBMS_REDACT.REDACT_US_SSN_ENTIRE

Redacts the entire Social Security number when the column is a VARCHAR2 data type. For example, the number 987-65-4320becomes XXX-XX-XXXX.

DBMS_REDACT.REDACT_NUM_US_SSN_F5

Redacts the first 5 numbers of Social Security numbers when the column is a NUMBER data type. For example, the number 987654320 becomes XXXXX4320.

DBMS_REDACT.REDACT_NUM_US_SSN_L4

Redacts the last 4 numbers of Social Security numbers when the column is a NUMBER data type. For example, the number 987654320 becomes 98765XXXX.

DBMS_REDACT.REDACT_NUM_US_SSN_ENTIRE

Redacts the entire Social Security number when the column is a NUMBER data type. For example, the number 987654320 becomes XXXXXXXXX.

DBMS_REDACT.REDACT_ZIP_CODE

Redacts a 5-digit postal code when the column is a VARCHAR2data type. For example, 95476 becomes XXXXX.

DBMS_REDACT.REDACT_NUM_ZIP_CODE

Redacts a 5-digit postal code when the column is a NUMBER data type. For example, 95476 becomes XXXXX.

DBMS_REDACT.REDACT_DATE_MILLENNIUM

Redacts dates that are in the DD-MON-YY format to 01-JAN-00(January 1, 2000).

DBMS_REDACT.REDACT_DATE_EPOCH

Redacts all dates to 01-JAN-70.

DBMS_REDACT.REDACT_CCN16_F12

Redacts a 16-digit credit card number, leaving the last 4 digits displayed. For example, 5105 1051 0510 5100 becomes ****-****-****-5100.

举例1：使用DBMS_REDACT.REDACT_US_SSN_F5加密

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'U1', 
   object_name         => 'TAB1', 
   column_name         => 'ID',
   policy_name         => 'redact_ssns3', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
   expression          => '1=1',
   policy_description  => 'Partially redacts 1st 5 digits in SS numbers',
   column_description  => 'ssn contains Social Security numbers');
END;
/
SQL> BEGIN
 DBMS_REDACT.alter_POLICY(
   object_schema       => 'U1', 
   object_name         => 'TAB1', 
   column_name         => 'addr',
   policy_name         => 'redact_ssns3', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
   expression          => '1=1',
   policy_description  => 'Partially redacts 1st 5 digits in SS numbers',
   column_description  => 'ssn contains Social Security numbers');
END;
/

加密效果：

举例2：使用字符型数据格式

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'U1', 
   object_name         => 'TAB1', 
   column_name         => 'ADDR',
   policy_name         => 'redact_tab1', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => 'VVVVVVVVVVVV,VVVVVVVVVVVV,*,4,12',
   expression          => '1=1');
END;
/

加密效果：addr列第4到12位替换成了*

举例3：使用数字型数据格式

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema       => 'U1', 
   object_name         => 'TAB1', 
   column_name         => 'TELNUM',
   policy_name         => 'redact_tab1', 
   function_type       => DBMS_REDACT.PARTIAL,
   function_parameters => '0,4,11',   
   expression          => '1=1');
END;
/

加密效果：ID列从第4位到11位替换成了0

第三种，Regular expressions(正则表达式编纂)：

正则表达式编纂，主要用于非固定长度的character类型数据进行部分加密；

类型

说明

regexp_pattern

Describes the search pattern for data that must be matched. If it finds a match, then Oracle Database replaces the data as specified by the regexp_replace_string setting.

regexp_replace_string

Specifies how you want to replace the data to be redacted.

regexp_position

Specifies the starting position for the string search. The value that you enter must be a positive integer indicating the character of the column_name data where Oracle Database should begin the search. The default is 1 or the RE_BEGINNING shortcut, meaning that Oracle Database begins the search at the first character of the column_name data.

regexp_occurrence

Specifies how to perform the search and replace operation. The value that you enter must be a nonnegative integer indicating the occurrence of the replace operation:If you specify 0 or the RE_ALL shortcut, then Oracle Database replaces all of the occurrences of the match.If you specify the RE_FIRST shortcut, then Oracle Database replaces the first occurrence of the match.If you specify a positive integer n, then Oracle Database replaces the nth occurrence of the first match.If the occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth.

regexp_match_parameter

Specifies a text literal that lets you change the default matching behavior of the function. The behavior of this parameter is the same for this function as for the REGEXP_REPLACE SQL function.

举例：测试email加密

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'U1', 
   object_name            => 'TAB3', 
   column_name            => 'EMAIL',
   policy_name            => 'redact_3', 
   function_type          => DBMS_REDACT.REGEXP,
   function_parameters    => NULL,
   expression             => '1=1',
   regexp_pattern         => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,
   regexp_replace_string  => DBMS_REDACT.RE_REDACT_EMAIL_NAME,
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => DBMS_REDACT.RE_FIRST);
END;
/

加密前： 1234556778@qq.com

加密后：xxxx@qq.com

第四种，Random redaction(随机编纂)：

随机加密，每次展现的加密结果是随机的，举例：

SQL> begin
dbms_redact.add_policy(
object_schema=>'U1',
object_name=>'TAB1',
POLICY_NAME=>'REDACT_1',
column_name=>'ADDR',
function_type=>DBMS_REDACT.RANDOM,
expression=>'1=1');
end;
/

效果：每次执行查询，查询出来的结果都是不一样的。

第五种，No Redaction(不编纂)：

No redaction严格的说并没有加密效果，它只是用于测试编纂的一些策略问题，并不对查询结果产生影响。

用法举例：

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema    => 'U1', 
   object_name      => 'TAB1', 
   column_name      => 'ADDR',
   policy_name      => 'redact_1', 
   function_type    => DBMS_REDACT.NONE,
   expression       => '1=1');
END;
/

用户管理技巧：

以上介绍了5种(实际有效果的是4种)数据编纂方法，可以帮助我们实现不同的加密需求。那如何将以上加密效果实现到具体用户上呢？

在前面的加密测试过程中，我们知道创建策略时expression是可以指定特殊角色的，比如redact_role：

expression => ‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”REDACT_ROLE”) = ”TRUE”’

因此我们可以在这里规定：

若用户没有这个角色权限，获取的是真实数据。

若用户含有这个角色权限，获取的是加密数据。

通过简单的赋权操作，达到快速实现用户加密管理。

如果后期要增加新的加密用户，我们直接将该角色赋予该用户即可：

SQL> grant redact_role to USERNAME

反之，回收权限将取消用户加密效果：

SQL> revoke redact_role from USERNAME

查看哪些用户被赋予了redact_role:

SQL> select grantee from dba_role_privs where granted_role='REDACT_ROLE'

备注：对于权限，Redaction不能对sys和system用户进行数据的加密。因为他们都有EXP_FULL_DATABASE这个角色, 而这个角色又包含了EXEMPT REDACTION POLICY系统权限。同时，也不能直接赋予用户dba权限，dba自动包含EXP_FULL_DATABASE角色。测试过程中发现，对于拥有dba权限的用户来说，表的数据可以加密操作，但没有实际加密效果。

4. 参考文档

《Database Advanced Security Administrator’s Guide》

《Advanced Security Guide》

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

相关推荐: 数据结构之”顺序表”

前言 个人主页: :✨✨✨初阶牛✨✨✨ 推荐专栏: c语言初阶 个人信条: 知行合一 本篇简介:>:讲解数据结构的入门知识,线性结构之顺序表. 金句分享: ✨人最高级的炫耀,是你这一生拒绝过什么.✨ @TOC 一、线性表 线性表（linear list）…

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: