Build a flexible infrastructure to protect sensitive data.

John, the lead DBA at Acme Bank, is involved in a very important initiative regarding security and privacy. Jane, the chief information security officer of the company, has outlined Acme's security strategy, and John has identified his team's responsibilities.

Enterprise security, Jane explains at a meeting with IT managers at Acme, can be thought of as a series of protective layers. To illustrate her point, Jane uses a nesting doll—a doll with a hollow body inside which another hollow doll is kept, which, in turn, contains another hollow doll, and so on. The last of four or five of these hollow dolls contains some type of prize. Getting to the prize means removing the layers of the dolls one by one, and if the layers can't be removed for some reason, the prize becomes increasingly difficult to get to. To get to corporate information assets, Jane explains, an intruder must also defeat many layers of security.

The first layer of defense is the firewall around the whole information infrastructure of the organization, which keeps outsiders from accessing any of the information sources inside the company. However, no organization is an island and firewalls are far from airtight; "holes" or ports are necessary to let legitimate traffic from outside flow in.

If an intruder gets past the external firewall, that person will be required to supply a password to access the server or perhaps be asked to provide other authentication credentials such as security certificates. This is the next layer of security. After being authenticated, the legitimate user must be allowed to access only those assets that person is supposed to access. If a user gets into the database but has no authority to see any table, view, or any other data source, the information is still protected. This mechanism is the next layer of security.

Jane stresses that it is possible for an intruder to somehow defeat all of the protective measures and get to the enterprise data. From a planning perspective, this possibility must be accepted, analyzed, and accounted for. The only option left for defending against an intruder at this point, the last layer of security, is to alter the data, via a process known as encryption, in such a way that the intruder will not find it useful. Encryption alters data to make it unreadable to all except those who know how to decipher the information.

Database Encryption

When John leaves the IT managers' meeting, he immediately calls together his direct reports to talk about his team's encryption strategy and implementation.

He presents his take on the encryption strategy to his team, beginning with a brief overview of the encryption process. He presents a simple example in which the account balance value is altered by the addition of a secret one-digit number. If the secret number is 2, for example, and the real balance value is 3467, the encrypted value will be 3469. The real value can be deciphered from the encrypted value by the deduction of the number 2, a process known as decryption, John explains. This logic of adding a specific number to the real data is called the encryption algorithm. Here, the value 2, which is added by the algorithm, is known as the encryption key. Encrypting a value involves passing the original data and the encryption key to the encryption algorithm to create encrypted data, as shown in Figure 1.

Figure 1: Encryption mechanism

During decryption, the logic is reversed, producing the original value. Because the same key is used to encrypt and decrypt, this scheme is also known as symmetric encryption.

Encryption algorithms are most often in the public domain; hence, the security lies in choosing a difficult-to-guess key. If hackers were to guess the 1-digit key, in this example, they would have to take only as many as 10 guesses—a number from 0 to 9. However, if the key were two digits, they would have to take as many as 100 guesses—a number from 0 to 99. The longer the key, John explains, the more difficult it is to guess it.

Oracle-Supplied Packages

In Oracle Database 10g, John continues, users can implement these encryption techniques by using functions and procedures available in a built-in package named DBMS_CRYPTO. Another package, DBMS_OBFUSCATION_TOOLKIT, also available in Oracle Database 10g and earlier releases, offers a subset of the functionality provided by DBMS_CRYPTO. But because Acme Bank's systems are built on Oracle Database 10g, the newer package offers more functionality, and Oracle recommends it over the older toolkit, John decides to use the DBMS_CRYPTO package, and no one in the room disagrees.

Key Generation. Because the security of the encrypted value depends on how hard it is to guess the key, using an appropriate key is a major step in the encryption process. A key can be any value of data type RAW, but unless it is random enough, an intruder will be able to guess the key. For instance, John warns, the key can't be something such as your pet's name or your date of birth; it must be truly random. "How do you generate such a random key?" asks one junior DBA. John answers that random values can be generated with the built-in package DBMS_RANDOM, but true cryptographically acceptable randomness means using the function RANDOMBYTES in the package DBMS_CRYPTO. This function accepts one parameter of data type BINARY_INTEGER and produces a RAW value of that length. This value can then be used as a key. John demonstrates the usage with a simple PL/SQL routine, shown in Listing 1.

