Chapter 1: Introduction

Alliance Key Manager (AKM) is a server that creates, stores, manages and distributes symmetric encryption keys to applications that need to use them to protect sensitive data. This document describes one way an Oracle developer can retrieve an encryption key from the key server and use it to encrypt and decrypt data in a table.

Customers who deploy Oracle database applications have a wide variety of choices in programming language, operating system platforms, and database features. Oracle supports Transparent Data Encryption (TDE) in the Enterprise edition of its database application when the Advanced Security option is installed. For customers who implement the Standard edition of the database, or for customers who want to implement data protection at the application level, this document demonstrates one way to do this with Oracle PL/SQL and the Alliance Key Manager.

Townsend Security provides a shared library for encryption key retrieval for Linux platforms. This shared library makes it easy for developers to retrieve encryption keys from the Alliance Key Manager server. Once installed and configured, this shared library can be accessed directly from PL/SQL applications to retrieve encryption keys. In this document we demonstrate how to install the Townsend Security shared library on Red Hat Enterprise Linux, how to configure it, how to retrieve encryption keys in PL/SQL applications, and how to encrypt and decrypt data in Oracle 10g and 11g database tables.

If you use Java, Perl, PHP, or other languages, please refer to the AKM Resources Page for other key retrieval sample applications.

Change log

Version Date Description
1.0.0.001 12/28/2011 Initial version.
2.1.13.001 8/1/2013 Reformat and revise for AKM version 2.1.13.
3.0.0.001 2/9/2015 Updates.
3.0.0.002 6/18/2015 Name change from “AKM Oracle Quick Start Guide” to “AKM Guide for Oracle Developers”.
4.6.1.001 11/8/2019 Updated links and references to technical information.

Chapter 2: Before You Begin

Operating system

This example demonstrates key retrieval and encryption on a Red Hat Enterprise Linux platform. Townsend Security supports a number of other Linux distributions and the process of installing and using the Townsend Security key retrieval software will be very similar on those platforms.

OpenSSL

On Linux platforms, the Alliance Key Manager key retrieval library uses the OpenSSL library to create secure connections to the key server. You must install OpenSSL before attempting to use the key retrieval shared library.

Oracle database

The sample code in this document was developed on an Oracle 11g database platform. It should work without modification on an Oracle 10g database platform.

Programming language

The example code in this document is written in Oracle’s PL/SQL language. Townsend Security supports a wide variety of development and language platforms. Please see the AKM Evaluation Page for other examples. You will find examples of key retrieval for Java, Perl, PHP, C#, VBNET, COBOL, C/C++, and other development languages.

3: Installing the AKM Key Retrieval Library

Installing the Townsend Security ALLKeyRtv shared library

On the Linux platform the key retrieval shared library is installed from a deb or RPM package provided on the AKM Evaluation Page. Locate the package that is appropriate for your platform and use the Alliance Key Manager documentation to install the shared library. In this example the shared library named liballkeyrtv.so is installed into the Red Hat Enterprise Linux platform in the directory /usr/lib.

Configuring the shared library

The shared library uses a configuration file named AKMClient.ini. The name of the file is not significant and your PL/SQL application will pass the full path and name to the shared library. For this example, the configuration file is installed in the /tmp directory. Please note that for some versions of Linux this may not be an appropriate location for the configuration file as the directory may be periodically cleared by the operating system.

You must edit this configuration file to provide the location of the key server, and the path to the certificates and private keys used for the secure connection to the key server. The following lines give a brief description of the configuration parameters. Please refer to the AKM Evaluation Page for more information.

Sample configuration file AKMClient.ini

In the directory with this Quick Start Guide you will find the sample configuration file AKMClient.ini. The following sections are extracts from this file.

[IP]

This section defines the location of the Alliance Key Manager key server. You can specify the location using an IP address or a DNS name followed by the port number. You can also specify the connection time out values in seconds and milliseconds:

KeyStoreIpPort=96.126.127.53:6000
ConnectTimeoutSecs=5
ConnectTimeoutMSecs=0

[Cert]

This section defines the location of the Certificate Authority (CA) certificate, key server client certificate, and key server client private key. You should receive these certificates and private key files from your key server security administrator.

The VerifyDepth option indicates the depth of certificate validation with authenticating the key server. This should normally be 1.

