oracle 透明加密

一、sqlnet.ora檔案新增wallet路徑
beijing:/u01/app/oracle/admin/prod/wallet$ more /u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora 
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.


NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/prod/wallet )
))
beijing:/u01/app/oracle/admin/prod/wallet$ 
二、開啟透明加密功能
beijing:/u01/app/oracle/product/11.1.0/db_1/network/admin$ sqlplus / as sysdba


SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 25 15:13:09 2016


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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 
SQL> alter system set encryption key authenticated by "abcde";

System altered.


SQL> 
SQL> 
三、在wallet裡產生一個密碼檔案
beijing:/u01/app/oracle/admin/prod/wallet$ 
beijing:/u01/app/oracle/admin/prod/wallet$ ll
total 4
-rw-r--r-- 1 oracle oinstall 1693 Apr 25 15:13 ewallet.p12
beijing:/u01/app/oracle/admin/prod/wallet$ 
beijing:/u01/app/oracle/admin/prod/wallet$ 
SQL> r
  1* select * from dba_encrypted_columns


OWNER      TABLE_NAME COLUMN_NAM ENCRYPTION_ALG                SAL
---------- ---------- ---------- ----------------------------- ---
SCOTT      T          INFO       AES 192 bits key              YES

SQL> 
SQL> 
SQL> insert into t values(1,'abcde');


1 row created.


SQL> commit;


Commit complete.


SQL> 


四、關閉wallet，加密列無法查詢

SQL> alter system set wallet close identified by "abcde";
alter system set wallet close identified by "abcde"
                              *
ERROR at line 1:
ORA-28364: invalid wallet operation
SQL> alter system set wallet close;


System altered.


SQL> 




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






SQL> select id from t;


        ID
----------
         1


SQL> 
SQL> 
SQL> 
SQL> 


SQL> alter system set wallet open identified by "abcde";


System altered.


SQL> 
SQL> 
SQL> col info for a20
SQL> r
  1* select * from t


        ID INFO
---------- --------------------
         1 abcde


SQL>