Oracle Advanced Security TDE(Transparent Data Encryption透明資料加密)提供了業界先進的資料庫加密解決方案。
TDE自動對寫入到Oracle資料庫中的資料進行加密，既可以對像信用卡號和社會保險號這樣的個別應用程式表列進行加密，也可以加密整個表空間。
備份資料庫時，加密的檔案在目標介質上仍保持其加密狀態，能保護其上的資訊不會外洩。

11g的表空間加密依賴於oracle wallet以及wallet中的金鑰，要先建立一個“wallet錢包”，這個錢包裡面儲存著金鑰，Oracle就是透過這個金鑰對錶空間進行加密和解密。既可以手動開啟wallet (每次資料庫啟動以後，需要手動開啟wallet)，也可以自動開啟wallet (每次資料庫啟動以後會自動開啟)。

SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER                 STATUS
-------------------- --------------------------------------- ------------------
file             /u01/app/oracle/admin/dg01prmy/wallet   CLOSED

SQL> alter system set encryption key identified by "welcome1";
alter system set encryption key identified by "welcome1"
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet

$ ls /u01/app/oracle/admin/dg01prmy/wallet
ls: cannot access /u01/app/oracle/admin/dg01prmy/wallet: No such file or directory
$ mkdir /u01/app/oracle/admin/dg01prmy/wallet

SQL> alter system set encryption key identified by "welcome1";
System altered.

SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER                 STATUS
-------------------- --------------------------------------- ------------------
file             /u01/app/oracle/admin/dg01prmy/wallet   OPEN

$ ls /u01/app/oracle/admin/dg01prmy/wallet
ewallet.p12

SQL> alter system set encryption wallet close identified by "welcome1";
System altered.

SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER                 STATUS
-------------------- --------------------------------------- ------------------
file             /u01/app/oracle/admin/dg01prmy/wallet   CLOSED

SQL> alter system set encryption wallet open identified by "welcome1";
System altered.

SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER                 STATUS
-------------------- --------------------------------------- ------------------
file             /u01/app/oracle/admin/dg01prmy/wallet   OPEN
SQL>

預設情況下，每次資料庫被關閉，錢包也被關閉。加密表空間與wallet的關係如下
1.Oracle表空間的加密與解密完全是基於wallet錢包中的金鑰進行的。
2.如果wallet是open狀態，可以使用其中的金鑰，進行加密與解密。
3.如果wallet是close狀態，此時加密表空間是不可用的，例如查詢、修改都不允許
4.刪除表是不需要金鑰的，無論wallet是open或close狀態，可直接刪除。

建立加密表空間
使用ENCRYPTION 選項，透過USING 選項指定加密演算法，預設使用AES128演算法。
注意，storage 選項必須指定ENCRYPT。
SQL> CREATE TABLESPACE george_ts_encrypt DATAFILE '/u01/app/oracle/oradata/dg01prmy/ts_encrypt.dbf' SIZE 200M autoextend on maxsize unlimited ENCRYPTION DEFAULT STORAGE(ENCRYPT);
Tablespace created.

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;
TABLESPACE_NAME        ENC
------------------------------       ---
SYSTEM                               NO
SYSAUX                               NO
UNDOTBS1                          NO
TEMP                                    NO
USERS                                  NO
GEORGE_TBS                     NO
GEORGE_TS_ENCRYPT   YES

7 rows selected.

被加密的資料檔案，臨時表空間、undo表空間和redo日誌、記憶體中的資料都是被保護的。
在執行加密或解密操作前錢包要開啟；或者配置自動開啟。

SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------------------------------------- ------------------
file             /u01/app/oracle/admin/dg01prmy/wallet                          CLOSED

SQL> CREATE TABLE customer_payment
(first_name VARCHAR2(20),
last_name VARCHAR2(20),
amount NUMBER(10),
credit_card_number VARCHAR2(20)) TABLESPACE GEORGE_TS_ENCRYPT;
2    3    4    5
Table created.

SQL> insert into customer_payment values('George','Wang',2015,'123456790');
insert into customer_payment values('George','Wang',2015,'123456790')
            *
ERROR at line 1:
ORA-28365: wallet is not open

SQL> alter system set wallet open identified by "welcome1";
System altered.

SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------------------------------------- ------------------
file             /u01/app/oracle/admin/dg01prmy/wallet                          OPEN

SQL> insert into customer_payment values('George','Wang',2015,'123456790');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from customer_payment;

FIRST_NAME LAST_NAME AMOUNT CREDIT_CARD_NUMB
-----------            ----------             ------------   ----------------
George               Wang                 2015           123456790

SQL> alter system set wallet close identified by "welcome1";
System altered.

SQL> select * from customer_payment;
select * from customer_payment
              *
ERROR at line 1:
ORA-28365: wallet is not open

Oracle 11g 表空間加密

相關文章