The TrustedCACertDir is the path to the directory that contains your CA certificates.

The TrustedCACert is the full path and file name of the CA certificate for the key server.

The ClientSignedCert and ClientPrivateKey values are the full paths to your key server client certificate and private key. These are PEM files that are provided to you by your key manager security administrator.

VerifyDepth=1
TrustedCACertDir=/tmp/certs
TrustedCACert=/tmp/certs/CASelfSignedCert.pem
ClientSignedCert=/tmp/certs/AKMClientSignedCert.pem
ClientPrivateKey=/tmp/certs/AKMClientPrivKey.pem

[Log]

This section defines logging options. The Syslog option is used to indicate key retrieval logging to the syslog facility. The Stderr option indicates the output location for error messages:

Syslog=2
Stderr=2

Chapter 4: Configuring Oracle

Oracle external procedure listener files

You must configure the Oracle external procedure listener in order to use the Alliance Key Manager shared library. This section provides examples of the two files that must be modified to use the shared library.

listener.ora

The following is the content of this file for the sample application. The values in your environment may be different. The EXTPROC_DLLS=ANY value is required.

# listener.ora Network Configuration File: /opt/oracle/112/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/112)
      (PROGRAM = extproc)
      (ENV="EXTPROC_DLLS=ANY,LD_LIBRARY_PATH=/opt/oracle/112/lib/")
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /opt/oracle

tnsnames.ora

The following is the content of this file used for the sample application. The values in your environment may be different.

