User Defined Functions
User Defined Functions (UDFs) enable interfacing with the NAE Server directly by accessing the procedures in CDP. These UDFs can be used to get session status, retrieve information from the properties file, generate random binary values, convert data types, and encrypt and decrypt data.
Encryption and Decryption UDFs
The encryption and decryption UDFs enable accessing the cryptographic functionality of the NAE Server from the database. There are two types of cryptographic UDFs: command line and name. The command line UDFs take the key name, algorithm name, and IV as parameters and apply these values to the input data. The names of these UDFs include the prefix 'cl'. The input parameters are detailed in the table below.
The name UDFs take the schema, table name, column name, and IV as parameters and use these values to recall the encryption metadata (key name, algorithm, and IV). That metadata is then applied to the input data. The names of these UDFs include the prefix 'nm'. The input parameters are detailed below.
Name UDF operations are not permitted while online migration/key rotation is in progress. The encryption and decryption UDFs take the following parameters:
| Parameter Name | Data Type | Description |
|---|---|---|
| inputData | varies by UDF | The value to be encrypted or decrypted. The data type is abbreviated in the UDF name. This parameter is used for both the command line and the name UDFs. |
| keyName | varchar2 | The name of the key to use to encrypt or decrypt the data. This key must exist on the NAE Server, and the requester must have permission to use the key for the operation requested. This parameter is used for the command line UDFs. For FPE, non-versioned keys are used. |
| algorithmName | varchar2 | For Standard Encryption: A string representing the algorithm, the mode in which the key is used (CBC or ECB), and what kind of padding to use. — The format of standard encryption algorithm is AES/CBC/PKCS5Padding. —For FPE: A string representing the algorithm for FPE is in the following format: FPE/AES/ — CARD10:TweakAlgorithm: TweakData(orFPEenc ryptionFormat) — CARD62:TweakAlgorithm: TweakData For example, FPE/AES/CARD10:SHA1:1122 is an algorithm name for FPE, where FPE/AES/CARD10 is algorithm and SHA1 is tweak algorithm and 1122 is tweak data. This parameter is used for the command line UDFs. |
| schemaName | varchar2 | The schema on which the table resides. This parameter is used for the name UDFs. |
| tableName | varchar2 | The table on which the column resides. This parameter is used for the name UDFs. |
| columnName | varchar2 | The column to be operated on. This parameter is used for the name UDFs. |
| iv | raw | The value to use as the Initialization Vector (IV) for data being encrypted or decrypted with a symmetric key (DES, DES-EDE, or AES algorithms) in the CBC mode. For other algorithms or symmetric keys used in the ECB mode, this value is ignored. For operations, which use DES or DES-EDE keys, the IV must be eight-byte; for AES keys, the IV must be sixteen-byte. If an IV is specified here, it must be base 16 encoded. This parameter is used for both the command line and the name UDFs. The IV value for standard encryption can be null or empty for command line UDFs. For such cases, the value of the IV associated with the Key is used as default value of the IV. The value of IV for FPE encryption will be of 112 s-integers. |
The following table lists the UDFs by data type for the standard encryption:
| Data Type | Function | Action |
|---|---|---|
| BFILE | ing_e_bfile_cl430 | encrypt |
| ing_e_bfile_nm430 | encrypt | |
| ing_d_bfile_cl430 | decrypt | |
| ing_d_bfile_nm430 | decrypt | |
| BLOB | ing_e_blob_cl430 | encrypt |
| ing_e_blob_nm430 | encrypt | |
| ing_d_blob_cl430 | decrypt | |
| ing_d_blob_nm430 | decrypt | |
| CHAR | ing_e_chr_cl430 | encrypt |
| ing_e_chr_nm430 | encrypt | |
| ing_d_chr_cl430 | decrypt | |
| ing_d_chr_nm430 | decrypt | |
| CLOB | ing_e_clob_cl430 | encrypt |
| ing_e_clob_nm430 | encrypt | |
| ing_d_clob_cl430 | decrypt | |
| ing_d_clob_nm430 | decrypt | |
| DECIMAL | ing_e_dec_cl430 | encrypt |
| ing_e_dec_nm430 | encrypt | |
| ing_d_dec_cl430 | decrypt | |
| ing_d_dec_nm430 | decrypt | |
| DATETIME | ing_e_dtm_cl430 | encrypt |
| ing_e_dtm_nm430 | encrypt | |
| ing_d_dtm_cl430 | decrypt | |
| ing_d_dtm_nm430 | decrypt | |
| NCHAR | ing_e_ncr_cl430 | encrypt |
| ing_e_ncr_nm430 | encrypt | |
| ing_d_ncr_cl430 | decrypt | |
| ing_d_ncr_nm430 | decrypt | |
| NUMBER | ing_e_dec_cl420 | encrypt |
| ing_e_dec_nm420 | encrypt | |
| ing_d_dec_cl420 | decrypt | |
| ing_d_dec_nm420 | decrypt | |
| NVARCHAR2 | ing_e_nvc_cl430 | encrypt |
| ing_e_nvc_nm430 | encrypt | |
| ing_d_nvc_cl430 | decrypt | |
| ing_d_nvc_nm430 | decrypt | |
| VARCHAR2 | ing_e_vrc_cl430 | encrypt |
| ing_e_vrc_nm430 | encrypt | |
| ing_d_vrc_cl430 | decrypt | |
| ing_d_vrc_nm430 | decrypt |
A fresh install has only _430 UDFs. An upgrade from 4.2 to 4.8 will contain both _430 UDFs and _420 UDFs for backwards compatibility.
The following table lists the UDFs by data type for the format preserving encryption:
| Data Type | Function | Action |
|---|---|---|
| CHAR | ing_e_chr_fcl430 | encrypt |
| ing_e_chr_fcl420 | encrypt | |
| ing_e_chr_nmf420 | encrypt | |
| ing_d_chr_nmf420 | decrypt | |
| ing_d_chr_fcl430 | decrypt | |
| ing_d_chr_fcl420 | decrypt | |
| NUMBER | ing_e_dec_nmf420 | encrypt |
| ing_e_dec_fcl430 | encrypt | |
| ing_e_dec_fcl420 | encrypt | |
| ing_d_dec_nmf420 | decrypt | |
| ing_d_dec_fcl430 | decrypt | |
| ing_d_dec_fcl420 | decrypt | |
| NCHAR | ing_e_ncr_nmf420 | encrypt |
| ing_e_ncr_fcl430 | encrypt | |
| ing_e_ncr_fcl420 | encrypt | |
| ing_d_ncr_nmf420 | decrypt | |
| ing_d_ncr_fcl430 | decrypt | |
| ing_d_ncr_fcl420 | decrypt | |
| NVARCHAR2 | ing_e_nvc_nmf420 | encrypt |
| ing_e_nvc_fcl430 | encrypt | |
| ing_e_nvc_fcl420 | encrypt | |
| ing_d_nvc_nmf420 | decrypt | |
| ing_d_nvc_fcl430 | decrypt | |
| ing_d_nvc_fcl420 | decrypt | |
| VARCHAR2 | ing_e_vrc_nmf420 | encrypt |
| ing_e_vrc_fcl430 | encrypt | |
| ing_e_vrc_fcl420 | encrypt | |
| ing_d_vrc_nmf420 | decrypt | |
| ing_d_vrc_fcl430 | decrypt | |
| ing_d_vrc_fcl420 | decrypt |
Normally, these UDFs are not called directly; they are integrated into the data migration functionality. Examples of direct calls to name and command line UDFs are shown below.
Direct Call to ing_e_vrc_nm430 for Standard Encryption
SELECT ingrian.ing_e_vrc_nm430
('yourVarchar', /* inputData */
'yourSchema', /* schemaName */
'yourTable', /* tableName */
'yourColumn', /* columnName */
'24A40F6DFB05F379630AC0533E7F6357') /* iv */
AS ENCRYPTED_VALUE
FROM dual;
---------------------------------
ENCRYPTED_VALUE
9F71F06D9F1E123B68D910F0EC34E8C5
Note
If online migration/key rotation is in progress, then the nm UDFs return an error.
Direct Call to ing_d_vrc_nm430 for Standard Encryption
SELECT ingrian.ing_d_vrc_nm430
('9F71F06D9F1E123B68D910F0EC34E8C5', /* inputData*/
'yourSchema', /* schemaName*/
'yourTable', /* tableName*/
'yourColumn', /* columnName*/
'24A40F6DFB05F379630AC0533E7F6357') /* iv */
AS DECRYPTED_VALUE
FROM dual;
------------------
DECRYPTED_VALUE
yourVarchar
Note
If online migration/key rotation is in progress, then the nm UDFs return an error.
Direct Call to ing_e_vrc_cl430 for Standard Encryption
SELECT ingrian.ing_e_vrc_cl430
('yourVarchar', /* inputData*/
'yourAESKey', /* keyName */
'AES/CBC/PKCS5Padding', /* algorithmName*/
'24A40F6DFB05F379630AC0533E7F6357') /* iv */
AS ENCRYPTED_VALUE
FROM dual;
---------------------------------
ENCRYPTED_VALUE
9F71F06D9F1E123B68D910F0EC34E8C5
Direct Call to ing_d_vrc_cl430 for Standard Encryption
SELECT ingrian.ing_d_vrc_cl430
('9F71F06D9F1E123B68D910F0EC34E8C5',/* inputData*/
'yourAESKey', /* keyName */
'AES/CBC/PKCS5Padding', /* algorithmName*/
'24A40F6DFB05F379630AC0533E7F6357') /* iv */
AS DECRYPTED_VALUE
FROM dual;
------------------
DECRYPTED_VALUE
yourVarchar
Direct Call to ing_e_chr_nmf420 for FPE Encryption
SELECT ingrian.ing_e_chr_nmf420 ('yourCharacter', /* inputData */
'yourSchema', /* schemaName */
'yourTable', /* tableName */
'yourColumn', /* columnName */
'0201070704010101020302060703040200010500060405030303040400030603050108030500000
507080408010502030806040006080406') /* iv */
AS ENCRYPTED_VALUE
FROM dual;
---------------------------------
ENCRYPTED_VALUE
084
Note
If online migration/key rotation is in progress, then the nm UDFs may return an error.
Direct Call to ing_d_chr_nmf420 for FPE Encryption
SELECT ingrian.ing_d_chr_nmf420
('084', /* inputData*/
'yourSchema', /* schemaName*/
'yourTable', /* tableName*/
'yourColumn', /* columnName*/
'0201070704010101020302060703040200010500060405030303040400030603050108030500000
507080408010502030806040006080406') /* iv */
AS DECRYPTED_VALUE
FROM dual;
------------------
DECRYPTED_VALUE
yourCharacter
Note
If online migration/key rotation is in progress, then the nm UDFs may return an error.
Direct Call to ing_e_chr_fcl430 for FPE Encryption
SELECT ingrian.ing_e_chr_fcl430
('yourCharacter', /* inputData */
'yourAESKeyName', /* keyName */
'FPE/AES/CARD10:SHA1:1234', /* algorithmName*/
'0201070704010101020302060703040200010500060405030303040400030603050108030500000
507080408010502030806040006080406') /* iv */
AS ENCRYPTED_VALUE FROM dual;
ENCRYPTED_VALUE
084
Direct Call to ing_d_chr_fcl430 for FPE Encryption
SELECT ingrian.ing_d_chr_fcl430
('084', /* inputData */
'yourAESKeyName', /* keyName */
'FPE/AES/CARD10:SHA1:1234', /* algorithmName*/
'0201070704010101020302060703040200010500060405030303040400030603050108030500000
507080408010502030806040006080406') /* iv */
AS DECRYPTED_VALUE FROM dual;
DECRYPTED_VALUE
yourCharacterData
Type Conversion UDFs
Note
To use the UDFs with FPE encryption (preceding two UDF examples), the padding should be in the format TweakAlgorithm:TweakData/FPEencryptionFormat. The value after : should be either Tweak Data or FPE encryption format. Valid values of Tweak Algorithm: NONE, SHA1, and SHA256. NONE is not supported with FPE encryption format, If the text used for entering the FPE encryption format in the UDF is not valid, then it will be treated as tweak data.
Thales encryption and decryption UDFs automatically convert the input data into RAW using the conversion UDFs shown below. Normally, these UDFs are not called directly, they are integrated into the data migration functionality.
The following table describes the process of converting various data types before the encryption UDFs are applied.
| Data Type | Conversion Method |
|---|---|
| VARCHAR2, CHAR, NVARCHAR2, and NCHAR | Converted directly into RAW. |
| NUMBER | Converted into a string and then converted into RAW. |
| DATE | Converted into a standard 31-byte string format, then converted into RAW. |
The following table lists the conversion UDFs.
| Name | Description |
|---|---|
| ingnumbertoraw | Converts a NUMBER value into a RAW value. |
| ingrawtonumber | Converts a RAW value into a NUMBER value. |
| ingdatetoraw | Converts a DATE value into a RAW value. |
| ingrawtodate | Converts a RAW value into a DATE value. |
| ingstringtoraw | Converts a string into a RAW value. |
| ingrawtostring | Converts a RAW value into a string value. |
| ingnstringtoraw | Converts an nstring value into a RAW value. |
| ingrawtonstring | Converts a RAW value into an nstring value. |
Examples of data type conversion UDFs
Examples of data type conversion UDFs are shown below.
To convert a number (1000) to a raw value, execute the command:
SQL> select ingnumbertoraw (1000) from dual;
Output:
INGNUMBERTORAW(1000)
----------------------------------------------------
31303030
To convert a string (TestString) to a raw value, execute the command:
SQL> select ingstringtoraw ('TestString') from dual;
Output:
INGSTRINGTORAW('TESTSTRING')
---------------------------------------------------
54657374537472696E67
Generate Random (ing_gn_rndm)
This UDF generates a random binary value of the specified number of bytes. This function is used mainly to generate random IVs. A number of bytes between 1 and 2000 can be requested.
| Parameter Name | Data Type | In/Out | Description |
|---|---|---|---|
| numBytes | number | In | The byte length of the binary value. |
| Return Value | raw | Out | The resulting number. |
Example
SELECT ing_gn_rndm (8)
FROM dual; ------------------
ING_GN_RNDM(8)
F3E3B728F8B5AFA7
