Methods for column encryption in Oracle

Methods for column encryption in Oracle 2011-12-22 17:21:13

Category: Linux

Oracle supports multiple column encryption methods:

1, transparent Data encryption (TDE):
CREATE TABLE Encrypt_col (id int,
TXT VARCHAR2 (+) encrypt using ' algorithm name ' identified by ' key ' no salt ';
Pros: Completely transparent to the application
Cons: Requires a database administrator to manage wallet, adds management complexity, and does not prevent the database administrator from viewing the original text.

2, using Dbms_crypto package
Advantage: As long as the application saves the key, the database administrator cannot see:

Test:
DECLARE
Input_string VARCHAR2 (+): = ' Secret Message ';
Output_string VARCHAR2 (200);
Encrypted_raw Raw (2000); --Stores encrypted binary text
Decrypted_raw Raw (2000); --Stores decrypted binary text
Num_key_bytes number: = 256/8; --key length of the bits (bytes)
Key_bytes_raw Raw (32); --Stores 256-bit encryption key
Encryption_type Pls_integer: =--Total Encryption type
Dbms_crypto. encrypt_aes256
+ Dbms_crypto. Chain_cbc
+ Dbms_crypto. PAD_PKCS5;
BEGIN
Dbms_output. Put_Line (' Original string: ' | | input_string);
Key_bytes_raw: = Dbms_crypto. Randombytes (num_key_bytes);
Dbms_output. Put_Line (' Key_bytes_raw: ' | | key_bytes_raw);

Encrypted_raw: = Dbms_crypto. ENCRYPT
(
src = utl_i18n. String_to_raw (input_string, ' Al32utf8 '),
Typ = Encryption_type,
Key = Key_bytes_raw
);
Dbms_output. Put_Line (' Encrypted string: ' | | encrypted_raw);

Decrypted_raw: = Dbms_crypto. DECRYPT
(
src = Encrypted_raw,
Typ = Encryption_type,
Key = Key_bytes_raw
);
Output_string: = utl_i18n. Raw_to_char (Decrypted_raw, ' Al32utf8 ');

Dbms_output. Put_Line (' decrypted string: ' | | output_string);
END;
/

==>
Original String:secret Message
Key_bytes_raw:2d9ab3be8cdfbcd942162d0f6c90956c503e432d5128be26116c39ba4dac6b96
Encrypted STRING:51E36E4576F951A940B671394B2FDC05
Decrypted String:secret Message

------------------
Transparent data encryption frequently asked Questions

What are the associated costs of TDE?
Encrypted table spaces do not require higher storage space than unencrypted tablespace.

The overhead associated with column-level TDE is divided into two areas: storage and performance. In terms of performance, the overhead associated with encrypting or decrypting common properties, such as credit card numbers, is estimated at 5%. When an index is built on an encrypted column, the creation of the index uses the ciphertext. If a TDE-encrypted column is indexed and referenced to an SQL statement, Oracle transparently encrypts the values used in the SQL statement and uses redaction to index the query. TDE-related storage overhead can be significant because each encrypted value has an additional 20-byte integrity check. In addition, TDE fills the encrypted value to 16 bytes, so if the credit card number requires 9 bytes of storage, encrypting the number will require an additional 7 bytes of storage space. Finally, if you specify a salt for the encrypted value, the salt will require an additional 16 bytes of storage space. In summary, a column in an encrypted table will require 33 to 48 bytes of extra storage per row.

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

Is the data still encrypted on the network?
Data that is encrypted with TDE is decrypted first before leaving the database. However, this data can be encrypted over the network via Oracle's network encryption solution, which is included with TDE in the Oracle Advanced Security option. Oracle's network encryption solution encrypts all data that is transferred on the Oracle Sql*net.

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

Can anyone decrypt the data as long as it is authorized by the application?
Yes, TDE is enabling customers to apply encryption within the database without affecting existing applications. Returning data in an encrypted format destroys an existing application. The advantage of TDE is that encryption does not incur the overhead of a traditional database encryption solution, and traditional scenarios require triggers and views.

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

How does TDE differ from the encryption methods provided by Oracle?
Oracle introduced the Dbms_obfuscation_toolkit through Oracle8i. Oracle 10g version 1 introduces a new Dbms_crypto package. The various APIs in these packages can be used to manually encrypt data in a database. However, this application must manage the encryption key and make the required cryptographic and decryption operations by invoking the API.

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

What encryption algorithms can be used with TDE?
TDE supports 3des168, AES128 (table space Encryption default algorithm), AES192 (column-level TDE default algorithm), and AES256.

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

Can a third-party encryption algorithm be used instead of the algorithm provided by TDE?
No, you cannot insert other cryptographic algorithms.

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

What is the licensing method for TDE?
TDE is packaged as part of the Oracle Advanced Security option, which is included in Oracle Enterprise Edition.

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

Can I use TDE on a column with a FOREIGN KEY constraint?
When using tablespace encryption, even if a table is outside the encrypted table space, the foreign key is encrypted in the same way as all other elements stored in the encrypted table space. Column-level TDE does not support encryption of columns used in foreign KEY constraints. This is because individual tables have their own special encryption keys.

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

Can I encrypt the columns used in a join?
OK. The join table is transparent to both the application and the user, even if the column for the join condition is encrypted.

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

What data types does TDE support?
Because table space encryption encrypts the entire tablespace, there is no limit on the supported data types.

The following data types can be encrypted using column-level TDE:

VARCHAR2 NVARCHAR2
Number Date
Binary_float binary_double
Timestamp RAW
Char nchar
Securefile lobs

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

When is column-level TDE or tablespace encryption used?
Determine the choice of a specific method based on the following guidelines:

Encrypt only data that needs to be encrypted for compliance reasons (Pci-dss's credit card and account number; used to notify the legal address, name and personal identification number (driver's license, ID) of a vulnerability similar to CA SB 1386), or to encrypt other key data that you believe, such as salary, research results, Customer information.
If you need to encrypt data in a foreign key column, or if you need an index other than the B-tree, or if you need to encrypt data types that are not supported by column-level TDE, you can choose Tablespace encryption.
If column-level TDE meets your security and safety requirements, you can choose from performance test results

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

