Oracle透明資料加密

yingyifeng306發表於2021-06-17


文件說明

Oracle 資料庫使用身份驗證、授權和稽核機制來保護資料庫中的資料,但沒有能夠保護儲存在作業系統上的資料檔案。為了保護這些資料檔案,Oracle資料庫提供透明資料加密(TDE)。該文件主要說明 oracle 高階安全元件中的透明資料加密 (TDE) 部分。

 

指定錢夾存放位置

在目錄 $ORACLE_HOME/network/admin 下找到檔案 sqlnet.ora ,新增如下內容:

# Oracle Advanced Security Transparent Data Encryption

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY= /u01/app/oracle/product/11.2/network/admin/encryption_wallet)))

注意:如果是 RAC 環境, oracle 建議將錢夾位置放置在共享檔案系統上,以便各個節點共享訪問。

 

建立目錄

$cd /u01/app/oracle/product/11.2/network/admin/

$mkdir  encryption_wallet

 

建立主加密鍵

SQL> select * from v$encryption_wallet;

WRL_TYPE     WRL_PARAMETER                      STATUS

----------   ----------------------------   ------------------

file  /u01/app/oracle/product/11.2/network/admin/encryption_wallet

CLOSED

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "test";

System altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE     WRL_PARAMETER                      STATUS

----------   ----------------------------   ------------------

file  /u01/app/oracle/product/11.2/network/admin/encryption_wallet

OPEN

 

開啟和關閉錢夾

第一次設定萬能金鑰會自動開啟錢夾,每次資料庫被關閉,錢夾也關閉。在加密或解密之前必須確保錢夾被開啟。可以配置自動登入開啟(可選)。

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "test";

(注:mount狀態下就可以開啟錢夾)

關閉錢夾: ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "test";

 

加密列和表空間具體過程

a. 加密表中一行測試

 

(1)     新建表透明加密

 

SQL> conn test/test;

Connected.

SQL> create table tde (id number(10),data varchar2(50) encrypt);

 

Table created.

 

SQL> insert into tde select user_id,username from dba_users;

 

10 rows created.

 

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

SQL> conn / as sysdba;

Connected.

SQL> select * from test.tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

SQL> select * from dba_encrypted_columns;

 

 

OWNER                          TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME                    ENCRYPTION_ALG                SAL INTEGRITY_AL

------------------------------ ----------------------------- --- ------------

TEST                           TDE

DATA                           AES 192 bits key              YES SHA-1

 

開啟錢夾才能查詢:

 

SQL> alter system set encryption wallet close identified by "hzmcdba";

 

System altered.

 

SQL> select * from test.tde;

select * from test.tde

*

ERROR at line 1:

ORA-28365: wallet is not open

 

 

SQL> alter system set encryption wallet open identified by "hzmcdba";

 

System altered.

 

SQL> select * from test.tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

(2)     已有表透明加密

 

SQL> conn test/test;

Connected.

SQL> create table existing_table(id number(10),data varchar2(50));

 

Table created.

 

SQL> insert into existing_table select user_id,username from dba_users;

 

10 rows created.

 

SQL> select * from existing_table;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

SQL> alter table existing_table modify(data encrypt);

 

Table altered.

 

b. 加密整體表空間

 

建立加密表空間

SQL> conn test/test;

Connected.

 

SQL> create tablespace encryptedtbs datafile '/oracle/app/orcl/secure01.dbf' size 5m encryption default storage(encrypt);

 

Tablespace created.

 

(1)     已有表移動至加密表空間

 

SQL> create table no_to_yes(id number(10),data varchar2(50));

 

Table created.

 

SQL> insert into no_to_yes select user_id,username from dba_users;

 

10 rows created.

 

SQL> alter system set encryption wallet close identified by "hzmcdba";

 

System altered.

 

SQL> select * from no_to_yes;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

SQL> alter table no_to_yes move tablespace encryptedtbs;

 

Table altered.

 

SQL> select table_name,tablespace_name from user_tables;

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TDE                            USERS

EXISTING_TABLE                 USERS

NO_TO_YES                      ENCRYPTEDTBS

 

SQL> alter system set encryption wallet close identified by "hzmcdba";

 

System altered.

 

SQL> select * from no_to_yes;

select * from no_to_yes

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

(2)     在加密表空間中新建表測試

 

SQL> show user;

USER is "TEST"

SQL> create table encrypted_table (id number,name varchar(10)) tablespace encryptedtbs;

 

Table created.

 

SQL> insert into encrypted_table values (1,'hzmcdba');

 

1 row created.

 

SQL> select * from encrypted_table;

 

        ID NAME

---------- ----------

         1 hzmcdba

 

SQL> alter system set encryption wallet close identified by "hzmcdba";

 

System altered.

 

SQL> select * from encrypted_table;

select * from encrypted_table

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

SQL> alter system set encryption wallet open identified by "hzmcdba";

 

System altered.

 

SQL> select * from encrypted_table;

 

        ID NAME

---------- ----------

         1 hzmcdba

 

路徑

 

Oracle 11gR2 RAC 節點能夠共享錢包。 Oracle 建議在共享檔案系統上建立錢包,這樣允許所有例項訪問相同的共享錢包,無需手動複製和同步所有節點上的錢包。

Oracle RAC 中一個例項對錢包進行操作(如開啟或關閉錢包),它會為 Oracle RAC 中所有例項開啟或關閉。

使用共享檔案系統時,需要確保所有 Oracle RAC 例項的 ENCRYPTION_WALLET_LOCATION WALLET_LOCATION 引數指向相同的共享錢包位置。安全管理員還需要透過分配相應的目錄許可權來確保共享錢包的安全性。

 

錢包的建立部署測試如下:

 

測試結果:指定錢夾存放目錄在共享磁碟中時,建立錢夾失敗!

指定錢夾存放目錄在本地時,錢夾可建立成功!

 

1.        指定錢包存放目錄在共享磁碟中

 

[oracle@rac1 admin]$ cat sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY= +data/orcl/wallet)))

 

[oracle@rac1 admin]$ scp sqlnet.ora oracle@rac2:/oracle/app/product/11.2.0/db_1/network/admin/

sqlnet.ora                                          100%   94     0.1KB/s   00:00

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

+data/orcl/wallet

CLOSED

 

 

SQL> alter system set encryption key identified by "test";

alter system set encryption key identified by "test"

*

ERROR at line 1:

ORA-28368: cannot auto-create wallet

 

說明:不能夠在 +data/orcl/wallet 目錄下建立檔案 ewallet.p12 往下看第 3 小點

 

2.        本地錢包配置及測試過程:

 

1 節點:

[oracle@rac1 admin]$ vi sqlnet.ora   

 

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=( DIRECTORY=$ORACLE_HOME)))

 

[oracle@rac1 admin]$ scp sqlnet.ora oracle@rac2:/oracle/app/product/11.2.0/db_1/network/admin/

sqlnet.ora        

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

$ORACLE_HOME

CLOSED

 

 

SQL> alter system set encryption key identified by "test";

 

System altered.

 

把錢夾複製到二節點:

 

[oracle@rac1 db_1]$ scp ewallet.p12 oracle@rac2:/oracle/app/product/11.2.0/db_1/

ewallet.p12                                         100% 2845     2.8KB/s   00:00

 

2 節點查詢:

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

$ORACLE_HOME

OPEN

 

1 節點建個加密表:

SQL> create user test identified by "test";

 

User created.

 

SQL> grant dba to test;

 

Grant succeeded.

 

SQL> conn test/test;

Connected.

 

SQL> create table tde (id number(10),data varchar2(50) encrypt);

 

Table created.

 

SQL> insert into tde select user_id,username from dba_users;

 

10 rows created.

 

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

         0 SYS

         5 SYSTEM

        34 ORACLE

        35 TEST

         9 OUTLN

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

        14 DIP

        21 ORACLE_OCM

 

10 rows selected.

 

SQL> commit;

 

Commit complete.

 

2 節點進行查詢:

 

SQL> conn test/test;

