Security, is big business these days, putting additional stress on DBAs and developers to develop methods of securing corporate information resources. In the past, we have all mostly been concerned with security to some extent; providing and securing logons, becoming more careful with granting permissions and even limiting access to production servers seems to be the norm now (finally), to name only a few scenarios. But as I've been privy to a few PL/SQL Developer interviews of late, one thing keeps coming up, that falls under security, that I thought I'd just dedicate this article to, that being one form of encryption within Oracle.

In the last security related article, I focused on some questions that related to Oracle's transparent encryption, Transparent Data Encryption (TDE) and Transparent Tablespace Encryption (TTE). As we saw, this type of encryption, TDE and TTE, by nature only protects data from outside the database and as soon as access privileges are granted, either through database authorization or through application, a user can very quickly and easily see the information. In this series of questions, we will take a little different route and briefly expose what user defined encryption is all about. Take this article, use it as a starting place for your investigations, and practice for your next interview.

1. What would you consider an algorithm to be in regards to encryption?

An algorithm, often called a cipher, is the method, code, logic, or means, by which we alter the value of sensitive information.

2. What is a key in regards to encryption?

A key is the known piece of special knowledge, or unknown to those trying to break the encryption, that is used in conjunction with an algorithm to uniquely encrypt information. Stated another way, the key is a special piece of information, parameter, value, etc. that is used in the algorithm to help produce the output of the algorithm, the encrypted information.

3. Algorithms are commonly and openly available on the Net. How can this be secure if everyone has access to the algorithms we use to encrypt our data?

Obviously, the code itself that performs the encryption is not sacred, what is sacred is the key we choose, the encryption key, which determines how secure our encryption process is. For this reason, selection of a key and key management is of great importance. If someone is able to guess our key or find our key, they will then have a head start on decrypting our information.

4. What is the difference between a public key and a private key?

Very basically, usage of a private key requires a secured method to send a single key that is used both for encryption and decryption. Usage of a public key, on the other hand, instead uses a generated related key pair by the receiver of information where the public key is sent to the sender of information and used for encryption but then the private key is used for decryption on the receiver side.

5. Key management is of great concern. Where might a key be stored for security purposes?

Keys may be stored within the database, out on the file system, or somewhere that is controlled by an end user. Storing the key within the database has some advantages that include ease of access for SQL or PL/SQL codes as well as some inherent security reasons as it is hidden from intruders until database access rights are broken. Storing the key on the file system allows us to disassociate database with key but now brings up both system and database security issues depending on who and how system and access to the key file is managed from within the database. Letting end users keep their own keys and control usage, while taking the burden off DBAs and system administrators poses a completely new set of issues that we normally associate with stolen or misplaced keys.

6. What is the DBMS_CRYPTO package?

DBMS_CRYPTO is a package supplied by Oracle that contains cryptographic functions and procedures that aid in the encryption and decryption process. While it is not feasible for me to present an example or all the functions or procedures, you should at least understand what the main components are of this package. Therefore, DBMS_CRYPTO:

• is installed within the SYS schema
• contains hash functions
• contains message authentication code functions
• contains encryption algorithms
• contains block cipher suites
• contains block cipher chaining modifiers
• contains block cipher padding modifiers

7. How would you generate a key with the DBMS_CRYPTO package?

Here is a bit of a trick question. The DBMS_CRYPTO package actually does not generate keys, nor does it do any management of your keys. DBMS_CRYPTO does allow you to use some of its functions to aid in the generation of key material but does not generate them explicitly.

I hope that this article will take you a little bit farther down the security road and help you with your next PL/SQL interview. I've attempted to introduce a few of the main concepts and ideas that you should know when creating your own encryption tactics; giving sort of a lay of the land where you can at least begin to understanding user-based encryption. This is much different from what was introduced in the last article, TDE & TTE, and requires a lot more work planning and executing, specifically key management. As always take these areas and crack open some Oracle documentation, search on the key words, and begin reading on the concepts and implementation tactics. There are nice examples out there that should be examined, especially if you haven't setup any user defined encryption before.

As was mentioned in the last article, I would highly recommend investigating third-party security products that are available on the market. While encryption is quite powerful, a security product on top of encryption can add an extra layer of security to help eliminate theft or malicious intent.

Related Articles

Addressing Security in Your Next PL/SQL Developer Job Interview
Top 10 PL/SQL Developer Job Interview Questions to Demonstrate Your Coding Skill
Top 5 SQL Questions for an Oracle Database PL/SQL Job Interview
Oracle Technical Interview
Oracle Technical Interview Questions Answered - Part 1
Oracle Technical Interview Questions Answered - Part 2

Back to DBAsupport.com