Can the encryption key be changed?
A universal key for tablespace encryption cannot be regenerated. The workaround is to create a new encrypted tablespace, back up the current tablespace, transfer all tables and other content from the old tablespace to the new table space, and then delete the old encrypted table space.

Column-level TDE uses a two-layer key mechanism. When column-level TDE is applied to an existing application table column, a new table key (for all columns in the table) is created and stored in the Oracle data dictionary. The table key is encrypted with a universal key. The universal key is generated when TDE is initialized and stored in an Oracle wallet outside the database or in an HSM device that complies with PKCS#11. Both the universal key and the column key can be independently modified based on the company's security policy. Oracle recommends that you back up your wallet before and after you modify the Universal key.

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

Can I store a universal key for Tablespace encryption in an HSM device?
No. If you migrate from column encryption in 10GR2 to column and tablespace encryption in 11gR1, you must first regenerate the universal key on the 11GR1 database to automatically add a universal key for tablespace encryption to your software wallet, and then port the column Universal key to the HSM device.

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

Can I encrypt an existing table space?
No. The workaround is to create a new encrypted tablespace, back up the current tablespace, transfer all tables and other content from the old tablespace to the new table space, and then delete the old encrypted table space.

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

Can compression work with table space encryption?
Yes, the content is encrypted after it is compressed.

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

Can a transportable tablespace work in conjunction with an encrypted table space?
OK. The condition is that either the universal key must be available on the target database or, if a different universal key is used, the data is exported and imported using an Oracle data pump, which can be done through optional encryption of the dump file.

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

What is a wallet?
A wallet is a container for storing authentication and signing certificates, including the TDE Universal key, PKI private key, license, and trust certificate required for SSL. With TDE, you can use a wallet on the server to protect the TDE universal key. In addition, Oracle requires an entity that communicates on SSL to contain a wallet. In addition to Diffie-hellman, the wallet should contain a list of version 3 licenses, private keys, and trust certificates.

Oracle offers two types of wallets: Crypto wallets and auto-open wallets. We recommend encrypting the Wallet for TDE (file name is EWALLET.P12). The wallet needs to be opened manually before the database is started and when the TDE encrypted data is accessed. If the wallet is not open, the database returns an error when querying for data that is protected by TDE. The automatically opened wallet (filename is Cwallet.sso) opens automatically when the database starts. It is therefore suitable for unattended Data Guard environments where encrypted columns are routed to level two sites.

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

