Manage Oracle Encryption Keys for ISMS Compliance

Earlier I covered managing encryption keys on the WAS with a JAR module. Here’s how to manage keys when encryption and decryption happen inside procedures called by the Oracle Scheduler.

Design

• Store the encryption key encrypted in a separate file on the server.
• Have Oracle read the file and load the encryption key.
• Use a package function to decrypt that value and return the encryption key.
• Wrap the package so only the key administrator can see the source.
• Wrap every procedure that calls that function.

Store the encryption key

Encrypt the encryption key and save it to a file on the server. Create the folder as the Oracle install account, or create it as root and grant folder permissions to the Oracle account so Oracle can access it.

Create the directory and file:

mkdir /home/oracle/enc_key
vi /home/oracle/enc_key/enc_key.txt

Enter:

ED6CF66DAF90E7D9C6935E1B47643210791603D8B6745F0E31B2C14CFA31A3C7

cat /home/oracle/enc_key/enc_key.txt
ED6CF66DAF90E7D9C6935E1B47643210791603D8B6745F0E31B2C14CFA31A3C7

Read the file with UTL_FILE

UTL_FILE is a package that provides functions and procedures for accessing files managed by the operating system. File paths are accessed through a DIRECTORY object, so first confirm that access is allowed.

Create and query the Oracle directory:

CREATE DIRECTORY ENC_KEY AS '/home/oracle/enc_key';

SELECT * FROM ALL_DIRECTORIES;

Confirm that the created file can be accessed:

DECLARE
  FHANDLE UTL_FILE.FILE_TYPE;
  FBUFFER VARCHAR2(64);
BEGIN
  FHANDLE := UTL_FILE.FOPEN('ENC_KEY', 'enc_key.txt', 'R');
  UTL_FILE.GET_LINE(FHANDLE, FBUFFER);
  DBMS_OUTPUT.PUT_LINE(FBUFFER);
  UTL_FILE.FCLOSE(FHANDLE);
END;

Result:

ED6CF66DAF90E7D9C6935E1B47643210791603D8B6745F0E31B2C14CFA31A3C7

Create the package

Create an ENCKEY package with a GET_ENC_KEY function that returns the encryption key.

Package specification

CREATE OR REPLACE PACKAGE TEST.ENCKEY IS
  FUNCTION GET_ENC_KEY RETURN VARCHAR2;
END ENCKEY;

Package body

CREATE OR REPLACE PACKAGE BODY TEST.ENCKEY IS
  FUNCTION GET_ENC_KEY RETURN VARCHAR2 IS
    FHANDLE UTL_FILE.FILE_TYPE;
    FBUFFER VARCHAR2(64);
    INPUT_STRING VARCHAR2(64);
    KEY_RAW RAW(16) := UTL_RAW.CAST_TO_RAW('test1234test1234');
    OUTPUT_RAW RAW(1024);
    V_OUT_STRING VARCHAR2(1024);
    AES_CBC_PKCS5 CONSTANT PLS_INTEGER :=
      DBMS_CRYPTO.ENCRYPT_AES128
      + DBMS_CRYPTO.CHAIN_CBC
      + DBMS_CRYPTO.PAD_PKCS5;
  BEGIN
    -- CREATE DIRECTORY ENC_KEY AS '/home/oracle/enc_key'
    -- SELECT * FROM ALL_DIRECTORIES
    FHANDLE := UTL_FILE.FOPEN('ENC_KEY', 'enc_key.txt', 'R');
    UTL_FILE.GET_LINE(FHANDLE, FBUFFER, 64);
    INPUT_STRING := FBUFFER;
    UTL_FILE.FCLOSE(FHANDLE);

    IF INPUT_STRING IS NULL THEN
      RETURN NULL;
    END IF;

    OUTPUT_RAW := DBMS_CRYPTO.DECRYPT(
      src => INPUT_STRING,
      typ => AES_CBC_PKCS5,
      key => KEY_RAW
    );
    V_OUT_STRING := UTL_I18N.RAW_TO_CHAR(OUTPUT_RAW, 'AL32UTF8');
    RETURN V_OUT_STRING;
  END GET_ENC_KEY;
END ENCKEY;

After creating the package function, confirm that it returns the key correctly:

SELECT ENCKEY.GET_ENC_KEY FROM dual;

Wrap the package

DBMS_DDL provides wrapping for DDL statements that start with CREATE OR REPLACE PROCEDURE or PACKAGE, along with other DDL-related features. Using the CREATE_WRAPPED procedure both hides the package source and compiles it.