Connected.

 

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

         0 SYS

         5 SYSTEM

        34 ORACLE

        35 TEST

         9 OUTLN

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

        14 DIP

        21 ORACLE_OCM

 

10 rows selected.

 

(資料可以查到!)

錢夾部署成功!

 

1 節點:

SQL> alter system set encryption wallet close identified by "test";

 

System altered.

 

SQL> select * from tde;

select * from tde

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

2 節點查詢:

 

SQL> select * from tde;

select * from tde

*

ERROR at line 1:

ORA-28365: wallet is not open

 

2 節點開啟錢包:

 

SQL> alter system set encryption wallet open identified by "test";

 

System altered.

 

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

         0 SYS

         5 SYSTEM

        34 ORACLE

        35 TEST

         9 OUTLN

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

        14 DIP

        21 ORACLE_OCM

 

10 rows selected.

 

1 節點查詢:

 

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

         0 SYS

         5 SYSTEM

        34 ORACLE

        35 TEST

         9 OUTLN

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

        14 DIP

        21 ORACLE_OCM

 

10 rows selected.

 

3.        1 小點中,我們指定錢夾位置在共享磁碟上,然後建立錢夾,建立錢夾失敗,在這直接把錢夾複製到共享磁碟上,看能否成功!

 

把錢包複製到共享磁碟上:

 

ASMCMD> cp /oracle/app/product/11.2.0/db_1/ewallet.p12.bak +data/orcl/wallet/ewallet.p12.bak

ASMCMD-8012: cannot determine file type for file

ORA-15056: additional error message

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 322

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

 

複製失敗,從這也說明了前面 直接指定共享磁碟路徑建立錢夾失敗的原因,共享磁碟上不支援此類檔案。

 

 

端配置 錢夾

指定錢夾存放位置

備庫:

在目錄 $ORACLE_HOME/network/admin 下找到檔案sqlnet.ora ,新增如下內容:

# Oracle Advanced Security Transparent Data Encryption

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY=/u01/app/oracle/product/11.2/network/admin/encryption_wallet)))

 

建立目錄

備庫:

$cd /u01/app/oracle/product/11.2/network/admin/

$mkdir  encryption_wallet

 

複製primary 端加密錢夾

將生產端錢夾目錄下的ewallet.p12 複製到standby 端指定的錢夾目錄下。對於DG 來說這樣的設定還不夠,因為錢夾在這個時候還沒有被開啟,歸檔仍舊無法正常應用。我們對於備端建議使用無人值守的自動開啟的錢包。

 

建立自動開啟的錢夾

備庫:

自動開啟錢夾(檔名是 cwallet.sso )在資料庫啟動時會自動開啟。

兩種方法可建立自動開啟的錢夾

-- 命令列工具“orapki

 

$ cd $ORACLE_HOME/network/admin/ encryption_wallet

$ orapki wallet create –wallet $ORACLE_HOME/network/admin/encryption_wallet -auto_login_local

提示輸入primary 設定的錢夾密碼

 

錢夾存放目錄下會生成一個新檔案cwallet.sso

 

-- 圖形化方式顯示

oracle 使用者執行owm ,開啟錢夾管理工具:

 

選擇“開啟錢夾”,找到存放錢夾的路徑:

輸入正確的密碼,開啟錢夾:

 

在選單欄選中錢夾,勾上"Auto Login" 的核取方塊,表示自動登入開啟;

 

配置完成後儲存退出

 

錢夾存放目錄下會生成一個新檔案cwallet.sso

 

此時DG 端開啟應用程式即可正常應用日誌。

alter database recover managed standby database disconnect from session;

 

說明

DG 主庫部署 wallet ,備庫不部署 wallet

 

檢視 alert 日誌發現,歸檔能夠正常傳輸到備庫,但是查詢應用情況就發現,歸檔並不能被應用在備庫中:

SQL> SELECT SEQUENCE#, APPLIED,FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

 SEQUENCE# APPLIED   FIRST_TIM NEXT_TIME

---------- --------- --------- ---------

        88 YES       12-MAY-17 12-MAY-17

        89 YES       12-MAY-17 12-MAY-17

        90 YES       12-MAY-17 13-MAY-17

        91 YES       13-MAY-17 13-MAY-17

        92 YES       13-MAY-17 13-MAY-17

        93 YES       13-MAY-17 13-MAY-17

        94 YES       13-MAY-17 13-MAY-17

        95 YES       13-MAY-17 13-MAY-17

        96 YES       13-MAY-17 13-MAY-17

        97 YES       13-MAY-17 13-MAY-17

        98 YES       13-MAY-17 13-MAY-17

 

 SEQUENCE# APPLIED   FIRST_TIM NEXT_TIME

---------- --------- --------- ---------

        99 YES       13-MAY-17 13-MAY-17

       100 YES       13-MAY-17 13-MAY-17

       101 YES       13-MAY-17 13-MAY-17

       102 YES       13-MAY-17 13-MAY-17

       103 YES       13-MAY-17 13-MAY-17

       104 YES       13-MAY-17 13-MAY-17

       105 YES       13-MAY-17 13-MAY-17

       106 YES       13-MAY-17 13-MAY-17

       107 YES       13-MAY-17 13-MAY-17

       108 YES       13-MAY-17 13-MAY-17

       109 NO        13-MAY-17 13-MAY-17

 

 SEQUENCE# APPLIED   FIRST_TIM NEXT_TIME

---------- --------- --------- ---------

       110 NO        13-MAY-17 13-MAY-17

       111 NO        13-MAY-17 13-MAY-17

       112 NO        13-MAY-17 13-MAY-17

       113 NO        13-MAY-17 13-MAY-17

       114 NO        13-MAY-17 13-MAY-17

       115 NO        13-MAY-17 13-MAY-17

       116 NO        13-MAY-17 13-MAY-17

       117 NO        13-MAY-17 13-MAY-17

       118 NO        13-MAY-17 13-MAY-17

       119 NO        13-MAY-17 13-MAY-17

       120 NO        13-MAY-17 13-MAY-17

 

 SEQUENCE# APPLIED   FIRST_TIM NEXT_TIME

---------- --------- --------- ---------

       121 NO        13-MAY-17 13-MAY-17

       122 NO        13-MAY-17 13-MAY-17

 

alert 日誌報錯:

Media Recovery Log /arch/1_109_936453293.dbf

Apply redo for TSE master key re-key failed: wallet error 28365

Standby Crash Recovery aborted due to error 28365.

Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3517.trc:

ORA-28365: wallet is not open

Recovery interrupted!

Recovered data files to a consistent state at change 1238216

Completed Standby Crash Recovery.

 

備庫開啟wallet (從主庫複製而來)是否可以正常應用日誌

 

測試如下:

 

主庫SCP 到備庫:

[oracle@localhost wallet]$ scp ewallet.p12  192.168.40.71:/oracle/app/admin/orcl/wallet

oracle@192.168.40.71's password:

ewallet.p12                                            100% 2845     2.8KB/s   00:00

 

備庫開啟wallet 錢包:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY wallet;

System altered.

 

手動應用mrp 日誌:

alter database recover managed standby database disconnect from session;

 

檢視 alert 日誌發現成功應用:

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /arch/1_123_936453293.dbf

Media Recovery Log /arch/1_124_936453293.dbf

Media Recovery Log /arch/1_125_936453293.dbf

Completed: alter database recover managed standby database disconnect from session

Media Recovery Log /arch/1_126_936453293.dbf

Media Recovery Log /arch/1_127_936453293.dbf

Media Recovery Log /arch/1_128_936453293.dbf

Media Recovery Log /arch/1_129_936453293.dbf

Media Recovery Log /arch/1_130_936453293.dbf

Media Recovery Log /arch/1_131_936453293.dbf

Media Recovery Log /arch/1_132_936453293.dbf

Media Recovery Log /arch/1_133_936453293.dbf

Media Recovery Log /arch/1_134_936453293.dbf

Media Recovery Log /arch/1_135_936453293.dbf

Media Recovery Log /arch/1_136_936453293.dbf

Media Recovery Log /arch/1_137_936453293.dbf

 