What is the Key management feature in TDE?
Key management in TDE includes the following features:

Generated
Store
Replace
Destroyed
Build-generating a universal key in TDE can be done either through the random number generator (RNG) in the database, or the administrator can provide a string of up to 70 characters on its own.

Storage-one option is to store the universal key in the Oracle software wallet. The file should be protected by an appropriate OS license. According to the PKCS#5 standard, the wallet is encrypted using the wallet password. The wallet password is not stored anywhere, and in Oracle Enterprise Manager 11g, the password can be split between any number of users or administrators who must enter their own password fragments in the correct order, while none of them knows the entire password. This password can even protect the universal key from being used by users with "root" privileges.
Another option is to store the TDE Universal key for column-level TDE on a hardware security module (HSM). Communication between Oracle Database 11g and HSM devices is based on the standard pkcs#11 interface, which enables many HSM vendors to interoperate with Oracle. Once a universal key is generated on the device, it will never leave the device without encryption.

Replace-A new TDE universal key can be created with a simple command. At the moment, all the column keys in the database are decrypted with the old universal key and then encrypted with the new Lemi key. There is no change to the application data, so the process does not affect the availability of your database. For backup and restore operations, the expired universal key is stored in the wallet.

Destroy-Use the command line utility "Mkstore" to destroy the Universal key. Make a backup of the wallet before using "Mkstore", and use the program with caution because it is easy to delete the current universal key (the key cannot be replaced or regenerated).

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

How do I protect the wallet and TDE universal keys?
Oracle database 11g allows the column-level TDE universal key to be stored on an HSM device that complies with the PKCS#11 standard, and the universal key never leaves the device unencrypted.

