Previous articles in the IRI blog detailed the static data masking of new database data using /INCLUDE logic or /QUERY syntax in scheduled IRI FieldShield job scripts that required changes in column values to detect updates. This article describes a more passive but integrated way to trigger FieldShield masking functions on the basis of SQL events, including in a real-time data masking context; in other words, to mask data as it’s produced or queried in real-time.

More specifically, this article documents the installation and use of stored procedures that call FieldShield data masking library functions to mask PII in Oracle 12c on Windows. It can also serve as a ‘procedure model’ for other databases and operating systems.

Minimum Requirements

• A version of the Oracle database that is JVM-enabled
• Oracle server /bin must be in the system PATH
• ORACLE_HOME environment variable must be set
• A licensed copy of IRI FieldShield and its Sandkey software development kit
• Oracle Java JRE 8
• Microsoft .NET v4.8 Framework

Installation

Once you have the sandkey.zip folder, extract the sandkey.jar and sandkeyFunctions.jar files, and the libsandkey.dll file that corresponds to your operating system. The libsandkey.dll file is in the .zip file’s root folder

Follow these steps after you have the files:

• Open a command prompt, navigate to where the files are located, and enter these commands in the format of:

loadjava -r -u <username>/<password>@<database> <filename>

If you get an error related to CREATE$java$LOB$TABLE while doing this, you likely have insufficient privileges and may need to run the command once as SYSTEM.

• Place the libsandkey.dll file in the bin folder of the Oracle server. Alternatively, add the location of the file to the system path.
• Connect to the database as SYSTEM using SQL*Plus, or a similar program, and enter the following query:

select seq, kind, grantee, name, enabled from dba_java_policy where name like ‘%java.lang.RuntimePermission%’;

Find the policy labeled “java.lang.RuntimePermission#loadLibrary.*”. In this case, it is number 99. This policy needs to be temporarily disabled in order to grant permission to the Sandkey user in the database.

• Now that the policy number is known, issue the following commands to disable the policy, grant permission to a user, and then re-enable the policy:

exec dbms_java.disable_permission(99);

exec dbms_java.grant_permission( ‘IRIDEMO’, ‘SYS:java.lang.RuntimePermission’, ‘loadLibrary.libsandkey’, ‘’ );

exec dbms_java.enable_permission(99);

Make sure that the username is fully capitalized as shown, or the system will not recognize it.

• Create the following functions within  SQL*Plus by running a script, or by manually inputting these commands:

CREATE FUNCTION enc_aes256 (input VARCHAR2, pass VARCHAR2) RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'sandkeyFunctions.Functions.encaes256(java.lang.String, java.lang.String) return java.lang.String';

/

CREATE FUNCTION enc_fp_ascii (input VARCHAR2, pass VARCHAR2) RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'sandkeyFunctions.Functions.encfpascii(java.lang.String, java.lang.String) return java.lang.String';

/

CREATE FUNCTION enc_fp_alphanum (input VARCHAR2, pass VARCHAR2) RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'sandkeyFunctions.Functions.encfpalphanum(java.lang.String, java.lang.String) return java.lang.String';

/

CREATE FUNCTION dec_aes256 (input VARCHAR2, pass VARCHAR2) RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'sandkeyFunctions.Functions.decaes256(java.lang.String, java.lang.String) return java.lang.String';

/

CREATE FUNCTION dec_fp_ascii (input VARCHAR2, pass VARCHAR2) RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'sandkeyFunctions.Functions.decfpascii(java.lang.String, java.lang.String) return java.lang.String';

/

CREATE FUNCTION dec_fp_alphanum (input VARCHAR2, pass VARCHAR2) RETURN VARCHAR2

AS LANGUAGE JAVA

NAME 'sandkeyFunctions.Functions.decfpalphanum(java.lang.String, java.lang.String) return java.lang.String';

/

• You can now create stored procedures or triggers using the Sandkey encryption and decryption functions, which are compatible with the same static data encryption functions in the utility versions of IRI FieldShield, IRI DarkShield, and IRI CellShield.

Use Case: PL/SQL Trigger

Here is an example that uses a Sandkey encryption function to automatically encrypt data as it is inserted in the EMPLOYEES table:

This trigger uses the enc_fp_ascii (ASCII format preserving encryption) function to encrypt the LAST_NAME of the new employee, and the enc_fp_alphanum function to encrypt the PHONE_NUMBER of the new employee in each inserted row.

Before the data is inserted, the trigger passes the new data as input, and password (encryption key value) as pass, into the Sandkey encryption functions. The function returns the ciphertext result of each value as output to the new row.

In this example, as five new rows were inserted into the table, note how the LAST_NAME and PHONE_NUMBER columns were encrypted with the functions described above.

In fact, once installed, Sandkey masking functions can be used in any way desired within the capabilities of PL/SQL to support more “dynamic” event drive data masking requirements.

Contact fieldshield@iri.com for help with this approach in your database environment.

Use Case: Decrypted View

This example shows the creation of a decrypted view based on the EMPLOYEES table.

The decrypted view is created similar to how any view would be created, with the exception of how the data columns are called. Individual columns are decrypted using their corresponding functions, in the format of:

<functionName>(<columnName>, ‘<password/key>’) <displayName>

The creation of a decrypted view allows for designated users to query data from the view, while allowing the actual data in the EMPLOYEES table to remain encrypted. Below is an image of how the view would appear when queried.

The integration of Sandkey and Oracle allows for greater control over the security of your data through automatic encryption triggers, decrypted views, or custom stored procedures. More generally, using FieldShield masking routines and procedures like PL/SQL, you can now mask sensitive data on the basis of real-time database events for a variety of use cases.