演示一个TDE的数据加密示例，并用logminer验证加密效果

àì夳堔傛蜴生んèń 2022-03-27 10:12 61阅读 0赞

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]$ ls
    samples  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/jack
    Connected.
    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;