Oracle TDE的学习

最新推荐文章于 2024-09-05 07:30:44 发布
最新推荐文章于 2024-09-05 07:30:44 发布
阅读量448 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/junnor/p/5163426.html
版权

  1. TDE的开启和关闭

设置wallet目录，在参数文件sqlnet.ora中，按照下面的格式加入信息

# Oracle Advanced Security Transparent Data Encryption

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/product/11.2.4/db_1/network/admin/encryption_wallet)))

   

创建该目录

su - oracle

mkdir -p /u01/app/oracle/product/11.2.4/db_1/network/admin/encryption_wallet

   

创建master key文件，指定wallet密码，使用SYS用户登入系统，建立加密文件

ORA-28388: database is not open in read/write mode

创建万能密钥。

SQL>ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "junshi66";

[oracle@primary rpdm]$ cd /u01/app/oracle/product/11.2.4/db_1/network/admin/encryption_wallet/

[oracle@primary encryption_wallet]$ ll

总用量 4

-rw-r--r-- 1 oracle asmadmin 2845 1月 15 14:48 ewallet.p12

   

启动、关闭Wallet

打开钱包（第一次设置万能密钥会自动打开钱包）

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "junshi66";

ORA-28354: wallet 已经打开

   

SQL> alter system set encryption wallet close identified by "junshi66"; --关闭

System altered

   

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "junshi66"; --打开

System altered

到此，已经成功配置了Wallet，创建了master key。

 

注意：

如果是dg环境，需要把主库上的 这几个文件、目录都要同步过去。

   

   

  1. 创建环境使用部分

    1. 加密表空间

创建加密表空间 S

CREATE TABLESPACE S DATAFILE '+DATA' SIZE 200M autoextend on maxsize unlimited ENCRYPTION DEFAULT STORAGE(ENCRYPT);

创建用户 suser

create user suser identified by oracle default tablespace S;

grant connect,resource to suser;

创建表 st1

conn suser/oracle

create table st1 (id number CONSTRAINT id_nn NOT NULL,

name VARCHAR2(40),

PRIMARY KEY (id)

);

插入数据

insert into st1 values (1,'aaa');

insert into st1 values (2,'bbb');

insert into st1 values (3,'ccc');

insert into st1 values (4,'ddd');

创建普通表空间 P

CREATE TABLESPACE P DATAFILE '+DATA' SIZE 200M autoextend on maxsize unlimited;

创建用户 puser

create user puser identified by oracle default tablespace P;

grant connect,resource to puser;

创建表 pt1

conn puser/oracle

create table pt1 (id number CONSTRAINT id_nn NOT NULL,

name VARCHAR2(40),

PRIMARY KEY (id)

);

插入数据

insert into pt1 values (1,'aaa');

insert into pt1 values (2,'bbb');

insert into pt1 values (3,'ccc');

insert into pt1 values (4,'ddd');

   

   

  1. 加密列

suser 下创建表 stable1 、 stable2

conn suser/oracle

create table stable1 (id number ENCRYPT NOT NULL ,

name VARCHAR2(40) ENCRYPT

);

insert into stable1 values (1,'aaa');

insert into stable1 values (2,'bbb');

insert into stable1 values (3,'ccc');

insert into stable1 values (4,'ddd');

   

   

create table stable2 (id number NOT NULL,

name VARCHAR2(40) ENCRYPT

);

insert into stable2 values (1,'aaa');

insert into stable2 values (2,'bbb');

insert into stable2 values (3,'ccc');

insert into stable2 values (4,'ddd');

   

psuer下创建 ptable1 、 ptable2

conn puser/oracle

create table ptable1 (id number ENCRYPT NOT NULL ,

name VARCHAR2(40) ENCRYPT

);

insert into ptable1 values (1,'aaa');

insert into ptable1 values (2,'bbb');

insert into ptable1 values (3,'ccc');

insert into ptable1 values (4,'ddd');

   

create table ptable2 (id number NOT NULL,

name VARCHAR2(40) ENCRYPT

);

insert into ptable2 values (1,'aaa');

insert into ptable2 values (2,'bbb');

insert into ptable2 values (3,'ccc');

insert into ptable2 values (4,'ddd');

   

   

  1. 目标数据库

CREATE TABLESPACE S DATAFILE '/u01/app/oracle/oradata/demo/S01.dbf' SIZE 200M autoextend on maxsize unlimited;

