普通网站可以做商城,wordpress自动留言,湖南seo公司,百度下载安装2022最新版本文回答2个问题#xff1a;
11g下简明的TDE设置过程由于11g不支持在线TDE#xff0c;介绍2中11g下的加密表空间的迁移方法
设置表空间TDE之前
表空间没有加密时#xff0c;很容易探测到明文数据#xff1a;
create tablespace unsectbs datafile unsectbs.dbf size 10…本文回答2个问题
11g下简明的TDE设置过程由于11g不支持在线TDE介绍2中11g下的加密表空间的迁移方法
设置表空间TDE之前
表空间没有加密时很容易探测到明文数据
create tablespace unsectbs datafile unsectbs.dbf size 10m autoextend on next 10m maxsize unlimited;
create table unsectbl tablespace unsectbs as select * from dba_users;
create unique index idx1 on unsectbl(user_id);SQL select TABLESPACE_NAME from user_tables where table_name UNSECTBL;TABLESPACE_NAME
------------------------------
UNSECTBSSQL !strings /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/unsectbs.dbf
}|{z
h7/dORCL
UNSECTBS
SPATIAL_WFS_ADMIN_USR
EXPIRED LOCKED
USERS
TEMP
DEFAULT
DEFAULT_CONSUMER_GROUP
10G 11G
PASSWORD,
SPATIAL_CSW_ADMIN_USR
EXPIRED LOCKED
USERS
TEMP
...设置表空间TDE
compatibility 需设为11.2或以上
SQL show parameter compatibleNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0通过tnsping可以得到sqlnet.ora的路径
$ tnsping orclTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-NOV-2023 12:39:17Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:
/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST instance-20231116-1239-db11g)(PORT 1521))) (CONNECT_DATA (SERVICE_NAME orcl)))
OK (0 msec)$ vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora在sqlnet.ora中追加以下语句指定wallet的位置。
ENCRYPTION_WALLET_LOCATION(SOURCE(METHODFILE)(METHOD_DATA(DIRECTORY/home/oracle/app/oracle/wallet)))执行以下命令创建加密的wallet以及master key
-- 目录必须存在否则报错ORA-28368
SQL !mkdir /home/oracle/app/oracle/walletSQL ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY Easy2rem;System altered.SQL !ls /home/oracle/app/oracle/wallet
ewallet.p12创建加密表空间
alter system set db_create_file_dest/home/oracle/app/oracle/oradata/orcl;CREATE TABLESPACE sectbs
DATAFILE secure01.dbf
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);确认已加密
SQL select TABLESPACE_NAME, ENCRYPTED from user_tablespaces;TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
SECTBS YES6 rows selected.SQL create table sectbl tablespace sectbs as select * from dba_users;Table created.
设置表空间TDE之后
SQL !strings /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/secure01.dbf
}|{z
h7/dORCL
SECTBS
Zdp!
2VN?
9*.2vq
[f9k
zG
23WV-
Y6w
/2.-
m:Wp.
Z-]
D8
$gU%
? 5T
...加密已有的表空间
11g不支持加密已有的表空间只支持新建。
但我们可以将未加密表空间中的表迁移到加密的表空间中
SQL alter table unsectbl move tablespace sectbs;Table altered.SQL select TABLESPACE_NAME from user_tables where table_name UNSECTBL;TABLESPACE_NAME
------------------------------
SECTBS注意表的索引会变为无效因此需要rebuild。详见这里。
SQL select status from user_indexes where index_name IDX1;STATUS
--------
UNUSABLESQL alter index idx1 rebuild;Index altered.SQL select status from user_indexes where index_name IDX1;STATUS
--------
VALID另一种迁移方式是通过数据泵导出再导入。
表的导出
$ expdp test/Welcome1orcl tablesunsectblExport: Release 11.2.0.4.0 - Production on Mon Nov 20 05:30:50 2023Copyright (c) 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
Starting TEST.SYS_EXPORT_TABLE_01: test/********orcl tablesunsectbl
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported TEST.UNSECTBL 14.32 KB 31 rows
Master table TEST.SYS_EXPORT_TABLE_01 successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:/home/oracle/app/oracle/admin/orcl/dpdump/expdat.dmp
Job TEST.SYS_EXPORT_TABLE_01 successfully completed at Mon Nov 20 05:30:54 2023 elapsed 0 00:00:04表的导入原表已先行删除
$ impdp test/Welcome1orcl remap_tableunsectbl:sectbl remap_tablespaceunsectbs:sectbs dumpfileexpdat.dmpImport: Release 11.2.0.4.0 - Production on Mon Nov 20 05:54:24 2023Copyright (c) 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
Master table TEST.SYS_IMPORT_FULL_01 successfully loaded/unloaded
Starting TEST.SYS_IMPORT_FULL_01: test/********orcl remap_tableunsectbl:sectbl remap_tablespaceunsectbs:sectbs dumpfileexpdat.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported TEST.SECTBL 14.32 KB 31 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job TEST.SYS_IMPORT_FULL_01 successfully completed at Mon Nov 20 05:54:24 2023 elapsed 0 00:00:00
这个实验说明了2点问题
expdp 表的导出也会导出索引索引的名字
参考
Oracle 11G Tutorial | Transparent Data Encryption | InfiniteSkills TrainingBack to basics with Transparent Data Encryption (TDE)Objective: Create an encrypted Oracle 11g tablespace with Transparent Data Encryption (TDE)Tablespace Encryption in Oracle 11g Database Release 1Transparent Data Encryption in Oracle 11g8.2.5 Encrypting Entire Tablespaces8.4 Example: Getting Started with TDE Column Encryption and TDE Tablespace EncryptionTDE Tablespace Encryption8.3.6 Using Transparent Data Encryption in a Multi-Database Environment
