怎么进行Oracle Data Redaction数据加密

今天就跟大家聊聊有关怎么进行Oracle Data Redaction数据加密，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
Oracle Data Redaction是Oracle安全加密类的高级功能，可用于对于敏感数据的加密处理，加密配置处理均在Oracle层面实现。这是一项和安全相关的技术类别，对于指定的用户可以限制某些表的某些列显示被加密改过的值。对于Redaction之前，可能需要自定义加密函数、创建特定的视图，或者在存储到数据库的时候就用加密算法进行加密。而Redaction可以直接对数据进行加密，不会影响到数据真实的存储，对应用透明，不需要改动。
对于权限，Redaction不能对sys和system用户进行数据的加密。因为他们都有EXP_FULL_DATABASE这个角色, 而这个角色又包含了EXEMPT REDACTION POLICY系统权限。同时，也不能直接赋予用户dba权限，dba自动包含EXP_FULL_DATABASE角色。测试过程中发现，对于拥有dba权限的用户来说，表的数据可以加密操作，但没有实际加密效果。
对于常用的加密类型说明如下：
1.Full redaction：对某字段数据全部加密，number类型的列将全部返回为0，character类型的列将全部返回为空格，日期类型返回为yyyy-mm-dd；
2.Partial redaction：对列中的一部分数据进行redact，比如，可以对身份证号或手机号的中间几位设置返回为*，剩下的几位保持不变，这种场景适用于固定长度；
3.Regular expressions：对于非固定长度的character类型数据进行部分加密；
4.Random redaction：随机加密，每次展现的加密结果是不一定一样；本次加密测试环境介绍信息如下：OS版本Oracle版本是否RACRHEL6.511.2.0.4.170418是

本次只模拟几种常用加密场景，创建用户、表，并赋予相应权限；若无特殊说明时，调用DBMS_REDACT所使用的均为zhangxg用户。SQL> create
user zhangxg identified by zhangxg;User created.SQL> grant connect,resource to zhangxg;Grant
succeeded.SQL> grant select
on sys.redaction_policies TO zhangxg;Grant
succeeded.SQL> grant select
on sys.redaction_columns TO zhangxg;Grant
succeeded.SQL> grant execute
on dbms_redact TO zhangxg;Grant
succeeded.

