Enhancing Security - Expert Oracle and Java Security - page 283

To meet the requirements of encrypting data at rest, we need to be able to decrypt data at a different
time, in a different place by a different user. That said, it should be obvious that we cannot “negotiate” a
key or set of keys at the time they are needed. Somewhere we need to store the encryption key, and
retrieve it at some unspecified time in the future to decrypt the data.
The burden falls on the developer to decide where to store the encryption keys for use with
DBMS_CRYPTO . There are benefits of having the password stored right in the Oracle database with the
encrypted data. One benefit is that when we restore a backup of the encrypted data, we also restore the
decryption key. The risk here is that, if someone gets access to the encrypted data, they might also get
access to the decryption key.
One viable alternative would be to store the decryption key in the client application and to pass it to
Oracle database when we want to decrypt the data; however, that hardly seems more secure. No, we will
stick with storing the encryption key in the database, and we will take a couple steps to assure that it
can't be used by anyone outside our application. Before I tell you how I do it, think about how you would
do it.
Encryption at Rest Key Store
We will store our encryption/decryption keys, at least the genesis of the keys, in an ordinary Oracle table,
t_application_key —see Listing 11-15. It is possible we may want to store these keys on a separate Oracle
instance and acquire them across a DB link. In that way, the encrypted data and encryption keys will be
backed up separately. We will provide a version number column, in case we want more than one key per
database, possibly for a different application. You'll see why that's probably unnecessary. We also create
an index and a view, v_application_key , of this table.
 Note You will find this SQL script in a file named Chapter11/AppSec.sql .
Listing 11-15. Table of Encryption at Rest Keys
CREATE TABLE appsec.t_application_key
(
key_version NUMBER(3) NOT NULL,
-- Max Key size 1024 bits (128 Bytes)
key_bytes RAW(128) NOT NULL,
create_ts DATE DEFAULT SYSDATE
);
One assurance we want to make is that the key is never changed. We are going to do that with an on
update/delete trigger. This trigger, shown in Listing 11-16, will basically turn the attempted update
around and reject it.
Listing 11-16. Before Update/Insert Trigger on Table of Encryption at Rest Keys
CREATE OR REPLACE TRIGGER appsec.t_application_key_budr BEFORE UPDATE OR DELETE
ON appsec.t_application_key FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR (-20001,'Cannot UPDATE or DELETE Records in V_APPLICATION_KEY.');
END;