關閉mrp 程式並將資料庫啟動到read only 模式後查詢資料:

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL> alter database open read only;

SQL> select * from test.employee;

 

NAME                   ID

-------------------------------------------------------------------------------test                    456

 

test2                   789

 

test3                    123

 

SQL> select table_name,column_name from DBA_ENCRYPTED_COLUMNS;

 

TABLE_NAME                     COLUMN_NAME

------------------------------ ------------------------------

EMPLOYEE                       ID

 

Dg 切換測試:

 

備庫:(自動開啟錢夾)

[oracle@ljw orcl]$ orapki wallet create -wallet /oracle/app/wallet/orcl -auto_login_local

Oracle PKI Tool : Version 11.2.0.4.0 - Production

Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

 

Enter wallet password:         

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

OPEN

 

主庫:(手動開啟錢夾)

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

OPEN

 

主備切換:

 

主庫:

SQL> alter system switch logfile;

 

System altered.

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

           453

 

備庫查詢:

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

           453

 

主庫:

SQL> select switchover_status from v$database;

 

SWITCHOVER_STATUS

--------------------

TO STANDBY

 

SQL> Alter database commit to switchover to physical standby with session shutdown;

 

Database altered.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             432016952 bytes

Database Buffers          348127232 bytes

Redo Buffers                2596864 bytes

SQL> alter database mount standby database;

 

Database altered.

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

Database altered.

 

備庫:

 

SQL> select switchover_status from v$database;

 

SWITCHOVER_STATUS

--------------------

TO PRIMARY

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

 

Database altered.

 

SQL> shutdown immediate

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             432016952 bytes

Database Buffers          348127232 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

 

切日誌查詢:

 

主庫:

SQL> alter system switch logfile;

 

System altered.

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

           455

 

備庫:

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

           455

 

主庫查詢加密表:

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

OPEN

 

SQL> conn test/test;

Connected.

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

備庫(原來為主庫):

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

CLOSED

(注:原來的主庫沒有建立自動錢包,所以切成備庫以後,需要手動開啟)

 

 

錢夾的備份

         正如上述,已經加密過的表列或者表空間,錢夾必須開啟才能夠查詢到裡面的資料。如果錢夾丟失,那就意味著加密資料的丟失,所以錢夾的備份是及其重要的。錢夾一開始建立就應該得到有效的備份,放在不同的磁碟上。不要和資料庫檔案所在磁碟相同,這可以避免資料檔案和錢夾同時被盜(當然了,如果同時被盜,想透過資料庫查詢加密資料,不知道錢夾的密碼,也是無法查詢到加密資料的)。

         除了錢夾需要有效的備份之外,錢夾的密碼千萬不能忘記。如果忘記了錢夾密碼,錢夾就無法開啟,加密的資料也就無法查詢到,這也就意味著加密資料的丟失。 Oracle 沒有提供解決錢夾密碼丟失的方法

         綜上,如果使用透明加密來加密資料,要想加密資料不丟失,必須做到兩點:1. 錢夾必須存在(備份的重要性);2. 錢夾的密碼不能夠忘記。

 

這以加密表空間為例:

使用者連入資料庫進行資料更新或者查詢時,所涉及到的物件會自動加密或自動解密;

但加密表空間裡的所有資料都是以加密的格式被儲存在磁碟上,磁碟或備份介質被盜時,裡面的資料也不會被盜取,因為裡面是亂碼的。舉例如下:

book_list 未加密,其所在資料檔案為 books01.dbf

no_to_yes 加密過,其所在資料檔案為 secure01.dbf

授權使用者連入資料庫查詢這兩張表:

SQL> select * from book_list;

 

        BOOKID BOOKNAME

---------- ----------

         1 note

        2 good

         3 name

 

       SQL> select * from no_to_yes;

 

        ID DATA

---------- ----------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

      

TDE 已為使用者自動解密,所以查詢到的都是正常的資料。

如果使用作業系統命令 strings 直接檢視檔案中內容,未加密表空間的資料檔案中的內容為明文,而加密過表空間對應的資料檔案中的內容則為亂碼。

