PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle Transparent Data Encryption
Version 11.1
 
General
Warnings: As of 25 September, 2007 TDE was considered part of the Advanced Security option for the Enterprise Edition only. Before putting any of this code into production verify Oracle's licensing requirements are met.

TDE can only be used on columns that do not have bitmap indexes, are not involved in referential (foreign key) constraints, etc. Be sure you read the docs on limitations.

Be sure to check restrictions and issues related to the use of import/export, RAC, and transportable tablespaces (different in 10g from 11g)
Related Data Dictionary Objects
col$ ts$ gv$encryption_wallet
dba_encrypted_columns dba_tables gv$wallet

Restrictions		 You cannot use transparent data encryption to encrypt columns used in foreign key constraints. This is because every table has a unique column encryption key.

Transparent data encryption encrypts and decrypts data at the SQL layer. Oracle Database utilities and features that bypass the SQL layer cannot leverage the services provided by transparent data encryption. Do not use transparent data encryption with the following database features:
  • Index types other than B-tree
  • Range scan search through an index
  • External large objects (BFILE)
  • Materialized View Logs
  • Synchronous Change Data Capture
  • Transportable Tablespaces
  • Original import/export utilities

Warning: Before using TDE be sure you have read the documentation and understand backup and recovery, export / import, and other important considerations.

Log onto Oracle using SQL*Plus conn uwclass/uwclass

Create Wallet directory in operating system		 -- Note: This step is identical with the one performed with SECUREFILES.
-- if a wallet already exists skip this step.

host

-- mkdir $ORACLE_BASE\admin\<SID>\wallet
mkdir $ORACLE_BASE\admin\orabase\wallet

exit

Alter SQLNET.ORA file		 -- Note: This step is identical with the one performed with SECUREFILES.
-- if a wallet already exists skip this step.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=FILE) (METHOD_DATA = (DIRECTORY = c:\oracle\admin\orabase\wallet)))

-- Note: if you do not use this wallet location you will likely
-- receive ORA-28368: cannot auto-create wallet when setting the key

Set Encryption Key		 conn uwclass/uwclass

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "N0way!";

desc gv$encryption_wallet

col wrl_parameter format a40

SELECT * FROM gv$encryption_wallet;

Load the master encryption key following a restart		 ALTER SYSTEM SET [ENCRYPTION] WALLET OPEN IDENTIFIED BY <password>;
desc gv$encryption_wallet

col wrl_parameter format a50

SELECT *
FROM gv$encryption_wallet;

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "N0way!";

-- Failure to do so will result in: ORA-28365: wallet is not open

SELECT *
FROM gv$encryption_wallet; 

Close the wallet		 ALTER SYSTEM SET WALLET CLOSE;
ALTER SYSTEM SET WALLET CLOSE;
 
Tablespace Level

Create tablespace		 CREATE TABLESPACE <tablespace_name>
DATAFILE '<path_and_file_name>' SIZE <bytes>
LOGGING ONLINE PERMANENT BLOCKSIZE <bytes>
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
conn / as sysdba

CREATE TABLESPACE securespace1
DATAFILE 'c: emp\secure01.dbf' SIZE 25M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

SELECT tablespace_name, encrypted
FROM dba_tablespaces;

desc gv$encrypted_tablespaces

SELECT et.inst_id, ts.name, et.encryptionalg, et.encryptedts
FROM gv$encrypted_tablespaces et, ts$ ts
WHERE et.ts# = ts.ts#;

set long 1000000

SELECT dbms_metadata.get_ddl('TABLESPACE', 'SECURESPACE1')
FROM dual;

CREATE TABLESPACE securespace
DATAFILE 'c: emp\secure02.dbf' SIZE 25M
ENCRYPTION USING '3DES168'
DEFAULT STORAGE(ENCRYPT);

SELECT et.inst_id, ts.name, et.encryptionalg, et.encryptedts
FROM gv$encrypted_tablespaces et, ts$ ts
WHERE et.ts# = ts.ts#;

ALTER USER uwclass QUOTA UNLIMITED ON securespace1;
ALTER USER uwclass QUOTA UNLIMITED ON securespace2;

conn uwclass/uwclass

CREATE TABLE t1 (
testcol VARCHAR2(20))
TABLESPACE securespace1;