# tnsnames.ora Network Configuration File: /opt/oracle/112/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.localdomain)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
 (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Restarting the listener

After configuring the listener.ora and tnsnames.ora files you must restart the Oracle listener:

lsnrctl stop;lsnrctl start

Chapter 5: Sample PL/SQL Code and Database

Sample applications

Three sample PL/SQL applications are provided:

  • SalesCreate.sql – Creates the user “sales”, a sample table and data, and procedures

  • SalesDisplay.sql – Lists the values in the table

  • SalesDrop.sql – Deletes the sample table and code

The SalesCreate.sql application provides most of the logic you will need for your own applications. Most importantly, it demonstrates how to reference the AKM key retrieval library as an external C application, and how to call it to retrieve encryption keys.

The following sections discuss some important sections of the sample code. Please refer to the entire file provided on the AKM Evaluation Page.

Creating the environment

The first section of the SalesCreate.sql file creates the sample user as a DBA, grants authority to the environment, and initializes the environment:

create user sales identified by sales;
grant dba to SALES;
grant execute on DBMS_CRYPTO to sales;
connect sales/sales

Creating the table

The next section in the SalesCreate.sql file creates the sample table and inserts a few rows into the table:

create table sales.orders(customer_number number(4), customer_name varchar2(30), 
street_address varchar2(100), city varchar2(30), state varchar2(2), zip_code 
number(5), credit_card_number varchar2(64), key_name varchar2(30), 
key_instance_name varchar2(30), initialization_vector varchar2(100));


insert into sales.orders
(customer_number,customer_name,street_address,city,state,zip_code,credit_card_number,key_name,key_instance_name,initialization_vector)
  values (1,	'William Washington',	'539 Miller Valley Rd',	'Prescott','AZ',86301,'411111111111111',null,null,null);

insert into sales.orders
(customer_number,customer_name,street_address,city,state,zip_code,credit_card_number,key_name,key_instance_name,initialization_vector)
  values (2,	'Amanda Smith',	'610 West Grove Street','Boise','ID',83702,'522222222222222',null,null,null);

insert into sales.orders
(customer_number,customer_name,street_address,city,state,zip_code,credit_card_number,key_name,key_instance_name,initialization_vector)
  values (3,	'Jose Fernandez',	'169 E.Beck Street',	'Columbus',	
'OH',	43206,	'633333333333333',null,null,null);

insert into sales.orders
(customer_number,customer_name,street_address,city,state,zip_code,credit_card_number,key_name,key_instance_name,initialization_vector)
  values (4,	'Bill Jones',	'1228 East Morehead Street',	'Charlotte','NC',28204,'744444444444444',null,null,null);
  commit;

Shared library reference

The next section in the SalesCreate.sql file defines the reference to the AKM key retrieval shared library using the full path and file name for the shared library:

create or replace library liballkeyrtv
    as '/usr/lib/liballkeyrtv.so';

Oracle procedure definition for key retrieval

The next section of the SalesCreate.sql file defines an Oracle procedure for key retrieval and links it to the C definition in the shared library. This definition provides a mapping between Oracle data definition types and the C data definition types in the shared library. Note that external names are case sensitive:

create or replace procedure Ora_ALLKeyRtv(p_pcaKeyName in char,
	            p_pcaKeyInstance in char,
		    p_pulFormat in binary_integer,
		    p_pcaIniPath in char,
                    p_pulKeySize in binary_integer,
		    p_pcaKey out char,
		    p_pcaInstanceReturned out char,
                    p_pcaExpiration out char,
		    p_pcaLastRolledDate out char,
		    pulReturn out binary_integer)
    as external
    	library liballkeyrtv
    	name "ALLKeyRtv"
    	language c
    	    	parameters (p_pcaKeyName by reference string,
	            p_pcaKeyInstance by reference string,
		    p_pulFormat by reference unsigned long,
		    p_pcaIniPath by reference string,
                    p_pulKeySize by reference unsigned long,
		    p_pcaKey by reference string,
		    p_pcaInstanceReturned by reference string,
                    p_pcaExpiration by reference string,
		    p_pcaLastRolledDate by reference string,
		    pulReturn by reference unsigned long);

Oracle function definition for key retrieval

The next section of the SalesCreate.sql file defines an Oracle function definition for key retrieval by PL/SQL programs. Note that the key name, key instance name, and configuration file names will be different in your applications. This sample uses a 256-bit AES encryption key.

Create or replace function Ora_KeyGet return varchar2 is
	p_pcaKeyName char(40);
        p_pcaKeyInstance char(24);
        p_pulFormat char(1);
        p_pcaIniPath char(200);
        p_pulKeySize  char(3);
        p_pcaKey char(256);
        p_pcaInstanceReturned char(24);
        p_pcaExpiration char(8);
        p_pcaLastRolledDate char(8);
        p_pulReturn char(4);
begin
	p_pcaKeyName := 'AES256                                  ';
        p_pcaKeyInstance  := '5bBryTJ397Rb8AM8x9TabA==';
        p_pulFormat  := '2';
        p_pcaIniPath := '/tmp/AKMClient.ini' || chr(0) ;
        p_pulKeySize := '256';
        p_pcaKey  := '00000000000000000000000000000000000000000000000000000000000000000';
        p_pcaInstanceReturned := '000000000000000000000000';
        p_pcaExpiration := '00000000';
        p_pcaLastRolledDate := '00000000';
        p_pulReturn  := '0'; 

	Ora_ALLKeyRtv(p_pcaKeyName,
        	           p_pcaKeyInstance,
                	   p_pulFormat,
                   	   p_pcaIniPath,
                   	   p_pulKeySize,
                    	   p_pcaKey ,
                    	   p_pcaInstanceReturned ,
                    	   p_pcaExpiration ,
                       	   p_pcaLastRolledDate ,
                    	   p_pulReturn);
if to_number(p_pulReturn)<>0 then 
return 'Error : (' || to_char(p_pulReturn) || ')' ;
else
-- return substr(rtrim(p_pcakey),1,length(rtrim(p_pcakey))-1);
return rtrim(p_pcakey);
end if;
end;

Encryption function

The next section of the SalesCreate.sqp file defines a function to encrypt a string using a key from the AKM key server:

CREATE OR REPLACE FUNCTION encrypt_string (
        input_string        IN  VARCHAR2,
	raw_seed            IN  RAW,
	Init_Vector         IN  RAW
    ) RETURN RAW
    IS

    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;

   AES_CBC_NONE        CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
        converted_string    RAW(64);
        encrypted_string    RAW(64);

    BEGIN
        converted_string := UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8');
        encrypted_string :=
             DBMS_CRYPTO.ENCRYPT(
                 src => converted_string
                ,typ => AES_CBC_NONE
                ,key => raw_seed
                ,iv =>  Init_Vector);

        RETURN encrypted_string;

    EXCEPTION
        WHEN OTHERS THEN
            SQLERRMSG := SQLERRM;
            SQLERRCDE := SQLCODE;
            RETURN NULL;

    END encrypt_string;

Decryption function

The next section of the SalesCreate.sqp file defines a function to decrypt a string using a key from the AKM key server:

CREATE OR REPLACE FUNCTION decrypt_string (
        input_string        IN  VARCHAR2,
	raw_seed            IN  RAW,
	Init_Vector         IN  RAW
    ) RETURN VARCHAR2
    IS
    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;

   AES_CBC_NONE        CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
        converted_string    VARCHAR2(64);
        decrypted_string    VARCHAR2(64);

    BEGIN

        decrypted_string :=
            DBMS_CRYPTO.DECRYPT(
                 src => input_string
                ,typ => AES_CBC_NONE
                ,key => raw_seed
                ,iv =>  Init_Vector);

        converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_string, 'AL32UTF8');

        RETURN converted_string;

    EXCEPTION
        WHEN OTHERS THEN
            SQLERRMSG := SQLERRM;
            SQLERRCDE := SQLCODE;
            RETURN NULL;

    END decrypt_string;