Code Listing 1: Encrypting a value

  1  declare
  2     enc_val   raw (2000);
  3     l_key     raw (2000);
  4     l_key_len number := 128;
  5     l_mod     number := dbms_crypto.ENCRYPT_AES128
  6                       + dbms_crypto.CHAIN_CBC
  7                       + dbms_crypto.PAD_PKCS5;
  8  begin
  9     l_key   := dbms_crypto.randombytes (l_key_len);
 10     enc_val := dbms_crypto.encrypt
 11        (
 12           UTL_I18N.STRING_TO_RAW ('SECRET', 'AL32UTF8'),
 13           l_mod,
 14           l_key
 15        );
 16      -- The encrypted value enc_val can be used here
 17  end;   

Explaining the code, John points out that the DBMS_CRYPTO package offers several types of algorithms and associated key lengths for Acme's encryption project (see Table 1).

Table 1: Constants for algorithms in DBMS_CRYPTO

The first column in Table 1—Constant Name—shows the constant defined in the package to specify the different algorithms and the key length. For instance, to specify a 128-bit key according to the Advanced Encryption Standard (AES), you use the constant DBMS_CRYPTO.ENCRYPT_AES128, John explains (see line 5 of Listing 1). The longer the key, the less the chance that an intruder will be able to guess it but the more work the server has to do during encryption and decryption. To strike a balance between security and stress on the server, John chooses the middle ground—a 128-bit-key AES algorithm.

Next, the type of chaining, which divides the data into chunks to prepare for encryption in block ciphering, is defined, as shown in Listing 1, line 6. The most common format is Cipher Block Chaining (CBC), specified by a constant defined in the DBMS_CRYPTO package as CHAIN_CBC. Other chaining options include Electronic Code Book format (CHAIN_ECB), Cyber Feedback (CHAIN_CFB), and Output Feedback (CHAIN_OFB).

Finally, in block ciphering, John explains, the data is usually encrypted in blocks of eight characters. If the length of the input data is not a multiple of eight, you add a character or characters, in a process known as padding. A simple option is to use zeroes as padding. John points out that the constant PAD_ZERO defined in the DBMS_CRYPTO package pads with zeroes but that padding with zeroes is not considered very secure, because a potential intruder might be able to guess that. More-secure padding is based on Public-Key Cryptography Standards # 5 (PCKS#5), specified by the constant PKCS5 in the DBMS_CRYPTO package and demonstrated in Listing 1, line 7. If you're sure, John comments, that the length of the data is already a multiple of the block size, there is no need to pad, and you can specify that by using the constant PAD_NONE.

These three parameters—the algorithm with the key length, the chaining method, and the padding method—are all combined and passed to the built-in function ENCRYPT in DBMS_CRYPTO. The ENCRYPT function needs this input value to be a RAW data type. Listing 1, line 12 converts the input value to a RAW value, which is then passed to the ENCRYPT function.

As a means of standardization, John continues, Acme has made a decision to adopt the AES algorithm with 128-bit keys, CBC chaining, and PCKS #5 padding across all applications. Using these values, John builds a simpler function GET_ENC_VAL, shown in Listing 2, that accepts only two parameters—the input value and the key—and returns the encrypted value.

Code Listing 2: A simple encryption function

  1  create or replace function get_enc_val
  2  (
  3     p_in    in varchar2,
  4     p_key   in raw
  5  )
  6  return raw is
  7     l_enc_val raw (2000);
  8     l_mod     number := dbms_crypto.ENCRYPT_AES128
  9                      + dbms_crypto.CHAIN_CBC
 10                      + dbms_crypto.PAD_PKCS5;
 11  begin
 12     l_enc_val := dbms_crypto.encrypt
 13        (
 14           UTL_I18N.STRING_TO_RAW
 15              (p_in, 'AL32UTF8'),
 16           l_mod,
 17           p_key
 18        );
 19     return l_enc_val;
 20* end;