CREATE TABLE t2 (
testcol VARCHAR2(20))
TABLESPACE securespace2;

SELECT ta.table_name, ts.tablespace_name, ts.encrypted
FROM user_tables ta, user_tablespaces ts
WHERE ta.tablespace_name = ts.tablespace_name;

-- not listed in this view
 SELECT * FROM user_encrypted_columns;
 
Table Level

Default Encryption

Encrypts with default 3 Key Triple DES 168 bits key

 CREATE TABLE <table_name> (
<column_name>  <column_data_type>,
<column_name>  <column_data_type> ENCRYPT);
conn uwclass/uwclass

CREATE TABLE tde (
SSN        VARCHAR2(11),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
salary     NUMBER(6) ENCRYPT);

desc tde

desc user_tab_cols

desc user_encrypted_columns

SELECT *
FROM user_encrypted_columns;

set long 1000000

SELECT dbms_metadata.get_ddl('TABLE', 'TDE')
FROM dual;

conn / as sysdba

desc col$

SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'TDE';

SELECT name, property
FROM col$
WHERE obj# = 71844;
-- from $ORACLE_HOME/rdbms/admin/dcore.bsq

create table col$ /* column table */
..
property number not null, /* column properties (bit flags): */
/* 0x0001 = 1 = ADT attribute column */
/* 0x0002 = 2 = OID column */
/* 0x0004 = 4 = nested table column */
/* 0x0008 = 8 = virtual column */
/* 0x0010 = 16 = nested table's SETID$ column */
/* 0x0020 = 32 = hidden column */
/* 0x0040 = 64 = primary-key based OID column */
/* 0x0080 = 128 = column is stored in a lob */
/* 0x0100 = 256 = system-generated column */
/* 0x0200 = 512 = rowinfo column of typed table/view */
/* 0x0400 = 1024 = nested table columns setid */
/* 0x0800 = 2048 = column not insertable */
/* 0x1000 = 4096 = column not updatable */
/* 0x2000 = 8192 = column not deletable */
/* 0x4000 = 16384 = dropped column */
/* 0x8000 = 32768 = unused column - data still in row */
/* 0x00010000 = 65536 = virtual column */
/* 0x00020000 = 131072 = place DESCEND operator on top */
/* 0x00040000 = 262144 = virtual column is NLS dependent */
/* 0x00080000 = 524288 = ref column (present as oid col) */
/* 0x00100000 = 1048576 = hidden snapshot base table column */
/* 0x00200000 = 2097152 = attribute column of a user-defined ref */
/* 0x00400000 = 4194304 = export hidden column,RLS on hidden col */
/* 0x00800000 = 8388608 = string column measured in characters */
/* 0x01000000 = 16777216 = virtual column expression specified */
/* 0x02000000 = 33554432 = typeid column */
/* 0x04000000 = 67108864 = Column is encrypted */
/* 0x20000000 = 536870912 = Column is encrypted without salt */
..;

conn uwclass/uwclass

INSERT INTO tde
(ssn, first_name, last_name, salary)
VALUES
(100, 'Dan', 'Morgan', 100);
COMMIT;


Examine Results		 desc tde

SELECT * FROM tde;

desc user_tab_cols

SELECT column_name, 

Dump the block		 set serveroutput on

SELECT ssn,
dbms_rowid.rowid_to_absolute_fno(rowid, 'UWCLASS', 'EMPLOYEZ') ABSOLUTE_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO, dbms_rowid.rowid_row_number(rowid) ROWNUMBER
FROM employez;

ALTER SYSTEM DUMP DATAFILE 6 BLOCK 5926;

host

cd $ORACLE_BASE/diag/rdbms/orabase/orabase/trace

vi orabase_ora_3756.trc

exit
SELECT * FROM tde;

Trace file c:\oracle\product\diag dbms\orabase\orabase race\orabase_ora_3756.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Beta
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:657M/2038M, Ph+PgF:2721M/3932M, VA:1263M/2047M
Instance name: orabase
Redo thread mounted by this instance: 1
Oracle process number: 29
Windows thread id: 3756, image: ORACLE.EXE (SHAD)


