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 Supplemental for other key retrieval sample applications.
|2.1.13.001||8/1/2013||Reformat and revise for AKM version 2.1.13.|
|3.0.0.002||6/18/2015||Name change from “AKM Oracle Quick Start Guide” to “AKM Guide for Oracle Developers”.|
Chapter 2: Before You Begin
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.
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.
The sample code in this document and on the AKM Supplemental was developed on an Oracle 11g database platform. It should work without modification on an Oracle 10g database platform.
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 Supplemental 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 Supplemental. 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
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 Supplemental 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.
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=126.96.36.199:6000 ConnectTimeoutSecs=5 ConnectTimeoutMSecs=0
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.
VerifyDepth option indicates the depth of certificate validation with authenticating the key server. This should normally be 1.
TrustedCACertDir is the path to the directory that contains your CA certificates.
TrustedCACert is the full path and file name of the CA certificate for the key server.
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
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:
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.
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
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
tnsnames.ora files you must restart the Oracle listener:
lsnrctl stop;lsnrctl start
Chapter 5: Sample PL/SQL Code and Database
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
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 Supplemental.
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;
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;
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.
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.
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:
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:
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.
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
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.