Looking at the package content, calling GET_ENC_KEY returns the encryption key, and the key used to encrypt/decrypt that encryption key is also visible in the source. For that reason, wrap both the specification and the body, and keep the plaintext source separately.

VARCHAR2 has a maximum length of 4,000 bytes, so for longer content use the CLOB type (CLOB max length is 4GB).

Wrap the package specification

DECLARE
  src CLOB;
BEGIN
  src := '
CREATE OR REPLACE PACKAGE TEST.ENCKEY IS
  FUNCTION GET_ENC_KEY RETURN VARCHAR2;
END ENCKEY;
';
  DBMS_DDL.CREATE_WRAPPED(src);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Wrap the package body

DECLARE
  src CLOB;
BEGIN
  src := '
CREATE OR REPLACE PACKAGE BODY TEST.ENCKEY IS
  FUNCTION GET_ENC_KEY RETURN VARCHAR2 IS
    FHANDLE UTL_FILE.FILE_TYPE;
    FBUFFER VARCHAR2(64);
    INPUT_STRING VARCHAR2(64);
    KEY_RAW RAW(16) := UTL_RAW.CAST_TO_RAW(''test1234test1234'');
    OUTPUT_RAW RAW(1024);
    V_OUT_STRING VARCHAR2(1024);
    AES_CBC_PKCS5 CONSTANT PLS_INTEGER :=
      DBMS_CRYPTO.ENCRYPT_AES128
      + DBMS_CRYPTO.CHAIN_CBC
      + DBMS_CRYPTO.PAD_PKCS5;
  BEGIN
    -- CREATE DIRECTORY ENC_KEY AS ''/home/oracle/enc_key''
    -- SELECT * FROM ALL_DIRECTORIES
    FHANDLE := UTL_FILE.FOPEN(''ENC_KEY'', ''enc_key.txt'', ''R'');
    UTL_FILE.GET_LINE(FHANDLE, FBUFFER, 64);
    INPUT_STRING := FBUFFER;
    UTL_FILE.FCLOSE(FHANDLE);

    IF INPUT_STRING IS NULL THEN
      RETURN NULL;
    END IF;

    OUTPUT_RAW := DBMS_CRYPTO.DECRYPT(
      src => INPUT_STRING,
      typ => AES_CBC_PKCS5,
      key => KEY_RAW
    );
    V_OUT_STRING := UTL_I18N.RAW_TO_CHAR(OUTPUT_RAW, ''AL32UTF8'');
    RETURN V_OUT_STRING;
  END GET_ENC_KEY;
END ENCKEY;
';
  DBMS_DDL.CREATE_WRAPPED(src);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Confirm the wrapped package. It is stored in wrapped form like this:

CREATE OR REPLACE PACKAGE BODY TEST.ENCKEY wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
346 258
d7fOkKhxMcBsoykWCbGefn3PBr0wg3lezPYFfC8ZCk7Vud6fEXwuYY1LJTdWT3bzLmXxaPdJ
f/K0ymMojCgFP3O5fsYK6+Z2Gvi5vHL+Vb6UwJarRpc9W2fV/jBUfUuFwxcBd//m1n/MevOm
HAvBYmiZlr8VIG+IF8knSX55L0UR8SU5DPkMCFAGYoxcsI6SJk1TOlNdU2F5H17jhwWLlFrg
+m0gZ64tvJMVwZhBkOJjjqjofc5xXbGwuyQ/+RDkgdLOuPK70LA0ZJ9Mkbn0jyOCSxlJ/kBd
Npl0oStlDRKIIjf136I476AbFHnv33c27YBMEGMhqhGM1fofIhTlcSBhuCod9Ey6GzTjxFEc
FFoDFWC68Rw/F+/GSFuWTG1hhca9XdWF0K4oKfXkXiK1P4sKLCNBnjCHYLpWYGGUX7ZUr7r/
tzzjclSJIdFZdoRYrJNjy3DaC8q5yLpXllvGV6R5sqNMX4YVnav4hDWhk280RpiWgvBcKFAb
f3z9cqml3NNlSNmb35h2m30ahyB5oYg0eHrSBrXV99YVz7T1zHkS73LV+awCUWhHbSg3Rldl
z9H1Qv4o30538w==

Verify the function call

Confirm that the function still returns the key correctly after wrapping. crypto.encrypt is the function from the earlier Oracle DBMS_CRYPTO post.

SELECT crypto.encrypt('personal_info', ENCKEY.GET_ENC_KEY)
FROM dual;

Closing notes

Because calling ENCKEY.GET_ENC_KEY reveals the encryption key, every procedure that calls this function should also be wrapped. I handled those the same way with the CLOB type.