怎么進行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：隨機加密，每次展現的加密結果是不一定一樣；

本次加密測試環境介紹信息如下：

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數據加密有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。