Sample encryption procedure

The next section of the SalesCreate.sqp defines a sample procedure to encrypt the credit card number field in the sample database.

create or replace procedure encrypt_data is
 InitVector raw(32);
 seed                VARCHAR2(64);
 converted_seed      RAW(64);

begin
  seed := Ora_KeyGet;
  converted_seed := HEXTORAW(seed);
  for i in (select  t.customer_number,
               t.credit_card_number
          from sales.orders t
       )
  Loop
  InitVector:= DBMS_CRYPTO.RANDOMBYTES(32);
  update sales.orders t
   set t.initialization_vector = InitVector,
       t.credit_card_number    = encrypt_string(i.credit_card_number,converted_seed,InitVector),
       t.key_name              = 'AES256',
       T.KEY_INSTANCE_NAME     = '5bBryTJ397Rb8AM8x9TabA=='
   where t.customer_number=i.customer_number;
  End Loop;
  COMMIT;

end encrypt_data;

Sample decryption procedure

The last section of the SalesCreate.sqp defines a sample procedure to decrypt the credit card number field in the sample database.

create or replace procedure decrypt_data is
 InitVector raw(32);
 seed                VARCHAR2(64);
 converted_seed      RAW(64);
begin
  seed := Ora_KeyGet;
  converted_seed := HEXTORAW(seed);
  for i in (select  t.customer_number,
		t.initialization_vector,
               t.credit_card_number
          from sales.orders t
       )
  Loop
     InitVector := HEXTORAW(i.initialization_vector);
    update sales.orders t
    set t.initialization_vector = NULL,
        t.credit_card_number = decrypt_string(i.credit_card_number,converted_seed,InitVector) ,
        t.key_name = NULL,
        t.KEY_INSTANCE_NAME = NULL
    where t.customer_number=i.customer_number;

  End Loop;
  COMMIT;

end decrypt_data;

Chapter 6: Running the Sample Code

Before you start

Please be sure you have installed and configured the shared library before running the sample application. You must also restart the Oracle listener. See the steps above.

Run SalesCreate.sql

Sign on as user sys and run SalesCreate.sql file at an SQL prompt to create the environment. Be sure that all commands complete successfully. This will create the user sales and set up the sample tables and functions.

Run SalesDisplay.sql

Sign on as the user sales and run SalesDisplay.sql at an SQL prompt. This will display the sample data. Note that you can use this procedure to verify the encrypted or unencrypted status of the credit card information in the database.

Encrypt the credit card data

Sign on as the user “sales” and execute encrypt_data at an SQL prompt:

execute encrypt_data

This will encrypt the sample credit card data in the database. You can then run SalesDispaly.sql at the database prompt to verify that the information is encrypted.

Decrypt the credit card data

Sign on as the user “sales” and execute decrypt_data at an SQL prompt:

execute decrypt_data

This will decrypt the sample credit card data in the database. You can then run SalesDispaly.sql at the database prompt to verify that the information is not encrypted.

Run SalesDrop.sql

Sign on as the user “sys” and run SalesDrop.sql at an SQL prompt. This will remove the sample application and table.

Chapter 7: Problem Determination

Return codes

The AKM key retrieval library returns an error code as the last parameter in the list. This error code will have a value of 0 (zero) when the key retrieval call was successful. Any non-zero value indicates an error condition. Please refer to the AKM Error Codes Reference for a listing of common error codes from the shared library and from the key server.