[root@ljw jiami]# strings books01.dbf (未加密)

}|{z

WORCL

BOOKS

name,

good,

note

 

[root@ljw jiami]# strings secure01.dbf (加密過)

MMFJ?8E

iHa!c

jVIa9`o

y8wt0

zcnf

'3{b9

cVe9

+w!N

(9zq9`

[6.H

o1U"

h[%l

\=zm\

,O;Y

Ur9(R

^ UP

Qr*<

AYB=K

=h>:

]?      9

TDV.J2

YL]dV

 Z_!

!Q_#

p4|o

(s])s

&q=99

Zqkn

(記憶體中為明文,硬碟中為密文)

 

(注:如果檔案很大,則可以使用管道 head –n 行數 指定要顯示的行數)

       例: [oracle@ljw orcl]$ strings books01.dbf | head -n 3   

 

結論:匯出工具 EXP 無法匯出加密過的表,具體測試如下:

(錢包開啟和錢包關閉時,匯出加密過的表進行測試)

 

錢包關閉時:

SQL> select * from dba_encrypted_columns;

 

OWNER                          TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME                    ENCRYPTION_ALG                SAL INTEGRITY_AL

------------------------------ ----------------------------- --- ------------

TEST                            TDE

DATA                           AES 192 bits key              YES SHA-1

 

TEST                           EXISTING_TABLE

DATA                           AES 192 bits key              YES SHA-1

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

CLOSED

 

[oracle@ljw backup]$ exp system/oracle tables=test.tde file=tde.dmp log=tde.log

 

Export: Release 11.2.0.4.0 - Production on Sat Jun 24 03:48:27 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

 

About to export specified tables via Conventional Path ...

Current user changed to TEST

EXP-00107: Feature (COLUMN ENCRYPTION) of column DATA in table TEST.TDE is not supported. The table will not be exported.

Export terminated successfully with warnings.

 

錢包開啟時:

SQL> alter system set encryption wallet open identified by "hzmcdba123";

 

System altered.

 

[oracle@ljw backup]$ exp system/oracle tables=test.tde file=tde.dmp log=tde.log

 

Export: Release 11.2.0.4.0 - Production on Sat Jun 24 03:58:43 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

 

About to export specified tables via Conventional Path ...

Current user changed to TEST

EXP-00107: Feature (COLUMN ENCRYPTION) of column DATA in table TEST.TDE is not supported. The table will not be exported.

Export terminated successfully with warnings.

 

結論: 1. 匯出時,錢包需要開啟,否則都會報錯。

2. 匯出後,匯入時,錢包也需要開啟,否則也都會報錯。

3. 匯出後,匯入另一個資料庫,如果那個資料庫沒有錢夾,匯入失敗。如果那個資料庫有錢夾,並且錢夾開啟(測試中另一庫中新建錢夾密碼與原庫不一樣),也會匯入成功,具體測試如下:

 

1.        匯出時,錢包需要開啟,否則都會報錯

 

錢夾關閉:

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

CLOSED

 

匯出:

[oracle@ljw backup]$ expdp system/oracle directory=dump_file_dir dumpfile=tde.dmp tables=test.tde

 

Export: Release 11.2.0.4.0 - Production on Sat Jun 24 04:15:09 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dump_file_dir dumpfile=tde.dmp tables=test.tde

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-31693: Table data object "TEST"."TDE" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-28365: wallet is not open

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  /oracle/backup/tde.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Sat Jun 24 04:15:16 2017 elapsed 0 00:00:05

 

2.        匯出後,匯入時,錢包也需要開啟,否則也都會報錯

 

錢夾開啟:

SQL> alter system set encryption wallet open identified by " hzmcdba123";

 

System altered.

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

OPEN

 

匯出:

[oracle@ljw backup]$ expdp system/oracle directory=dump_file_dir dumpfile=tde.dmp tables=test.tde

 

Export: Release 11.2.0.4.0 - Production on Sat Jun 24 04:33:37 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dump_file_dir dumpfile=tde.dmp tables=test.tde

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "TEST"."TDE"                                5.570 KB      10 rows

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

  /oracle/backup/tde.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Sat Jun 24 04:33:44 2017 elapsed 0 00:00:06

 

匯入同一個庫:

錢夾關閉時匯入:

SQL> alter system set encryption wallet close identified by "hzmcdba123";

 

System altered.

 

[oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp

 

Import: Release 11.2.0.4.0 - Production on Sat Jun 24 04:59:04 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump_file_dir dumpfile=tde.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"TEST"."TDE" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "TEST"."TDE" ("ID" NUMBER(10,0), "DATA" VARCHAR2(50 BYTE) ENCRYPT USING 'AES192' 'SHA-1') SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"TEST"."TDE" creation failed

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sat Jun 24 04:59:07 2017 elapsed 0 00:00:02

 

錢夾開啟匯入:

SQL> alter system set encryption wallet open identified by "hzmcdba123";

 

System altered.

 

[oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp

 

Import: Release 11.2.0.4.0 - Production on Sat Jun 24 05:03:23 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump_file_dir dumpfile=tde.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."TDE"                                5.570 KB      10 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Jun 24 05:03:28 2017 elapsed 0 00:00:04

 

匯入成功。

 

SQL> select * from test.tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

注:錢夾開啟,從同一個庫中匯出後匯入成功。

 

匯入:(另一個庫)

[oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp

 

Import: Release 11.2.0.4.0 - Production on Sat Jun 24 04:54:33 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump_file_dir dumpfile=tde.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"TEST"."TDE" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "TEST"."TDE" ("ID" NUMBER(10,0), "DATA" VARCHAR2(50 BYTE) ENCRYPT USING 'AES192' 'SHA-1') SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"TEST"."TDE" creation failed

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sat Jun 24 04:54:36 2017 elapsed 0 00:00:02

 

SQL> select * from test.tde;

select * from test.tde

                   *

ERROR at line 1:

ORA-00942: table or view does not exist

 

注:匯入另一個庫失敗,因為沒有啟用透明加密錢夾。

 

建立錢包,匯入

SQL> alter system set encryption key identified by "hzmcdba";

 

System altered.

 

[oracle@ljw backup]$ impdp system/oracle directory=dump_file_dir dumpfile=tde.dmp

 

Import: Release 11.2.0.4.0 - Production on Sat Jun 24 05:41:31 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump_file_dir dumpfile=tde.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."TDE"                                5.570 KB      10 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Jun 24 05:41:37 2017 elapsed 0 00:00:04

 

SQL> select * from test.tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

注:匯入另一庫,需要有錢包,錢包開啟即可。

 

結論:不管錢包是否開啟都可以正常備份,恢復時需要開啟錢包。

 

備份:

SQL> select table_name from user_tables where tablespace_name='ENCRYPTEDTBS';

 

TABLE_NAME

------------------------------

ENCRYPTED_TABLE

NO_TO_YES

 

錢夾關閉:

SQL> alter system set encryption wallet close identified by "hzmcdba123";

 

System altered.

 

RMAN> backup datafile 7;

 

Starting backup at 24-JUN-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/oracle/app/orcl/secure01.dbf

channel ORA_DISK_1: starting piece 1 at 24-JUN-17

channel ORA_DISK_1: finished piece 1 at 24-JUN-17

piece handle=/oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1 tag=TAG20170624T084727 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-JUN-17

 

Starting Control File and SPFILE Autobackup at 24-JUN-17

piece handle=/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-09 comment=NONE

Finished Control File and SPFILE Autobackup at 24-JUN-17

 

錢夾開啟:

SQL> alter system set encryption wallet open identified by "hzmcdba123";

 

System altered.

 

RMAN> backup datafile 7;

 

Starting backup at 24-JUN-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/oracle/app/orcl/secure01.dbf

channel ORA_DISK_1: starting piece 1 at 24-JUN-17

channel ORA_DISK_1: finished piece 1 at 24-JUN-17

piece handle=/oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1 tag=TAG20170624T084842 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-JUN-17

 

Starting Control File and SPFILE Autobackup at 24-JUN-17

piece handle=/oracle/app/product/11.2.0/db_1/dbs/c-1471212201-20170624-0a comment=NONE

Finished Control File and SPFILE Autobackup at 24-JUN-17

 

恢復:

錢夾關閉:

[oracle@ljw orcl]$ rm secure01.dbf

 

1.        用之前關閉錢夾備份的備份集經進行恢復:

RMAN> restore datafile 7 from tag='TAG20170624T084727';

 

Starting restore at 24-JUN-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00007 to /oracle/app/orcl/secure01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1

channel ORA_DISK_1: piece handle=/oracle/app/product/11.2.0/db_1/dbs/2ts7j74v_1_1 tag=TAG20170624T084727

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 24-JUN-17

 

RMAN> recover datafile 7 from tag='TAG20170624T084727';

 

Starting recover at 24-JUN-17

using channel ORA_DISK_1

 

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/24/2017 08:53:15

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

 datafile 7

ORA-00283: recovery session canceled due to errors

ORA-28365: wallet is not open

 

SQL> alter system set encryption wallet open identified by "hzmcdba123";

 

System altered.

 

RMAN> recover datafile 7 from tag='TAG20170624T084727';

 

Starting recover at 24-JUN-17

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:00

 

Finished recover at 24-JUN-17

 

SQL> alter database open;

 

Database altered.

 

SQL> select * from test.jiami;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

        14 DIP

        21 ORACLE_OCM

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

         0 SYS

         5 SYSTEM

         9 OUTLN

 

10 rows selected.

 

2.        用開啟錢夾備份的備份集經進行恢復:

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             465571384 bytes

Database Buffers          314572800 bytes

Redo Buffers                2596864 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/oracle/app/orcl/secure01.dbf

 

RMAN> restore datafile 7 from tag='TAG20170624T084842';

 

Starting restore at 24-JUN-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00007 to /oracle/app/orcl/secure01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1

channel ORA_DISK_1: piece handle=/oracle/app/product/11.2.0/db_1/dbs/2vs7j77a_1_1 tag=TAG20170624T084842

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 24-JUN-17

 

RMAN> recover datafile 7 from tag='TAG20170624T084842';

 

Starting recover at 24-JUN-17

using channel ORA_DISK_1

 

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/24/2017 09:00:19

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

 datafile 7

ORA-00283: recovery session canceled due to errors

ORA-28365: wallet is not open

 

SQL> alter system set encryption wallet open identified by "hzmcdba123";

 

System altered.

 

RMAN> recover datafile 7 from tag='TAG20170624T084842';

 

Starting recover at 24-JUN-17

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:00

 

Finished recover at 24-JUN-17

 

SQL> alter database open;

 

Database altered.

 

SQL> select * from test.jiami;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

        14 DIP

        21 ORACLE_OCM

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

         0 SYS

         5 SYSTEM

         9 OUTLN

 

10 rows selected.

 

開啟,資料能否可查測試

結論:資料檔案可開啟,加密資料可查(當然了,錢夾也拷過去,錢夾密碼也知道)。

           只複製資料檔案,錢夾沒有複製,加密資料不可查(模擬資料檔案被盜)

 

情景一:

 

SQL> drop tablespace dabiao including contents and datafiles;

 

Tablespace dropped.

 

SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on;

 

Tablespace created.

 

SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao;

 

Table created.

 

SQL> begin

  2  for i in 1 .. 10

  3  loop

  4  insert into dabiao values ( i, 'ljw' );

  5  end loop;

  6  commit;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL> alter table dabiao modify(name encrypt);

 

Table altered.

 

SQL> select * from dabiao;

 

        ID NAME

---------- ----------

         1 ljw

         2 ljw

         3 ljw

         4 ljw

         5 ljw

         6 ljw

         7 ljw

         8 ljw

         9 ljw

        10 ljw

 

10 rows selected.

 

SQL> alter system set encryption wallet close identified by "hzmcdba";

 

System altered.

 

SQL> select * from dabiao;

select * from dabiao

              *

ERROR at line 1:

ORA-28365: wallet is not open

                       

關閉資料:

SQL> conn / as sysdba   

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

scp -r /oracle/* oracle@192.168.142.11:/oracle/

 

另一臺:

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             448794168 bytes

Database Buffers          331350016 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

SQL> conn test/test;  

Connected.

SQL> select * from dabiao;

select * from dabiao

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

SQL> alter system set encryption wallet open identified by "hzmcdba";

 

System altered.

 

SQL> select * from dabiao;

 

        ID NAME

---------- ----------

         1 ljw

         2 ljw

         3 ljw

         4 ljw

         5 ljw

         6 ljw

         7 ljw

         8 ljw

         9 ljw

        10 ljw

 

10 rows selected.

 

開啟查詢成功!

 

情景二:

 

源庫:

 

[oracle@ljw orcl]$ pwd

/oracle/app/orcl

[oracle@ljw orcl]$ ls

books01.dbf    control02.ctl  redo02.log  sysaux01.dbf  temp01.dbf  test_tde       users01.dbf

control01.ctl  redo01.log     redo03.log  system01.dbf  test.dbf    undotbs01.dbf

[oracle@ljw orcl]$ scp * oracle@192.168.142.11:/oracle/app/orcl/

oracle@192.168.142.11's password:

books01.dbf                                                                  100% 5128KB   5.0MB/s   00:01   

control01.ctl                                                                100% 9840KB   9.6MB/s   00:00   

control02.ctl                                                                100% 9840KB   9.6MB/s   00:00   

redo01.log                                                                   100%   50MB  50.0MB/s   00:01   

redo02.log                                                                   100%   50MB  50.0MB/s   00:01   

redo03.log                                                                   100%   50MB  50.0MB/s   00:01   

sysaux01.dbf                                                                 100%  600MB  30.0MB/s   00:20   

system01.dbf                                                                 100%  700MB  29.2MB/s   00:24   

temp01.dbf                                                                   100%   20MB   5.0MB/s   00:04   

test.dbf                                                                     100% 5128KB   5.0MB/s   00:01   

test_tde                                                                     100% 5128KB   5.0MB/s   00:00   

undotbs01.dbf                                                                100% 1710MB  25.9MB/s   01:06   

users01.dbf                                                                  100% 5128KB   5.0MB/s   00:00   

[oracle@ljw orcl]$ cd $ORACLE_HOME

[oracle@ljw db_1]$ cd dbs

[oracle@ljw dbs]$ ls

25s4jm22_1_1              c-1471212201-20170617-00  c-1471212201-20170627-00  hc_orcl.dat   orapworcl

27s4k331_1_1              c-1471212201-20170617-01  c-1471212201-20170627-01  hc_test.dat   orapwtest

arch1_152_944363414.dbf   c-1471212201-20170617-02  c-1471212201-20170627-02  init.ora      snapcf_orcl.f

c-1471212201-20170519-00  c-1471212201-20170617-03  c-1471212201-20170627-03  initTEST.ora  spfileorcl.ora

c-1471212201-20170519-05  c-1471212201-20170617-04  c-1471212201-20170630-00  lkORCL        spfiletest.ora

c-1471212201-20170519-06  c-1471212201-20170617-05  c-1471212201-20170630-01  lkTEST

[oracle@ljw dbs]$ scp * oracle@192.168.142.11:/oracle/app/product/11.2.0/db_1/d

dbs/         dc_ocm/      deinstall/   demo/        diagnostics/ dv/         

[oracle@ljw dbs]$ scp * oracle@192.168.142.11:/oracle/app/product/11.2.0/db_1/dbs/

oracle@192.168.142.11's password:

25s4jm22_1_1                                                                 100%  307MB  34.2MB/s   00:09   

27s4k331_1_1                                                                 100%  279MB  25.4MB/s   00:11   

arch1_152_944363414.dbf                                                      100%   50MB  49.9MB/s   00:01   

c-1471212201-20170519-00                                                     100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170519-05                                                     100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170519-06                                                     100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-00                                                     100% 9664KB   3.2MB/s   00:03   

c-1471212201-20170617-01                                                     100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-02                                                     100% 9664KB   9.4MB/s   00:01   

c-1471212201-20170617-03                                                     100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-04                                                     100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-05                                                     100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170627-00                                                     100% 9728KB   9.5MB/s   00:00   

c-1471212201-20170627-01                                                     100% 9792KB   9.6MB/s   00:01   

c-1471212201-20170627-02                                                     100% 9792KB   9.6MB/s   00:00   

c-1471212201-20170627-03                                                     100% 9920KB   9.7MB/s   00:00   

c-1471212201-20170630-00                                                     100% 9920KB   9.7MB/s   00:00   

c-1471212201-20170630-01                                                     100% 9920KB   9.7MB/s   00:01   

hc_orcl.dat                                                                  100% 1544     1.5KB/s   00:00   

hc_test.dat                                                                  100% 1544     1.5KB/s   00:00   

init.ora                                                                     100% 2851     2.8KB/s   00:00   

initTEST.ora                                                                 100%  705     0.7KB/s   00:00   

lkORCL                                                                       100%   24     0.0KB/s   00:00   

lkTEST                                                                       100%   24     0.0KB/s   00:00   

orapworcl                                                                    100% 1536     1.5KB/s   00:00   

orapwtest                                                                    100% 1536     1.5KB/s   00:00   

snapcf_orcl.f                                                                100% 9840KB   9.6MB/s   00:00   

spfileorcl.ora                                                               100% 2560     2.5KB/s   00:00   

spfiletest.ora                                                               100% 2560     2.5KB/s   00:00 

 

 

SQL> select * from dba_encrypted_columns;

 

OWNER                          TABLE_NAME

------------------------------ ------------------------------

COLUMN_NAME                    ENCRYPTION_ALG                SAL INTEGRITY_AL

------------------------------ ----------------------------- --- ------------

TEST                           TDE

DATA                           AES 192 bits key              YES SHA-1

 

TEST                           SALT

NAME                           AES 192 bits key              YES SHA-1

 

 

SQL> conn test/test;

Connected.

SQL> select * from tde;

select * from tde

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

 

目標庫:

相關檔案被傳過來

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             448794168 bytes

Database Buffers          331350016 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

SQL> conn test/test;

Connected.

SQL> select * from tde;

select * from tde

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

SQL> alter system set encryption wallet open identified by "hzmcdba123";

alter system set encryption wallet open identified by "hzmcdba123"

*

ERROR at line 1:

ORA-28367: wallet does not exist

 

注:如果檔案被盜,如果想透過資料庫檢視資料,已被加密的表是無法查詢到資料的,因為有錢夾的保護。

 

結論:對錶加密失敗,提交後,才能加密成功

 

SQL> create table tijiao (id number,name varchar(10));

 

Table created.

 

SQL> insert into tijiao values (1,'ljw');

 

1 row created.

 

SQL> insert into tijiao values (2,'test');

 

1 row created.

 

SQL> insert into tijiao values (3,'dba');

 

1 row created.

 

SQL> insert into tijiao values (4,'hzmc');

 

1 row created.

 

SQL> insert into tijiao values (5,'hzmcdba');

 

1 row created.

 

另一個視窗:

SQL> conn test/test;

Connected.

SQL> alter table tijiao modify(name encrypt);

alter table tijiao modify(name encrypt)

            *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

 

SQL> alter table tijiao modify(name encrypt);

alter table tijiao modify(name encrypt)

            *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

 

SQL> alter table tijiao modify(name encrypt);

alter table tijiao modify(name encrypt)

            *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

提交:

SQL> commit;

 

Commit complete.

 

 

SQL> alter table tijiao modify(name encrypt);

 

Table altered.

 

,測試其隨著表的變大,加密表空間大小和加密時間所需長短

結論: 1 . 當資料檔案沒有開啟自動擴充套件,對錶中列進行加密,空間不足時,加密會失敗。

2 .如下表所示(資料檔案開啟自動擴充套件):

 

 

加密資料量

50 萬行( 8M

100 萬行( 16M

300 萬行( 49M

加密前資料檔案大小

100

100

100

加密後資料檔案大小

100

119.25

345.5

加密前表空間 FREE 大小

91

83

50

加密前表大小

8

16

49

加密後表空間 FREE 大小

44

6.25

16.5

加密後表大小

55

112

328

加密所用時間

1 分鐘

1 45

5 35

 

SQL> alter system set encryption wallet open identified by "hzmcdba";

 

System altered.

 

SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m;      

 

Tablespace created.

 

SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao;

 

Table created.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                          100

SYSAUX                                          600

UNDOTBS1                                        200

BOOKS                                             5

USERS                                             5

TEST                                              5

SYSTEM                                          700

 

7 rows selected.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                           99

SYSAUX                                        475.5

UNDOTBS1                                   189.5625

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

no rows selected

 

SQL> begin

  2  for i in 1 .. 3000000

  3  loop

  4  insert into dabiao values ( i, 'ljw' );

  5  end loop;

  6  commit;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                           50

SYSAUX                                        475.5

UNDOTBS1                                          2

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

49M

 

為該表加密

 

SQL> alter table dabiao modify(name encrypt);

alter table dabiao modify(name encrypt)

*

ERROR at line 1:

ORA-01653: unable to extend table TEST.DABIAO by 1024 in tablespace DABIAO

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                            3

SYSAUX                                        475.5

UNDOTBS1                                      1.625

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

96M

 

SQL> alter system set encryption wallet close identified by "hzmcdba";

 

System altered.

 

SQL> select * from dabiao where id > 2999995;

 

        ID NAME

---------- ----------

   2999996 ljw

   2999997 ljw

   2999998 ljw

   2999999 ljw

   3000000 ljw

 

由於空間不足會導致加密失敗。下面測試 50 萬行、 100 萬行以及 300 萬行資料加密前後空間大小及其所用時間。

 

SQL> alter system set encryption wallet open identified by "hzmcdba";

 

System altered.

 

SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on;

 

Tablespace created.

 

SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao;

 

Table created.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                          100

SYSAUX                                          600

UNDOTBS1                                        505

BOOKS                                             5

USERS                                             5

TEST                                              5

SYSTEM                                          700

 

7 rows selected.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                           99

SYSAUX                                        475.5

UNDOTBS1                                      1.625

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

no rows selected

 

SQL> begin

  2  for i in 1 .. 500000

  3  loop

  4  insert into dabiao values ( i, 'ljw' );

  5  end loop;

  6  commit;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                           91

SYSAUX                                        475.5

UNDOTBS1                                          2

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

8M

 

對錶進行加密

 

SQL> alter table dabiao modify(name encrypt);

 

Table altered. (耗時 1 分鐘!)

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                           44

SYSAUX                                        475.5

UNDOTBS1                                          2

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

55M

 

 

恢復測試環境到初始狀態,插入 100 萬行資料再來過:

 

SQL> drop tablespace dabiao including contents and datafiles;

 

Tablespace dropped.

 

SQL>  create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on;

 

Tablespace created.

 

SQL> create table dabiao(id number,name varchar(10)) tablespace dabiao;

 

Table created.

 

SQL> begin

  2  for i in 1 .. 1000000

  3  loop

  4  insert into dabiao values ( i, 'ljw' );

  5  end loop;

  6  commit;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                           83

SYSAUX                                        475.5

UNDOTBS1                                         23

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

16M

 

對錶進行加密

 

SQL> alter table dabiao modify(name encrypt);

 

Table altered. (耗時 1 45 秒!)

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                       119.25

SYSAUX                                          600

UNDOTBS1                                        785

BOOKS                                             5

USERS                                             5

TEST                                              5

SYSTEM                                          700

 

7 rows selected.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                         6.25

SYSAUX                                      475.625

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

6 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

112M

 

恢復測試環境到初始狀態,插入 300 萬行資料再測試:

 

SQL> drop tablespace dabiao including contents and datafiles;

 

Tablespace dropped.

 

SQL> create tablespace dabiao datafile '/oracle/app/orcl/dabiao.dbf' size 100m autoextend on;

 

Tablespace created.

 

SQL>  create table dabiao(id number,name varchar(10)) tablespace dabiao;

 

Table created.

 

SQL> begin

  2  for i in 1 .. 3000000

  3  loop

  4  insert into dabiao values ( i, 'ljw' );

  5  end loop;

  6  commit;

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                          100

SYSAUX                                          600

UNDOTBS1                                        990

BOOKS                                             5

USERS                                             5

TEST                                              5

SYSTEM                                          700

 

7 rows selected.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                           50

SYSAUX                                      475.625

UNDOTBS1                                          2

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

7 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

49M

 

進行加密:

 

SQL> alter table dabiao modify(name encrypt);

 

Table altered. (耗時 5 35 秒!)

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                        345.5

SYSAUX                                          600

UNDOTBS1                                       1710

BOOKS                                             5

USERS                                             5

TEST                                              5

SYSTEM                                          700

 

7 rows selected.

 

SQL> select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024

------------------------------ --------------------

DABIAO                                         16.5

SYSAUX                                     475.5625

BOOKS                                         3.875

USERS                                        3.9375

TEST                                         3.9375

SYSTEM                                     423.9375

 

6 rows selected.

 

SQL> SELECT segment_name AS TABLENAME,BYTES/1024/1024||'M'  FROM user_segments WHERE segment_name='DABIAO';

 

TABLENAME

--------------------------------------------------------------------------------

BYTES/1024/1024||'M'

-----------------------------------------

DABIAO

328M

 

結論:錢夾丟失後,恢復即可重新開啟錢包。(重新建立一個一模一樣的錢包是不可行的,無法查詢到之前已經加密過的資料)

 

1.        備份錢包後,刪掉錢包進行測試

資料庫沒關掉之前,加密的資料仍然是可查的

 

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

關庫重啟

SQL> shutdown immediate;

SQL> startup;

 

SQL> select * from tde;

select * from tde

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

SQL> alter system set encryption wallet open identified by "hzmcdba123";

alter system set encryption wallet open identified by "hzmcdba123"

*

ERROR at line 1:

ORA-28367: wallet does not exist

 

把錢包恢復

[oracle@ljw db_1]$ cp ewallet.p12.bak ewallet.p12

 

SQL> alter system set encryption wallet open identified by "hzmcdba123";

 

System altered.

 

SQL> select * from tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

資料可查

2.        重新建立一個一模一樣錢夾可行性測試

 

刪除錢夾檔案

[oracle@ljw db_1]$ rm ewallet.p12

 

SQL> alter system set encryption wallet close identified by "hzmcdba123";   // 錢包可關閉

 

System altered.

 

SQL> alter system set encryption wallet open identified by "hzmcdba123";

alter system set encryption wallet open identified by "hzmcdba123"

*

ERROR at line 1:

ORA-28367: wallet does not exist

 

SQL> alter system set encryption key identified by "hzmcdba123";

alter system set encryption key identified by "hzmcdba123"

*

ERROR at line 1:

ORA-28362: master key not found

 

重啟

 

SQL> alter system set encryption key identified by "hzmcdba123";

alter system set encryption key identified by "hzmcdba123"

*

ERROR at line 1:

ORA-28396: rekey of enc$ dictionary table failed

 

雖然報錯,但新的 wallet 檔案還是生成了,上面的 ORA-28362 意指資料庫中還存在有使用老的 masterkey 加密的 encryption key ,但這個老的 masterkey 沒有包含在當前新建的 wallet 檔案裡

 

-rw-r--r--   1 oracle oinstall  2845 Jun 25 07:11 ewallet.p12

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

----------------------------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

OPEN

 

SQL> select * from test.tde;

select * from test.tde

                   *

ERROR at line 1:

ORA-28362: master key not found

 

(重新建錢包不可行,只能有之前的備份進行恢復)

 

場景九:錢夾的重建測試

結論:錢夾可以重建,但是重建後的錢夾不能查詢使用舊錢夾中的主金鑰加密的資料,即先前加密的資料丟失。

 

[oracle@ljw orcl]$ mv ewallet.p12 ewallet.p12.bak

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

CLOSED

 

SQL> alter system set encryption key identified by "hzmcdba123";

alter system set encryption key identified by "hzmcdba123"

*

ERROR at line 1: (其實就是一條告警資訊)

ORA-28362: master key not found

 

(新的 wallet 檔案會生成。提示資訊,上面的 ORA-28362 意指資料庫中還存在有使用老的 masterkey 加密的 encryption key ,但這個老的 masterkey 沒有包含在當前新建的 wallet 檔案裡,這意味著用老的 masterkey 加密的 encryption key 無法被解密,之前加密的資料不可訪問,即資料丟失。)

 

(新的錢夾生成)

SQL> conn test/test;

Connected.

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

OPEN

 

SQL> select * from tde;   (之前加密的表tde

select * from tde

              *

ERROR at line 1:

ORA-28362: master key not found

(之前加密的資料丟失)

 

SQL> create table new_tde (id number(10),data varchar2(50) encrypt);

 

Table created.

 

SQL> insert into new_tde select user_id,username from dba_users;

 

10 rows created.

 

SQL> select * from new_tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

SQL> alter system set encryption wallet close identified by "hzmcdba123";

 

System altered.

 

SQL> select * from tde;

select * from tde

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

 

SQL> select * from new_tde;

select * from new_tde

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

結論:建立一個表(加密列),預設 salt ,不能再該加密列上建立索引。如果需要建立索引,必須指定為 no salt

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

OPEN

 

SQL> create table salt (id number,name varchar(10) encrypt);

 

Table created.

 

SQL> insert into salt values(1,'dba');

 

1 row created.

 

SQL> create index salt_name_index on salt(name);

create index salt_name_index on salt(name)

                                     *

ERROR at line 1:

ORA-28338: Column(s) cannot be both indexed and encrypted with salt

 

SQL> alter table salt modify(name encrypt no salt);

 

Table altered.

 

SQL> create index salt_name_index on salt(name);

 

Index created.

 

SQL> alter table salt modify(name encrypt salt);

alter table salt modify(name encrypt salt)

                        *

ERROR at line 1:

ORA-28338: Column(s) cannot be both indexed and encrypted with salt

 

SQL> drop index salt_name_index;

 

Index dropped.

 

SQL> alter table salt modify(name encrypt salt);

 

Table altered.

 

結論:主外來鍵列不能夠被加密

 

SQL> conn test/test;

Connected.

 

SQL> create table primarykey(id number,name varchar(10),constraint pkey primary key(name));

 

Table created.

 

SQL> insert into primarykey values(1,'hzmcdba');

 

1 row created.

 

SQL> create table foreignkey(name varchar(10),score number,constraint fkey foreign key(name) references primarykey(name));

 

Table created.

 

SQL> insert into foreignkey values('hzmcdba','98');

 

1 row created.

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

CLOSED

 

SQL> alter system set encryption wallet open identified by "hzmcdba";

 

System altered.

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

OPEN

 

為主鍵加密:

 

SQL> alter table primarykey modify(name encrypt);

alter table primarykey modify(name encrypt)

                              *

ERROR at line 1:

ORA-28335: referenced or referencing FK constraint column cannot be encrypted

 

為外來鍵加密:

SQL> alter table foreignkey modify(name encrypt);

alter table foreignkey modify(name encrypt)

                              *

ERROR at line 1:

ORA-28335: referenced or referencing FK constraint column cannot be encrypted

 

結論: blob 欄位不能被加密

 

SQL> alter table table_blob modify(ph encrypt);

alter table table_blob modify(ph encrypt)

*

ERROR at line 1:

ORA-43856: Unsupported LOB type for SECUREFILE LOB operation

 

結論:不能將加密列作為分割槽鍵,這樣會導致,雖然表能成功建立,但是建立出來的表不是分割槽表的情況。但是不作為分割槽鍵的列,可以成為加密列。

 

建立加密表空間:

SQL> CREATE TABLESPACE encryptedtbs02

  2  DATAFILE '/oracle/app/oradata/orcl/encryptedtbs02.dbf' SIZE 100M

  3  ENCRYPTION USING 'AES256'

  4  DEFAULT STORAGE(ENCRYPT);

 

Tablespace created.

 

SQL> CREATE TABLESPACE encryptedtbs03

  2  DATAFILE '/oracle/app/oradata/orcl/encryptedtbs03.dbf' SIZE 100M

  3  ENCRYPTION USING 'AES256'

  4  DEFAULT STORAGE(ENCRYPT);

 

建立加密表:

 

CREATE TABLE test (

first_name VARCHAR2(128),

empID NUMBER ENCRYPT 'NOMAC' NO SALT ,

salary NUMBER(6)

)

partition by hash(empID)

(

partition part_01 tablespace encryptedtbs02,

partition part_02 tablespace encryptedtbs03

);

Table created.

 

雖然表是建立成功,但是有一個錯誤提示:

ERROR at line 1:

ORA-28346: an encrypted column cannot serve as a partitioning column

ora - 28346: 一個加密列不能作為分割槽列

 

那麼我們去查詢表的加密資訊及分割槽資訊:

SQL> select table_name,column_name from DBA_ENCRYPTED_COLUMNS;

 

TABLE_NAME                     COLUMN_NAME

------------------------------ ------------------------------

EMPLOYEE                       ID

TEST                           EMPID

 

SQL> select * from USER_TAB_PARTITIONS;

no rows selected

 

可以看到該表雖然建立成功,但是並沒有分割槽成功 ,作為對比,我們建立另外一個分割槽列不是加密列的表:

CREATE TABLE test01 (

first_name VARCHAR2(128),

empID NUMBER ENCRYPT 'NOMAC' NO SALT ,

salary NUMBER(6)

)

partition by hash(first_name)

(

partition part_01 tablespace encryptedtbs02,

partition part_02 tablespace encryptedtbs03

);

Table created.

 

同樣查詢資訊:

SQL> select table_name,column_name from DBA_ENCRYPTED_COLUMNS;

 

TABLE_NAME                     COLUMN_NAME

------------------------------ ------------------------------

EMPLOYEE                       ID

TEST                           EMPID

TEST01                         EMPID

 

SQL> select table_name,partition_name,tablespace_name from USER_TAB_PARTITIONS where table_name='TEST01';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

TEST01                         PART_01                        ENCRYPTEDTBS02

TEST01                         PART_02                        ENCRYPTEDTBS03

 

結論:如果一臺伺服器上有多個資料庫,建立一個錢夾即可使用於多個資料庫。(錢夾的配置只需在 sqlnet.ora 中指定錢夾存放位置,然後 alter system set encryption wallet open identified by "hzmcdba" 建立錢夾,此時只有記住這個密碼,這個錢夾 copy 到哪個資料庫中都可以使用)但是不建議這樣做, oracle 建議還是一個錢夾對一個庫,具體測試看情節二。

 

情景一:

 

[oracle@ljw ~]$ export ORACLE_SID=test;

[oracle@ljw ~]$ echo $ORACLE_SID

test

[oracle@ljw ~]$ sqlplus / as sysdba;

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 30 07:17:39 2017

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             465571384 bytes

Database Buffers          314572800 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

SQL> show parameter db_name;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      test

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

CLOSED

 

SQL> show user;

USER is "SYS"

SQL> alter system set encryption wallet open identified by "hzmcdba";

 

System altered.

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/product/11.2.0/db_1

OPEN

 

 

SQL> create user test identified by "test";

 

User created.

 

SQL> grant dba to test;

 

Grant succeeded.

 

SQL> conn test/test;

Connected.

SQL> create table jiami(id number,name varchar(10) encrypt);

 

Table created.

 

SQL> insert into jiami values(1,'hzmcdba');

 

1 row created.

 

SQL> select * from jiami;

 

        ID NAME

---------- ----------

         1 hzmcdba

 

SQL> alter system set encryption wallet close identified by "hzmcdba";

 

System altered.

 

SQL> select * from jiami;

select * from jiami

              *

ERROR at line 1:

ORA-28365: wallet is not open

 

情景二:

 

 

[oracle@ljw admin]$ vi sqlnet.ora

 

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/app/wallet/$ORACLE_SID)))

 

 

test 庫:

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ljw ~]$ sqlplus / as sysdba;

 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 30 08:29:37 2017

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

CLOSED

 

SQL> alter system set encryption key identified by "hzmcdba123";

alter system set encryption key identified by "hzmcdba123"

*

ERROR at line 1:

ORA-28362: master key not found 因為之前已經配置,錢包照樣生成

 

 

SQL> alter system set encryption wallet close identified by "hzmcdba123";

 

System altered.

 

SQL> alter system set encryption wallet open identified by "hzmcdba123";

 

System altered.

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

OPEN

 

場景十五:資料庫檔案和自動開啟錢夾檔案被拷到另一臺資料庫上開啟測試

結論:相關資料檔案被複製到其他資料庫進行開啟,如果自動開啟錢夾也被拷過去,能查詢到被加密的資料。

 

[oracle@ljw orcl]$ orapki wallet create -wallet /oracle/app/wallet/orcl/ -auto_login_local

Oracle PKI Tool : Version 11.2.0.4.0 - Production

Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

 

Enter wallet password:          

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             448794168 bytes

Database Buffers          331350016 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

 

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/$ORACLE_SID

OPEN

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

[oracle@ljw orcl]$ ls -rtl

total 8

-rw-r--r-- 1 oracle oinstall 2845 Jun 24 05:40 ewallet.p12

-rw------- 1 oracle oinstall 2923 Jul  4 01:15 cwallet.sso

 

[oracle@ljw orcl]$ scp * oracle@192.168.142.11:/oracle/app/orcl/

oracle@192.168.142.11's password:

Permission denied, please try again.

oracle@192.168.142.11's password:

books01.dbf                                    100% 5128KB   5.0MB/s   00:00   

control01.ctl                                  100% 9840KB   9.6MB/s   00:00   

control02.ctl                                  100% 9840KB   9.6MB/s   00:00   

redo01.log                                     100%   50MB  50.0MB/s   00:01   

redo02.log                                     100%   50MB  50.0MB/s   00:01   

redo03.log                                     100%   50MB  50.0MB/s   00:00   

sysaux01.dbf                                   100%  600MB  26.1MB/s   00:23   

system01.dbf                                   100%  700MB  31.8MB/s   00:22   

temp01.dbf                                     100%   20MB  20.0MB/s   00:00   

test.dbf                                       100% 5128KB   5.0MB/s   00:00   

test_tde                                       100% 5128KB   5.0MB/s   00:01   

undotbs01.dbf                                  100% 1710MB  30.0MB/s   00:57   

users01.dbf                                    100% 5128KB   5.0MB/s   00:00 

 

[oracle@ljw dbs]$ scp * oracle@192.168.142.11:/oracle/app/product/11.2.0/db_1/dbs/

oracle@192.168.142.11's password:

25s4jm22_1_1                                   100%  307MB  43.9MB/s   00:07   

27s4k331_1_1                                   100%  279MB  23.3MB/s   00:12   

arch1_152_944363414.dbf                        100%   50MB  49.9MB/s   00:01   

c-1471212201-20170519-00                       100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170519-05                       100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170519-06                       100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-00                       100% 9664KB   9.4MB/s   00:01   

c-1471212201-20170617-01                       100% 9664KB   9.4MB/s   00:01   

c-1471212201-20170617-02                       100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-03                       100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-04                       100% 9664KB   9.4MB/s   00:00   

c-1471212201-20170617-05                       100% 9664KB   9.4MB/s   00:01   

c-1471212201-20170627-00                       100% 9728KB   9.5MB/s   00:00   

c-1471212201-20170627-01                       100% 9792KB   9.6MB/s   00:01   

c-1471212201-20170627-02                       100% 9792KB   9.6MB/s   00:00   

c-1471212201-20170627-03                       100% 9920KB   9.7MB/s   00:00   

c-1471212201-20170630-00                       100% 9920KB   4.8MB/s   00:02   

c-1471212201-20170630-01                       100% 9920KB   9.7MB/s   00:00   

hc_orcl.dat                                    100% 1544     1.5KB/s   00:00   

hc_test.dat                                    100% 1544     1.5KB/s   00:00   

init.ora                                       100% 2851     2.8KB/s   00:00   

initTEST.ora                                   100%  705     0.7KB/s   00:00   

lkORCL                                         100%   24     0.0KB/s   00:00   

lkTEST                                         100%   24     0.0KB/s   00:00   

orapworcl                                      100% 1536     1.5KB/s   00:00   

orapwtest                                      100% 1536     1.5KB/s   00:00   

snapcf_orcl.f                                  100% 9840KB   9.6MB/s   00:00   

spfileorcl.ora                                 100% 2560     2.5KB/s   00:00   

spfiletest.ora                                 100% 2560     2.5KB/s   00:00

 

 

[oracle@ljw orcl]$ scp cwallet.sso oracle@192.168.142.11:/oracle/app/wallet/orcl/

oracle@192.168.142.11's password:

cwallet.sso                                    100% 2923     2.9KB/s   00:00 

 

192.168.142.11

 

[oracle@ljw admin]$ vi sqlnet.ora

 

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/app/wallet/orcl)))

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  784998400 bytes

Fixed Size                  2257352 bytes

Variable Size             448794168 bytes

Database Buffers          331350016 bytes

Redo Buffers                2596864 bytes

Database mounted.

Database opened.

SQL> select * from v$encryption_wallet;

 

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/oracle/app/wallet/orcl

OPEN

 

 

SQL> select * from test.tde;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

         0 SYS

         5 SYSTEM

        31 APPQOSSYS

         9 OUTLN

        14 DIP

        30 DBSNMP

        32 WMSYS

        21 ORACLE_OCM

 

10 rows selected.

 

場景十六:對一張現有的表進行加密,是否影響其觸發器測試

結論:對一張已有表進行加密,不會對其觸發器產生影響

 

SQL> create table clean (id number(10),data varchar2(50));

 

Table created.

 

SQL> insert into clean select user_id,username from dba_users;

 

10 rows created.

 

SQL> create table del_clean (id number(10),data varchar2(50));

 

Table created.

 

SQL> create or replace trigger tr_del_clean

  2  before delete

  3  on clean

  4  for each row

  5  begin

  6  insert into del_clean(id,data) values(:old.id,:old.data);

  7  end;

  8  /

 

Trigger created.

 

SQL> select * from clean;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

        14 DIP

        21 ORACLE_OCM

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

         0 SYS

         5 SYSTEM

         9 OUTLN

 

10 rows selected.

 

SQL> delete clean where id=0;

 

1 row deleted.

 

SQL> select * from del_clean;

 

        ID DATA

---------- --------------------------------------------------

         0 SYS

 

SQL> select * from clean;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

        14 DIP

        21 ORACLE_OCM

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

         5 SYSTEM

         9 OUTLN

 

9 rows selected.

 

 

對錶clean 進行加密:

 

SQL> alter table clean modify(data encrypt);

 

Table altered.

 

SQL> delete clean where id=5;

 

1 row deleted.

 

SQL> select * from clean;

 

        ID DATA

---------- --------------------------------------------------

        34 JSS

        35 TEST

        14 DIP

        21 ORACLE_OCM

        31 APPQOSSYS

        30 DBSNMP

        32 WMSYS

         9 OUTLN

 

8 rows selected.

 

SQL> select * from del_clean;

 

        ID DATA

---------- --------------------------------------------------

         0 SYS

         5 SYSTEM

 

1.        現有表空間是否能夠直接加密???

 

驗證結果:現有表空間 不能直接加密,但可以建個加密表空間,然後將該表空間中表move 到加密表空間中。

 

2.        錢夾密碼忘記了怎麼辦,是否有恢復方法???

 

Oracle 沒有提供解決錢夾密碼丟失的方法。所以,錢夾密碼千萬不能忘記,這也是使用TDE 需要承擔的風險。

 

 


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23732248/viewspace-2777104/,如需轉載,請註明出處,否則將追究法律責任。

相關文章