Protegrity

This article explains how to use Protegrity to enhance data security in Exasol through tokenization.

Tokenization is the process of substituting sensitive data with non-sensitive data in the form of tokens. Protegrity allows you to use tokens and mask data in your Exasol database using defined functions (UDFs) with the Protegrity Application Protector REST API.

Prerequisites

You must have a working installation of Protegrity version 9.1. This section does not explain how to install and deploy Protegrity.

Protegrity versions earlier than 9.1 are not supported.

Preparation

Setting up a script language alias

Integration of Protegrity with Exasol is accomplished by allowing users to define new scalar SQL functions (User Defined Function) using a special script language created for that purpose. The script language is included in Exasol but is not active by default. In order to use it, you must define a script language alias using the system or session parameter SCRIPT_LANGUAGES:

ALTER SYSTEM SET SCRIPT_LANGUAGES='... PROTEGRITY=udfplugin:///applicationprotector_rest ...'

In the statement above, ... represents existing content in the session parameter. The following definitions are included by default in the session parameter:

R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3

To make Protegrity Application Protector REST UDFs available in your current SQL session, the complete ALTER SESSION statement would therefore be:

ALTER SESSION SET SCRIPT_LANGUAGES='R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3 PROTEGRITY=udfplugin:///applicationprotector_rest'

To make the script alias available for all future sessions, define it using the ALTER SYSTEM statement:

ALTER SYSTEM SET SCRIPT_LANGUAGES='R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3 PROTEGRITY=udfplugin:///applicationprotector_rest'

If your SCRIPT_LANGUAGES variable is different from the default, change the statements in the examples as needed.

You can choose any name for the script alias.

SESSION/SCOPE users

When communicating with the REST API, the database has to decide which ESA user to identify as. There are two options:

The SESSION user is the user who logged into the SQL session.
The SCOPE user is identical to the session user, except for the case where a user defines a view that contains Protegrity UDFs. In this case the owner of the view is sent as user name.

The name of the script language alias defines whether the session user or the scope user is used.

PROTEGRITY=udfplugin:///applicationprotector_rest (as in the examples above) will use the scope user.
PROTEGRITY=udfplugin:///applicationprotector_rest_ua will use the session user.

If your data strategy requires both behaviors, you can define multiple language aliases in the SCRIPT_LANGUAGES parameter.

It is not possible to prevent a session user from redefining the SCRIPT_LANGUAGES session variable. A user is effectively able to arbitrarily change the behavior.

This means that the scope user feature cannot be used to limit the access of a user that also is known to ESA in any way, as this user could just switch to session user behavior by redefining the script languages alias.

Methods

Protect/Unprotect

Once you have defined PROTEGRITY as a script language alias, you can create UDFs using this alias.

The script code of these UDFs consists of a number of key-value pairs. Each line contains a key-value pair where the key consists of the beginning of the line up to (and not including) the first blank. The part after the blank until the end of the line is the value.

Example:

CREATE OR REPLACE PROTEGRITY SCALAR SCRIPT protect_email(din VARCHAR(2000000)) RETURNS VARCHAR(2000000) AS
HOST prod.example.com
DATA_ELEMENT_NAME tok_email
METHOD protect
/

The data element tok_email must be defined in the Protegrity ESA connected to the REST API.

With this definition in place, you can protect email data using the UDF like a scalar function. For example:

SELECT protect_email(user_email) FROM USERS;

The corresponding unprotect method UDF can be defined as in this example:

CREATE OR REPLACE PROTEGRITY SCALAR SCRIPT unprotect_email(din VARCHAR(2000000)) RETURNS VARCHAR(2000000) AS
HOST prod.example.com
DATA_ELEMENT_NAME tok_email
METHOD unprotect
/

For a full list of supported keys, see Supported parameters.

Reprotect

Reprotect method UDFs require the specification of two data element names. For example:

CREATE OR REPLACE PROTEGRITY SCALAR SCRIPT protect_email(din VARCHAR(2000000)) RETURNS VARCHAR(2000000) AS
HOST prod.example.com
OLD_DATA_ELEMENT_NAME tok_email_old
NEW_DATA_ELEMENT_NAME tok_email_new
METHOD reprotect
/

Special Methods

The following special methods can be used to create UDFs that facilitate debugging and diagnostics.

Identity

Use this SQL statement to create a UDF that returns the input data without calling the REST API. This method is mainly intended for debugging and performance measurements. For example:

CREATE OR REPLACE PROTEGRITY SCALAR SCRIPT identity(din VARCHAR(2000000)) RETURNS VARCHAR(2000000) AS
HOST prod.example.com
METHOD identity
/

Whoami

Use this SQL statement to create a UDF that returns the user name that is currently used in calls to to the REST API.

CREATE OR REPLACE PROTEGRITY SCALAR SCRIPT whoami() RETURNS VARCHAR(2000000) AS
HOST prod.example.com
METHOD whoami
/

For example, in a session with SESSION user root, this would be the result when using this UDF:

SELECT whoami()
==> ROOT

You can use the whoami method to demonstrate the difference between session and scope use. Given this view, created as SYS user:

CREATE VIEW whoami_view AS SELECT whoami() AS wai FROM dual
GRANT SELECT ON whoami_view TO root

For the following queries, executed as user root, the results depend on the sessions settings.

ALTER SESSION SET SCRIPT_LANGUAGES='protegrity=udfplugin:///applicationprotector_rest_ua';
-- the next query returns ROOT
SELECT * FROM whoami_view;

