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]$