*** 2007-09-26 16:02:18.484
*** SESSION ID:(129.1471) 2007-09-26 16:02:18.484
*** CLIENT ID:() 2007-09-26 16:02:18.484
*** SERVICE NAME:(SYS$USERS) 2007-09-26 16:02:18.484
*** MODULE NAME:(SQL*Plus) 2007-09-26 16:02:18.484
*** ACTION NAME:() 2007-09-26 16:02:18.484

Start dump data blocks tsn: 7 file#:6 minblk 5926 maxblk 5926
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7, rdba=25171750
BH (0x107F988C) file#: 6 rdba: 0x01801726 (6/5926) class: 1 ba: 0x10750000
set: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0 lid: 0x00000000,0x00000000
dbwrid: 0 obj: 71844 objn: 71844 tsn: 7 afn: 6
hash: [0x1A3E8E0C,0x2EF1F6C0] lru: [0x1BBFCCEC,0x1BBE753C]
ckptq: [NULL] fileq: [NULL] objq: [0x1A3E8C5C,0x1A3E8F9C]
st: XCURRENT md: NULL tch: 4
flags: block_written_once redo_since_read gotten_in_current_mode
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
buffer tsn: 7 rdba: 0x01801726 (6/5926)
scn: 0x0000.00e04b4c seq: 0x01 flg: 0x06 tail: 0x4b4c0601
frmt: 0x02 chkval: 0x8a49 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x10750000 to 0x10752000
10750000 0000A206 01801726 00E04B4C 06010000 [....&...LK......]
10750010 00008A49 001D0001 000118A4 00E04B4B [I...........KK..]
10750020 1FE80000 00321F02 0180170A 00100008 [......2.........]
10750030 00000587 00C00F26 000B0292 00008000 [....&...........]
10750040 00E04A21 001A0004 0000054B 00C0052D [!J......K...-...]
10750050 00310318 00002001 00E04B4C 00000000 [..1.. ..LK......]
10750060 00000000 00010100 0014FFFF 1F3E1F52 [............R.>.]
10750070 00001F3E 1F520001 00000000 00000000 [>.....R.........]
10750080 00000000 00000000 00000000 00000000 [................]
Repeat 482 times
10751EB0 00000000 004C000A 000C001C 00000001 [......L.........]
10751EC0 00011844 00011844 00000001 00000000 [D...D...........]
10751ED0 001D150A 00010C08 00000000 00C00A40 [............@...]
10751EE0 0003028E 00E00CE6 00000000 00E00CEA [................]
10751EF0 0CD20000 2EFA9FC0 00E01AEB 00000000 [................]
10751F00 00C0007C 00000000 00000000 10020504 [|...............]
10751F10 001B0005 00000534 00C007D9 000102DA [....4...........]
10751F20 0000C000 00DFE9FC 00000007 008111FF [................]
10751F30 00801BE3 00000001 0018000A 000C001C [................]
10751F40 00000004 00011830 00011830 00000001 [....0...0.......]
10751F50 00000000 0105150A 057A0000 10020504 [..........z.....]
10751F60 00120008 0000057A 00C00A40 0002028E [....z...@.......]
10751F70 00008000 00E01AEA 00000010 008010D4 [................]
10751F80 008010D3 00250001 0018000A 000C0040 [......%.....@...]
10751F90 00000002 00011830 00011830 00000001 [....0...0.......]
10751FA0 00C00A42 0005150A 057A0000 10020505 [B.........z.....]
10751FB0 00120002 022CDD71 02C20204 6E614403 [....q.,......Dan]
10751FC0 726F4D06 346E6167 5AF842D3 753EAD6D [.Morgan4.B.Zm.>u]
10751FD0 B781D0BA 160313B1 EB403997 9C1BAB1E [.........9@.....]
10751FE0 7A1D5F56 6C06C676 6C0F15D5 A356DB32 [V_.zv..l...l2.V.]
10751FF0 D9E716D7 83886448 F2DDBFF1 4B4C0601 [....Hd........LK]
Block header dump: 0x01801726
Object id on Block? Y
seg/obj: 0x118a4 csc: 0x00.e04b4b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x180170a ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.010.00000587 0x00c00f26.0292.0b C--- 0 scn 0x0000.00e04a21
0x02 0x0004.01a.0000054b 0x00c0052d.0318.31 --U- 1 fsc 0x0000.00e04b4c
bdba: 0x01801726
data_block_dump,data header at 0x10750064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x10750064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f52
avsp=0x1f3e
tosp=0x1f3e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f52
block_row_dump:
tab 0, row 0, @0x1f52
tl: 70 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c2 02
col 1: [ 3] 44 61 6e
col 2: [ 6] 4d 6f 72 67 61 6e
col 3: [52]
d3 42 f8 5a 6d ad 3e 75 ba d0 81 b7 b1 13 03 16 97 39 40 eb 1e ab 1b 9c 56
5f 1d 7a 76 c6 06 6c d5 15 0f 6c 32 db 56 a3 d7 16 e7 d9 48 64 88 83 f1 bf
dd f2
end_of_block_dump
BH (0x1A3E8E0C) file#: 6 rdba: 0x01801726 (6/5926) class: 1 ba: 0x1A0C0000
set: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0 lid: 0x00000000,0x00000000
dbwrid: 0 obj: 71844 objn: 71844 tsn: 7 afn: 6
hash: [0x1B3F689C,0x107F988C] lru: [0x1ABEF80C,0x123EF3FC]
lru-flags: hot_buffer
ckptq: [NULL] fileq: [NULL] objq: [0x26BED32C,0x19FF056C]
st: CR md: NULL tch: 0
cr: [scn: 0x0.e04b4a],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.e04b4a],[sfl: 0x0],[lc: 0x0.0]
flags: redo_since_read gotten_in_current_mode
cr pin refcnt: 0 sh pin refcnt: 0
buffer tsn: 7 rdba: 0x01801726 (6/5926)
scn: 0x0000.00e04a21 seq: 0x01 flg: 0x02 tail: 0x4a210601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x1A0C0000 to 0x1A0C2000
1A0C0000 0000A206 01801726 00E04A21 02010000 [....&...!J......]
1A0C0010 00000000 001D0001 000118A4 00E04A1E [.............J..]
1A0C0020 1FE80000 00321F02 0180170A 00100008 [......2.........]
1A0C0030 00000587 00C00F26 000B0292 00002001 [....&........ ..]
1A0C0040 00E04A21 00000000 00000000 00000000 [!J..............]
1A0C0050 00000000 00000000 00000000 00000000 [................]
1A0C0060 00000000 00010100 0014FFFF 1F3E1F52 [............R.>.]
1A0C0070 00001F3E 1F520001 00000000 00000000 [>.....R.........]
1A0C0080 00000000 00000000 00000000 00000000 [................]
Repeat 482 times
1A0C1EB0 00000000 004C000A 000C001C 00000001 [......L.........]
1A0C1EC0 00011844 00011844 00000001 00000000 [D...D...........]
1A0C1ED0 001D150A 00010C08 00000000 00C00A40 [............@...]
1A0C1EE0 0003028E 00E00CE6 00000000 00E00CEA [................]
1A0C1EF0 0CD20000 2EFA9FC0 00E01AEB 00000000 [................]
1A0C1F00 00C0007C 00000000 00000000 10020504 [|...............]
1A0C1F10 001B0005 00000534 00C007D9 000102DA [....4...........]
1A0C1F20 0000C000 00DFE9FC 00000007 008111FF [................]
1A0C1F30 00801BE3 00000001 0018000A 000C001C [................]
1A0C1F40 00000004 00011830 00011830 00000001 [....0...0.......]
1A0C1F50 00000000 0105150A 057A0000 10020504 [..........z.....]
1A0C1F60 00120008 0000057A 00C00A40 0002028E [....z...@.......]
1A0C1F70 00008000 00E01AEA 00000010 008010D4 [................]
1A0C1F80 008010D3 00250001 0018000A 000C0040 [......%.....@...]
1A0C1F90 00000002 00011830 00011830 00000001 [....0...0.......]
1A0C1FA0 00C00A42 0005150A 057A0000 10020505 [B.........z.....]
1A0C1FB0 00120002 012CDD71 02C20204 6E614403 [....q.,......Dan]
1A0C1FC0 726F4D06 346E6167 1EE12218 5D3CFFD0 [.Morgan4."....<]]
1A0C1FD0 B3B524D7 FBDC526F BA7C407E 2343139C [.$..oR..~@|...C#]
1A0C1FE0 872FC309 C5CED36E 6C9FB4F8 00480C74 [../.n......lt.H.]
1A0C1FF0 226FDB67 6FD4DAAA B61C3A62 4A210601 [g.o"...ob:....!J]
Block header dump: 0x01801726
Object id on Block? Y
seg/obj: 0x118a4 csc: 0x00.e04a1e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x180170a ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.010.00000587 0x00c00f26.0292.0b --U- 1 fsc 0x0000.00e04a21
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01801726
data_block_dump,data header at 0x1a0c0064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x1a0c0064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f52
avsp=0x1f3e
tosp=0x1f3e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f52
block_row_dump:
tab 0, row 0, @0x1f52
tl: 70 fb: --H-FL-- lb: 0x1 cc: 4
col 0: [ 2] c2 02
col 1: [ 3] 44 61 6e
col 2: [ 6] 4d 6f 72 67 61 6e
col 3: [52]
18 22 e1 1e d0 ff 3c 5d d7 24 b5 b3 6f 52 dc fb 7e 40 7c ba 9c 13 43 23 09
c3 2f 87 6e d3 ce c5 f8 b4 9f 6c 74 0c 48 00 67 db 6f 22 aa da d4 6f 62 3a
1c b6
end_of_block_dump
Block dump from disk:
buffer tsn: 7 rdba: 0x01801726 (6/5926)
scn: 0x0000.00e04b4c seq: 0x01 flg: 0x06 tail: 0x4b4c0601
frmt: 0x02 chkval: 0x8a49 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0EAC8200 to 0x0EACA200
EAC8200 0000A206 01801726 00E04B4C 06010000 [....&...LK......]
EAC8210 00008A49 001D0001 000118A4 00E04B4B [I...........KK..]
EAC8220 1FE80000 00321F02 0180170A 00100008 [......2.........]
EAC8230 00000587 00C00F26 000B0292 00008000 [....&...........]
EAC8240 00E04A21 001A0004 0000054B 00C0052D [!J......K...-...]
EAC8250 00310318 00002001 00E04B4C 00000000 [..1.. ..LK......]
EAC8260 00000000 00010100 0014FFFF 1F3E1F52 [............R.>.]
EAC8270 00001F3E 1F520001 00000000 00000000 [>.....R.........]
EAC8280 00000000 00000000 00000000 00000000 [................]
Repeat 482 times
EACA0B0 00000000 004C000A 000C001C 00000001 [......L.........]
EACA0C0 00011844 00011844 00000001 00000000 [D...D...........]
EACA0D0 001D150A 00010C08 00000000 00C00A40 [............@...]
EACA0E0 0003028E 00E00CE6 00000000 00E00CEA [................]
EACA0F0 0CD20000 2EFA9FC0 00E01AEB 00000000 [................]
EACA100 00C0007C 00000000 00000000 10020504 [|...............]
EACA110 001B0005 00000534 00C007D9 000102DA [....4...........]
EACA120 0000C000 00DFE9FC 00000007 008111FF [................]
EACA130 00801BE3 00000001 0018000A 000C001C [................]
EACA140 00000004 00011830 00011830 00000001 [....0...0.......]
EACA150 00000000 0105150A 057A0000 10020504 [..........z.....]
EACA160 00120008 0000057A 00C00A40 0002028E [....z...@.......]
EACA170 00008000 00E01AEA 00000010 008010D4 [................]
EACA180 008010D3 00250001 0018000A 000C0040 [......%.....@...]
EACA190 00000002 00011830 00011830 00000001 [....0...0.......]
EACA1A0 00C00A42 0005150A 057A0000 10020505 [B.........z.....]
EACA1B0 00120002 022CDD71 02C20204 6E614403 [....q.,......Dan]
EACA1C0 726F4D06 346E6167 5AF842D3 753EAD6D [.Morgan4.B.Zm.>u]
EACA1D0 B781D0BA 160313B1 EB403997 9C1BAB1E [.........9@.....]
EACA1E0 7A1D5F56 6C06C676 6C0F15D5 A356DB32 [V_.zv..l...l2.V.]
EACA1F0 D9E716D7 83886448 F2DDBFF1 4B4C0601 [....Hd........LK]
Block header dump: 0x01801726
Object id on Block? Y
seg/obj: 0x118a4 csc: 0x00.e04b4b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x180170a ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.010.00000587 0x00c00f26.0292.0b C--- 0 scn 0x0000.00e04a21
0x02 0x0004.01a.0000054b 0x00c0052d.0318.31 --U- 1 fsc 0x0000.00e04b4c
bdba: 0x01801726
data_block_dump,data header at 0xeac8264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0eac8264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f52
avsp=0x1f3e
tosp=0x1f3e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f52
block_row_dump:
tab 0, row 0, @0x1f52
tl: 70 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c2 02
col 1: [ 3] 44 61 6e
col 2: [ 6] 4d 6f 72 67 61 6e
col 3: [52]
d3 42 f8 5a 6d ad 3e 75 ba d0 81 b7 b1 13 03 16 97 39 40 eb 1e ab 1b 9c 56
5f 1d 7a 76 c6 06 6c d5 15 0f 6c 32 db 56 a3 d7 16 e7 d9 48 64 88 83 f1 bf
dd f2
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 5926 maxblk 5926
 
TDE Variations

Encrypt Using		 CREATE TABLE <table_name> (
<column_name> <data_type>,
<column_name> <data_type> ENCRYPT USING '<encryption_algorithm>');
CREATE TABLE tde_using (
ssn        VARCHAR2(11),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
salary     NUMBER(6) ENCRYPT USING '3DES168');

desc tde_using

SELECT *
FROM user_encrypted_columns;

Encrypt Identified By

Demonstrated using an external table

 CREATE TABLE <table_name> (
<column_name> <data_type>,
<column_name> <data_type>, ENCRYPT IDENTIFIED BY '<encryption_key>');
CREATE TABLE reg_ext (
object_name,
object_type)
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "CTEMP"
LOCATION('reg.dat'))
REJECT LIMIT UNLIMITED
AS SELECT RPAD(object_name,52) obj_name, RPAD(object_type,52) obj_type
FROM user_objects;

CREATE TABLE tde_ext (
object_name ENCRYPT IDENTIFIED BY "xIcf3T9u",
object_type ENCRYPT IDENTIFIED BY "xIcf3T9u")
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "CTEMP"
LOCATION('tde.dat'))
REJECT LIMIT UNLIMITED
AS SELECT RPAD(object_name,52) obj_name, RPAD(object_type,52) obj_type
FROM user_objects;

desc reg_ext
desc tde_ext

SELECT * FROM reg_ext;
SELECT * FROM tde_ext;

-- open the file in the file system

SALT		 CREATE TABLE <table_name> (
<column_name> <data_type>,
<column_name> <data_type>, ENCRYPT SALT);
CREATE TABLE tde_salt (
ssn        VARCHAR2(11),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
salary     NUMBER(6) ENCRYPT SALT);

desc tde_salt

SELECT *
FROM user_encrypted_columns;

NOSALT		 CREATE TABLE <table_name> (
<column_name> <data_type>,
<column_name> <data_type>, ENCRYPT);
CREATE TABLE tde_nosalt (
ssn        VARCHAR2(11),
first_name VARCHAR2(30),
last_name  VARCHAR2(30),
salary     NUMBER(6) ENCRYPT NO SALT);

desc tde_nosalt

SELECT *
FROM user_encrypted_columns;
 
ALTER Transparent Data Encryption

ENCRYPT		 ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT>);
desc tde_salt

ALTER TABLE tde_salt MODIFY (first_name ENCRYPT);

desc tde_salt

SALT		 ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT SALT>);
ALTER TABLE tde_salt MODIFY (last_name ENCRYPT SALT);

desc tde_salt
NOSALT ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT NO SALT>);
ALTER TABLE tde_salt MODIFY (first_name ENCRYPT NO SALT);
REKEY
If the encryption key has been changed		 ALTER TABLE <table_name> REKEY;
ALTER TABLE tde_salt REKEY;
 
End Decryption

DECRYPT		 ALTER TABLE <table_name> MODIFY (<column_name> DECRYPT>);
desc tde_salt

ALTER TABLE tde_salt MODIFY (first_name DECRYPT);

desc tde_salt
 
Related Topics
DBMS_LOB
Large Objects (LOBs)
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----