怎么进行Oracle Data Redaction数据加密

今天就跟大家聊聊有关怎么进行Oracle Data Redaction数据加密,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。

1.Oracle 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版本

是否RAC

RHEL6.5

11.2.0.4.170418

 


2. Oracle Redaction加密测试

2.1环境测试准备

本次只模拟几种常用加密场景,创建用户、表,并赋予相应权限;若无特殊说明时,调用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

2.2对表的某些字段加密

调用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                 5000

SQL> SQL>
conn nosee/zhangxg

Connected.

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       5000

SQL> conn
nosee/zhangxg

Connected.

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

2.3Redaction的权限控制

对于权限的控制,我们可以直接选择用户去过滤,但用户过多时可以使用role来进行权限的控制。

nosee用户看到的结果也是加密的

SQL> show
user

USER is
"ZHANGXG"

SQL>

SQL> select
* from tab1;

EMPLOYEE_ID
FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY

———–
——————– ————————- ———– ———-

        100 Steven               NPnj                      ***-**-9056       7000

        101 Neena                ~<'`utz                   ***-**-6578       5000

SQL> conn
nosee/zhangxg

Connected.

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       5000

SQL> conn
nosee/zhangxg

Connected.

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/zhangxg

Connected.

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       5000

SQL>

SQL> conn /
as sysdba

Connected.

SQL>

SQL> grant
redac_role to zhangxg;

Grant
succeeded.

SQL> conn
zhangxg/zhangxg

Connected.

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       5000

SQL> conn
cansee/zhangxg

Connected.

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       5000

SQL> conn
nosee/zhangxg

Connected.

SQL> select
* from zhangxg.tab1;

EMPLOYEE_ID
FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY

———–
——————– ————————- ———– ———-

        100 Steven               King                      ***-**-9056       7000

        101 Neena                Kochhar                   ***-**-6578       5000

2.4Redaction的使用限制

通过测试可以得出以下结论:

1.对加密的列不能同时使用distinct和order by,如果使用,必须加一层select,无论对于使用的用户是否是加密可见;

2.加密的结果优先级高于函数,比如distinct;

3.对于group
by不影响结果的准确性,与未加密结果一致;

4.加密后的表无法进行CTAS(create table as select)操作;

对于加密后剩余字符不一样时,distinct结果是不影响的

SQL> show user

USER is "NOSEE"

SQL> col SOCIAL_SECURITY for a25

SQL> select * from zhangxg.tab1;

EMPLOYEE_ID FIRST_NAME  
LAST_NAME   SOCIAL_SECURITY     SALARY

———– ———— ———– —————— ——-

        100 Steven       King        ***-**-9056           7000

        101 Neena        Kochhar     ***-**-6578           5000

SQL> select distinct SOCIAL_SECURITY from zhangxg.tab1;

SOCIAL_SECURITY

————————-

***-**-9056

***-**-6578

SQL> conn zhangxg/zhangxg

Connected.

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-9056

247-85-9056

对于加密用户来说,distinct结果是1行,说明是先进行的加密,后进行distinct

SQL> conn nosee/zhangxg

Connected.

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
order by SOCIAL_SECURITY);

SOCIAL_SECURITY

————————-

***-**-9056

***-**-9056

SQL> conn zhangxg/zhangxg

Connected.

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

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

对于group by,加密后的效果是不影响分组判断

SQL>  conn zhangxg/zhangxg

Connected.

SQL> select SOCIAL_SECURITY,count(*) from zhangxg.tab1 group by
SOCIAL_SECURITY;

SOCIAL_SECURITY            
COUNT(*)

————————- ———-

334-08-9056                       
1

247-85-9056                       
1

SQL>  conn nosee/zhangxg

Connected.

SQL>

SQL>  select
SOCIAL_SECURITY,count(*) from zhangxg.tab1 group by SOCIAL_SECURITY;

SOCIAL_SECURITY            
COUNT(*)

————————- ———-

***-**-9056                       
1

***-**-9056                       
1

对于加密后的表无法进行CTAS操作,其实逻辑上是对的,既然加密了,如果能ctas成功那么加密就是去了意义

SQL> show user

USER 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         5000

SQL> 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 sysdba

Connected.

SQL> grant exempt redaction policy to NOSEE;

Grant succeeded.

SQL>  conn nosee/zhangxg

Connected.

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

3.附录

    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数据加密有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注亿速云行业资讯频道,感谢大家的支持。

原创文章,作者:3628473679,如若转载,请注明出处:https://blog.ytso.com/203753.html

(0)
上一篇 2021年11月28日
下一篇 2021年11月28日

相关推荐

发表回复

登录后才能评论