AesEncryptDecrypt UDX

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > Transforming Data in s-Server >

AesEncryptDecrypt UDX

Previous pageReturn to chapter overviewNext page

The AES Encryption/Decryption UDX performs Advanced Encryption Standard (AES) Encryption compatible with the MySQL AES_ENCRYPT and AES_DECRYPT functions. You can call the UDX multiple times within a single pipeline. Typically, you will call it once for encryption and once for decryption.

You call the UDX with a set of input parameters, which define the encryption or decryption. These are defined in a table below and include:

A Boolean encrypt/decrypt indicator to specify encryption or decryption of the inputted set of columns for this invocation.
The name of a file containing the AES encryption key. The file is stored in the $SQLSTREAM_HOME/plugin/jndi directory and secured for read access by the s-Server user. This file is a standard java properties file with a line which contains key=value where value is the 16 byte encryption key. Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/5.0.XXX.
A list of input columns (as defined in the input cursor) which appear in the output column list (as defined in the SQL function). These are copied to output columns with the same case-sensitive name as the supplied input column. Input columns which do not appear in the output column list are discarded.

Mapping

Input columns which appear in the mapping column list are encrypted/decrypted according to a mapping definition, a mapping character, and a separator character. Mapped columns must appear in the output column list. Mapped columns which appear in the output column list are encrypted/decrypted.  Null SQL columns are not encrypted/decrypted and are set to Null on output.

The mapping definition, mapping character, and separator character may alternately be placed in the same properties file as the key with the following property names: KEYMAP, KEYPAIRSEPARATOR, and KEYMAPSEPARATOR. For example: mapping column list 'c1=c1enc;c2=C2ENC’ and separator ';' and mapping '=' indicates that input column c1 is encrypted/decrypted to output column c1enc and input columns c2 is encrypted/decrypted to output column C2ENC.

Encrypted Columns

Encrypted columns must be of type VARBINARY and of at least the proper multiple of 16 bytes. (Longer VARBINARY is acceptable.) Encryption is performed on character strings. Columns which are defined with data types other than CHAR or VARCHAR are converted to a character string (for encryption) and from a character string (for decryption).