Access to software wallets (which are required for tablespace-encrypted and encrypted dump files and for backups created using a software-based universal key) should be limited by file-level licensing. The password that encrypts the wallet (based on pkcs#5) should contain more than 10 alphanumeric characters. Software wallet passwords can be modified by Oracle Wallet Manager. Changing the wallet password does not affect the TDE universal key (they are not related to each other). The wallet password can even block users who have "root" permission and access the wallet file to obtain the TDE universal key.

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

In which default location is Oracle Wallet Manager looking for a wallet?
In Unix and Linux, the location is

/etc/oracle/wallets/<oracle Software owner User name>
Typically converted to

/etc/oracle/wallets/oracle

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

Can I use Oracle Wallet Manager (OWM) to create crypto wallets and universal keys for TDE
No. If you use Oracle Wallet Manager to create an encrypted wallet, you cannot include the universal key required by TDE. Only the following SQL commands are available:

Sql> alter system set encryption key identified by "Wallet_password";
The ability to create a software wallet (if it does not exist) and add a universal key to it.

Sql> alter system set encryption key identified by "Userid:password";
The ability to create a universal key within an HSM device. The user ID and password are the certificates of the user created within the HSM device to support communication with the Oracle database. Prior to this, the HSM vendor's library would need to be placed in the archived directory, and the string "Method=file" would need to be changed to "METHOD=HSM" in Sqlnet.ora.

If you want to continue to use the same universal key after upgrading to an HSM device, use:

Sql> alter system set encryption key identified by "Userid:password"

Migrate using "Wallet_password";

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

How do I create an auto-open wallet?
The Auto-open wallet (' Cwallet.sso ') needs to be created with an existing crypto wallet (' ewallet.p12 ') so that the universal key can be used in the auto open wallet.

You can open the Crypto wallet in Oracle Wallet Manager (OWM), select the "Auto Login" check box, and then select "Save" to write the automatically opened wallet to disk, or use the command line tool "Orapki":

Orapki Wallet Create-wallet <wallet_location>-auto_login
In both cases, a wallet password is required.

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

When using Oracle secure backup, how do you avoid backing up the Oracle TDE wallet to the tape on which the RMAN database backup resides?
RMAN adds database files, redo logs, and so on to the backup file, so the wallet that is encrypted or automatically opened will not be part of the database backup. Oracle Secure Backup (OSB) uses datasets to define the operating system files to be backed up. The OSB automatically excludes the automatically opened wallet (' Cwallet.sso '). The Crypto wallet (' EWALLET.P12 ') will not be automatically excluded. You need to use the Exclude DataSet statement to specify the files that need to be skipped during the backup process:

Exclude Name EWALLET.P12
For a detailed description of the EXCLUDE keyword

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

What is Oracle secure Backup (OSB)?
OSB provides an optimized, efficient tape backup solution for Oracle databases. OSB can store data on tape in an encrypted format, preventing backup tapes from being stolen.

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

Can I encrypt a backup sent to disk using an Oracle secure backup?
No. However, Oracle RMAN can be used with Oracle Advanced Security to encrypt database backups on disk.

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

Can TDE work together with Data Guard and Oracle RAC?
OK. TDE can work with Data Guard (physical or logical backups) and real application clusters (RAC), but only if you have to copy the wallet that contains the universal key to another instance and open the wallets on each instance so that they can use the universal key.

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

Are there other database features that cannot work with column-level TDE?
When data passes through the SQL layer, column-level TDE transparently encrypts and decrypts the data. Some features of Oracle skip the SQL layer and therefore cannot take advantage of TDE:

Materialized View Logs
Sync. and async. CDC (change data capture)
Classical lobs (Securefile lobs
is supported)

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

Can I use a sql*loader with a direct path to load data into a table containing encrypted columns?
OK. There will be no changes to the Sql*loader syntax,. dat files, and. ctl files. If the target table contains encrypted columns, the data is transparently encrypted at load time. The following simple example shows how to use a sql*loader with a direct path. Simply drag a line from the Ulcase6.sql

Sal Number (7,2),
Change to

Sal Number (7,2) encrypt no salt,
and use the correct syntax for Sql*loader:

Sqlldr Userid=scott/tiger control=ulcase6.ctl Log=ulcase6.log direct=true

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

How do I recover data after I regenerate the universal key and/or column key?

The universal key has changed since the backup was created until it is restored.
In this case, recovering the table that contains the encrypted column is transparent to the user. The TDE Wallet records the history of the expired universal key and automatically applies the correct universal key to the backup data. That's why it's so important to back up your wallet often (back up to a different location than your encrypted data).
The Lemi key for all or part of the encrypted column has been regenerated after the backup has been created until it is restored:
If you have used Rman to create a backup file on disk, the file will not be encrypted unless you instruct Rman to perform an encryption operation. When the file is read back, its columns are re-encrypted with the new Lemi key, regardless of whether RMAN has encrypted the file.

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

How do I encrypt a column in a very large table (containing billions of rows)?
Encrypting a column in an existing table allows only Read access to the table and does not allow DML operations. With billions of rows, this limited availability window will last for a long time. Online redefinition only requires a window with a very short duration to lock the original table. The length of time is independent of the number of encrypted data or redefined complexity, and is transparent to users and applications.

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

When you encrypt existing data in a column, you view the database file, and sometimes you still see some clear text values. Why?
This is the same situation as if the table or file was deleted, but the data is still visible on disk. During the life cycle of a table, data may be segmented, re-organized, sorted, copied, and moved within the tablespace. This leaves the "virtual copy" of the data in the database file. When you encrypt an existing column, only the most recent valid copy is encrypted, leaving the old plaintext version in the virtual copy. If you access data files that contain tablespace directly, bypassing access control for the database (for example, a database with a hex editor), you can sometimes see old plaintext values before those blocks are overwritten by the database. To minimize risk, follow the recommendations below:

Create a new table space in the new data file (Create Tablespace ...)
Encrypt the plaintext values in the original tablespace and data files (ALTER table ...). ENCRYPT)
Perform step 2 on all tables that contain encrypted columns
Move all the tables in the original table space to the new data file (ALTER table ...). MOVE ...)
Delete Original tablespace (drop tablespace) do not use the "and datafiles" parameter, Oracle recommends using a more powerful method for OS-level operations, see step 6
Use "Shred", "Eraser", "SDelete", or other commands for your platform to delete old data files at the OS level

It is recommended that you use step 6th to reduce the probability of virtual copies of database files, whether they are generated by the operating system or by the storage firmware.

Methods for column encryption in Oracle