Protegrity

Prerequisites

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

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_72 ...'

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_72'

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_72'

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:

1. The SESSION user is the user who logged into the SQL session.

2. 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_72 (as in the examples above) will use the scope user.

• PROTEGRITY=udfplugin:///applicationprotector_rest_72_ua will use the session user.

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

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 localhost:9080
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 localhost:9080
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 localhost:9080
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 localhost:9080
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 localhost:9080
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_72_ua';
-- the next query returns ROOT
SELECT * FROM whoami_view;

ALTER SESSION SET SCRIPT_LANGUAGES='protegrity=udfplugin:///applicationprotector_rest_72';
-- 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 localhost:9080
METHOD version
/

Supported parameters

This section lists all supported parameters for Application Protector REST.

The following parameters are used in production:

Additional parameters

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

Deployment examples

Deployment on cluster nodes	Deployment using external protection service

$ sudo ./PepServerSetup_Linux_x64_7.2.1.33.sh
Please enter ESA host name or IP address
[]:
aa.bbb.ccc.dd # <- replace by real address!
Please enter the user name for downloading certificates
[]:
admin
Please enter the password for downloading certificates
[]:
Unpacking...
Extracting files...
Downloading certificates from 3.250.122.75:8443...
% Total
% Received % Xferd Average Speed
Time
Dload Upload
Total
100 30720 100 30720
0
0 24037
0 0:00:01
Time
Time Current
Spent
Left Speed
0:00:01 --:--:-- 24037
Extracting certificates...
Certificates successfully downloaded and stored in /opt/protegrity/defiance_dps/data.
Protegrity PepServer installed in /opt/protegrity/defiance_dps.
$ sudo ./ApplicationProtectorRESTLinux_x64_7.2.1.15.sh -dir /opt/protegrity/
XX: -dir
Unpacking...
Extracting files...
gzip: stdin: unexpected end of file
tar: Child returned status 1
tar: Error is not recoverable: exiting now
Application Protector REST API installed in /opt/protegrity//applicationprotector/rest
$ sudo vim /opt/protegrity/applicationprotector/rest/conf/configuration.json
$ sudo mkdir /opt/protegrity/certs/
$ sudo tar zxf sample-certs.tgz -C /opt/protegrity/certs/
$ sudo apt update
$ apt install openjdk-17-jre-headless
$ sudo /opt/protegrity/defiance_dps/bin/pepsrvctrl start
Protegrity PEP Server
Version 7.2.1.33
Copyright (c) 2004-2020 Protegrity Corporation. All Rights Reserved.
$ nohup java -cp /opt/protegrity/applicationprotector/rest/bin/*:/opt/protegrity/
applicationprotector/rest/lib/* com.protegrity.ap.rest.Api -conf /opt/protegrity/
applicationprotector/rest/conf/configuration.json </dev/null &>/dev/null &
[1] 62833
$ curl https://localhost:9080/rest-v1/protect -H ’Content-Type: application/json’ --data
’{"protect": {"policyusername": "SYS","dataelementname": "simple","bulk":{"id": 1,"
data": [{"id": 1,"content":"YWJjZGVmZ2hpamtsbW5vcHFyc3R1dnh5eg=="},{"id": 2,"content
": "MDEyMzQ1Njc4OQ=="}]}}}’ --insecure
{"protect":{"bulk":{"id":1,"returntype":"success","data":[{"id":1,"returncode":"/rest-v1/
returncodes/id/6","returntype":"success","content":"
SWlhTGlFMlBjQ1RZOGdwQ3JoWDB0emMzRQ=="},{"id":2,"returncode":"/rest-v1/returncodes/id
/6","returntype":"success","content":"QnAyWUIyUnJibA=="}]}}}