TDE列加密

SQL>  ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "ljc123"; 

System altered.


SQL> alter system set  wallet close authenticated by "ljc123";

System altered.
对数据库中表的某一列或者几列进行加密。
oracle 11g wallet默认保存目录：select * from v$encryption_wallet;
SQL> col WRL_PARAMETER for a25
SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER             STATUS
-------------------- ------------------------- ------------------
file                 /u01/admin/juice/wallet   CLOSED

在对应的路径下创建wallet文件夹：mkdir wallet
[oracle@water admin]$ mkdir wallet


在wallet中创建key:
SQL> alter system set wallet open authenticated by "ljc123";

System altered.

SQL>  create user ljc identified by oracle default tablespace users;

User created.

SQL> grant APP_SELECTOR to ljc;

Grant succeeded.

SQL> create table ljc.tde1 as select * from dba_objects;

Table created.

SQL> create table ljc.tde2 as select * from dba_objects;

Table created.

SQL> @ind
Display indexes where table or index name matches %%LJC.tde2%%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
LJC                  TDE2                           IDX_TDE2_NAME                     1 OBJECT_NAME


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED       DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
LJC                  TDE2                           IDX_TDE2_NAME                  NORMAL     NO   VALID    NO   N     3        432         52010      86275      43773 2021-09-12 15:13:53 1      VISIBLE
SQL> 

SQL> alter table LJC.tde1 modify OBJECT_NAME  encrypt using 'AES128' ;

Table altered.


SQL>  ALTER TABLE LJC.TDE2 MODIFY(OBJECT_NAME ENCRYPT using 'AES128' no salt);

SQL> desc
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      OWNER                                    VARCHAR2(30)
    2      OBJECT_NAME                              VARCHAR2(128) ENCRYPT
    3      SUBOBJECT_NAME                           VARCHAR2(30)
    4      OBJECT_ID                                NUMBER
    5      DATA_OBJECT_ID                           NUMBER
    6      OBJECT_TYPE                              VARCHAR2(19)
    7      CREATED                                  DATE
    8      LAST_DDL_TIME                            DATE
    9      TIMESTAMP                                VARCHAR2(19)
   10      STATUS                                   VARCHAR2(7)
   11      TEMPORARY                                VARCHAR2(1)
   12      GENERATED                                VARCHAR2(1)
   13      SECONDARY                                VARCHAR2(1)
   14      NAMESPACE                                NUMBER
   15      EDITION_NAME                             VARCHAR2(30)

SQL> 


看了下表的ddl 

  CREATE TABLE "LJC"."TDE2"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128) ENCRYPT USING 'AES128' 'SHA-1' NO SALT ,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30)
   ) 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" ;

++++++ exp/imp & expdp/impdp ++++++

[oracle@water ~]$ exp dbmt/dbmt file=exp.dmp tables=LJC.TDE2 log=exp.log

Export: Release 11.2.0.4.0 - Production on 星期日 9月 12 16:02:43 2021

Copyright © 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 AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
Current user changed to LJC
EXP-00107: Feature (COLUMN ENCRYPTION) of column OBJECT_NAME in table LJC.TDE2 is not supported. The table will not be exported.
Export terminated successfully with warnings.
[oracle@water ~]$

[oracle@water ~]$ expdp dbmt/dbmt directory=DATA_PUMP_DIR tables=LJC.TDE2 dumpfile=exp.dmp logfile=exp.log

Export: Release 11.2.0.4.0 - Production on 星期日 9月 12 16:08:26 2021

Copyright © 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
启动 “DBMT”.“SYS_EXPORT_TABLE_01”: dbmt/******** directory=DATA_PUMP_DIR tables=LJC.TDE2 dumpfile=exp.dmp logfile=exp.log
正在使用 BLOCKS 方法进行估计…
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 12 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-31693: 表数据对象 “LJC”.“TDE2” 无法加载/卸载并且被跳过, 错误如下:
ORA-29913: 执行 ODCIEXTTABLEPOPULATE 调出时出错
ORA-28365: Wallet 未打开
ORA-39173: 已将加密的数据以未加密方式存储在转储文件集中。
已成功加载/卸载了主表 “DBMT”.“SYS_EXPORT_TABLE_01”

---

DBMT.SYS_EXPORT_TABLE_01 的转储文件集为:
/u01/exp.dmp
作业 “DBMT”.“SYS_EXPORT_TABLE_01” 已经完成, 但是有 2 个错误 (于 星期日 9月 12 16:08:35 2021 elapsed 0 00:00:05 完成)

[oracle@water ~]$

SQL> alter system set wallet open authenticated by “ljc123”;

System altered.

[oracle@water u01]$ expdp dbmt/dbmt directory=DATA_PUMP_DIR tables=LJC.TDE2 dumpfile=exp.dmp logfile=exp.log

Export: Release 11.2.0.4.0 - Production on 星期日 9月 12 16:11:50 2021

Copyright © 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
启动 “DBMT”.“SYS_EXPORT_TABLE_01”: dbmt/******** directory=DATA_PUMP_DIR tables=LJC.TDE2 dumpfile=exp.dmp logfile=exp.log
正在使用 BLOCKS 方法进行估计…
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 12 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . 导出了 “LJC”.“TDE2” 8.366 MB 86275 行
ORA-39173: 已将加密的数据以未加密方式存储在转储文件集中。
已成功加载/卸载了主表 “DBMT”.“SYS_EXPORT_TABLE_01”

---

DBMT.SYS_EXPORT_TABLE_01 的转储文件集为:
/u01/exp.dmp
作业 “DBMT”.“SYS_EXPORT_TABLE_01” 已经完成, 但是有 1 个错误 (于 星期日 9月 12 16:11:56 2021 elapsed 0 00:00:05 完成)

[oracle@water u01]$