Actu
Ulf Mattsson, Protegrity Corporation: How to encrypt indexed columns in Oracle
December 2008 by Ulf Mattsson, CTO, Protegrity Corporation
Indexes are created on a specific column or a set of columns. When the database table is selected, and WHERE conditions are provided, the database will typically use the indexes to locate the records, avoiding the need to do a full table scan. In many cases searching on an encrypted column will require the database to perform a full table scan regardless of whether an index exists. For this reason, encrypting a column that is part of an index is not recommended in general, but it can be useful in some situations if support for accelerated index-search on encrypted data is supported.
You can encrypt indexed columns in Oracle and also avoid table scans when searching on encrypted data, but plan carefully before encrypting information in indexed fields. Look-ups and searches in large databases may be seriously degraded by the computational overhead of decrypting the field contents each time searches are conducted if accelerated database indexes are not used. This can prove frustrating at first because most often administrators index the fields that must be encrypted – social security numbers or credit card numbers. New planning considerations are needed when determining what fields to index; if accelerated database indexes are not used.
Some advanced solutions enable encrypted data to be searched without the overhead of first decrypting into clear text. Only the result set is decrypted to clear text. Most vendor solutions force the data to be decrypted before being searched. An index search for an exact match of an encrypted value within a column is possible with several vendor solutions, provided that the same initialisation vector is used for the entire column. On the other hand, searching for partial matches on encrypted data within a database can be challenging and can result in full table scans if support for accelerated indexsearch on encrypted data is not used.
Additional tuning by adding an accelerated search index for encrypted columns reduced the response-time and the number of rows to decrypt, by a factor between 10 and 45 for some of the queries in some benchmarks on Oracle. Encrypted columns can be a primary key or part of a primary key, since the encryption of a piece of data is stable (i.e., it always produces the same result), and no two distinct pieces of data will produce the same cipher text, provided that the key and initialization vector used are consistent. However, when encrypting entire columns of an existing database, depending on the data migration method, database administrators might have to drop existing primary keys, as well as any other associated reference keys, and re-create them after the data is encrypted. For this reason, encrypting a column that is part of a primary key constraint is not recommended if support for accelerated index-search on encrypted data is not used. Since primary keys are automatically indexed there are also performance considerations, particularly if support for accelerated index-search on encrypted data is not used.
A foreign key constraint can be created on an encrypted column. However, special care must be taken during migration. In order to convert an existing table to one that holds encrypted data, all the tables with which it has constraints must first be identified. All referenced tables have to be converted accordingly. In certain cases, the referential constraints have to be temporarily disabled or dropped to allow proper migration of existing data. They can be re-enabled or recreated once the data for all the associated tables is encrypted. Due to this complexity, encrypting a column that is part of a foreign key constraint is not recommended, if automated deployment tools are not used. Unlike indexes and primary keys, though, encrypting foreign keys generally does not present a performance impact. Indexes are created to facilitate the search of a particular record or a set of records from a database table.
You may consider a solution that can accelerate search on encrypted columns on Oracle by utilizing the Oracle Domain Index (part of Oracle Extensible Indexing). I suggest that you review some additional resources on best practices for data encryption on Oracle:
http://www.developersdex.com/gurus/articles/881.asp,
http://www.seouc.com/Presentations/Best_Practices_Mattsson.pdf
http://hosteddocs.ittoolbox.com/UM070805.pdf,
