ORACLE透明加密場景模擬
這以加密表空間為例：
使用者連入資料庫進行資料更新或者查詢時，所涉及到的物件會自動加密或自動解密；
但加密表空間裡的所有資料都是以加密的格式被儲存在磁碟上，磁碟或備份介質被盜時，裡面的資料也不會被盜取，因為裡面是亂碼的。舉例如下：
表 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-2887191/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 透明加密Oracle加密
- Oracle透明資料加密Oracle加密
- 5601. 設計有序流 （場景模擬）
- jvm 之 記憶體溢位場景模擬JVM記憶體溢位
- MySQL單表模擬鎖的幾個場景MySql
- 模擬RI鎖定導致阻塞的場景
- ORA-00060: Deadlock detected（場景模擬)
- ORACLE透明加密安裝操作Oracle加密
- Oracle TDE（透明資料加密）Oracle加密
- Oracle Transparent Data Encryption 透明加密（一）Oracle加密
- Oracle Transparent Data Encryption 透明加密（二）Oracle加密
- 使用IDEA模擬git命令使用的常見場景IdeaGit
- JVM 深入筆記（2）記憶體溢位場景模擬JVM筆記記憶體溢位
- JVM 深入筆記（2）記憶體區溢位場景模擬JVM筆記記憶體
- 【Control File】Oracle個別控制檔案丟失故障場景模擬及處理方法Oracle
- 透明資料加密加密
- 透明閘道器一些特殊場景的配置
- Jmeter(五十)_效能測試模擬真實場景下的使用者操作JMeter
- 2.9.2 透明資料加密加密
- 【進階3-4期】深度解析bind原理、使用場景及模擬實現
- Apollo模擬平臺如何Hold住99.9999%的複雜場景？
- 必讀：常見場景的加密選擇加密
- [zt]Oracle 10G：透明資料加密技術(TDE)Oracle 10g加密
- 歐洲加密貨幣經紀商尋求透明市場的監管加密
- Oracle磁帶備份模擬Oracle
- Oracle模擬併發指令碼Oracle指令碼
- 【DEADLOCK】Oracle“死鎖”模擬Oracle
- Oracle 10G 新特性--透明資料加密技術(TDE)Oracle 10g加密
- Oracle LOCK內部機制及最佳實踐系列(二)模擬RI鎖定導致阻塞的場景，並分析v$lockOracle
- HDU 5237-Base64（模擬-K輪加密）加密
- 達夢加密之外部加密函式的半透明列加密加密函式
- Hadoop KMS配置(HDFS透明加密)Hadoop加密
- TDE 透明表空間加密 Wallet加密
- 關於oracle死鎖的模擬Oracle
- 個人收藏oracle模擬題part 1Oracle
- 資料加密 第六篇：透明檔案加密加密
- [重慶思莊每日技術分享]-oracle 12c透明加密Oracle加密
- 阿里雲方案：遊戲App深度相容性壓測，模擬真實使用者線上場景阿里遊戲APP