Decryption

"When the time comes to decipher the encoded data, how do we do it?" asks Jill, one of John's developers.

John explains that DBMS_CRYPTO has a function for that too: DECRYPT. It accepts the source data to be decrypted; the key used during encryption; and the combined parameter of algorithm, key length, chaining, and padding schemes. The same key and the modifiers used during encryption must be passed along with the value to be decrypted. Because Acme Bank uses a standard algorithm, key length, and padding, John creates a simple function to decrypt encrypted values, as shown in Listing 3. This function accepts only two parameters—the encrypted value and the key—and returns the decrypted value as a VARCHAR2 data type. The conversion from the RAW data type is done in line 20 of Listing 3.

Code Listing 3: A simple decryption function

  1  create or replace function get_dec_val
  2  (
  3     p_in    in raw,
  4     p_key   in raw
  5  )
  6  return varchar2
  7  is
  8     l_ret     varchar2 (2000);
  9     l_dec_val raw (2000);
 10     l_mod     number := dbms_crypto.ENCRYPT_AES128
 11                       + dbms_crypto.CHAIN_CBC
 12                       + dbms_crypto.PAD_PKCS5;
 13  begin
 14     l_dec_val := dbms_crypto.decrypt
 15        (
 16           p_in,
 17           l_mod,
 18           p_key
 19        );
 20        l_ret:= UTL_I18N.RAW_TO_CHAR
 21                     (l_dec_val, 'AL32UTF8');
 22             return l_ret;
 23* end;

Key Management

With the building blocks in place, John's team is looking for a complete encryption solution built on the DBMS_CRYPTO package. The biggest challenge in encryption, John explains, is not generating keys or using the functions but managing the keys used in the encryption process. Because the same key is used to encrypt and decrypt a value, it must be reliably guarded to protect the data. At the same time, however, the applications and users must have access to the keys to decrypt the values for normal use. The challenge is figuring out where to store the keys and how to make sure they are available to legitimate users only, John explains. He lays out two options for managing keys:

1. Use the same key for all records
2. Use a different key for each record

With option 1, John continues, a single key is used to encrypt the value in all the rows. In this case, there are several options for storing the key:

• In the database—A key table owned by a special non-application owner can be used to store the key. John writes a simple function that merely returns the key as an output parameter. The users receive execute privileges in this procedure, and no user has any privileges on the key table. The function contains several checks and balances to make sure users have the proper privileges to get the key. Because the function is the only source for getting the key, users can be authenticated easily and given access to the key.
  
• In the filesystem—Keeping the key in the database protects from most intruders but not from DBAs who may have access to any table. In addition, making sure users making requests are indeed legitimate can be very difficult. Storing the key in a filesystem DBAs don't have access to, even on a different server such as an application server, may be a better idea. However, this also means that if the key is somehow lost because filesystems are damaged, the encrypted data is lost as well, forever.
  
• With the user—A third option, John shows, can be to let users keep the key somewhere, such as on a memory stick or a client machine. This way, no one other than legitimate users can access the sensitive data. This is particularly useful in situations such as in data warehouses where encrypted data is sent regularly to users who have the key already. If the data is stolen along the way, the sensitive information is still protected. However, the risk of data loss is highest here, because users are more likely to lose the key.

John proposes the "With the user" single-key solution for a small number of cases, such as publishing summarized content to various users who have received the keys earlier.

The biggest drawback of this approach is the vulnerability of the key to theft. If the key is stolen, all the data in the database is compromised. Therefore, John suggests a different approach to protecting sensitive data in OLTP databases. The database has a table named ACCOUNT_MASTER, where a sensitive data element, the account holder's name, is stored. The column containing the name, ACC_NAME, needs to be encrypted. The primary key of the table is ACCOUNT_NO. Here is what the ACCOUNT_MASTER table looks like:

SQL> desc account_master

Name        Null?     Type
----------  --------  ------------
ACCOUNT_NO  NOT NULL  NUMBER
ACC_NAME              VARCHAR2(200)
ACC_TYPE              CHAR(1)

John suggests using a different key for each row of the ACCOUNT_MASTER table, which eliminates the risk of databasewide exposure in case of a key theft. He creates a table called ACCOUNT_MASTER_ENC to store the encrypted values of the account name and creates another table to hold the keys used to encrypt the values. These tables—ACCOUNT_MASTER_ENC and ACCOUNT_MASTER_KEYS—look like this:

SQL> desc account_master_enc

Name           Null?     Type
------------   --------  --------    
ACCOUNT_NO     NOT NULL  NUMBER
ACC_NAME_ENC             RAW(2000)
 
SQL> desc account_master_keys

 Name       Null?     Type
----------  --------  ---------  
ACCOUNT_NO  NOT NULL  NUMBER
KEY         NOT NULL  RAW(2000)

Next John creates the view VW_ACCOUNT_MASTER, shown in Listing 4, to join these three tables to get the decrypted value. He points out line 8 in Listing 4, where the value is decrypted with the function GET_DEC_VAL mentioned earlier. Because the function returns a value as a VARCHAR2 data type, it will be shown as a VARCHAR2(2000) column; hence, line 7 has a CAST function to make it a VARCHAR2(20) type.

This view, not the table, is what is granted to the other users. John creates a public synonym ACCOUNT_MASTER pointing to the view VW_ACCCOUNT_MASTER, not to the table of the same name.

Code Listing 4: View for account master

  1  create or replace view
  2     vw_account_master
  3  as
  4  select
  5     m.account_no as account_no,
  6     m.acc_type as acc_type,
  7     cast (
  8             get_dec_val (e.acc_name_enc, k.key)
  9     as varchar2(20)) as acc_name
 10  from
 11     account_master m,
 12     account_master_enc e,
 13     account_master_keys k
 14  where
 15     k.account_no = e.account_no
 16*    and m.account_no = e.account_no; 

Because the public synonym ACCOUNT_MASTER points to the view, users can select from the view, but Jill the developer asks how the users will manipulate the data in the table. By using an INSTEAD OF trigger (shown in Listing 5), explains John. This trigger manipulates the data in the table whenever a user inserts or updates the view. The INSTEAD OF trigger fires when the data is either inserted or updated in the view, which, in turn, does an INSERT or UPDATE on the actual tables. While updating, it makes sure the encrypted values are also updated.

Code Listing 5: INSTEAD OF trigger on the view

  1  create or replace trigger io_vw_acc_master
  2  instead of insert or update on
  3  vw_account_master
  4  for each row
  5  declare
  6     l_key    raw(2000);
  7  begin
  8     if (inserting) then
  9        l_key := dbms_crypto.randombytes (128);
 10        insert into account_master
 11        (account_no, acc_type, acc_name)
 12        values
 13        (
 14           :new.account_no,
 15           :new.acc_type,
 16           :new.acc_name
 17        );
 18        insert into account_master_enc
 19        (account_no, acc_name_enc)
 20        values
 21        (
 22           :new.account_no,
 23           get_enc_val (
 24              :new.acc_name,
 25              l_key )
 26        );
 27        insert into account_master_keys
 28        (account_no, key)
 29        values
 30        (
 31           :new.account_no,
 32           l_key
 33        );
 34     else
 35        select key
 36        into l_key
 37        from account_master_keys
 38        where account_no = :new.account_no;
 39        update account_master
 40        set acc_name = :new.acc_name,
 41           acc_type = :new.acc_type
 42        where account_no = :new.account_no;
 43        update account_master_enc
 44        set acc_name_enc =
 45           get_enc_val (:new.acc_name, l_key)
 46        where account_no = :new.account_no;
 47     end if;
 48* end;

Because the users can't see the data in the table but only through the view, the information is protected. A different view can be built on the base table to show only encrypted values, protecting the original contents.