Transparent data encryption TDE

Creating TDE and wallet in 12c

ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
   (METHOD_DATA =
    (DIRECTORY = /opt/app/oracle/admin/$ORACLE_UNQNAME/wallet)
   )
  )


--Create key store

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/app/oracle/admin/shwm/wallet/' IDENTIFIED BY "xxxxxxxx";


-- Open
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "xxxxxxxx";

-- Close
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "xxxxxxxx";


--create masterkey

administer key management set key identified by "xxxxxxxx" with backup;



--Auto login

administer key management create auto_login keystore from keystore '/opt/app/oracle/admin/shwm/wallet/' identified by "xxxxxxxx";



--encryption tablespace online 12c

alter system set compatible='12.2.0.0.0' scope=spfile;

select tablespace_name,encrypted from dba_tablespaces where tablespace_name='TXT';

ALTER TABLESPACE txt ENCRYPTION ONLINE USING 'AES256' ENCRYPT;


******************************************************************************
TDE we have column level encryption starting from 10G and tablespace level from 11G

      select * from v$encryption_wallet

For tablespace level

We cannot convert an existing tablespace to encrypted tablespace.

Onley way to do is creating new encrypted tablespace and transferring the objects either by ctas or expdp

encrypted tablespaces can be created by using create clause at the time of TBS creation.

Column level encryption can be done on existing columns

Draw back is performance overhead and range scans are not possible if index is present on encrypted column. For example using like operator in query.

For either of these you need database wallet installed and configured.

If you restart db you need to open wallet or else none of the data can be pulled out.

For reference on wallet creation check here

http://dbasravan.blogspot.com/2015/03/wallet.html

Sample commands for creating encrypted tablespace and encrypting columns are listed below

CREATE TABLE tde_test (

  id    NUMBER(10),

  data  VARCHAR2(50) ENCRYPT

)

TABLESPACE tde_test;

SELECT * FROM dba_encrypted_columns;

CREATE TABLESPACE ts_tde

DATAFILE '/u01/app/oracle/oradata/ora11g/ts_tde01.dbf' 

  SIZE 20m ATOEXTEND ON NEXT 5m

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

ENCRYPTION USING '3DES168'

DEFAULT STORAGE (ENCRYPT);

SELECT tablespace_name , encrypted 

FROM dba_tablespaces;

TABLESPACE_NAME                ENC

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

SYSTEM                         NO

SYSAUX                         NO

UNDOTBS1                       NO

TEMP                           NO

USERS                          NO

EXAMPLE                        NO

TS_TDE                         YES

SELECT t.name , e.encryptionalg , e.encryptedts

FROM v$tablespace t , v$encrypted_tablespaces e

WHERE t.ts# = e.ts#;

NAME                           ENCRYPT ENC

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

TS_TDE                         3DES168 YES

alter table cc add (SOCIAL_SEC_NO varchar2(9) encrypt using 'AES128');

alter table cc modify (cc_no encrypt using 'AES128');

Encryption Salt

Consider this scenario. An intruder has stolen the backups of the medical records database containing the patient information; but since he does not have the wallet password he will not be able to open the wallet and see the clear text values. He can, however, still read the raw data files and see the encrypted values. This action by itself does not reveal the sensitive data, but it may reveal a pattern which might help the intruder. For instance, assume the intruder knows a specific patient and the diagnosis made on her - cancer. From the data files, he can see the encrypted value of this diagnosis code "cancer". Then he can scan the file to see the identical values in other records, which will help him know who else has the same diagnosis code, i.e. cancer. So even though he may not know the actual value, he has learned who all have the same diagnosis by establishing a pattern. Similarly by knowing some key patients, he can learn a lot about other patients by this pattern analysis. This may not be acceptable as a security standard.

To prevent such a possibility, you can add some "salt". This is merely a random value added in the process to make the encrypted values different even if the clear text values are the same. In many cases, this is actually desirable; hence TDE adds a salt to the value by default. Now even if two patients have the same diagnosis code, the encrypted value stored in the database will be very different.

In some cases, you may not want to add a salt. In that case, you can override the default by specifying the NO SALT clause while defining encryption. For instance, while modifying the column for encryption, you can use:

alter table cc modify (cc_no encrypt using 'AES256' no salt);

The "NO SALT" clause does not add salt to the clear text value before encrypting. To remove salt from a previously encrypted table, you can issue:

alter table cc modify (cc_no encrypt no salt)

If you have defined a column as encrypted with salt, you can't create an index on it. If you do attempt it, you will get the following error:

ORA-28338: cannot encrypt indexed column(s) with salt

You can remove salt by the statements shown above. Note that the removal of salt actually triggers the r-encryption of the column that may generate a large amount of undo and redo.

***********************
Note: check below to verify key in all instances of RAC is same
select ts#, masterkeyid, utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(masterkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(masterkeyid,5,length(masterkeyid)))) masterkeyid_base64 FROM v$encrypted_tablespaces;