1.创建一个加密钱包文件夹

[oracle@yft ~]$ mkdir -p $ORACLE_HOME/admin/$ORACLE_SID/wallet

2.开启透明数据机密功能

[oracle@yft ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/[oracle@yft admin]$ lssamples  shrept.lst  sqlnet.ora  tnsnames1305116AM1948.bak  tnsnames.ora[oracle@yft admin]$ view sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/grid/product/11.2.0/grid/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/product/11.2.0/db_1/admin/yft/wallet)))

3.设置加密秘钥

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

4.创建一个测试用户

SQL> create tablespace jack datafile '/u01/app/oracle/oradata/yft/jack01.dbf' size 500m;Tablespace created.SQL> create user jack identified by jack default tablespace jack;User created.

5.授予权限

SQL> grant dba to jack;Grant succeeded.

6.使用测试用户登录，并创建一个加密表

SQL> conn jack/jackConnected.SQL> create table jack (id int primary key,name varchar2(10) encrypt using 'AES192');Table created.

7.查看加密表是否创建成功

SQL> desc dba_encrypted_columns; Name                       Null?    Type ----------------------------------------- -------- ---------------------------- OWNER                       NOT NULL VARCHAR2(30) TABLE_NAME                   NOT NULL VARCHAR2(30) COLUMN_NAME                   NOT NULL VARCHAR2(30) ENCRYPTION_ALG                     VARCHAR2(29) SALT                            VARCHAR2(3) INTEGRITY_ALG                        VARCHAR2(12)SQL> set linesize 200;SQL> select * from dba_encrypted_columns;OWNER                   TABLE_NAME              COLUMN_NAME             ENCRYPTION_ALG           SAL INTEGRITY_AL------------------------------ ------------------------------ ------------------------------ ----------------------------- --- ------------JACK                   JACK                  NAME                 AES 192 bits key           YES SHA-1

8.插入测试数据并查看表中的数据

SQL> insert into jack values(1,'aa');1 row created.SQL> commit;Commit complete.SQL> select * from jack;    ID NAME---------- ----------     1 aa

9.关闭钱包功能并再次查看表中的数据

SQL> alter system set wallet close identified by "123456";System altered.SQL> select * from jack;select * from jack              *ERROR at line 1:ORA-28365: wallet is not open

10.再次打开钱包并查看钱包是否打开

SQL> alter system set wallet open identified by "123456";System altered.SQL> col wrl_parameter for a35;SQL> select * from v$encryption_wallet;WRL_TYPE         WRL_PARAMETER                                          STATUS------------    -----------------------------------             ------------------file             /u01/app/oracle/product/11.2.0/db_1/admin/yft/wallet   OPEN

11.logminer验证加密

----启用日志挖掘功能---- SQL> alter database add supplemental log data;Database altered.SQL> alter database add supplemental log data (primary key) columns;Database altered.----查看当前所用的日志文件---- SQL> set linesize 260;SQL> select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------     1        1           7   52428800       512        1 NO  CURRENT              804363 06-JUN-13     2.8147E+14     2        1           5   52428800       512        1 NO  INACTIVE              790992 05-JUN-13         798139 06-JUN-13     3        1           6   52428800       512        1 NO  INACTIVE              798139 06-JUN-13         804363 06-JUN-13----查看当前的scn号---- SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------          805566----插入一条数据---- SQL> insert into jack values(2,'bb');1 row created.SQL> commit;Commit complete.----查看结束的scn号---- SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------          805574----创建一个可用于分析的重做日志文件的清单---- SQL> exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/yft/redo01.log',options => dbms_logmnr.new);PL/SQL procedure successfully completed.----进行日志收集---- SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog,startscn=>805566,endscn=>805574);PL/SQL procedure successfully completed.----分析日志，可以看到的明文的SQL---- SQL> col sql_redo for a40;SQL> col sql_undo for a50;SQL> select operation,sql_redo,sql_undo from v$logmnr_contents;OPERATION             SQL_REDO                                 SQL_UNDO--------------------- ---------------------------------------  --------------------------------------------------START                 set transaction read write;INSERT                insert into "JACK"."JACK"("ID","NAME")    delete from "JACK"."JACK" where "ID" = '2' and "NA                      values ('2','bb');                        ME" = 'bb' and ROWID = 'AAAR68AAFAAAACHAAB';COMMIT                 commit;----关闭钱包---- SQL> alter system set wallet close identified by "123456";System altered.----再次分析日志---- SQL> select operation,sql_redo,sql_undo from v$logmnr_contents;OPERATION             SQL_REDO                                  SQL_UNDO--------------------- --------------------------------------    --------------------------------------------------START                 set transaction read write;INSERT                insert into "JACK"."JACK"("ID","NAME")    delete from "JACK"."JACK" where "ID" = '2' and "NA                      values ('2',HEXTORAW('3813e2c4328d91953d0 ME" = HEXTORAW('3813e2c4328d91953d08d9eef423b18304                      8d9eef423b1830437fd9f3eabb89cf3c080684e1  37fd9f3eabb89cf3c080684e16894489401dac7bc9826b49f8                      6894489401dac7bc9826b49f8a55fea0021f1c76  a55fea0021f1c7662e5f') and ROWID = 'AAAR68AAFAAAAC                      62e5f'));                                 HAAB';COMMIT                 commit;