Top 6 Security Questions for a PL/SQL Developer Job Interview

Security, in general, is big business these days. You need only look at the big database players to see an increase in effort and money being dedicated to acquiring various security vendors to fill various niches. Yes, as users of databases, we have all 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. Application security, like database security, is only as good as the developer and underlying database or feature set, as developers can easily introduce bugs or bugs can exist within the internal database code itself. Staying on top of current trends and understanding the impact of using a feature are at the core of securing the database.

One security topic, Encryption within Oracle, often comes up in PL/SQL Developer interviews. Hopefully you can take this article and use it as a starting place for your investigations and practice for your next interview.

1. What is Encryption?

This seems like a very simple question, and it is. Encryption is nothing more than a way to transform what would normally be legible/recognizable data into an unreadable format through the use of a secret key and encryption algorithm. It is extremely important in today’s environments, to encrypt data such as social security numbers, credit card numbers, any account number, passwords, health information, as well as just general information you don’t want various people to see because of corporate structure.

2. What is Network Encryption?

Network encryption is nothing more than ensuring data that travels through a network and between client and server is encrypted; helping reduce the possibility that a network packet sniffer has been put in place to capture and steal information.

3. What is Transparent Data Encryption?

Transparent Data Encryption refers to the ability to encrypt specific table columns or a tablespace; relying on the database to automatically encrypt data as it is modified within an object. As users select column data that has been encrypted this way it is then decrypted; making this a very easy way to implement encryption and is quite transparent for user and performance aspects.

Clearly, you can see that this type of encryption relies heavily on application security as well as database security since decryption automatically happens if given access to a tool or application that can simply SELECT and access information from the table. It does however provide a nice, and easy, encryption method that will help protect data if an attack is made that circumvents traditional database access control mechanisms. Creating a table that has a column encrypted is as easy as using the ENCRYPT keyword:

CREATE TABLE scott.emp_ssn
( empno NUMBER(9), ssn NUMBER(9) ENCRYPT USING 'AES256');

4. You get the Oracle error “ORA-28365: wallet is not open”, what is wrong and how to fix?

Obviously, the wallet is not opened and you must open one but let’s take this up a notch. A wallet is where Oracle stores the master encryption key, outside the database and in an operating system file. A wallet can be created with a series of commands such as the following:

a. Add to the end of the $ORACLE_HOME/network/admin/sqlnet.ora file.

		ENCRYPTION_WALLET_LOCATION=
		(SOURCE=
		(METHOD=file)
		(METHOD_DATA=
		(DIRECTORY=/opt/app/oracle/product/11.2.0/dbhome_1/ora_wallets)))

b. As the oracle user, create the wallet directory.

		[oracle@wagner dbhome_1]$ mkdir ora_wallets

c. Make sure the compatible parameter is greater than 10.2

d. As sys, or high ranking user with admin privileges, set an encryption password for the wallet.

		ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "<some-password>";

5. Any design limitations on encrypted columns?

Developers are often asked to design data models and this can be a big issue if you are not aware of it. Encrypted columns cannot be used as primary keys or used in a foreign key relation. Consider the following example and the error given.

SQL> CREATE TABLE scott.emp_ssn (
empno NUMBER(9) ENCRYPT USING 'AES256',
ssn NUMBER(9) ENCRYPT USING 'AES256');
Table created.
SQL> ALTER TABLE scott.emp_ssn ADD PRIMARY KEY (empno);
2 ALTER TABLE scott.emp_ssn
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt

However, for objects created in tablespaces that have encryption enabled, the ability to add constraints such as a primary key are not restricted; clearly giving data modelers an added advantage to using encrypted tablespaces.

SQL> CREATE TABLESPACE encrypts
DATAFILE '/opt/app/oracle/oradata/vm11r2/encryptts01.dbf' SIZE 100M
ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLE scott.emp_ssn (
empno NUMBER(9),
ssn NUMBER(9))
TABLESPACE encryptts;
Table created.
SQL> ALTER TABLE scott.emp_ssn ADD PRIMARY KEY (empno); 
Table altered.
SQL> select dts.tablespace_name, dts.encrypted
from dba_tables dt, dba_tablespaces dts
where dt.table_name = 'EMP_SSN'
and dt.tablespace_name = dts.tablespace_name;
TABLESPACE_NAME ENC
------------------------------ ---
ENCRYPTTS YES

6. Compare and contrast Transparent Data Encryption (TDE) with Transparent Tablespace Encryption (TTE). What about performance?

By the above two questions we can easily see that TDE disallows constraints such as primary keys and foreign key relations, TTE is a better choice when constraints are involved. By encrypting and decrypting one could almost guess that there would be some form of performance hit to the database. After all, there are extra CPU cycles needed to encrypt before INSERTing into a table and extra CPU cycles needed to decrypt when SELECTing. Moreover, you would be right. Actually, TDE also poses some additional performance issues that are negated by TTE; the biggest being that with TTE, after reading encrypted data, it is decrypted and resides in the SGA as clear text, allowing index scans to operate more efficiently and requiring decryption to happen only once as long as decrypted data remains in SGA.

I hope that this article will get you started down the road to understanding encryption, at least the easiest encryption method within Oracle. You should be able to at least talk briefly about what encryption is and how TDE & TTE work. Just remember that TDE and TTE by nature only protect data from outside the database. As soon as access privileges are granted, through either database authorization or application, a user can very quickly and easily see the information. Clearly, a much more stringent method of encryption may be needed for your environment.

As an added exercise, not covered in this article, I would also start investigating what third-party security products are available and how they might improve the security of both database and application. You just might be surprised at how easily they accomplish their goals and plug up vulnerabilities within a database environment that might take months of coding.

» See All Articles by Columnist James Koopmann