ALTER SESSION SET SCRIPT_LANGUAGES='protegrity=udfplugin:///applicationprotector_rest';
-- the next query returns SYS
SELECT * FROM whoami_view;

Version

To create a UDF that returns the version of the Application Protector REST software, use the following SQL statement:

CREATE OR REPLACE PROTEGRITY SCALAR SCRIPT version() RETURNS VARCHAR(2000000) AS
HOST prod.example.com
METHOD version
/

TLS authentication

This section explains how to install certificate files in Exasol when using TLS authentication between Exasol and Protegrity.

Certificate files are usually generated when deploying Protegrity (or you may have used an existing certificate). The following example does not explain how to generate the certificates.

Use Exasol Deployment Tool (c4) to connect to the cluster operating system (COS) on any of the database nodes in your Exasol system. For example:
```
c4 connect -t1/cos
```
In COS, create a directory that will contain the TLS certificate files:
```
mkdir /exa/etc/protegrity-ssl
```

Upload the following certificate files to the new directory:


	ca.crt	Contains the certificate authority (CA) certificates
	client.crt	Contains the client certificate
	client.key	Contains the client key (must not be password protected)

Set the following permissions on the directory:
```
chmod 755 /exa/etc/protegrity-ssl
```
Change ownership of the files to the user that is running database processes on the OS level. In the following example, the user is exadefusr:
```
chown exadefusr:root /exa/etc/protegrity-ssl/*
```

Set the following permissions on the files:

chmod 644 /exa/etc/protegrity-ssl/ca.crt
chmod 644 /exa/etc/protegrity-ssl/client.crt
chmod 600 /exa/etc/protegrity-ssl/client.key

Supported parameters

This section lists all supported parameters for Application Protector REST.

The following parameters are used in production:

Parameter	Data type
`METHOD`	String
Possible values are `protect`, `unprotect`, `reprotect`, `version`, `whoami`, and `identity`.

`HOST`	String
Possible values are in the format `<host>:<port>` and specify the address of the REST API service. Multiple values can be specified, separated by single space characters. During processing, requests will be randomly distributed among the hosts in the list.

`BASIC_AUTH_CONNECTION`	String
Possible values are any Exasol connection name. This allows to store the address of the REST API service in Exasol connection objects, and facilitates using HTTP basic authentication with the REST API by specifying a user name and password in the Exasol connection object. If both `HOST` and `BASIC_AUTH_CONNECTION` are provided, the host name in `HOST` is used instead of the one in the connection object.

`DATA_ELEMENT_NAME`	String
Specifies the name of the Protegrity data element to be used by the REST API service. This parameter is required for the methods `protect` and `unprotect`.

`OLD_DATA_ELEMENT_NAME`, `NEW_DATA_ELEMENT_NAME`	String
These parameters specify the elements to be used with the `reprotect` method.

`VERIFY_SSL`	Boolean
Specifies whether or not the REST API service host’s SSL certificate should verified. Possible values are `true` and `false`. The default value is `true`.

`ATTEMPTS`	Integer
Specifies the number of times the connector should retry to send a message to the REST API if it fails to provide and answer for a request. The default value is 100.

`EXTERNAL_IV`, `EXTERNAL_TWEAK`	String
IV and Tweaks options for the `protect` and `unprotect` methods. For more information, refer to the Protegrity website.

`NEW_EXTERNAL_IV`, `OLD_EXTERNAL_IV`, `NEW_EXTERNAL_TWEAK`, `OLD_EXTERNAL_TWEAK`	String
IV and Tweaks options for the `reprotect` method. For more information about these options, contact Protegrity.

Additional parameters

The following additional parameters can be useful when testing a deployment or debugging problems:

Parameter	Data type
`SEND_RECEIVE_VERBOSE`	Boolean
Possible values are `true` and `false`. The default value is `false`. If the parameter is set to `true`, the connector will print information about the messages it receives. For more information, see Debug UDF Script Output

`MAX_MESSAGE_ENTRIES`	Integer
This parameter limits the number of values sent to the REST API service. The default value is 8192. You can only specify an upper bound. The actual number of values sent to the service can be lower. There is no way to increase the number of values.

`MAX_TOKEN_SIZE_FOR_PROTECT`	Integer
This parameter limits the size of values used in the `protect` method. If the value in the statement is larger than the parameter value, the connector will refuse the value and return an error message. The default value is 4096. This limit is applied before data is sent to the REST API. The limitations of the REST API are not affected by this option.

`MAX_TOKEN_SIZE_FOR_UNPROTECT`	Integer
This parameter limits the size of values used in the `unprotect`, `reprotect`, and `identity` methods. If the value in the statement is larger than the parameter value, the connector will refuse the value and return an error message. The default value is 4096. This limit is applied before data is sent to the REST API. The limitations of the REST API are not affected by this option.

`UTF8_DEBUG`	Boolean
Possible values are `true` and `false`. The default value is `false`. If set to true, the UDF will check the validity of the UTF-8 encoding of every message returned from the REST API service and issue an error message that may help in analyzing eventual problems.

`TIMEOUT`	Integer
The number of milliseconds to set as a timeout for answers from the REST API. For no timeout, set the value to 0 (zero). The default value is 0 (zero).

`RETRY_ON_TIMEOUT`	Boolean
Specifies whether or not to retry to send the message to the REST API after a timeout. Possible values are `true` and `false`. The default value is `true`.