Thursday, 11 September 2014

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
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)