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;
select object_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;
/
3.編寫本文遇到了非常多的問題，同時參考了一些網路的部落格，在此表示感謝
官方文件參考文件：Database Advanced Security Administrator's Guide
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26964624/viewspace-2149314/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12c - Data RedactionOracle
- Oracle Data Redaction實驗記錄Oracle
- Data Guardian for Mac(資料加密工具)Mac加密
- [20181018]Oracle Database 12c: Data Redaction.txtOracleDatabase
- oracle資料加密Oracle加密
- ORACLE資料加密(轉)Oracle加密
- Oracle透明資料加密Oracle加密
- Oracle Transparent Data Encryption 透明加密（一）Oracle加密
- Oracle Transparent Data Encryption 透明加密（二）Oracle加密
- Oracle TDE（透明資料加密）Oracle加密
- oracle 資料庫加加密。Oracle資料庫加密
- Oracle資料泵(Oracle Data Pump) 19cOracle
- oracle 資料泵 content=data_onlyOracle
- Oracle Data Pump 11G 資料泵元件Oracle元件
- Oracle對資料進行加密&解密處理Oracle加密解密
- 使用ssh tunnels加密連線oracle資料庫加密Oracle資料庫
- 資料加密加密
- Oracle10g資料字典（Data Dictionary for Oracle 10g）-ztOracle 10g
- Data Guardian for Mac(mac資料加密軟體) v5.1.4註冊版Mac加密
- 資料編織 (Data Fabric) vs 資料網格 (Data Mesh)
- 異構資料庫資料遷移 oracle to mysql之oracle sqlloader和mysql load data資料庫OracleMySql
- PG data wrapper 連線oracle 資料庫oracle_fdw 安裝APPOracle資料庫
- data dictionary（資料字典）
- 資料庫加密資料庫加密
- iOS - 資料加密iOS加密
- 透明資料加密加密
- Android資料加密之Aes加密Android加密
- Android資料加密之Rsa加密Android加密
- Android資料加密之Des加密Android加密
- Oracle資料庫12c最新安全工具Data RedactionFPOracle資料庫
- Oracle RAC & Data Guard搭建高可用資料庫系統方案Oracle資料庫
- 從SQLFile檔案分析Oracle Data Pump資料匯入行為SQLOracle
- [zt]Oracle 10G：透明資料加密技術(TDE)Oracle 10g加密
- 資料加密（對稱加密和非對稱加密）加密
- 資料加密-國密SM2對資料進行加密加密
- 2.9.2 透明資料加密加密
- JuiceFS 資料加密原理UI加密
- 資料加密方式:APM加密