CREATE TABLESPACE P DATAFILE '/u01/app/oracle/oradata/demo/P01.dbf' SIZE 200M autoextend on maxsize unlimited;

create user suser identified by oracle default tablespace S;

grant connect,resource to suser;

create user puser identified by oracle default tablespace P;

grant connect,resource to puser;

   

  1. 创建dblink 

CREATE PUBLIC DATABASE LINK "linksource"

CONNECT TO system

IDENTIFIED BY "oracle"

USING 'source';

   

  1. 管理部分

    1. schema的迁移（exp\imp,expdp\impdp）

    expdp:

    export LANG=AMERICAN_AMERICA.ZHS16GBK

    expdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=expdp2user.log schemas=suser,puser

       

[oracle@primary ~]$ export LANG=AMERICAN_AMERICA.ZHS16GBK

[oracle@primary ~]$ expdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=expdp2user.log schemas=suser,puser

Export: Release 11.2.0.4.0 - Production on Tue Jan 26 17:21:33 2016

Copyright (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, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=EXPDP dumpfile=expdp2users.dmp logfile=expdp2user.log schemas=suser,puser

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 384 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

. . exported "PUSER"."PT1" 5.437 KB 4 rows

. . exported "PUSER"."PTABLE1" 5.445 KB 4 rows

. . exported "PUSER"."PTABLE2" 5.445 KB 4 rows

. . exported "SUSER"."ST1" 5.437 KB 4 rows

. . exported "SUSER"."STABLE1" 5.445 KB 4 rows

. . exported "SUSER"."STABLE2" 5.445 KB 4 rows

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/backup/expdp/expdp2users.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Tue Jan 26 17:22:06 2016 elapsed 0 00:00:27

   

   

exp:

export LANG=AMERICAN_AMERICA.ZHS16GBK

exp system/oracle file=/backup/expdp/exp2user.dmp log=/backup/expdp/exp2user.log owner=suser,puser

   

[oracle@primary ~]$ exp system/oracle file=/backup/expdp/exp2user.dmp log=/backup/expdp/exp2user.log owner=suser,puser

Export: Release 11.2.0.4.0 - Production on Tue Jan 26 17:22:31 2016

Copyright (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, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SUSER

. exporting foreign function library names for user PUSER

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SUSER

. exporting object type definitions for user PUSER

About to export SUSER's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SUSER's tables via Conventional Path ...

EXP-00111: Table ST1 resides in an Encrypted Tablespace S and will not be exported

EXP-00111: Table STABLE1 resides in an Encrypted Tablespace S and will not be exported

EXP-00111: Table STABLE2 resides in an Encrypted Tablespace S and will not be exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

About to export PUSER's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export PUSER's tables via Conventional Path ...

. . exporting table PT1 4 rows exported

EXP-00107: Feature (COLUMN ENCRYPTION) of column ID in table PUSER.PTABLE1 is not supported. The table will not be exported.

EXP-00107: Feature (COLUMN ENCRYPTION) of column NAME in table PUSER.PTABLE2 is not supported. The table will not be exported.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

   

可以看到，exp无法导出有加密列的表。

   

on 192.168.80.200

impdp

export LANG=AMERICAN_AMERICA.ZHS16GBK

impdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log schemas=suser,puser

没有迁移对应的密钥时

[oracle@single1102 ~]$ export LANG=AMERICAN_AMERICA.ZHS16GBK

[oracle@single1102 ~]$ impdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log schemas=suser,puser

   

Import: Release 11.2.0.4.0 - Production on Wed Jan 27 17:56:18 2016

   

Copyright (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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log schemas=suser,puser

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SUSER" already exists

ORA-31684: Object type USER:"PUSER" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE:"SUSER"."STABLE1" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "SUSER"."STABLE1" ("ID" NUMBER ENCRYPT USING 'AES192' 'SHA-1' NOT NULL ENABLE, "NAME" VARCHAR2(40 BYTE) ENCRYPT USING 'AES192' 'SHA-1') 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 DEF

ORA-39083: Object type TABLE:"SUSER"."STABLE2" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "SUSER"."STABLE2" ("ID" NUMBER NOT NULL ENABLE, "NAME" VARCHAR2(40 BYTE) ENCRYPT USING 'AES192' 'SHA-1') 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)

ORA-39083: Object type TABLE:"PUSER"."PTABLE1" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "PUSER"."PTABLE1" ("ID" NUMBER ENCRYPT USING 'AES192' 'SHA-1' NOT NULL ENABLE, "NAME" VARCHAR2(40 BYTE) ENCRYPT USING 'AES192' 'SHA-1') 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 DEF

ORA-39083: Object type TABLE:"PUSER"."PTABLE2" failed to create with error:

ORA-28365: wallet is not open

Failing sql is:

CREATE TABLE "PUSER"."PTABLE2" ("ID" NUMBER NOT NULL ENABLE, "NAME" VARCHAR2(40 BYTE) ENCRYPT USING 'AES192' 'SHA-1') 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)

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "PUSER"."PT1" 5.437 KB 4 rows

. . imported "SUSER"."ST1" 5.437 KB 4 rows

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 6 error(s) at Wed Jan 27 17:56:27 2016 elapsed 0 00:00:06

显示 无法创建"SUSER"."STABLE1"、"SUSER"."STABLE2"、"PUSER"."PTABLE1"、"PUSER"."PTABLE2"。原因是 ORA-28365: wallet is not open

   

   

如果将源库上的 wallet和sqlnet.ora同步过来之后，重启目标库后再次导入；

[oracle@single1102 ~]$ impdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log schemas=suser,puser

   

Import: Release 11.2.0.4.0 - Production on Wed Jan 27 18:00:59 2016

   

Copyright (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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log schemas=suser,puser

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SUSER" already exists

ORA-31684: Object type USER:"PUSER" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "PUSER"."PT1" 5.437 KB 4 rows

. . imported "PUSER"."PTABLE1" 5.445 KB 4 rows

. . imported "PUSER"."PTABLE2" 5.445 KB 4 rows

. . imported "SUSER"."ST1" 5.437 KB 4 rows

. . imported "SUSER"."STABLE1" 5.445 KB 4 rows

. . imported "SUSER"."STABLE2" 5.445 KB 4 rows

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Wed Jan 27 18:01:11 2016 elapsed 0 00:00:10

可以看到，如果目标库上有了对应的密钥后，数据就可以导入。

另外，重启目标库后

SQL> select * from v$encryption_wallet;

   

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------

file

/u01/app/oracle/product/11.2.4/db_1/network/admin/encryption_wallet

OPEN

   

发现 wallet是自动open的，说明这个属性是保存在/u01/app/oracle/product/11.2.4/db_1/network/admin/encryption_wallet目录下的配置文件中，而非保存在数据库中的。

   

   

  1. imp

export LANG=AMERICAN_AMERICA.ZHS16GBK

create user suser identified by oracle default tablespace S;

grant connect,resource to suser;

create user puser identified by oracle default tablespace P;

grant connect,resource to puser;

imp system/oracle file=/backup/expdp/exp2user.dmp log=/backup/expdp/imp2user.log fromuser=suser,puser

   

[oracle@single1102 admin]$ imp system/oracle file=/backup/expdp/exp2user.dmp log=/backup/expdp/imp2user.log fromuser=suser,puser

Import: Release 11.2.0.4.0 - Production on Wed Jan 27 18:05:57 2016

Copyright (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

   

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SYSTEM's objects into SYSTEM

. importing SUSER's objects into SUSER

. importing PUSER's objects into PUSER

. . importing table "PT1" 4 rows imported

Import terminated successfully without warnings.

只能导入非加密的表。

   

   

  1. 表的迁移（exp\imp,expdp\impdp）

expdp:

export LANG=AMERICAN_AMERICA.ZHS16GBK

expdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=expdp2user.log tables=suser,puser

   

   

on 192.168.80.200

impdp

export LANG=AMERICAN_AMERICA.ZHS16GBK

impdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log tables=suser.stable1,puser.ptable1

   

通过上面的导入导出部分得知，目标库上如果要导入数据，必须要有相关的wallet,此处已经实现该点。

[oracle@single1102 admin]$ impdp system/oracle directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log tables=suser.stable1,puser.ptable1

Import: Release 11.2.0.4.0 - Production on Wed Jan 27 18:15:38 2016

Copyright (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 "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=EXPDP dumpfile=expdp2users.dmp logfile=impdp2user.log tables=suser.stable1,puser.ptable1

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "PUSER"."PTABLE1" 5.445 KB 4 rows

. . imported "SUSER"."STABLE1" 5.445 KB 4 rows

Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 27 18:15:46 2016 elapsed 0 00:00:05

   

   

   

   

  1. 远端的expdp和impdp        

   

直接从源库上导入到本地：

SQL> CREATE PUBLIC DATABASE LINK "linksource"

CONNECT TO system

IDENTIFIED BY "oracle"

USING 'source';

Database link created.

SQL> select * from puser.pt1@linksource;

ID NAME

---------- ----------------------------------------

1 aaa

2 bbb

3 ccc

4 ddd

   

 

 

 

 

export LANG=AMERICAN_AMERICA.ZHS16GBK

impdp system/oracle directory=EXPDP network_link='linksource' logfile=impdp2user.log tables=suser.stable1,puser.ptable1

[oracle@single1102 admin]$ impdp system/oracle directory=EXPDP network_link='linksource' logfile=impdp2user.log tables=suser.stable1,puser.ptable1

Import: Release 11.2.0.4.0 - Production on Wed Jan 27 18:23:17 2016

Copyright (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 "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=EXPDP network_link=linksource logfile=impdp2user.log tables=suser.stable1,puser.ptable1

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . imported "PUSER"."PTABLE1" 4 rows

. . imported "SUSER"."STABLE1" 4 rows

Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 27 18:23:35 2016 elapsed 0 00:00:15

   

   

直接在本地库上导出源库上的表

export LANG=AMERICAN_AMERICA.ZHS16GBK

expdp system/oracle directory=EXPDP network_link='linksource' dumpfile=expdp2usersfromsource.dmp logfile=exppdp2userfromsource.log schemas=suser,puser

   

  1. 移动表到不同表空间

alter table &tablename move tablespace &tablespacename;

conn puser/oracle

alter table ptable1 move tablespace S;

alter table pt1 move tablespace S;

   

conn suser/oracle

alter table stable1 move tablespace P;

alter table st1 move tablespace P;

   

SQL> conn puser/oracle

Connected.

SQL> alter table ptable1 move tablespace S;

Table altered.

SQL> alter table pt1 move tablespace S;

Table altered.

   

   

SQL> conn suser/oracle

Connected.

SQL> alter table stable1 move tablespace P;

Table altered.

SQL> alter table st1 move tablespace P;

Table altered.        

   

  1. 创建、重建、移动索引（加密列）

conn suser/oracle

SQL> desc stable1

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER ENCRYPT

NAME VARCHAR2(40) ENCRYPT

 

在id上创建主键

SQL> alter table stable1 add constraint pk_id primary key(id);

alter table stable1 add constraint pk_id primary key(id)

*

ERROR at line 1:

ORA-28338: Column(s) cannot be both indexed and encrypted with salt        

ORA-28338:

cannot encrypt indexed column(s) with salt

Cause:        An attempt was made to encrypt index column with salt.

Action:        Alter the table and specify column encrypting without salt.

   

处理方式

alter table stable1 modify (id ENCRYPT no salt);

SQL> alter table stable1 modify (id ENCRYPT no salt);

Table altered.

SQL> alter table stable1 add constraint pk_id primary key(id);

Table altered.

1）加密列的属性必须为no salt才可以创建索引

   

   

   

  1. 在id列上创建非btree索引

删除 主键约束

alter table stable1 drop constraint pk_id;

   

create BITMAP index id_idx on stable1(id) *

ERROR at line 1:

ORA-28337: the specified index may not be defined on an encrypted column

2）加密列只能创建b-tree索引

   

   

   

   

检查执行计划的变化：

SQL> alter table stable1 add constraint pk_id primary key(id);

Table altered.

   

SQL> select * from stable1;

Execution Plan

----------------------------------------------------------

Plan hash value: 3852586757

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 412 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| STABLE1 | 4 | 412 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2) 动态采样

Statistics

----------------------------------------------------------

47 recursive calls

0 db block gets

79 consistent gets

0 physical reads

0 redo size

672 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

7 sorts (memory)

0 sorts (disk)

4 rows processed

   

SQL> select * from stable1 where id=2;

Execution Plan

----------------------------------------------------------

Plan hash value: 2030797596

--------------------------------------------------------------------------------

-------

   

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim

e |

   

--------------------------------------------------------------------------------

-------

   

| 0 | SELECT STATEMENT | | 1 | 103 | 1 (0)| 00:

00:01 |

   

| 1 | TABLE ACCESS BY INDEX ROWID| STABLE1 | 1 | 103 | 1 (0)| 00:

00:01 |

   

|* 2 | INDEX UNIQUE SCAN | PK_ID | 1 | | 0 (0)| 00:

00:01 |

   

--------------------------------------------------------------------------------

-------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID"=2) 走索引

Statistics

----------------------------------------------------------

11 recursive calls

0 db block gets

21 consistent gets

0 physical reads

0 redo size

590 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

1 rows processed

   

SQL> select * from stable1 where id>2;

Execution Plan

----------------------------------------------------------

Plan hash value: 3852586757

   

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 206 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| STABLE1 | 2 | 206 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

   

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(INTERNAL_FUNCTION("ID")>2) 内部加密算法

Note

-----

- dynamic sampling used for this statement (level=2) 动态采样

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

648 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

 

 

 

   

  1. 添加、更换加密算法

加密列

为了使用TDE加密列，所有你需要做的只是在定义列的时候增加一个简单的谓词"ENCRYPT"。在定义之前，理所当然的你需要决定采用什么样的加密算法和密钥长度。

目前表的所有数据是明文的，你想转换SSN列为加密的，因此SSN保存了敏感的"社会保险号"，你可以通过如下方式设定：

alter table accounts modify (ssn encrypt);

这条语句完成了如下两件事：

为表创建了一个表密钥，如果你修改同一个表中的另外的列为加密的，将会使用同一个表密钥

将所有列的值转换为加密的形式

这条语句并不修改数据类型或者列的长度，也不创建触发器或者视图。

 

缺省情况下采用192位密钥长度的AES算法。你也可以选择不同的算法，只需要在SQL命令中指定即可。例如，如果要使用128位的AES算法，你可以采用如下语句：

alter table accounts modify (ssn encrypt using 'AES128');

你可以使用AES128、AES192、AES256、或者3DES168。这些值是自解释的，例如：AES256指采用AES算法、256位长度的密钥。

   

   

加密列之后，当查看表的时候你可以看到如下信息：

SQL> desc accounts

Name Null? Type

------------ ------------ --------------------------------------------------

ACC_NO NUMBER

ACC_NAME VARCHAR2(30)

SSN VARCHAR2(9) ENCRYPT

需要注意的是ENCRYPT关键字在数据类型之后。如果需要查找数据库中加密的列，你可以在数据字典视图中搜索DBA_ENCRYPTED_COLUMNS（TDE不能在SYS所有的表中启用）.

   

conn suser/oracle

alter table stable1 modify (id encrypt using 'AES128' no salt);

   

SQL> alter table stable1 modify (id number decrypt); ---取消加密

Table altered.

SQL> desc stable1

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER

NAME VARCHAR2(40) ENCRYPT

   

alter table stable1 modify (id number encrypt no salt);

SQL> desc stable1

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER ENCRYPT

NAME VARCHAR2(40) ENCRYPT

 

alter table stable1 modify (id encrypt using 'AES256' no salt);

ERROR at line 1:

ORA-28340: a different encryption algorithm has been chosen for the table

   

alter table stable1 modify (id encrypt using 'AES256' no salt);

ORA-28340: a different encryption algorithm has been chosen for the table

只能使用一种加密方式，不能修改。

   

SQL> alter table stable1 modify (name decrypt);

Table altered.

SQL> alter table stable1 modify (name encrypt using 'aes256');

alter table stable1 modify (name encrypt using 'aes256')

ERROR at line 1:

ORA-28340: a different encryption algorithm has been chosen for the table

   

   

alter table stable1 modify (name encrypt);

SQL> alter table stable1 modify (name encrypt);

Table altered.

SQL> desc stable1

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER ENCRYPT

NAME VARCHAR2(40) ENCRYPT

   

建表的时候确定加密方式

create table test(id number encrypt, name varchar2(20) encrypt using 'aes256');

SQL> create table test(id number encrypt, name varchar2(20) encrypt using 'aes256');

   

Table created.

   

SQL> desc test;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER ENCRYPT

NAME VARCHAR2(20) ENCRYPT

SQL> alter table test modify (name decrypt);

Table altered.

   

SQL> alter table test modify (name encrypt using 'aes128');

alter table test modify (name encrypt using 'aes128')

ERROR at line 1:

ORA-28340: a different encryption algorithm has been chosen for the table

   

   

SQL> alter table test modify (name encrypt using 'aes256');

Table altered.

   

总结：

加密方式一旦确定之后，就不能修改。

 

   

 

 

 

  1. dataguard环境配置

为了在dataguard中能正常使用encryption的功能，需要使用oracle 11g的版本支持，10g版本是不支持dg的。

使用方法是将 加密文件架和sqlnet.ora内容同步到 和主库一样的位置。即可。

  1. 报错记录

SQL> create table stable1 (id number ENCRYPT NOT NULL ,

2 name VARCHAR2(40) ENCRYPT,

3 PRIMARY KEY (id)

4 );

create table stable1 (id number ENCRYPT NOT NULL ,

*

ERROR at line 1:

ORA-28338: Column(s) cannot be both indexed and encrypted with salt

   

SQL> alter table test modify (name encrypt using 'aes128');

alter table test modify (name encrypt using 'aes128')

*

ERROR at line 1:

ORA-28340: a different encryption algorithm has been chosen for the table

  1. 总结归档及其他

   

1、加密方式：

表空间加密 是发生在数据存储的时候，也就是存储在文件上的数据已经被加密；

字段加密发生在SQL层，由SQL调用一个算法对数据进行加密处理。

   

2、加密的限制，比如：

– 索引类型 （加密列和加密表空间都只能创建b-tree索引）

– 都需要 no salt创建索引

– 外部大对象（bfiles)都不可以

– exp/imp不行，需要用expdp/impdp

转载于:https://www.cnblogs.com/junnor/p/5163426.html

weixin_33911824
关注
Oracle透明数据加密(TDE)真实环境使用分析
苏南生的CSDN博客
05-25 9300
23.2.13使用透明数据加密(TDE) 从Oracle数据库1Og的R2版本开始，一个叫做透明数据加密(TDE)的特性让数据加密变得极其容易。我们所需要做的就是把某列声明成加密的，剩下的全部由Oracle完成。当用户输入数据时，列值会被截获、加密，然后用加密后的格式保存。然后，当这一列被查询时，又会自动对列值进行解密，然后把解密后的文本（明文）返回给用户。用户甚至都不需要知道发生过加密和解
Oracle 11G TDE部署
qq_41557475的博客
05-13 397
本文仅用于个人学习记录，仅代表个人观点，如有错误之处，敬请批评指正。
oracle TDE学习系列 (3) — 如何备份？
java开发指南博客 【转载】
10-15 135
详见原文博客链接 http://www.killdb.com/2011/10/14/oracle-tde%e5%ad%a6%e4%b9%a0%e7%b3%bb%e5%88%97-3-%e5%a6%82%e4%bd%95%e5%a4%87%e4%bb%bd%ef%bc%9f.html
Oracle（105）什么是透明数据加密（TDE）？
最新发布
qq_43012298的博客
09-05 388
透明数据加密（TDE）是一种用于保护数据库中静态数据的加密技术，通过自动加密数据库文件和日志文件，确保数据在磁盘上是加密的。TDE 对应用程序是透明的，不需要对应用程序代码进行任何修改。上述步骤和代码示例展示了如何在 Microsoft SQL Server 中配置和使用 TDE，以满足具体的安全需求。
oracle TDE
weixin_33725722的博客
03-16 431
1 创建目录存放wallet mkdir /u01/app/oracle/oradata/prod1/wallet mkstore -wrl /u01/app/oracle/oradata/prod1/wallet -create 查看密钥文件： [oracle@iZm5eg3yspb12f3ke9ts08Z wallet_1]$ orapki wallet display -wallet /h...
Oracle-11g 中使用表空间透明数据加密（TDE）
weixin_30412013的博客
06-28 205
Oracle-11g 中使用表空间透明数据加密（TDE）的限制 TDE 表空间加密方式会在数据读写过程中加解密数据。与在 SQL 层面做加解密的 TDE 列加密方式相比，其限制要大幅减少。例如：数据类型、索引类型的限制。但 TDE 表空间加密方式仍存在如下限制： （TDE tablespace encryption encrypts/decrypts data during re...
我的Oracle 11g OCP学习笔记
04-25
4. **数据加密**：通过透明数据加密（TDE）功能，Oracle 11g可以在不改变应用程序的情况下加密数据库，保护敏感信息。 5. **数据恢复增强**：闪回数据库（Flashback Database）和恢复到任意时间点（Point-in-Time ...
oracle10学习
11-10
- 安全策略：了解Oracle的加密机制，如TDE（Transparent Data Encryption）和RAC（Real Application Clusters）的高可用性。 - 审计功能：启用审计，跟踪和分析数据库活动，以满足合规性和安全需求。 8. **Oracle...
Oracle学习资料doc
01-08
- 数据加密：学习Oracle的透明数据加密（TDE）功能，保护敏感数据的安全。 8. **Oracle开发与应用**： - JDBC与ODBC：了解Java和Oracle之间的接口，如何使用JDBC驱动连接和操作Oracle数据库。 - APEX...
oracle数据库管理大家学习一下
07-12
10. **数据库安全**：Oracle提供多种安全特性，如审计、透明数据加密（TDE）和数据屏蔽。理解这些特性并正确配置，可以有效保护数据库免受未经授权的访问和攻击。 11. **Oracle网络配置**：Oracle使用网络服务如...
oracle 透明数据加密（TDE）学习笔记（1）--wallet使用和管理
aocu40057610的博客
09-27 225
详见原文博客链接 http://www.killdb.com/2011/10/13/oracle-tde%e5%ad%a6%e4%b9%a0%e7%b3%bb%e5%88%971-wallet-%e4%bd%bf%e7%94%a8%e7%ae%a1%e7%90%86.html 转载于:https://www.cnblogs.com/lovewifelovelife/archive/...
oracle关闭tde,oracle TDE
weixin_39576127的博客
04-11 339
mkdir /u01/app/oracle/oradata/prod1/walletmkstore -wrl /u01/app/oracle/oradata/prod1/wallet -create2更改vi $ORACLE_HOME/network/admin/sqlnet.oraENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DA...
oracle透明数据加密TDE
weixin_33753845的博客
09-07 105
本文转自：http://blog.csdn.net/yah99_wolf/article/details/4586209 感谢博主：yah99_wolf 1 TDE如何工作？ 现在我们概述一下关键点：加密时你需要应用一个加密算法和一个加密密钥对明文输入的数据进行加解密操作；为了能够成功的解密，你必须知道加密采用的算法和密钥。 你所要做的只是定义需要...
oracle12c加密ted,Oracle数据透明加密-TDE
weixin_30697511的博客
04-03 828
介绍Oracle 10g R2提供了一个新的特性，让你只需要做如下动作：你可以不写一行代码，只需要声明你需要加密某列。当用户插入数据的时候，数据库透明的加密数据然后存储加密后的数据。同样的，当用户读取数据时，数据库自动进行解密。由于加解密操作对应用程序来说都是透明的，不需要应用程序修改代码，因此这个特性就叫做：透明数据加密(TDE)。简单来说默认不对数据进行TDE加密，数据库中的某些敏感数据是直接...
ORACLE TDE感觉不实用
cuizhuzou2563的博客
01-31 403
最近要对客户的工资表进行加密，研究下ORACLE的TDE，发现并不实用。 通过ORACLE TDE，我们可以对敏感的数据设置一个wallet来进行加密，如果有居心不良人员通过截取数据包或者偷取备份文件， 试图进行...
有关TDE配置的一些记录
wgz7747147820的博客
06-28 354
搜索TDE配置的顺序 The search order for the TDE keystore depends on how you have set either the instance initialization parameters, the sqlnet.ora parameters, or the environment variables. Oracle Database retrieves the keystore by searching in these locations, in
oracle tde技术,Oracle TDE 透明数据加密技术
weixin_33507838的博客
04-03 383
从ORALE 10GR2开始出现透明数据加密技术(Transparent Data Encryption，TDE)。TDE用来对数据加密，通常 SQL 执行的应用程序逻辑不需要进行更改，仍能正常运行。 换言之，应用程序可以使用同一语法将数据插入到应用程序表中，并且 Oracle 数据库在将信息写入磁盘之前将自动对数据进行加密。 随后的选择操作将透明地解密数据，因此应用程序将继续正常地运行。 这一点...
oracle TDE和DBMS_CRYPTO
conglie9094的博客
05-30 282
oracle提供的两种加密方式TDE和DBMS_CRYPTO包 一、DBMS_CRYPTO包（10g以及更高的版本） 这种方法，我们可以建构自己的基础架构，对数据进行加密，灵活性强，但是建构和管理相对TDE就比...
Oracle Database 11g：DBA新功能测试与升级指南
"Oracle_11g_面向DBA的新功能学习指南" Oracle Database 11g作为一款重要的企业级数据库管理系统，为DBA（数据库管理员）带来了诸多新特性与优化，旨在提升数据库的管理效率、稳定性和性能。以下是对Oracle ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值