SQL> create
user nosee identified by zhangxg;User created.SQL> grant
connect,resource to nosee;Grant
succeeded.SQL> CREATE
TABLE ZHANGXG.TAB1 ( 2
“EMPLOYEE_ID” NUMBER(6,0), 3
“FIRST_NAME” VARCHAR2(20), 4
“LAST_NAME” VARCHAR2(25), 5
“SOCIAL_SECURITY” VARCHAR2(11), 6
“SALARY” NUMBER(4,0) 7 );Table created.SQL> insert
into tab1 values (100,’steven’,’king’,’247-85-9056′,7000);1 row created.SQL> insert
into tab1 values
(101,’neena’,’kochhar’,’334-08-6578′,5000);1 row created.SQL> commit;commit
complete.SQL> grant
select on zhangxg.tab1 to nosee;grant
succeeded.SQL> select
* from tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-6578 5000
调用DBMS_REDACT包创建policy策略SQL> BEGIN 2
DBMS_REDACT.ADD_POLICY ( 3
object_schema =>
‘ZHANGXG’, 4
object_name =>
‘TAB1’, 5
policy_name =>
‘REDACT_1’, 6
column_name =>
‘SOCIAL_SECURITY’, 7
function_type =>
DBMS_REDACT.PARTIAL, 8
EXPRESSION =>’1=1′, 9
function_parameters =>
‘VVVFVVFVVVV,VVV-VV-VVVV,*,1,5’10 );11
END;12 /PL/SQL
procedure successfully completed.SQL>SQL> select * from tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECURITY SALARY———–
——————– ——————– —————— ———- 100 Steven King ***-**-9056 7000 101 Neena Kochhar ***-**-6578 5000SQL> SQL>
conn nosee/zhangxgConnected.SQL>SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven King ***-**-9056 7000 101 Neena Kochhar ***-**-6578 5000基于某列加密的基础上，再增加一个乱码显示的列，即多列加密显示SQL> BEGIN 2
DBMS_REDACT.ALTER_POLICY( 3
object_schema =>
‘ZHANGXG’, 4
object_name => ‘TAB1’, 5
policy_name =>
‘REDACT_1’, 6
action =>
DBMS_REDACT.ADD_COLUMN, 7
column_name =>
‘LAST_NAME’, 8
function_type =>
DBMS_REDACT.RANDOM); 9 END;10 /PL/SQL
procedure successfully completed.SQL> select
* from tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven K5r. ***-**-9056 7000 101 Neena ymP’@Ea ***-**-6578 5000SQL> conn
nosee/zhangxgConnected.SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven YZ$z ***-**-9056 7000 101 Neena HSTk}5l ***-**-6578 5000
对于权限的控制，我们可以直接选择用户去过滤，但用户过多时可以使用role来进行权限的控制。nosee用户看到的结果也是加密的SQL> show
userUSER is
“ZHANGXG”SQL>SQL> select
* from tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven NPnj ***-**-9056 7000 101 Neena ~
SQL> conn
nosee/zhangxgConnected.SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven @a8H ***-**-9056 7000 101 Neena `&&3P– ***-**-6578 5000更改策略，除zhangxg本身用户外，其他用户访问均为加密SQL> BEGIN 2
DBMS_REDACT.ALTER_POLICY ( 3
object_schema =>’ZHANGXG’, 4
object_name
=>’TAB1′, 5
policy_name
=>’REDACT_1′, 6
column_name
=>’SOCIAL_SECURITY’, 7
action =>
DBMS_REDACT.MODIFY_EXPRESSION, 8 expression =>’SYS_CONTEXT ( ”USERENV”,”SESSION_USER”
) !=”ZHANGXG”’ 9 );10 END;11 /PL/SQL
procedure successfully completed.SQL> select
* from tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-6578 5000SQL> conn
nosee/zhangxgConnected.SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven 5}2g ***-**-9056 7000 101 Neena $b=Z%,W ***-**-6578 5000创建角色和用户用于模拟SQL> create
user cansee identified by zhangxg;User created.SQL> grant
connect,resource to cansee;Grant
succeeded.SQL> grant
select on zhangxg.tab1 to cansee;Grant
succeeded.SQL> create
role redac_role;Role created.SQL> grant
redac_role to cansee;Grant
succeeded.
下面，我们尝试去掉一个列的加密效果，即去掉LAST_NAME字段的全加密策略SQL> BEGIN 2
DBMS_REDACT.ALTER_POLICY( 3
object_schema => ‘ZHANGXG’, 4
object_name => ‘TAB1’, 5
policy_name =>
‘REDACT_1’, 6
action =>
DBMS_REDACT.DROP_COLUMN, 7
column_name =>
‘LAST_NAME’, 8
expression
=>’1=1′); 9 END;10 /PL/SQL procedure
successfully completed.SQL>SQL> conn
nosee/zhangxgConnected.SQL> select
* from zhangxg.tab1;EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————– ———– ———- 100 Steven King ***-**-9056 7000 101 Neena Kochhar ***-**-6578 5000
再次更改策略，只让zhangxg和cansee用户查看，其他用户为加密结果；这里我们新建了REDAC_ROLE角色去控制，拥有该角色的用户可正常查看，没有该角色的用户查看结果为加密；SQL> BEGIN 2
DBMS_REDACT.ALTER_POLICY ( 3
object_schema
=>’ZHANGXG’, 4
object_name
=>’TAB1′, 5
policy_name
=>’REDACT_1′, 6
column_name
=>’SOCIAL_SECURITY’, 7
action =>
DBMS_REDACT.MODIFY_EXPRESSION, 8
expression =>
‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”REDAC_ROLE”) = ”FALSE”’ 9 );10 END;11 /PL/SQL
procedure successfully completed.SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven King ***-**-9056 7000 101 Neena Kochhar ***-**-6578 5000SQL>SQL> conn /
as sysdbaConnected.SQL>SQL> grant
redac_role to zhangxg;Grant
succeeded.SQL> conn
zhangxg/zhangxgConnected.SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-6578 5000SQL> conn
cansee/zhangxgConnected.SQL>SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-6578 5000SQL> conn
nosee/zhangxgConnected.SQL> select
* from zhangxg.tab1;EMPLOYEE_ID
FIRST_NAME LAST_NAME SOCIAL_SECU SALARY———–
——————– ————————- ———– ———- 100 Steven King ***-**-9056 7000 101 Neena Kochhar ***-**-6578 5000
通过测试可以得出以下结论：
1.对加密的列不能同时使用distinct和order by，如果使用，必须加一层select，无论对于使用的用户是否是加密可见；
2.加密的结果优先级高于函数，比如distinct；
3.对于group
by不影响结果的准确性，与未加密结果一致；
4.加密后的表无法进行CTAS（create table as select）操作；
对于加密后剩余字符不一样时，distinct结果是不影响的SQL> show userUSER is “NOSEE”SQL> col SOCIAL_SECURITY for a25SQL> select * from zhangxg.tab1;EMPLOYEE_ID FIRST_NAME
LAST_NAME SOCIAL_SECURITY SALARY———– ———— ———– —————— ——- 100 Steven King ***-*免费主机域名*-9056 7000 101 Neena Kochhar ***-**-6578 5000SQL> select distinct SOCIAL_SECURITY from zhangxg.tab1;SOCIAL_SECURITY————————-***-**-9056***-**-6578SQL> conn zhangxg/zhangxgConnected.SQL> select * from zhangxg.tab1;EMPLOYEE_ID FIRST_NAME
LAST_NAME SOCIAL_SECURITY SALARY———– ———— ———— —————- ———- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-6578 5000手动update，将SOCIAL_SECURITY后4位改成一致SQL> update tab1 set SOCIAL_SECURITY=’334-08-9056′ where
EMPLOYEE_ID=101;1 row updated.SQL> commit;Commit complete.SQL> select * from zhangxg.tab1;EMPLOYEE_ID FIRST_NAME
LAST_NAME SOCIAL_SECURITY SALARY———– ———— ———– —————— ———- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-9056 5000
此时，对于加密不限制用户来说，distinct结果是2行SQL> select distinct SOCIAL_SECURITY from zhangxg.tab1;SOCIAL_SECURITY————————-334-08-9056247-85-9056
对于加密用户来说，distinct结果是1行，说明是先进行的加密，后进行distinctSQL> conn nosee/zhangxgConnected.SQL> select distinct SOCIAL_SECURITY from zhangxg.tab1;SOCIAL_SECURITY————————-***-**-9056
当对加密列同时使用distinct和order by，报语法错误，该问题已确认为bug，而且没有补丁，Bug 19558306 ；SQL> select distinct SOCIAL_SECURITY from zhangxg.tab1 order by
SOCIAL_SECURITY;select distinct SOCIAL_SECURITY from zhangxg.tab1 order by
SOCIAL_SECURITY *ERROR at line 1:ORA-01791: not a SELECTed expression
做为workaround，可以将SQL改写为子查询SQL> select * from (select distinct SOCIAL_SECURITY from zhangxg.tab1
ord免费主机域名er by SOCIAL_SECURITY);SOCIAL_SECURITY————————-***-**-9056***-**-9056SQL> conn zhangxg/zhangxgConnected.SQL> select distinct SOCIAL_SECURITY from zhangxg.tab1 order by
SOCIAL_SECURITY;select distinct SOCIAL_SECURITY from zhangxg.tab1 order by SOCIAL_SECURITY *ERROR at line 1:ORA-01791: not a SELECTed expressionSQL> select * from
zhangxg.tab1;EMPLOYEE_ID FIRST_NAME
LAST_NAME SOCIAL_SECURITY SALARY———– ————– ———– —————— ———- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-9056 5000
对于group by，加密后的效果是不影响分组判断SQL> conn zhangxg/zhangxgConnected.SQL> select SOCIAL_SECURITY,count(*) from zhangxg.tab1 group by
SOCIAL_SECURITY;SOCIAL_SECURITY
COUNT(*)————————- ———-334-08-9056
1247-85-9056
1SQL> conn nosee/zhangxgConnected.SQL>SQL> select
SOCIAL_SECURITY,count(*) from zhangxg.tab1 group by SOCIAL_SECURITY;SOCIAL_SECURITY
COUNT(*)————————- ———-***-**-9056
1***-**-9056
1
对于加密后的表无法进行CTAS操作，其实逻辑上是对的，既然加密了，如果能ctas成功那么加密就是去了意义SQL> show userUSER is “NOSEE”SQL> select * from
zhangxg.tab1;EMPLOYEE_ID FIRST_NAME
LAST_NAME SOCIAL_SECURITY SALARY———– ———— ———- —————– —— 100 Steven King ***-**-9056 7000 101 Neena Kochhar ***-**-9056 5000SQL> create table ctastab as select * from zhangxg.tab1;create table ctastab as select * from zhangxg.tab1 *ERROR at line 1:ORA-28081: Insufficient privileges – the command references a redacted
object.SQL> conn / as sysdbaConnected.SQL> grant exempt redaction policy to NOSEE;Grant succeeded.SQL> conn nosee/zhangxgConnected.SQL> create table ctastab as select * from zhangxg.tab1;Table created.SQL> select * from ctastab;EMPLOYEE_ID FIRST_NAME
LAST_NAME SOCIAL_SECURITY SALARY———– ———— ———– —————– ——- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-9056 5000
同时，有了该权限后加密也是去了意义SQL> select * from zhangxg.tab1;EMPLOYEE_ID FIRST_NAME
LAST_NAME SOCIAL_SECURITY SALARY———– ———— ———– —————– ——- 100 Steven King 247-85-9056 7000 101 Neena Kochhar 334-08-9056 5000
1.对于已添加的策略，可通过下面视图进行查询select * from redaction_policies;selectobject_owner,object_name,column_name,function_type,function_parameters
from redaction_columns;
2.删除加密策略BEGIN
DBMS_REDACT.DROP_POLICY(
object_schema => ‘ZHANGXG’,
object_name => ‘TAB1’,
policy_name => ‘REDACT_1’);END;看完上述内容，你们对怎么进行Oracle Data Redaction数据加密有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注云技术行业资讯频道，感谢大家的支持。

相关推荐: 联机执行SQL语句进行数据备份的高级特性分析

这篇文章主要讲解了“联机执行SQL语句进行数据备份的高级特性分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“联机执行SQL语句进行数据备份的高级特性分析”吧！1.加密备份DM7提供加密备份的方式保护用户的备份…