I am a DBA (13 years). My task is to encrypt data in an Oracle database. I have read a lot on Oracle's obfuscation toolkit (9i). I have seen sample procedures. I am confused about a few things. I assume that triggers are used to execute the encrypt/decript code. How do I determine what the size, of the column to be encrypted, should be? I know that it must be a multiple of 8. What is the difference in column size when using 56-bit and 128-bit encryption? Is it possible to use indexes containing encrypted data? How so? Where can I find information on how to tune when using Oracle's OBTK?
Triggers will only help in encrypting data, no decrypting. To decrypt, you can store the logic in functions, procedures, or in the application code itself. In any case, you want to take great care of your encryption keys. I have a white paper titled "DBMS_OBFUSCATION_TOOLKIT Key Security" that addresses this issue on my Web site.
It is possible to index an encrypted column. I've done that very thing at my shop. But I had to do one of two things, either search for the encrypted form of the value, or write a function based index on that column and use a decrypt function in the index to match the decrypt function in my SQL statement.
Tuning SQL statements that use the OBTK is no different than tuning other SQL statements. The same principles apply.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.