Applied Oracle Security: Developing Secure Database and Middleware Environments- P8:Computer security is a field of study that continues to undergo significant changes at an extremely fast pace. As a result of research combined with increases in computing capacity, computer security has reached what many consider to be “early adulthood.” From advances in encryption and encryption devices to identity management and enterprise auditing, the computer security field is as vast and complex as it is sophisticated and powerful | 44 Part I Oracle Database Security New Features TDE Caveats Column-level TDE encrypts and decrypts data at the SQL level making it transparent to the end user. As a result several database features that access data at the kernel level are incompatible with column-level TDE. Using change data capture both synchronous and asynchronous streams in 10g materialized views transportable tablespaces and LOBs are not possible if they include objects containing encrypted columns. These limitations disappear with tablespace encryption available in 11g as you will see in the next section. Another potential drawback in TDE s column-based encryption is that it cannot be used in certain types of indexes or in the definition of a primary key foreign key PK FK relationship. Indexes built on encrypted columns are created using the encrypted values stored in the column. As a result use of an index is possible for an equality match provided that the encryption was defined using the no salt option. The index will not be used however in queries that do a range scan. In fact you will not be allowed to create an index on a column encrypted with the salt directive failing with ORA-28338 cannot encrypt indexed column s with salt . In the case of PK FK relationships since each table is encrypted using its own table-level key the keys are different among tables and therefore the encrypted values for similar strings are different. In this process the relationship that existed between the data is lost without first decrypting the column. While many will argue that natural keys those that are managed outside of the application such as SSNs employee IDs or stock numbers are not good candidates as primary keys for establishing uniqueness in a table the fact is they are and have been used as primary keys and are often found in applications that have over time been ported from other database environments. As such dealing with PK FK relationships in application that require the protection offered by
