Tuesday, August 30, 2011

Oracle advanced security Transparent Data Encryption (TDE) 11GR2

Overview:

TDE enables you to encrypt individual table columns or an entire tablespace. When a user inserts data into an encrypted column, transparent data encryption automatically encrypts the data. When users select the column, the data is automatically decrypted. After the selection, the data is re-encrypted.
Transparent data encryption helps protect data stored on media in the event that the storage media or data file gets stolen, because it stores the encryption keys in a security module (that is, a wallet) external to the database. Protecting data from this type of theft is required for most compliance regulations. The benefit to using transparent data encryption is that it requires little coding and is quick and easy to implement.
New features of TDE (11GR2)

1. One wallet can be used for tablespace and column encryption
2. Regenerate MASTER KEY using alter system set
3. Store master key in HCM or Wallet

Wallet creation

Before trying to encrypt tablespace or column, a wallet must be created if tablespace or column needs to be encrypted.
  1.  For oracle to identify wallet, we need to set ENCRYPTION_WALLET_LOCATION parameter in the   sqlnet.ora file.
  2. The default location for the wallet is $ORACLE_BASE/admin/$ORACLE_SID/wallet.
Note: As a best practice it is always good to use a separate wallet for TDE

In our example we store wallet master key in /etc/ORACLE/WALLETS/SABA (ORACLE_SID)

Since /etc is owned by "root", these directories are created by "root" when done, change ownership to "oracle:oinstall" and set the permissions to "oracle" only:

 # cd /etc
# mkdir –p ORACLE/WALLETS/SABA
# chown –R oracle:oinstall ORACLE
# chmod –R 700 ORACLE

Set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora to the newly created directory:
 
ENCRYPTION_WALLET_LOCATION =

(SOURCE = (METHOD = FILE)

METHOD_DATA = (DIRECTORY = /etc/ORACLE/WALLETS/SABA)))

Set the master encryption key in the wallet.

alter system set encryption key identified by “ankrose”;

This creates a wallet at the location defined in the sqlnet.ora

Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data.

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "ankrose";

ALTER SYSTEM SET WALLET CLOSE;

After successful creation of the wallet and master key, reduce permissions on the wallet file from the initial value, determined by "umask" for the "oracle" user :

Now you could see ewallet.p12 under,

$ cd /etc/ORACLE/WALLETS/SABA
$ chmod 600 ewallet.p12

It is highly recommended to always backup the wallet at the same time when backing up your database, but do not include the wallet on the same media as the database backup. Also, backup the wallet before any manipulation of its content, whether performing a master key re-key operation, or changing the wallet password.

Auto-Open

The encrypted wallet ("ewallet.p12") offers strong protection of the master key, by encrypting the wallet with the wallet password. Opening the wallet is a manual operation and must be performed to make the master encryption key available to the database. Optionally, the master key can be copied into an "auto-open" wallet. This can be done either using Oracle Enterprise Manager, Oracle Wallet Manager or the "orapki" utility:

$ orapki wallet create –wallet -auto_login

This command creates an auto-open wallet ("cwallet.sso"). In order to significantly strengthen your security when using an auto-open wallet, a local auto-open wallet can be created, starting with Oracle Database 11.1.0.7; it does not open on any machine other than the one it was created on:

$ orapki wallet create –wallet -auto_login_local

It is not possible use a local auto-open wallet in Oracle RAC when the wallet is to be stored centrally in ACFS.

NOTE - Do not delete the original encryption wallet. Re-keying the master key requires the original encryption wallet to be present. When the master key is re-keyed, the corresponding (local) auto-open wallet is updated automatically.

Changing the Wallet Password

Starting with Oracle Database release 11.1.0.7, the "orapki" utility has been enhanced to enable wallet password changes from the command line:

$ orapki wallet change_pwd -wallet

How do I create wallet if I have multiple database on the server

If there are multiple Oracle Databases installed on the same server, they must access their own individual TDE wallet. Sharing the same wallet between independent instances is not supported and can potentially lead to a loss of the encrypted data.
If the databases share the same ORACLE_HOME, they also share the same sqlnet.ora file in $TNS_ADMIN. In order to access their individual wallet, the DIRECTORY entry for the ENCRYPTION_WALLET_LOCATION needs to point each database to its own wallet location:
DIRECTORY = /etc/ORACLE/WALLETS/$ORACLE_SID
The names of the subdirectories under /etc/ORACLE/WALLETS/ reflect the ORACLE_SID names of the individual databases.

If the databases do not share the same ORACLE_HOME, they will also have their individual sqlnet.ora files that have to point to the individual subdirectories.

Tablespace encryption (TDE)

CREATE TABLESPACE user_tde
DATAFILE '/u01/app/oracle/oradata/SABA/user_tde01.dbf'
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING '3DES168'
DEFAULT STORAGE (ENCRYPT);

You can use the following algorithms while creating an encrypted tablespace.
AES128
AES192
AED256
3DES168

If you don't specify any algorithm with the encryption clause it will use AES128 as default.

SELECT tablespace_name , encrypted
FROM dba_tablespaces
Where tablespace_name=’USER_TDE’;

TABLESPACE_NAME                         ENC
------------------------------                 ------
USER_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
---------------             ---------------              ----------
USER_TDE                   3DES168                      YES

Table column encryption

CREATE TABLE test (id NUMBER(10),card VARCHAR2(50) ENCRYPT)
TABLESPACE users;
 
insert into test (id, card) VALUES (1, 'encrypted');
commit;

Note : There is a performance overhead associated with the encryption/decryption process

Views

DBA_ENCRYPTED_COLUMNS views are used to display information about encrypted columns.

SET LINESIZE 100
COLUMN owner FORMAT A15
COLUMN tble_name FORMAT A15
COLUMN column_name FORMAT A15

SELECT * FROM dba_encrypted_columns;

OWNER          TABLE_NAME          COLUMN_NAME      ENCRYPTION_ALG         SAL
------------    ---------------------   ------------------------   ---------------------------  --------
ANTONY                TEST                            CARD                   AES 192 bits key              YES

1 rows selected.

Hope this document help !!!

No comments:

Post a Comment