On encryption, the UDX pads values with values equal to the length of the padded data. This corresponds to RFC 2630 (see http://www.di-mgt.com.au/cryptopad.html ). On decryption, the UDX strips padding as appropriate. (These practices are compatible with MySQL AES_ENCRYPT and AES_DECRYPT functions for encrypting CHAR and VARCHAR. CHAR and VARCHAR strings have trailing spaces trimmed before encryption.) A column value which has been encrypted and later decrypted is assumed to have the identical datatype in its original value as after being encrypted and then decrypted. For example, a column with a BIGINT value is a 64 bit value, and will be encrypted and then decrypted to a BIGINT. If the datatypes of decrypted data are not correct, the results are indeterminate.

Assumptions:

VARCHAR columns do not contain trailing \0 values.

Parameter Definitions

Parameter

Type

Description

inputRows

CURSOR

The input stream of rows

Encrypt

BOOLEAN

Encryption (true) or decryption (false)

Dsname

VARCHAR(64)

File name in the $SQLSTREAM_HOME/plugin/jndi directory containing the AES encryption key

colNamesMap

VARCHAR(1024)

The mapping definition for input to output columns. Each input column name must be in the input stream. Each output column name must be in the output stream. If the colNamesMap is an empty string ‘’, the map is found in the Dsname file.

colPairSeparator

CHAR(1)

Character used to separate individual input to output column mappings. Usually a semi-colon (;) symbol.

colMapSeparator

CHAR(1)

Character used to define the input column to encrypted/decrypted output column. Usually an equal (=) sign.

Validation Errors

Validation errors are logged to the Trace.log.N file. The following errors are logged.

Trace Log Message

Meaning

Column Map Separator must be 1 character in length

Column map separator character not defined as 1 UTF-8 character

Column Pair Separator must be 1 character in length

Column pair separator character not defined as UTF-8 character

Column Pair Separator must differ from Column Map Separator

Column name separator character and the column pair separator character cannot be the same UTF-8 character.

Column Pair definition not <input column name> colMapSeparator <output column name>

Column pair definition is missing a column pair separator

Column Pair definition <input column name> colMapSeparator <output column name> does not have an input column

Column pair definition references an input column name which is not in the input stream

Column Pair definition <input column name> colMapSeparator <output column name> does not have an output column

Column pair definition references an output column name which is not in the output stream

Column Pair definition <input column name> colMapSeparator <output column name> cannot also be an input column

Column pair definition references an output column column name which is also an input column name

Column Pair definition <input column name> colMapSeparator <output column name> has an output column which is an incompatible type for AES Encryption

VARBINARY encrypted column is too narrow to hold the encrypted format for the input data.

Trace Definitions

You can set the tracing level for the AESEncryptDecrypt UDX in $SQLSTREAM_HOME/trace/Trace.properties, by changing the following:

com.sqlstream.plugin.EncryptDecrypt.AESEncryptDecrypt.level to.

Trace level

Meaning

WARNING

Error Conditions which disturb successful processing

INFO

Logs configuration definitions

FINE

Logs input and output column definitions at UDX initialization

FINER

Logs mapping input and output column definitions at UDX initialization

FINEST

Logs input and output column data (row by row)

Note that logging at the finest level is for debugging encryption. The contents of the Trace.log.N will contain both encrypted and decrypted data.

For installation:

1.Place the jar file in the $SQLSTREAM_HOME/plugin directory.
2.Link to the jar file in the $SQLSTREAM_HOME/plugin/autocp directory or use CREATE JAR.
3.Create the key properties file in $SQLSTREAM_HOME/plugin/jndi/ directory.

Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/5.1.0.XXX/s-Server

Example UDX Definition of Encryption

The following SQL creates a function called "Encrypt" which makes use of the AesEncryptDecrypt UDX to encrypt a set of input columns defined in colNamesMap and listed in the table returned by the function.

CREATE OR REPLACE FUNCTION "Encrypt"(

inputRows   CURSOR,  -- input stream

encrypt   BOOLEAN,  -- perform encryption or decryption

dsName    VARCHAR(64), -- name of file in the JNDI directory which contains the AES key

colNamesMap  VARCHAR(128), -- set of column names to be encrypted

colPairSeparator VARCHAR(1), -- separator character for list of column names

colMapSeparator VARCHAR(1) -- mapping character

)

RETURNS TABLE(

   -- un-modified columns from input stream

     ROWTIME        TIMESTAMP,

       "col1"         BIGINT,

       "col2"         INT,

       "col3"         SMALLINT,

       "col4"         BOOLEAN,

       "col5"         CHAR(16),

       "col6"         VARCHAR(16),

       "col7"         CHAR(40),

       "col8"         VARCHAR(40),

       -- encrypted columns, must be VARBINARY and a multiple of 16 bytes

       -- these column names cannot appear in the input stream

       "col1Enc"       VARBINARY(16),

       "col2Enc"       VARBINARY(16),

       "col3Enc"       VARBINARY(16),

       "col4Enc"       VARBINARY(16),

       "col5Enc"       VARBINARY(32),

       "col6Enc"       VARBINARY(32),

       "col7Enc"       VARBINARY(48),

       "col8Enc"       VARBINARY(48)

     

)

 LANGUAGE JAVA

 PARAMETER STYLE SYSTEM DEFINED JAVA

 NO SQL

 EXTERNAL NAME 'class com.sqlstream.plugin.EncryptDecrypt.AESEncryptDecrypt.aesEncryptDecrypt'

;

Sample Properties file contents

key=abcdefghijlmnopq

colPairSeparator=;

 

Stream Using Encrypt Function

The stream below makes use of this function. Eight input columns are encrypted and the resulting stream has 8 unencrypted columns and 8 encrypted columns.

CREATE OR REPLACE SCHEMA "Test";

SET SCHEMA '"Test"';

CREATE OR REPLACE STREAM "InputStream" (

 "col1"         BIGINT,

 "col2"         INT,

 "col3"         SMALLINT,

 "col4"         BOOLEAN,

 "col5"         CHAR(16),

 "col6"         VARCHAR(16),

 "col7"         CHAR(40),

 "col8"         VARCHAR(40)

) DESCRIPTION 'test';

 

CREATE OR REPLACE VIEW "InputEncrypted"

DESCRIPTION 'Encrypt the input data' AS

 SELECT STREAM *

 FROM TABLE("Encrypt"(

     CURSOR(SELECT STREAM *

         FROM "InputStream"), -- input stream

     true,

     'my_key',

     'col1=col1Enc;' ||

     'col2=col2Enc;' ||

     'col3=col3Enc;' ||

     'col4=col4Enc;' ||

     'col5=col5Enc;' ||

     'col6=col6Enc;' ||

     'col7=col7Enc;' ||

     'col8=col8Enc' ,

     '', -- ';',

     '='

     ));

Example UDX Definition of Decryption

The following SQL creates a function called "Decrypt" which makes use of the AesEncryptDecrypt UDX to decrypt a set of input columns defined in colNamesMap and listed in the table returned by the function.

CREATE OR REPLACE FUNCTION "Decrypt"(

 inputRows     CURSOR,    -- input stream

 encrypt      BOOLEAN,   -- perform encryption or decryption

 dsName       VARCHAR(64), -- name of file in the JNDI directory which contains the AES key

 colNamesMap    VARCHAR(128), -- set of column names to be decrypted

 colPairSeparator  VARCHAR(1),  -- separator character for list of column names

 colMapSeparator  VARCHAR(1)  -- mapping character

 )

 RETURNS TABLE(

       -- un-modified columns from input stream

       ROWTIME        TIMESTAMP,

       "col1"         BIGINT,

       "col2"         INT,

       "col3"         SMALLINT,

       "col4"         BOOLEAN,

       "col5"         CHAR(16),

       "col6"         VARCHAR(16),

       "col7"         CHAR(40),

       "col8"         VARCHAR(40),

       -- encrypted columns, must be VARBINARY and a multiple of 16 bytes

       "col1Enc"       VARBINARY(16),

       "col2Enc"       VARBINARY(16),

       "col3Enc"       VARBINARY(16),

       "col4Enc"       VARBINARY(16),

       "col5Enc"       VARBINARY(32),

       "col6Enc"       VARBINARY(32),

       "col7Enc"       VARBINARY(48),

       "col8Enc"       VARBINARY(48),

       -- decrypted columns, should be the same as the original

       "col1Dec"       BIGINT,

       "col2Dec"       INT,

       "col3Dec"       SMALLINT,

       "col4Dec"       BOOLEAN,

       "col5Dec"       CHAR(16),

       "col6Dec"       VARCHAR(16),

       "col7Dec"       CHAR(40),

       "col8Dec"       VARCHAR(40)

     

)

 LANGUAGE JAVA

 PARAMETER STYLE SYSTEM DEFINED JAVA

 NO SQL

 EXTERNAL NAME 'class com.sqlstream.plugin.EncryptDecrypt.AESEncryptDecrypt.aesEncryptDecrypt'

;

View Using Decrypt Function

The stream defined above is decrypted and the resuling stream has 8 unencrypted columns, 8 encrypted columns, and 8 decrypted columns (which match the original unencrypted columns).

SET SCHEMA '"Test"';

CREATE OR REPLACE VIEW "InputDecrypted"

DESCRIPTION 'Decrypt the input data' AS

 SELECT STREAM *

 FROM TABLE("Decrypt"(

     CURSOR(SELECT STREAM *

         FROM "InputEncrypted"), -- input stream

     false,

     'my_key',

     'col1Enc=col1Dec;' ||

     'col2Enc=col2Dec;' ||

     'col3Enc=col3Dec;' ||

     'col4Enc=col4Dec;' ||

     'col5Enc=col5Dec;' ||

     'col6Enc=col6Dec;' ||

     'col7Enc=col7Dec;' ||

     'col8Enc=col8Dec' ,

     '', -- ';',

     '='

     ));