JDBC

• 1: Application Protector - JDBC
• 1.1: Introduction
• 1.2: Overview
• 1.3: Installation
• 1.3.1: Prerequisites
• 1.3.2: Install JDBC Driver
• 1.3.3: Security Policy
• 1.3.3.1: Policy Sync
• 1.3.3.2: Cloud API on AWS
• 1.3.3.3: Cloud Protect Agent on AWS
• 1.3.3.4: Lambda Layer on AWS
• 1.3.3.5: Cloud Protect Agent on Azure
• 1.3.3.6:
• 1.3.4: Configure the schema
• 1.3.5: Configure the Application
• 1.3.6: Set the Policy User
• 1.3.7: Set Request ID
• 1.4: SQL Workbench/J
• 1.5: DBeaver
• 1.6: Upgrading to the Latest Version
• 1.7: Audit Logging
• 1.8: Developer Reference
• 1.8.1:
• 1.9: Known Limitations
• 1.10: Troubleshooting
• 1.11: Appendix
• 1.12:
• 1.13:
• 1.14:
• 1.15:
• 1.16:
• 1.17:
• 1.18:
• 1.19:
• 1.20:
• 1.21:
• 1.22:
• 1.23:
• 1.24:
• 1.25:
• 1.26:
• 1.27:
• 1.28:
• 1.29:
• 1.30:
• 1.31:
• 1.32:
• 1.33:
• 1.34:
• 1.35:
• 1.36:
• 1.37:
• 1.38:
• 1.39:
• 1.40:
• 1.41:
• 1.42:
• 1.43:
• 1.44:
• 1.45:
• 1.46:
• 1.47:
• 1.48:
• 1.49:
• 1.50:
• 1.51:
• 1.52:
• 1.53:
• 1.54:
• 1.55:
• 1.56:
• 1.57:
• 1.58:
• 1.59:
• 1.60:
• 1.61:
• 1.62:
• 1.63:
• 1.64:
• 1.65:
• 1.66:
• 1.67:
• 1.68:
• 1.69:
• 1.70:
• 1.71:
• 1.72:
• 1.73:
• 1.74:
• 1.75:
• 1.76:
• 1.77:
• 1.78:
• 1.79:
• 1.80:
• 2:

1 - Application Protector - JDBC

This document describes the high-level architecture of the JDBC Protector, the installation procedures, and provides guidance on performance. This document focuses on Protegrity-specific aspects and should be used in conjunction with the corresponding documentation for your cloud provider.

This guide may also be used with the Protegrity Enterprise Security Administrator Guide, which explains the mechanism for managing the data security policy.

1.1 - Introduction

This document describes the high-level architecture of the JDBC Protector, the installation procedures, and provides guidance on performance. This document focuses on Protegrity-specific aspects and should be used in conjunction with the corresponding documentation for your cloud provider.

This guide may also be used with the Protegrity Enterprise Security Administrator Guide, which explains the mechanism for managing the data security policy.

1.2 - Overview

Solution Overview

The Protegrity JDBC Protector enables Java applications to work with protected data in SQL databases. Installed as a Java Database Connectivity (JDBC) driver, the protector works in conjunction with the application’s existing database driver to make data protection transparent to the application. Java applications may use this driver to access protected data with little or no code changes.

This protector utilizes a data security policy maintained by a Protegrity Enterprise Security Administrator (ESA), similar to other Protegrity products. Using SQL queries and the JDBC API, Java applications can perform both de-identification (protect) and re-identification (unprotect) operations on data. When setting the current user, privileges will be restricted to the policies defined by the ESA for that user.

Features

Combined with an ESA security policy, the Protegrity JDBC Protector provides the following features:

• Enables JVM-based applications to perform transactions with protected data over JDBC
• Provides transparent data protection in the JDBC-layer using mapping metadata between protected columns and data elements in the security file
• Requires minimal application changes to utilize
• Supports most cloud-managed databases such as:
  • Amazon Aurora
  • Amazon RDS
  • Azure SQL Database
  • Databricks SQL
  • Google Cloud SQL
  • Oracle Autonomous Database
• Supports most SQL databases such as:
  • Db2 for Linux, Unix and Windows 11.5
  • Db2 for z/OS 12
  • Informix 14.10
  • MariaDB 10.4, 10.5, 10.6
  • MySQL 8.0
  • Oracle Database 19c, 21c
  • PostgreSQL 12, 13, 14, 15, 16
  • SQL Server 2019
• Supports most application servers such as:
  • Apache Tomcat
  • Tableau Server
  • WildFly

For more information about the available protection options, such as data types, tokenization, encryption, or length-preserving and non-preserving tokens, refer to the Protection Methods Reference.

Architecture

The JDBC Protector is installed into the application’s classpath and is registered as the primary JDBC driver. The protector driver operates in conjunction with the application’s existing native JDBC driver to provide transparent data protection between the application layer and the SQL database. This enables applications to work with protected data in SQL databases with minimal code or configuration modifications.

The JDBC Protector operates as an in-memory SQL engine for parsing and executing SQL queries through the application’s native driver. It uses a customer-provided JSON file to map protected fields in the SQL database to a security data element. This allows the protector to transparently protect and unprotect data within the JDBC layer. For example, on insert and update statements, the protector will protect data in the clear before it is written to the database. On read, the protector unprotects data before it is returned to the application. For search queries, it will substitute clear search strings with the tokenized equivalent value enabling full pushdown to the database including use of database indexes on protected columns.

Optimal query processing is pushed down to the underlying database driver in most cases. Certain queries (e.g. wildcard searches on protected data) may require in-memory processing. In such cases, the JDBC Protector will make one or more queries to the underlying database driver to retrieve the required data, and execute the remaining query in memory. This type of operation may involve increased JVM memory, CPU, and latency.

The JDBC Protector requires the security policy and a user to perform data protection operations. The user can either be a single configured service account or a dynamic user set on the connection by the application. A variety of security policy deployment and provisioning architectures are available. These alternatives are described later in this document.

Analytics on Protected Data

Protegrity’s format and length preserving tokenization scheme makes it possible to perform analytics directly on protected data. Tokens are join-preserving so protected data can be joined across datasets. Often statistical analytics and machine learning training can be performed without the need to re-identify protected data. However, a user or service account with authorized security policy privileges may re-identify subsets of data using SQL queries through the JDBC Protector.

1.3 - Installation

The JDBC Protector can be integrated into any Java application that utilizes JDBC for connecting to a database. Java programming skills are recommended for understanding the configuration changes required to integrate the JDBC Protector. Users without Java programming skills should start with the SQL Workbench/J section. It contains an example of integrating the JDBC Protector into an application for executing SQL queries against any JDBC-compatible database.

1.3.1 - Prerequisites

Prerequisites

The following prerequisites are required for installing the JDBC Protector:

• JDBC Protector distribution

• Java 8, 11, or 17

• Protegrity ESA 10.0.0 or later

• For installation on Linux, the following packages are required

  • libc 2.27 or later
  • libgcc 8.4.0 or later
  • libstdc++ 8.4.0 or later

• For installation on Windows, the Microsoft Visual C++ 2015 Redistributable Package is required

  https://aka.ms/vs/17/release/vc_redist.x64.exe

Supported Platforms

The JDBC Protector was tested against the following databases and platforms. It may be compatible with versions not listed below:

Cloud-managed databases:

• Amazon Aurora
• Amazon RDS
• Azure SQL Database
• Databricks SQL
• Google Cloud SQL
• Oracle Autonomous Database

Database versions:

• DB2 for Linux, UNIX, and Windows 11.5
• DB2 for z/OS 12
• Informix 14.10
• MariaDB 10.4, 10.5, 10.6
• MySQL 8.0
• Oracle Database 19c, 21c
• PostgreSQL 12, 13, 14, 15, 16
• SQL Server 2019

Required Skills and Abilities

1.3.2 - Install JDBC Driver

Install JDBC Driver

The JDBC Protector contains a JDBC 4.1 compliant driver. Add the jar files and the cloud-protect.properties file to the Java classpath for your application.

Deployment Packages

The JDBC Protector is delivered as an ApplicationProtector_<BUILD>_<RELEASE_VERSION>.zip deployment package on the my.protegrity.com customer portal. Download the package that matches the environment and architecture of the JVM that runs your application. The SRVL packages are intended for serverless runtimes such as AWS Lambda and Azure Functions.

<RELEASE_VERSION> is the build version of the release, for example ApplicationProtector_Linux-ALL-64_ARM-64_JDBC-1.8-64_10.0.0.303.zip.

* There is no ARM-64 deployment package for Windows. On Windows on ARM, download the Windows x86-64 package and run your application with an x64 JVM, so that the native library can be loaded through the built-in x64 emulation. An ARM-64 JVM fails to load the library and reports an UnsatisfiedLinkError.

To install an x64 JVM on Windows on ARM:

1. Download a Windows x64 build of Java 8, 11, or 17 from your JDK vendor. Do not select the aarch64 build.

2. Install it to a separate directory, such as C:\Program Files\Java\jdk-17-x64.

3. Start your application with that JVM and confirm the architecture:

   "C:\Program Files\Java\jdk-17-x64\bin\java.exe" -XshowSettings:properties -version
   

   The output must report os.arch = amd64. If it reports aarch64, the wrong JVM is in use.

Choose one of the following installation methods based on your application type:

Classpath Installation

For general Java applications, copy the required files to your application’s classpath.

Prerequisites

• Unzip the release build ApplicationProtector_<BUILD>_<RELEASE_VERSION>.zip
• Change into the classpath directory inside the unzipped directory

Steps

1. Copy the protegrity-jdbc-<version>.jar and the protegrity-protect-<platform>-<version>.jar files to your application.

   Note

   Many applications have a lib directory that is searched for jar files. Copying the JAR files to that directory will add it to the Java classpath.

2. If your application does not already have an slf4j-api.jar file, copy the provided slf4j-api.jar and slf4j-simple.jar files to your application.

3. Copy the cloud-protect.properties file to your application.

   Note

   Many applications have a conf directory that is searched for properties files. Copying the properties file to that directory will add it to the Java classpath.

Apache Maven Installation

Install the JDBC driver using Apache Maven.

Prerequisites

• Unzip the release build ApplicationProtector_<BUILD>_<RELEASE_VERSION>.zip
• Change into the maven directory inside the unzipped directory
• Java and Maven installed
• Access to a Maven repository

Steps

1. Open a terminal inside the maven directory.

2. Install to the local Maven repository. Run the following commands, replacing the placeholder values.

   mvn install:install-file \
     -Dfile=./iap-jdbc-maven-<RELEASE_VERSION>.jar \
     -DpomFile=./iap-jdbc-maven-<RELEASE_VERSION>-pom.xml
   
   mvn install:install-file \
     -Dfile=./protect-<PLATFORM>-<RELEASE_VERSION>-esa.jar \
     -DpomFile=./protect-<PLATFORM>-<RELEASE_VERSION>-esa-pom.xml \
     -Dclassifier=esa
   

3. (Optional) Deploy to a Maven repository:

   mvn deploy:deploy-file \
     -DgroupId=com.protegrity.jdbc \
     -DartifactId=iap-jdbc-maven \
     -Dversion=<RELEASE_VERSION> \
     -Dpackaging=jar \
     -Dfile=./iap-jdbc-maven-<RELEASE_VERSION>.jar \
     -DpomFile=./iap-jdbc-maven-<RELEASE_VERSION>-pom.xml \
     -DrepositoryId=<your-repo-id> \
     -Durl=<https://your.maven.repo/url>
   
   mvn deploy:deploy-file \
     -DgroupId=com.protegrity.cloud \
     -DartifactId=protect-<PLATFORM> \
     -Dversion=<RELEASE_VERSION> \
     -Dpackaging=jar \
     -Dfile=./protect-<PLATFORM>-<RELEASE_VERSION>-esa.jar \
     -DpomFile=./protect-<PLATFORM>-<RELEASE_VERSION>-esa-pom.xml \
     -Dclassifier=esa \
     -DrepositoryId=<your-repo-id> \
     -Durl=<https://your.maven.repo/url>
   

4. Add the deployed artifact to your application’s pom.xml, matching the groupId, artifactId, and version. Example:

   <dependency>
     <groupId>com.protegrity.jdbc</groupId>
     <artifactId>iap-jdbc-maven</artifactId>
     <version><RELEASE_VERSION></version>
   </dependency>
   
   <dependency>
     <groupId>com.protegrity.cloud</groupId>
     <artifactId>protect-<PLATFORM></artifactId>
     <version><RELEASE_VERSION></version>
   

1.3.3 - Security Policy

Protegrity Security Policy

The JDBC Protector requires access to a security policy. Several policy deployment architectures are supported as outlined below:

Policy Sync

Policy Sync — The JDBC Protector connects directly to an upstream Protegrity server (ESA 10.2.0+) via HTTPS to download the security policy. No separate agent process is required. The policy is cached locally and automatically refreshed. Configured entirely through JDBC connection properties.

Resilient Deployment Architectures

These architectures allow an application to function without direct access or dependency on an ESA:

Cloud API on AWS — The JDBC Protector invokes the Protegrity Cloud API function in AWS Lambda for handling data protection operations.

Cloud Protect Agent on AWS — A separate, serverless agent process fetches the security policy from ESA. The agent encrypts the file using Amazon KMS and stores it in Amazon S3. The JDBC Protector loads the security policy from S3 and decrypts it in memory using KMS.

Lambda Layer on AWS — The policy agent publishes the security policy as an AWS Lambda Layer. The JDBC Protector resolves the latest layer version, downloads the content, and extracts the policy package. This is an alternative to the S3-based approach that leverages native Lambda Layer versioning for policy updates.

Cloud Protect Agent on Azure — A separate, serverless agent process fetches the security policy from ESA. The agent encrypts the file using Azure Key Vault and stores it in Blob Storage. The JDBC Protector loads the security policy from Blob Storage and decrypts it in memory using Azure Key Vault.

Configuration

Refer to the following sections for further instructions:

• Policy Sync
• Cloud API on AWS
• Cloud Protect Agent on AWS
• Lambda Layer on AWS
• Cloud Protect Agent on Azure

1.3.3.1 - Policy Sync

Policy Sync

The Policy Sync mode downloads the security policy directly from an upstream Protegrity server (ESA 10.2.0+) via HTTPS. It does not require a separate agent process or cloud storage. The policy is cached locally and automatically refreshed at a configurable interval.

This mode is supported on Linux and Windows.

Requirements

• The JDBC Protector must already be installed on the Java classpath. For more information, see Install JDBC Driver.
• Upstream Protegrity server (ESA) version 10.2.0 or later (RPS API 1.10.0+)
• Network connectivity to the ESA HTTPS port (typically 443)
• A user account with Export Resilient Package and Can Create JWT Token permissions

Configuration

Policy Sync is configured entirely through JDBC extended connection properties. No cloud-protect.properties file is required.

Set ptyProtectUri to an rps:// URI pointing to the upstream server:

TLS Verification

Use ptyPolicySyncCaCertPath when connecting to a server with a private/internal CA. Use ptyPolicySyncInsecure=true only for development and testing.

Example: Extended Properties

Example: JDBC URL

jdbc:iap:postgresql://db.example.com:5432/appdb?user=appuser&password=dbpass;ptyProtectUri=rps://esa.internal.example.com:443;ptyPolicySyncUsername=policy_admin;ptyPolicySyncPassword=P@ssw0rd!2026;ptyPolicySyncDatastore=PROD_DATA_STORE;ptyPolicySyncCaseSensitive=no;ptyPolicySyncInsecure=true

How It Works

1. On first connection, the JDBC Protector authenticates with the upstream server using JWT.
2. It checks the server version (minimum 1.10.0 required).
3. It checks if a policy export is available using If-Modified-Since.
4. If available, downloads the policy package and caches it to disk.
5. Writes core.cfg (from ptyPolicySyncEmptyString/ptyPolicySyncCaseSensitive) next to the cached policy.
6. Loads the policy via the native decoder.
7. Periodically checks for policy updates (default: every 120 seconds).

On subsequent connections within the same JVM, the cached ProtectService is reused instantly (no server contact).

Caching

• Metadata (~/.pty-sync/sync-metadata.txt): Stores Last-Modified, datastore, and datastore key for If-Modified-Since checks.
• Policy cache (~/.pty-sync/cached-policy.pkg): Cached policy package for fast reload without re-downloading.
• Core config (~/.pty-sync/core.cfg): Generated from ptyPolicySyncEmptyString and ptyPolicySyncCaseSensitive. Updated on every policy load to reflect current connection properties.

If the datastore or datastore key configuration changes, the cache is invalidated and a fresh download is triggered.

Logging

The JDBC Protector uses SLF4J for logging. To enable file-based logging, add an SLF4J binding and configuration to your application’s classpath.

Using SLF4J Simple:

1. Add slf4j-simple to the classpath.

2. Create a simplelogger.properties file on the classpath:

org.slf4j.simpleLogger.defaultLogLevel=info
org.slf4j.simpleLogger.logFile=protegrity.log
org.slf4j.simpleLogger.showDateTime=true
org.slf4j.simpleLogger.dateTimeFormat=yyyy-MM-dd HH:mm:ss.SSS
org.slf4j.simpleLogger.showShortLogName=true

3. Restart the application. Logs will be written to protegrity.log in the working directory.

Set defaultLogLevel=debug for verbose output including HTTP request details and cache behavior.

Comparison with Other Modes

1.3.3.2 - Cloud API on AWS

Cloud API on AWS

The JDBC Protector will invoke the Protegrity Cloud API function in AWS Lambda to handle data protection operations. Audit logs will be written by the Protegrity Cloud API function to Amazon CloudWatch.

Before you begin

Install the Protegrity Cloud API on AWS. For more information, refer to the Cloud API on AWS guide.

Configure your Java application

1. Ensure that your application has access to invoke the Protegrity Cloud API function in AWS Lambda.

   AWS credentials will be loaded from the following sources: environment variables, Java system properties, AWS CLI, and the EC2 role.

2. Copy the protegrity-protect-aws-<version>.jar file to your Java application.

   See the Install JDBC Driver section for more information.

3. Create a cloud-protect.properties file and set uri to the Protegrity Cloud API function ARN. This file should be saved with your Java application’s configuration.

   uri=arn:aws:lambda:us-east-1:123456789012:function:Protegrity_Protect_RESTAPI
   

The following additional configuration options may be added to cloud-protect.properties:

1.3.3.3 - Cloud Protect Agent on AWS

Cloud Protect Agent on AWS

The Protegrity Cloud API on AWS will download the policy from your ESA server, encrypt it with AWS KMS, and store it on Amazon S3. The protector will download the policy from S3 and load it into memory. It will poll S3 and reload the policy if changes are detected. Audit logs will be written using the SLF4J logger.

Before you begin

Install the Protegrity Cloud API on AWS. For more information, refer to the 3.7 Policy Agent Installation section of the Cloud API on AWS v3.0 guide.

Configure your Java application

1. Ensure that your application has access to the AWS KMS key used to encrypt the policy.

   AWS credentials will be loaded from the following sources: environment variables, Java system properties, AWS CLI, and the EC2 role.

2. Copy the protegrity-protect-aws-<version>.jar file to your Java application.

   See the Install JDBC Driver section for more information.

3. Create a cloud-protect.properties file and set uri to the policy file in S3. This file should be saved with your Java application’s configuration.

   uri=s3://bucket/protegrity-policy.zip
   

To use a custom endpoint for Amazon S3, set the following properties in your cloud-protect.properties file:

1.3.3.4 - Lambda Layer on AWS

Lambda Layer on AWS

The Protegrity Cloud API on AWS deploys the policy package to both an S3 bucket and an AWS Lambda Layer. As an alternative to S3, the JDBC Protector can download the policy directly from the Lambda Layer. The protector resolves the latest published version of the layer, downloads the content, extracts the policy package, and loads it into memory. It periodically checks for new layer versions and reloads the policy when updates are detected.

Before you begin

• Install the Protegrity Cloud API on AWS (policy agent). The agent must be configured to publish the policy package as a Lambda Layer.
• Ensure that your application has IAM permissions to call lambda:ListLayerVersions and lambda:GetLayerVersion on the target layer ARN.

Configure your Java application

1. Ensure that your application has access to the AWS KMS key used to encrypt the policy.

   AWS credentials are loaded from the default credential provider chain: environment variables, Java system properties, AWS CLI profile, ECS task role, or EC2 instance role.

2. Copy the protegrity-protect-aws-<version>.jar file to your Java application.

   See the Install JDBC Driver section for more information.

3. Configure the Lambda Layer ARN using one of the methods below.

Option A: Connection properties

Set ptyProtectUri to the Lambda Layer ARN (without a version suffix):

Option B: cloud-protect.properties

Create a cloud-protect.properties file and set uri to the Lambda Layer ARN:

uri=arn:aws:lambda:us-east-1:123456789012:layer:policy-layer

How It Works

1. On first connection, the JDBC Protector calls ListLayerVersions to resolve the latest version of the configured layer.
2. It calls GetLayerVersion to obtain the presigned download URL for the layer content.
3. Downloads the layer content (a zip archive).
4. Extracts IMMUTABLE_POLICY_PACKAGE from the zip.
5. Decrypts and loads the policy into memory using the configured KMS key.
6. Periodically checks for new layer versions. When a new version is detected, the updated policy is downloaded and loaded.

On subsequent connections within the same JVM, the cached ProtectService is reused without contacting AWS.

Example: JDBC URL

jdbc:iap:postgresql://db.example.com:5432/appdb?user=appuser&password=dbpass;ptyProtectUri=arn:aws:lambda:us-east-1:123456789012:layer:policy-layer;ptyPolicyEncryptionKeyLabel=arn:aws:kms:us-east-1:123456789012:key/abcdef01-2345-6789-abcd-ef0123456789

Example: Extended Properties

IAM Permissions

The application’s IAM role or user must have the following permissions on the Lambda Layer:

{
  "Effect": "Allow",
  "Action": [
    "lambda:ListLayerVersions",
    "lambda:GetLayerVersion"
  ],
  "Resource": "arn:aws:lambda:us-east-1:123456789012:layer:policy-layer"
}

Additionally, kms:Decrypt permission is required on the KMS key used to encrypt the policy.

1.3.3.5 - Cloud Protect Agent on Azure

Cloud Protect Agent on Azure

The Protegrity Cloud API on Azure will download the policy from your ESA server, encrypt it with Azure Key Vault, and store it in Azure Blob Storage. The protector will download the policy from Storage and load it into memory. It will poll Storage and reload the policy if changes are detected. Audit logs will be written using the SLF4J logger.

Before you begin

Install the Protegrity Cloud API on Azure. For more information, refer to the section 3.7 Policy Agent Function App Installation in the Cloud API on Azure v3.0 guide.

Configure your Java application

1. Ensure that your application has access to the Azure Key Vault key used to encrypt the policy.

   Azure credentials will be loaded from the following sources: environment variables, managed identity, and the Azure CLI.

2. Copy the protegrity-protect-azure-<version>.jar file to your Java application.

   See the Install JDBC Driver section for more information.

3. Create a cloud-protect.properties file and set uri to the policy file in Storage. This file should be saved with your Java application’s configuration.

   uri=https://account.blob.core.windows.net/container/IMMUTABLE_POLICY_PACKAGE
   

1.3.3.6 -

Immutable AP Java

The Immutable Java Application Protector will download the policy from your ESA server, encrypt it, and store it in persistent storage.

Install the Protegrity Immutable Java Application Protector. For more information, refer to the 2 IAP Deployment Model section of the Application Protector Java Immutable Policy User Guide.

Configure your Java application:

1. Copy the protegrity-protect-linux64-<version>.jar (Linux) or the protegrity-protect-win64-<version>.jar (Windows) file to your Java application.

   See the Install JDBC Driver section for more information.

2. Create a cloud-protect.properties file with uri=rps://esa.example.com:443 as the contents. This file should be saved with your Java application’s configuration.

   You may also specify the path to AP Java. The default installation directory will be used if none is provided.

   • Linux:

     uri=rps://esa.example.com:443:/opt/protegrity/applicationprotector/java
     

   • Windows:

     uri=rps://esa.example.com:443:/C:/Program%20Files/Protegrity/Defiance%20AP/java
     

1.3.4 - Configure the schema

Configure the Schema

Create a schema.json file to map the database schema to the security policy.

Refer to the Schema Configuration section for a detailed schema.json description and usage.

1. Create a schema.json file and save it with your application’s configuration.

2. Edit the file and describe your database schema. Only protected fields need to be mapped. The following snippet shows an example:

   {"schema":
       {"database": [
           {"name": "database or schema name",
            "table": [
               {"name": "table name",
                "column": [
                   {"name": "protected column name",
                    "dataelement": "data element name"
                   },
                   {"name": "protected column name",
                    "dataelement": "data element name"
                   }
               ]}
           ]}
       ]}
   }
   

3. Set the ptyCatalogUri connection property to the path of the file.

   • Use classpath: when the file is in an application configuration directory or a JAR file that has been added to the JVM classpath. For example:

     classpath:/config/schema.json
     

   • Use file: when the file is in a local directory. For example:

     • Linux:

       file:/path/to/schema.json
       

     • Windows:

       file:///C:/path/to/schema.json
       

1.3.5 - Configure the Application

Your application should be reconfigured to use the JDBC Protector driver instead of your database driver. The following sections have steps for some common Java applications.

For more information on changing your JDBC configuration, refer to the documentation for your Java application.

Generic JDBC Application

For applications using the JDBC API, modify the JDBC URL and provide the path to the schema.json file described in the previous section.

Modify the application as follows:

1. Insert iap after the jdbc: prefix of the JDBC URL.

   For example, if the previous JDBC URL was:

   jdbc:mysql://localhost:3306
   

   Change it to:

   jdbc:iap:mysql://localhost:3306
   

2. Add ptyCatalogUri to the JDBC connection properties and provide the path to the schema.json file created in the previous step.

   For example:

   properties.setProperty("ptyCatalogUri", "file:/opt/schema.json");
   

Here is an example of using the JDBC Protector for connecting to a MySQL server:

Properties properties = new Properties();
properties.setProperty("user", "admin");
properties.setProperty("password", "");
properties.setProperty("ptyCatalogUri", "file:/opt/schema.json");

Connection connection =
        DriverManager.getConnection("jdbc:iap:mysql://localhost:3306/db", properties);

For additional JDBC connection properties, see the Connection Properties section.

Spring Boot

Applications using Spring Boot should provide application configuration in their application.properties or application.yml file, and then consume the provided configuration to create a JDBC connection or an XA data source as shown below.

Additional information on XA support can be found here.

1. Configure the application:

   protegrity.jdbc.iap.url=jdbc:iap:postgresql://localhost:5432/dbName
   protegrity.jdbc.postgres.url=jdbc:postgresql://localhost:5432/dbName
   protegrity.jdbc.postgres.username=user
   protegrity.jdbc.postgres.password=password
   protegrity.jdbc.policy=/opt/IMMUTABLE_POLICY_PACKAGE
   protegrity.jdbc.catalog=/opt/pty-schema.json
   protegrity.jdbc.policyUser=policy_user
   protegrity.jdbc.delegateClassName=org.postgresql.xa.PGXADataSource
   

2. Consume the application configuration:

   import com.protegrity.jdbc.JdbcUrl;
   import com.protegrity.jdbc.xa.ProtegrityXADataSource;
   import org.springframework.beans.factory.annotation.Value;
   import org.springframework.context.annotation.Bean;
   import org.springframework.context.annotation.Configuration;
   
   import javax.sql.XADataSource;
   import java.net.URI;
   import java.nio.file.Paths;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.util.Properties;
   
   @Configuration
   public class ApplicationConfiguration
   {
       @Value("${protegrity.jdbc.iap.url}")
       private JdbcUrl iapJdbcUrl;
   
       @Value("${protegrity.jdbc.postgres.url}")
       private JdbcUrl postgresJdbcUrl;
   
       @Value("${protegrity.jdbc.postgres.username}")
       private String username;
   
       @Value("${protegrity.jdbc.postgres.password}")
       private String password;
   
       @Value("${protegrity.jdbc.policyUser}")
       private String policyUser;
   
       @Value("${protegrity.jdbc.delegateClassName}")
       private String delegateClassName;
   
       private URI policy;
       private URI catalog;
   
       public ApplicationConfiguration(
               @Value("${protegrity.jdbc.catalog}") String catalog,
               @Value("${protegrity.jdbc.policy}") String policy)
       {
           this.policy = Paths.get(policy).toUri();
           this.catalog = Paths.get(catalog).toUri();
       }
   
       @Bean
       public Connection jdbc() throws SQLException
       {
           var properties = new Properties();
           properties.put("user", username);
           properties.put("password", password);
           properties.put("ptyCatalogUri", catalog.toString());
           properties.put("ptyProtectUri", policy.toString());
           properties.put("ptyPolicyUser", policyUser);
   
           return DriverManager.getConnection(iapJdbcUrl.toString(), properties);
       }
   
       @Bean
       public XADataSource xa()
       {
           // Currently, we only support using Postgres for XA transactions.
           var properties = new Properties();
           properties.put(ProtegrityXADataSource.PropertyNames.URL, postgresJdbcUrl);
           properties.put(ProtegrityXADataSource.PropertyNames.USER, username);
           properties.put(ProtegrityXADataSource.PropertyNames.PASSWORD, password);
           properties.put(ProtegrityXADataSource.PropertyNames.CATALOG_URI, catalog.toString());
           properties.put(ProtegrityXADataSource.PropertyNames.PROTECT_URI, policy.toString());
           properties.put(ProtegrityXADataSource.PropertyNames.POLICY_USER, policyUser);
           properties.put(ProtegrityXADataSource.PropertyNames.DELEGATE_CLASS_NAME, delegateClassName);
   
           // Pass other non-Protegrity properties by their name
           properties.put("defaultNullCollation", "low");
           properties.put("sslmode", "verify-ca");
   
           return new ProtegrityXADataSource(properties);
       }
   }
   

1.3.6 - Set the Policy User

Set the Policy User

The JDBC Protector will use the database username as the policy user if no other username is provided. Alternatively, the application can set the policy user for each connection by setting the POLICY_USER variable. An example is provided in the following snippet:

connection.createStatement().execute("SET POLICY_USER = 'jon.snow' ");

1.3.7 - Set Request ID

Set Request ID

The request_id is an optional field for storing metadata in protector audit records. It can be used to correlate application activity with the protector audit records. The request_id can be set for each connection by setting the REQUEST_ID variable. The value is empty until set by the application. This is only supported when using a security policy from a Cloud Protect Agent.

connection.createStatement()
        .execute("SET REQUEST_ID = '01978dbc-0582-d7e4-0000-002a3603a20d' ");

1.4 - SQL Workbench/J

SQL Workbench/J is a free application for executing SQL queries using any JDBC driver. It can be used with the JDBC Protector to verify and demonstrate its compatibility with supported databases. SQL queries, such as SELECT, INSERT, and UPDATE, can be executed and the results viewed in a table. It is not required when using the JDBC Protector with another application.

Users should be familiar with Protegrity software such as ESA and PEP Server, and have a basic understanding of how to use a JDBC driver. Permissions to add a trusted application in ESA may be required.

Install the Software

SQL Workbench/J is a free application available for Linux, macOS, and Windows. It requires Java in order to run which can be downloaded using the instructions below.

Before you begin

Install the Protegrity AP Java and PEP Server software for your platform. The cloud-protect.properties file is not required if AP Java is installed into the default location.

Download and install SQL Workbench/J:

1. Download the SQL Workbench/J generic package for all systems.

2. Create a new directory named Workbench and unzip the Workbench-Build128.zip file to this directory.

3. Copy the protegrity-jdbc-<version>.jar, protegrity-protect-<platform>-<version>.jar, slf4j-simple-<version>.jar, and slf4j-api-<version>.jar files from the JDBC Protector package to the Workbench directory.

4. Download the Java Runtime Environment (JRE) by running the following program from the Workbench directory:

   • Linux & macOS: Run the download_jre.sh program.
   • Windows: Right-click the download_jre.cmd file and select Run.

Configure the Driver

A JDBC driver allows SQL Workbench/J to connect to a specific type of database, such as Oracle or SQL Server. Each JDBC driver will have its own set of JAR file(s) that must be added to the application.

Configure your database driver:

1. Download the JDBC driver for your database. If it is a ZIP file, unzip the contents and locate the JAR file(s).

2. Open SQL Workbench/J by running the following program from the Workbench directory:

   • Linux & macOS: Run the sqlworkbench.sh program.
   • Windows: Right-click the SQLWorkbench.exe file and select Open.

3. In the Select Connection Profile window, click the Manage Drivers button.

4. Click the blank document button to create a new entry.

5. Enter a description into the Name field, such as Protegrity JDBC Protector.

6. Click the folder button to begin adding files.

   a. Add the JAR file(s) for your database.

   b. Add the protegrity-jdbc-<version>.jar, protegrity-protect-<platform>-<version>.jar, slf4j-simple-<version>.jar, and slf4j-api-<version>.jar files.

7. If prompted to select a driver, select the com.protegrity.jdbc.Driver option.

8. Click OK to close the Manage drivers window.

Configure the Profile

In SQL Workbench/J, a connection profile contains the configuration necessary to connect to a specific database. Profiles using the JDBC Protector can use connection properties for both the database and the JDBC Protector.

Configure a connection profile:

1. Click the profile button to create a new entry.

2. Replace New profile with a description for your connection profile.

3. Change the Driver to the Protegrity JDBC Protector driver you created in the previous section.

4. Enter the JDBC URL for your database into the URL field.

   For more information about JDBC URLs, refer to the documentation for your database.

5. Insert iap after the jdbc: prefix in the URL field.

   For example, if your URL field was:

   jdbc:mysql://localhost:3306/database
   

   Change it to:

   jdbc:iap:mysql://localhost:3306/database
   

6. Enter the Username and Password for your database into the appropriate fields.

7. Save the profile.

8. Close SQL Workbench/J, then reopen it.

9. Select the profile, then click OK to connect to the database.

You can now try entering any SELECT, INSERT, or UPDATE query into the Statement section and clicking the execute button. The results should appear in the Messages section.

For more information about error messages and solutions to common issues, refer to the Troubleshooting section.

Configure Data Protection

Database columns are protected automatically based on the policy defined in a schema.json file. This file maps the columns of a database table to the data element to be used for protect and unprotect operations.

Before you begin

Create a schema.json file according to the Schema Configuration section.

Set the schema for the profile:

1. In SQL Workbench/J, click File and select Connect window.

2. Select the profile you created previously.

3. Click the Extended Properties button.

4. Click the blank document button to add a new entry.

5. Enter ptyCatalogUri into the Property field (e.g., ‘file:/pty-schema.json’).

6. Enter the path to the schema.json file into the Value field.

7. Click OK to close the Edit extended properties window.

8. Save the profile.

9. Close SQL Workbench/J, then reopen it.

10. Select the profile, then click OK to connect to the database.

You can now try an INSERT query to write data to a protected column. The protected value should appear in the database. A subsequent SELECT query should return the original value.

For more information about error messages and solutions to common issues, refer to the Troubleshooting section.

Troubleshooting

Enabling logging can help in troubleshooting issues when using SQL Workbench/J.

Enable logging:

1. Open the File menu and select Manage Drivers.

2. Select the driver you created, such as Protegrity JDBC Protector.

3. Click the folder button to add a file.

4. Add the slf4j-simple.jar file.

   The file can be downloaded from Maven.

5. If prompted to select a driver, select the com.protegrity.jdbc.Driver option.

6. Click OK to close the Manage drivers window.

7. In SQL Workbench/J, open the Tools menu and select Options.

8. On the General tab, change the Log Level to DEBUG.

9. Make note of the location of the Logfile. When there is a problem with the JDBC Protector, open this file to view the error message.

10. Click OK to save the changes.

1.5 - DBeaver

DBeaver is a free, cross-platform database tool for executing SQL queries using any JDBC driver. It can be used with the JDBC Protector to verify and demonstrate its compatibility with supported databases. It is not required when using the JDBC Protector with another application.

Users should be familiar with Protegrity software such as ESA and PEP Server, and have a basic understanding of how to use a JDBC driver.

Install the Software

DBeaver Community Edition is available for Linux, macOS, and Windows. It requires Java in order to run (bundled with the installer).

Linux

Install DBeaver using the package manager:

sudo apt-get install dbeaver-ce

Or download from the DBeaver website.

Windows

Download and run the installer from the DBeaver website.

Configure the Driver

A custom JDBC driver must be configured in DBeaver to use the JDBC Protector.

Before you begin

Prepare the following files from the JDBC Protector package:

• protegrity-jdbc-<version>.jar
• protegrity-protect-<platform>-<version>.jar
• Database-specific JDBC driver JAR (e.g., postgresql-<version>.jar)

Steps

1. Open DBeaver and navigate to Database → Driver Manager.

2. Click New to create a new driver.

3. Fill in the driver settings:

   Field	Value
   Driver Name	IAP JDBC (PostgreSQL)
   Class Name	com.protegrity.jdbc.Driver
   URL Template	jdbc:iap:postgresql://{host}[:{port}]/[{database}]
   Default Port	5432

   Note

   Replace postgresql with the appropriate database type for your environment (e.g., mysql, oracle, sqlserver).

4. Switch to the Libraries tab and click Add File. Add the following JAR files:

   • protegrity-jdbc-<version>.jar
   • protegrity-protect-<platform>-<version>.jar
   • Database JDBC driver JAR (e.g., postgresql-<version>.jar)

5. Click OK to save the driver.

Create a Connection Profile

1. Click Database → New Database Connection.

2. Select the IAP JDBC driver created above.

3. In the Main tab, configure the connection URL:

   jdbc:iap:postgresql://host:port/database
   

4. Switch to the Driver properties tab and add the Protegrity connection properties:

   Property	Description	Example
   ptyProtectUri	Protection service URI	rps://esa-host:443
   ptyPolicySyncHost	Policy sync server host	18.234.164.217
   ptyPolicySyncPort	Policy sync server port	443
   ptyPolicySyncUsername	Policy sync username	admin
   ptyPolicySyncPassword	Policy sync password
   ptyPolicySyncCaCertPath	Path to CA certificate PEM file	/path/to/ProtegrityCA.pem
   ptyPolicySyncCaseSensitive	Case sensitivity	no
   ptyPolicySyncEmptyString	Empty string token name	empty
   ptyPolicySyncDatastoreKey	Datastore key hash
   ptyPolicyUser	Policy user for tokenization	master_useR
   ptyPolicyEncryptionKeyLabel	KMS key ARN for encryption	arn:aws:kms:...
   ptyPolicySyncIntervalSeconds	Policy sync interval in seconds	30
   ptyPolicySyncInsecure	Disable TLS verification	false

   Note

   These properties may also be configured using a cloud-protect.properties file placed on the driver classpath. Create a JAR containing the properties file and add it to the driver libraries.

5. Click Test Connection to verify the configuration.

6. Click Finish to save the connection.

Logging

DBeaver captures JDBC Protector log messages in its debug log:

• Linux: ~/.local/share/DBeaverData/workspace6/.metadata/dbeaver-debug.log
• Windows: %APPDATA%\DBeaverData\workspace6\.metadata\dbeaver-debug.log

For detailed stack traces, check the Eclipse platform log:

• Linux: ~/.local/share/DBeaverData/workspace6/.metadata/.log
• Windows: %APPDATA%\DBeaverData\workspace6\.metadata\.log

Troubleshooting

Invalid JDBC URL

Symptom: Invalid JDBC URL: jdbc:iap:postgresql://...

Solution: Ensure the connection uses the custom IAP JDBC driver (not the built-in PostgreSQL driver). Check that the provider is set to the custom driver in the connection settings.

No Protegrity protect service provider found for URI

Symptom: No Protegrity protect service provider found for URI: rps://...

Solution: The protegrity-protect-<platform>-<version>.jar (ESA) is missing from the driver libraries. Add it to the driver configuration.

Class not found: com.protegrity.cloud.protect.ProtectFactory

Symptom: Class definition not found: com/protegrity/cloud/protect/ProtectFactory

Solution: The ESA JAR is missing. Add protegrity-protect-<platform>-<version>.jar to the driver libraries.

No suitable JDBC driver found

Symptom: No suitable JDBC driver found for URL: jdbc:postgresql://...

Solution: The underlying database JDBC driver is missing. Add the database-specific driver JAR (e.g., postgresql-<version>.jar) to the IAP JDBC driver libraries.

Missing “uri” in protect configuration properties

Symptom: Missing "uri" in protect configuration properties

Solution: Add the ptyProtectUri property to the connection’s driver properties, or create a cloud-protect.properties file with uri=rps://host on the driver classpath.

1.6 - Upgrading to the Latest Version

Upgrading to the Latest Version

Before you begin

You can download the latest version of the deployment package from my.protegrity.com. Navigate to Data Protection > Application Protector to download the latest version.

Upgrade the JDBC Protector based on your installation method:

1. Unzip the deployment package ApplicationProtector_<BUILD>_<RELEASE_VERSION>.zip.

2. Close or stop the application using the JDBC Protector.

3. Upgrade the security policy deployment.

   • AP Java - You must uninstall the earlier version of the Application Protector before installing the new version. For more information about installing AP Java, refer to the AP Java and PEP Server section.
   • Cloud API on AWS - For more information about upgrading the Cloud API on AWS, refer to the Cloud API on AWS guide.
   • Cloud API on Azure - For more information about upgrading the Cloud API on Azure, refer to the Cloud API on Azure guide.

4. Upgrade the JDBC driver files based on your installation method.

   • Classpath Installation: Replace the following files in your application with the files from the deployment package:

     • protegrity-jdbc-<version>.jar
     • protegrity-protect-<platform>-<version>.jar

   • Maven Installation: Deploy the new version to your Maven repository and update your application’s dependencies.

     1. Deploy the new artifacts to Maven repository:

        mvn deploy:deploy-file \
          -DgroupId=com.protegrity.jdbc \
          -DartifactId=iap-jdbc-maven \
          -Dversion=<NEW_RELEASE_VERSION> \
          -Dpackaging=jar \
          -Dfile=./iap-jdbc-maven-<NEW_RELEASE_VERSION>.jar \
          -DpomFile=./iap-jdbc-maven-<NEW_RELEASE_VERSION>-pom.xml \
          -DrepositoryId=<your-repo-id> \
          -Durl=<https://your.maven.repo/url>
        

        mvn deploy:deploy-file \
          -DgroupId=com.protegrity.cloud \
          -DartifactId=protect-<PLATFORM> \
          -Dversion=<NEW_RELEASE_VERSION> \
          -Dpackaging=jar \
          -Dfile=./protect-<PLATFORM>-<NEW_RELEASE_VERSION>-esa.jar \
          -DpomFile=./protect-<PLATFORM>-pom.xml \
          -Dclassifier=esa \
          -DrepositoryId=<your-repo-id> \
          -Durl=<https://your.maven.repo/url>
        

     2. Update your application’s pom.xml with the new version:

        <dependency>
          <groupId>com.protegrity.jdbc</groupId>
          <artifactId>iap-jdbc-maven</artifactId>
          <version><NEW_RELEASE_VERSION></version>
        </dependency>
        
        <dependency>
          <groupId>com.protegrity.cloud</groupId>
          <artifactId>protect-<PLATFORM></artifactId>
          <version><NEW_RELEASE_VERSION></version>
          <classifier>esa</classifier>
        </dependency>
        

     3. Rebuild your application to incorporate the updated dependencies.

5. Restart your application.

1.7 - Audit Logging

The handling of audit records depends on how your policy is deployed. When using AP Java, audit logs are handled according to the PEP Server configuration. When using the Cloud API, audit records are recorded to CloudWatch. When using the Cloud Protect Agent, audit records are sent to the SLF4J logger. Application logs are always sent to the SLF4J logger.

The audit records sent to SLF4J are written as JSON records. Protection operations are batched together when possible, and an audit record is written for each batch.

Audit Record Fields

The fields are described in the following table.

Example Audit Records

The following are sample audit messages:

Protect Success:

{
      "additional_info": {
        "description": "Data protect operation was successful."
      },
      "cnt": 4000,
      "correlationid": "sf-query-id:01978dbc-0582-d7e4-0000-002a3603a20d",
      "logtype": "Protection",
      "level": "INFO",
      "origin": {
        "hostname": "localhost",
        "time_utc": 1635363966
      },
      "protection": {
        "dataelement": "deAddress",
        "operation": "Protect",
        "audit_code": 6,
        "policy_user": "test_user"
      },
      "process": {
        "id": "1",
        "thread_id": "849348352"
      },
      "client": {},
      "protector": {
        "family": "IAP Lambda",
        "version": "1.0.0",
        "vendor": "Cloud Protect",
        "pcc_version": "3.3.0.5",
        "core_version": "1.1.0"
      },
      "signature": {
        "key_id": "95f5a194-b0a4-4351-a",
        "checksum": "B324AF7C56944D91C47847A77C0367C594C0B948E7E75654B889571BD4F60A71"
      }
    }

User permission denied:

{
      "additional_info": {
        "description": "The user does not have the appropriate permissions to perform the requested operation."
      },
      "cnt": 4000,
      "correlationid": "sf-query-id:01978dbc-0582-d7e4-0000-002a3603a20d",
      "logtype": "Protection",
      "level": "ERROR",
      "origin": {
        "hostname": "localhost",
        "time_utc": 1635363966
      },
      "protection": {
        "dataelement": "deAddress",
        "operation": "Protect",
        "audit_code": 3,
        "policy_user": "test_user"
      },
      "process": {
        "id": "1",
        "thread_id": "849348352"
      },
      "client": {},
      "protector": {
        "family": "IAP Lambda",
        "version": "1.0.0",
        "vendor": "Cloud Protect",
        "pcc_version": "3.3.0.5",
        "core_version": "1.1.0"
      },
      "signature": {
        "key_id": "95f5a194-b0a4-4351-a",
        "checksum": "A216797C56944D91C47847A77C0367C594C0B948E7E75654B889571BD4F60A71"
      }
    }

Data element not found:

{
      "additional_info": {
        "description": "The data element could not be found in the policy in shared memory."
      },
      "cnt": 4000,
      "correlationid": "sf-query-id:01978dbc-0582-d7e4-0000-002a3603a20d",
      "logtype": "Protection",
      "level": "ERROR",
      "origin": {
        "hostname": "localhost",
        "time_utc": 1635363966
      },
      "protection": {
        "dataelement": "deAddress",
        "operation": "Protect",
        "audit_code": 2,
        "policy_user": "test_user"
      },
      "process": {
        "id": "1",
        "thread_id": "849348352"
      },
      "client": {},
      "protector": {
        "family": "IAP Lambda",
        "version": "1.0.0",
        "vendor": "Cloud Protect",
        "pcc_version": "3.3.0.5",
        "core_version": "1.1.0"
      },
      "signature": {
        "key_id": "95f5a194-b0a4-4351-a",
        "checksum": "AF09217C56944D91C47847A77C0367C594C0B948E7E75654B889571BD4F60A71"
      }
    }

1.8 - Developer Reference

The JDBC Protector driver supports the Java Service Provider mechanism and will be automatically detected by the DriverManager facility or a DataSource implementation. The class that implements java.sql.Driver is com.protegrity.jdbc.Driver. JDBC URLs should begin with jdbc:iap: in order to be recognized by the driver.

For example, if your current database connection URL is:

jdbc:mysql://localhost:3306/example

Then to use the JDBC Protector it should be modified to:

jdbc:iap:mysql://localhost:3306/example

A basic implementation of a javax.sql.DataSource connection factory is provided by com.protegrity.jdbc.PtyDataSource.

Connection Properties

The table below describes the JDBC connection properties accepted by the JDBC Protector. The optimal values are automatically applied for JDBC URLs that start with any of the following:

• jdbc:iap:awsathena:
• jdbc:iap:bigquery:
• jdbc:iap:db2:
• jdbc:iap:informix-direct:
• jdbc:iap:informix-sqli:
• jdbc:iap:mariadb:
• jdbc:iap:mysql:
• jdbc:iap:oracle:
• jdbc:iap:postgresql:
• jdbc:iap:presto:
• jdbc:iap:sqlserver:

To use a different JDBC URL, adjust the connection properties below to match your requirements:

SQL Conformance

The JDBC Protector can be configured to accept or reject certain SQL features and extensions based on the value of the conformance property used when creating the connection. The table below lists the possible values for this property.

Lexical Policy

The lexical policy defines the character used for quoting identifiers and whether to enable case-sensitive matching of identifiers. The value of the lex property used when creating the connection defines which lexical policy will be used. The table below lists the possible values for this property.

SQL Grammar

This section describes the SQL dialect recognized by the JDBC Protector’s default SQL parser. The default grammar is SQL 2003 standard with some extensions from Oracle, MySQL, PostgreSQL, and SQL Server.

CALL

CALL identifier '(' [ param [ , param ]* ] ')'

COMMIT

COMMIT [ WORK ] [ AND { CHAIN | NO CHAIN ] ]

DELETE

DELETE FROM [tablePrimary](jdbc_dev_calcite_ref_statementSelectTableExpression.md) [ [ AS ] alias ]
[ WHERE booleanExpression ]

INSERT/UPSERT

{ INSERT | UPSERT } INTO [tablePrimary](jdbc_dev_calcite_ref_statementSelectTableExpression.md)
[ '(' column [, column ]* ')' ]
{ [values](jdbc_dev_calcite_ref_statementSelectValues.md) | [query](jdbc_dev_calcite_ref_statementSelect.md) }
[ ON CONFLICT DO
    {
        NOTHING
    |   UPDATE SET identifier '=' expression [, identifier '=' expression ]*
    }
]

MERGE

MERGE INTO [tablePrimary](jdbc_dev_calcite_ref_statementSelectTableExpression.md) [ [ AS ] alias ]
USING [tablePrimary](jdbc_dev_calcite_ref_statementSelectTableExpression.md)
ON booleanExpression
[ WHEN MATCHED THEN UPDATE SET identifier '=' expression [, identifier '=' expression ]* ]
[ WHEN NOT MATCHED THEN INSERT VALUES '(' value [ , value ]* ')' ]

RESET

[ ALTER { SYSTEM | SESSION } ] RESET { identifier | ALL }

ROLLBACK

ROLLBACK [ WORK ] [ AND { CHAIN | NO CHAIN ] ] [ TO SAVEPOINT identifier ]

SAVEPOINT

[ RELEASE ] SAVEPOINT identifier

SELECT

[WITH [ RECURSIVE ] withItem [ , withItem ]* query]
SELECT [ hintComment ] [ STREAM ]
[ SKIP UnsignedNumericLiteralOrParam ]
[
  { FIRST | LIMIT } UnsignedNumericLiteralOrParam
  | TOP { '(' UnsignedNumericLiteralOrParam ')' | UnsignedNumericLiteralOrParam }
]
[ ALL | DISTINCT ]
{ * | projectItem [, projectItem ]* }
[ FROM tableExpression ]
[ WHERE booleanExpression ]
[ GROUP BY [ ALL | DISTINCT ] { groupItem [, groupItem ]* } ]
[ HAVING booleanExpression ]
[ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] query ]
[ QUALIFY booleanExpression ]
[ ORDER BY orderItem [, orderItem ]* ]
[ LIMIT [ start, ] { count | ALL } ]
[ OFFSET start { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { SHARE | UPDATE } [ { NOWAIT | SKIP LOCKED } ] ]

groupItem

    expression
|   '(' ')'
|   '(' expression [, expression ]* ')'
|   CUBE '(' expression [, expression ]* ')'
|   ROLLUP '(' expression [, expression ]* ')'
|   GROUPING SETS '(' groupItem [, groupItem ]* ')'

hintComment

hintComment:
        '/*+' { hintName | hintName '(' hintOptions ')' } '*/'


hintOptions:
        hintKVOption [, hintKVOption ]*
    |   optionName [, optionName ]*
    |   hintOptionValue [, hintOptionValue ]*

hintKVOption:
        optionName '=' stringLiteral
    |   stringLiteral '=' stringLiteral

hintOptionValue:
        stringLiteral | numericLiteral

matchRecognize

JDBC Protector uses Apache Calcite for SQL statement parsing. MATCH_RECOGNIZE is a SQL extension for recognizing sequences of events in complex event processing (CEP). It is experimental in Apache Calcite, and may not be fully implemented yet.

matchRecognize:
        MATCH_RECOGNIZE
        '('
        [ PARTITION BY expression [, expression ]* ]
        [ ORDER BY order ]
        [ MEASURES expression AS alias [, expression AS alias ]* ]
        [ ONE ROW PER MATCH | ALL ROWS PER MATCH ]
        [ AFTER MATCH skip ]
        PATTERN '(' [ '^' ] pattern [ '$' ] ')'
        [ WITHIN  intervalLiteral ]
        [ SUBSET  subsetItem [, subsetItem ]* ]
        DEFINE  variable AS condition [, variable AS condition ]*
        ')'

order:
        ORDER OF tableIdentifier
    |   [orderItem](jdbc_dev_calcite_ref_statementSelectOrder.md) [, [orderItem](jdbc_dev_calcite_ref_statementSelectOrder.md) ]*

skip:
        SKIP TO NEXT ROW
    |   SKIP PAST LAST ROW
    |   SKIP TO FIRST identifier
    |   SKIP TO [ LAST ] identifier

pattern:
        patternTerm [ '|' patternTerm ]*

patternTerm:
        patternFactor [ patternFactor ]*

patternFactor:
        patternPrimary [ patternQuantifier ]

patternPrimary:
        identifier
    | 	'(' pattern ')'
    | 	'{-' pattern '-}'
    | 	PERMUTE '(' pattern [ , pattern ]* ')'

patternQuantifier:
        '*'
    |   '+'
    |   '{' minRepeatNumericLiteral [ , maxRepeatNumericLiteral ] '}'
    |   '{' repeatNumericLiteral '}'
    |   '{-' pattern '-}'


intervalLiteral:
        INTERVAL [ '-' | '+' ] stringLiteral timeUnit [ TO timeUnit ]

subsetItem:
        identifier '=(' expression [ , expression ]* ')'

orderItem

expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

pivot

pivot:
        PIVOT '('
        pivotAgg [, pivotAgg ]*
        FOR { column |  '(' column [, column ]* ')'
        IN '(' pivotExpression [, pivotExpression ]* ')'
        ')'

pivotAgg:
        agg '(' [ ALL | DISTINCT ] value [, value ]* ')'
        [ [ AS ] alias ]

pivotExpression:
        { expression |  '(' expression [, expression ]* ')' } [ [ AS ] alias ]

projectItem

expression [ [ AS ] columnAlias ] | tableAlias . *

tableExpression

tableExpression:
        tableReference [, tableReference ]*
    |   tableExpression
        [ NATURAL ] [ { LEFT | RIGHT | FULL } [ OUTER ] ]
        JOIN tableExpression
        [ joinCondition ]
    |   tableExpression CROSS JOIN tableExpression
    |   tableExpression [ CROSS | OUTER ] APPLY tableExpression

tableReference:
        tablePrimary
        [ FOR SYSTEM_TIME AS OF expression ]
        [ [pivot](jdbc_dev_calcite_ref_statementSelectPivot.md) ]
        [ [unpivot](jdbc_dev_calcite_ref_statementSelectUnpivot.md) ]
        [ [matchRecognize](jdbc_dev_calcite_ref_statementSelectMatchRecognize.md) ]
        [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]

tablePrimary:
        [ [ catalogName . ] schemaName . ] tableName
        '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'
    |   tablePrimary [ [hintComment](jdbc_dev_calcite_ref_statementSelectHintComment.md) ] [ EXTEND ] columnDeclarations
    |   [ LATERAL ] '(' [query](jdbc_dev_calcite_ref_statementSelect.md) ')'
    |   UNNEST '(' expression ')' [ WITH ORDINALITY ]
    |   [ LATERAL ] TABLE
        '(' [ SPECIFIC ] functionName '(' expression [, expression ]* ')' ')'

joinCondition:
        ON booleanExpression
    |   USING '(' column [, column ]* ')'

columnDeclarations:
        '(' column type [ NOT NULL ] [, column type [ NOT NULL ] ]* ')'

unpivot

unpivot:
        UNPIVOT [ INCLUDING NULLS | EXCLUDING NULLS ] '('
        { column | '(' column [, column ]* ')' }
        FOR { column | '(' column [, column ]* ')' }
        IN '(' unpivotValue [, unpivotValue ]* ')'
        ')'

unpivotValue:
        column [ AS literal ]
    |   '(' column [, column ]* ')' [ AS '(' literal [, literal ]* ')' ]

values

values:
        { VALUES | VALUE } rowConstructor [, rowConstructor ]*

rowConstructor:
        { '(' ROW rowSpec ')' | ROW } rowSpec | expression

rowSpec:
        '(' { { values | [query](jdbc_dev_calcite_ref_statementSelect.md) } | DEFAULT } [ , { expression | DEFAULT } ]* ')'

windowSpec

'('
[ windowName ]
[ ORDER BY orderItem [, orderItem ]* ]
[ PARTITION BY expression [, expression ]* ]
[
    RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
|   ROWS numericExpression { PRECEDING | FOLLOWING }
]
')'

withItem

name
[ '(' column [, column ]* ')' ]
AS '(' [query](jdbc_dev_calcite_ref_statementSelect.md) ')'

SET

[ ALTER { SYSTEM | SESSION } ] SET identifier { TO | '=' } { literal | identifier | ON }

SET CONSTRAINTS

SET CONSTRAINTS { ALL | identifier [ , identifier ] } { DEFERRED | IMMEDIATE }

SET TRANSACTION

SET [ LOCAL ] TRANSACTION [transactionCharacteristics](jdbc_dev_calcite_ref_statementTransactionCharacteristics.md)

START TRANSACTION

START TRANSACTION transactionCharacteristics

transactionCharacteristics

transactionCharacteristics:
[ transactionMode [ , transactionMode ]* ]

transactionMode:
ISOLATION LEVEL transactionLevel
| { READ WRITE | READ ONLY }
| DIAGNOSTICS SIZE UnsignedIntLiteral

transactionLevel:
READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE

UPDATE

UPDATE [tablePrimary](jdbc_dev_calcite_ref_statementSelectTableExpression.md)
SET identifier '=' expression [, identifier '=' expression ]*
[ WHERE booleanExpression ]

Tokenization Types

This section describes how to use each of the Protegrity tokenization types with the JDBC Protector and supplements the Protegrity Protection Methods Reference Guide.

The following tokenization types are not currently supported:

• Unicode
• Binary

Numeric (0-9)

The Numeric token type tokenizes digits from 0 to 9.

Integer (0-9)

The Integer token type tokenizes 2, 4, or 8 byte sized integers.

Credit Card

The Credit Card token type helps maintain transparency, and also provides a way to clearly distinguish a token from the real value, which is a recommendation of the PCI DSS.

Alpha (A-Z)

The Alpha token type tokenizes both uppercase and lowercase letters.

Upper-case Alpha (A-Z)

The Upper-case Alpha token type tokenizes all alphabetic symbols as uppercase. After de-tokenization, all alphabetic symbols are returned as uppercase. This means that initial and detokenized values would not match if the input contains lowercase letters.

Alpha-Numeric (0-9, a-z, A-Z)

The Alpha-Numeric token type tokenizes all alphabetic symbols (both lowercase and uppercase letters), as well as digits from 0 to 9.

Upper Alpha-Numeric (0-9, A-Z)

The Upper Alpha-Numeric token type tokenizes uppercase letters A through Z and digits 0 to 9.

Lower ASCII

The Lower ASCII token type is provided to address the handling of spaces in such data types as CHAR and VARCHAR.

Printable

The Printable token type tokenizes ASCII printable characters from the ISO 8859-15 alphabet, which include letters, digits, punctuation marks, and miscellaneous symbols.

Date (YYYY-MM-DD, DD/MM/YYYY, MM.DD.YYYY)

The Date token type protects dates in big endian, little endian, or middle endian form.

The following formats are supported:

• YYYY<delim>MM<delim>DD
• DD<delim>MM<delim>YYYY
• MM<delim>DD<delim>YYYY

where <delim> is one of the allowed separators: . (dot), / (slash), or - (dash).

Date token types are not fully validated for input value as valid date. For example, a day value greater than 31 and month value greater than 12 results in an error, but the date input 2011-02-30 does not. However, the detokenized value would be 2011-03-02, which is not the initial value.

The following table shows examples of the way in which a value will be tokenized with the Date token.

• Date Tokenization for Cutover Dates of the Proleptic Gregorian Calendar

  Java does not accept the cutover dates of the Proleptic Gregorian Calendar. The cutover dates of the Proleptic Gregorian Calendar fall in the interval 1582-10-05 to 1582-10-14. These dates are converted to 1582-10-15. Conversion occurs by adding ten days to the source date. Due to this conversion, data loss occurs as the system is not capable to return the actual date value after the de-tokenization.

  The following points are applicable for the tokenization and de-tokenization of the cutover dates of the Proleptic Gregorian Calendar:

  • The tokenization of the date values in the cutover date range of the Proleptic Gregorian Calendar results in an ‘Invalid Input’ error.
  • During tokenization, an internal validation is performed to check whether the value is tokenized to the cutover date. If it is a cutover date, then the Year part (1582) of the tokenized value is converted to 3338 and then returned. During de-tokenization, an internal check is performed to validate whether the Year is 3338. If the Year is 3338, then it is internally converted to 1582.

Datetime (YYYY-MM-DD HH:MM:SS)

The Datetime token type was introduced in response to requirements to allow specific date parts to remain in the clear and for date tokens to be distinguishable from real dates. This token type will also allow for time to be tokenized (HH:MM:SS) with the exception of milliseconds (MMM).

The following table shows examples of the way in which a value will be tokenized with the Datetime token.

• Date Tokenization for Cutover Dates of the Proleptic Gregorian Calendar

  Java does not accept the cutover dates of the Proleptic Gregorian Calendar. The cutover dates of the Proleptic Gregorian Calendar fall in the interval 1582-10-05 to 1582-10-14. These dates are converted to 1582-10-15. Conversion occurs by adding ten days to the source date. Due to this conversion, data loss occurs as the system is not capable to return the actual date value after the de-tokenization.

Decimal

The Decimal token type tokenizes numbers which may have a precision and scale. The resulting token does not contain any zeros which makes it suitable to store in a decimal data type in a database. Any sign or decimal point delimiter are stripped from the input value before tokenization and put back after tokenization.

Email

Email token type allows tokenization of an email address. Email token keeps the domain name along with all characters after the “@” sign, and the “@” sign itself in the clear. The local part (i.e. the part before “@”) gets tokenized.

No Encryption

The No Encryption protection method when applied lets sensitive data be stored in the clear, but either monitors or masks its usage through a data security policy. The following table illustrates the supported data types and supplements the Protegrity Protection Methods Reference Guide.

Encryption Algorithms

Encryption is the conversion of data into a ciphertext using an algorithmic scheme. The following table illustrates the supported data types and supplements the Protegrity Protection Methods Reference Guide.

Hashing

The JDBC Protector does not currently support the Protegrity hashing protection methods.

Masking

For situations where data output restrictions must be applied for users, the Masking method can be used. The following table illustrates the supported data types and supplements the Protegrity Protection Methods Reference Guide.

Data Types

This section describes the data types that you can use with the JDBC Protector.

Standard Data Types Reference

The JDBC Protector leverages Apache Calcite for supporting the standard SQL data types. For more information and a list of available data types, refer to the Apache Calcite SQL Language reference guide.

Conversion Between Java and JDBC Types

The Protegrity JDBC Protector is flexible in the way it handles conversions between JDBC types and Java types. Any JDBC type can be converted to a String. Calling the ResultSet.getObject() method will return the Java object provided by the database driver. The following table shows the possible ResultSet conversions for each SQL type.

Functions and Operators

This section describes the SQL functions and operators that you can use with the JDBC Protector. Additionally, refer to the documentation for your database provider. Functions and operators must be supported by both the JDBC Protector and your database provider to use them in your SQL statements.

Standard Functions and Operators Reference

The JDBC Protector leverages Apache Calcite to provide common functions and operators for use in your SQL statements. For more information and a list of available functions and operators, refer to the Apache Calcite SQL Language reference guide.

The following SQL functions are provided in addition to those provided by Apache Calcite:

Protegrity Functions

The JDBC Protector provides SQL functions for controlling when the data protection operations occur. This can be used in combination with other SQL functions to, for example, encode protected data before it is stored in the database. When the JDBC Protector encounters one of the functions below in a SQL query, it runs the input expression locally and applies the data protection operation to the result.

pty_protect

Protects values using the given data element.

PTY_PROTECT(dataElement, values)

• Parameters

  Name	Type	Description
  dataElement	VARCHAR	Name of the data element.
  values	Any	Data to protect. For more information on supported data types, see Tokenization Types.

• Returns

  Protected values.

• Exception

  If the user does not have protect access rights in the policy or the input format is not supported, the UDF terminates with an error message.

• Example

  select pty_protect('deTokName', column) from table
  

  select "pty_protect"('deTokName', column) from table
  

pty_unprotect

Unprotects values using the given data element.

PTY_UNPROTECT(dataElement, values)

• Parameters

  Name	Type	Description
  dataElement	VARCHAR	Name of the data element.
  values	Any	Data to unprotect. For more information on supported data types, see Tokenization Types.

• Returns

  Unprotected value.

• Exception

  If the user does not have unprotect access rights in the policy or the input format is not supported, the UDF terminates with an error message.

• Example

  select pty_unprotect('deTokName', column) from table
  

  select "pty_unprotect"('deTokName', column) from table
  

User-Defined Functions

A user-defined function (UDF) is a SQL function that invokes a Java class to transform data. It can be used in combination with Protegrity Functions to modify how the data protection is applied.

A list of Java class names providing UDFs can be provided as the ptyFunctions property when opening a JDBC connection. The Java classes should be POJOs with one or more public static methods. These methods will be made available as a SQL function with the same name as the method.

The example below executes a SQL function using a UDF defined in the MyPlusFunction class. If this class is added to the com.example package, then the ptyFunctions connection property should be set to com.example.MyPlusFunction.

public class MyPlusFunction {
    public static void int MY_PLUS(int left, int right) {
        return left + right;
    }
}

SELECT MY_PLUS(1, 2)

For more information about which Java types are used for each SQL type, refer to the Conversion Between Java and JDBC Types section.

1.8.1 -

Tokenization Types

This section describes how to use each of the Protegrity tokenization types with the JDBC Protector and supplements the Protegrity Protection Methods Reference Guide.

The following tokenization types are not currently supported:

• Unicode
• Binary

Numeric (0-9)

The Numeric token type tokenizes digits from 0 to 9.

Integer (0-9)

The Integer token type tokenizes 2, 4, or 8 byte sized integers.

Credit Card

The Credit Card token type helps maintain transparency, and also provides a way to clearly distinguish a token from the real value, which is a recommendation of the PCI DSS.

Alpha (A-Z)

The Alpha token type tokenizes both uppercase and lowercase letters.

Upper-case Alpha (A-Z)

The Upper-case Alpha token type tokenizes all alphabetic symbols as uppercase. After de-tokenization, all alphabetic symbols are returned as uppercase. This means that initial and detokenized values would not match if the input contains lowercase letters.

Alpha-Numeric (0-9, a-z, A-Z)

The Alpha-Numeric token type tokenizes all alphabetic symbols (both lowercase and uppercase letters), as well as digits from 0 to 9.

Upper Alpha-Numeric (0-9, A-Z)

The Upper Alpha-Numeric token type tokenizes uppercase letters A through Z and digits 0 to 9.

Lower ASCII

The Lower ASCII token type is provided to address the handling of spaces in such data types as CHAR and VARCHAR.

Printable

The Printable token type tokenizes ASCII printable characters from the ISO 8859-15 alphabet, which include letters, digits, punctuation marks, and miscellaneous symbols.

Date (YYYY-MM-DD, DD/MM/YYYY, MM.DD.YYYY)

The Date token type protects dates in big endian, little endian, or middle endian form.

The following formats are supported:

• YYYY<delim>MM<delim>DD
• DD<delim>MM<delim>YYYY
• MM<delim>DD<delim>YYYY

where <delim> is one of the allowed separators: . (dot), / (slash), or - (dash).

Date token types are not fully validated for input value as valid date. For example, a day value greater than 31 and month value greater than 12 results in an error, but the date input 2011-02-30 does not. However, the detokenized value would be 2011-03-02, which is not the initial value.

The following table shows examples of the way in which a value will be tokenized with the Date token.

• Date Tokenization for Cutover Dates of the Proleptic Gregorian Calendar

  Java does not accept the cutover dates of the Proleptic Gregorian Calendar. The cutover dates of the Proleptic Gregorian Calendar fall in the interval 1582-10-05 to 1582-10-14. These dates are converted to 1582-10-15. Conversion occurs by adding ten days to the source date. Due to this conversion, data loss occurs as the system is not capable to return the actual date value after the de-tokenization.

  The following points are applicable for the tokenization and de-tokenization of the cutover dates of the Proleptic Gregorian Calendar:

  • The tokenization of the date values in the cutover date range of the Proleptic Gregorian Calendar results in an ‘Invalid Input’ error.
  • During tokenization, an internal validation is performed to check whether the value is tokenized to the cutover date. If it is a cutover date, then the Year part (1582) of the tokenized value is converted to 3338 and then returned. During de-tokenization, an internal check is performed to validate whether the Year is 3338. If the Year is 3338, then it is internally converted to 1582.

Datetime (YYYY-MM-DD HH:MM:SS)

The Datetime token type was introduced in response to requirements to allow specific date parts to remain in the clear and for date tokens to be distinguishable from real dates. This token type will also allow for time to be tokenized (HH:MM:SS) with the exception of milliseconds (MMM).

The following table shows examples of the way in which a value will be tokenized with the Datetime token.

• Date Tokenization for Cutover Dates of the Proleptic Gregorian Calendar

  Java does not accept the cutover dates of the Proleptic Gregorian Calendar. The cutover dates of the Proleptic Gregorian Calendar fall in the interval 1582-10-05 to 1582-10-14. These dates are converted to 1582-10-15. Conversion occurs by adding ten days to the source date. Due to this conversion, data loss occurs as the system is not capable to return the actual date value after the de-tokenization.

Decimal

The Decimal token type tokenizes numbers which may have a precision and scale. The resulting token does not contain any zeros which makes it suitable to store in a decimal data type in a database. Any sign or decimal point delimiter are stripped from the input value before tokenization and put back after tokenization.

Email

Email token type allows tokenization of an email address. Email token keeps the domain name along with all characters after the “@” sign, and the “@” sign itself in the clear. The local part (i.e. the part before “@”) gets tokenized.

1.9 - Known Limitations

The JDBC Protector aims to support many database types and versions, and provide compatibility with other Java APIs and libraries. This section lists the known incompatibilities and limitations. Full compatibility with every database and Java library cannot be guaranteed, and additional limitations may not be listed here.

The protector has the following limitations:

• CLOUDS-1250: When a query ends with a semicolon, an exception is thrown stating “parse failed: Encountered “;””.

  • Workaround: Remove the semicolon at the end of the query.

• CLOUDS-1324: When inserting a null value into a decimal column, an exception can be thrown stating “Unable to implement JdbcToEnumerableConverter”.

  • Workaround: Add a non-null value for the preceding column in the insert query.

• CLOUDS-2101: The Protegrity reprotect operation is not currently supported. Please submit an RFE if you require this feature in the JDBC Protector.

  • Workaround: Perform an unprotect operation followed by a protect operation.

• CLOUDS-2750: A pty_protect() call with an integer data type will cause the query to fail when the mask character is a non-numeric character such as an asterisk, hash, or hyphen.

  • Workaround: Either cast the integer value to a char type, or change the mask character to a number.

• CLOUDS-3057: The SQL functions CURRENT_ROLE, CURRENT_USER, SESSION_USER, SYSTEM_USER, and USER only work when connecting to a PostgreSQL database.

  • Workaround: Often these values can be obtained from other sources or replaced with a literal value.

• CLOUDS-3301: The QUARTER and WEEK intervals return incorrect results when used with the PostgreSQL DATE_ADD and DATE_SUBTRACT functions. The CENTURY, DECADE, DOW, DOY, EPOCH, and MILLENNIUM intervals cause an exception to be thrown.

  • Workaround: Convert the interval into a DAY, MONTH, or YEAR interval.

• CLOUDS-3409: A NullPointerException may be thrown when an UPDATE statement contains a sub-query.

  • Workaround: Remove the sub-query from the UPDATE statement and execute it separately. Then use the result of the sub-query in the UPDATE statement.

• CLOUDS-3601: The third-party libraries packaged with the Protegrity JDBC driver may be different from the versions of the same libraries used by your application. This can cause exceptions or undefined behavior when the versions do not match.

  • Workarounds:
    • See this section on how to install Protegrity JDBC Driver using Apache Maven.
    • Load the Protegrity JDBC driver in an isolated ClassLoader.

• CLOUDS-3602: The Protegrity Application Protector - JDBC includes the SLF4J library for your convenience in case your application does not already have SLF4J. Additional or different JAR files may be required to properly integrate the Protegrity JDBC driver logs with your application logging.

  • Workarounds:
    • See this section on how to install Protegrity JDBC Driver using Apache Maven.
    • Load the Protegrity JDBC driver in an isolated ClassLoader and use the included SLF4J Simple logging.

• CLOUDS-3722: Some data type conversions are not supported and throw an exception when they include a protected timestamp column with more than 3 digits of sub-second precision, such as TIMESTAMP(6) or TIMESTAMP(9). This includes converting from / to CHAR, VARCHAR, DATE, TIME, TIMESTAMP(0), and TIMESTAMP(3) columns.

  • Workaround: Perform the data type conversion in Java then use the result in your SQL query.

• CLOUDS-3783: Sorting on a protected column uses lexicographic ordering based on the Unicode value of each character. This ordering may be different from cleartext columns that use the database’s collation-based ordering which includes locale-specific ordering.

  • Workaround: Re-order the rows in Java if locale-specific ordering is required.

• CLOUDS-6456: XA transaction support is only available for PostgreSQL.

• CLOUDS-6539: When using SQL Server JDBC Driver version 13.x, Protector does not resolve references to the dbo schema. References to dbo result in the error “Object ‘dbo’ not found within ‘adhoc.’”.

  • Workaround: Use SQL Server JDBC Driver version 12.x. Alternatively, avoid usage of the dbo schema in SQL queries.

• CLOUDS-6749: A DELETE statement with a sub-query is disabled for all vendors other than PostgreSQL.

  • Workaround: Remove the sub-query from the DELETE statement and execute it separately. Then use the result of the sub-query to remove matching rows.

• CLOUDS-7532: The protector will process data from postgres tables with CITEXT, but CITEXT explicitly written into any input statement will throw an error.

  • Workaround: Remove explicit CITEXT specifiers in input statements where they are not required.

JDBC API

The following JDBC API methods are not implemented and will throw an exception. The application can bypass the JDBC Protector and invoke these methods from the underlying native driver’s connection; however, this will not utilize the JDBC Protector’s transparent protection capabilities.

• Connection#abort(Executor)
• Connection#createBlob()
• Connection#createClob()
• Connection#createNClob()
• Connection#createSQLXML()
• Connection#createStruct(String, Object[])
• ResultSet methods that modify the data
• ResultSet methods that modify the cursor
• ResultSet#isLast()
• Statement#getGeneratedKeys()

SQL Statements

The JDBC Protector can execute DQL and DML statements, such as SELECT, INSERT, UPDATE, and DELETE, in a variety of vendor-specific dialects. While a wide range of vendor-specific SQL extensions are supported, there is no guarantee that the JDBC Protector will handle every SQL statement without modification. The following SQL statements are known to not be supported:

• DDL statements such as CREATE, ALTER, or DROP
• DCL statements such as GRANT or REVOKE
• DELETE statements with a sub-query are supported only on PostgreSQL
• UPSERT statements
• Some vendor-specific SQL extensions are not supported, including:
  • Informix: Sequences are not supported
  • MariaDB: Sequences are not supported
  • PostgreSQL: ON CONFLICT statements which contain DO UPDATE or ON CONSTRAINT, or which do not use dynamic parameters in a PreparedStatement for protected columns

The application can bypass the JDBC Protector and invoke these methods from the underlying native driver’s connection; however, this will not utilize the JDBC Protector’s transparent protection capabilities.

Stored Procedures

The following limitations apply only to procedure calls:

• Procedure calls must use CallableStatement
• Literal input values to procedure calls are not protected
• Procedure calls that produce a ResultSet are not unprotected

1.10 - Troubleshooting

Troubleshooting

1.11 - Appendix

Reference material and configuration details.

Avatica

Apache Avatica is an open-source HTTP/REST server that provides SQL access to an Avatica-compliant client written in other languages. When used with the Protegrity JDBC Protector, it allows applications written in C#, Go, and Python to work with protected data.

A standalone server is provided for running Avatica as a service or as a Docker container. It is recommended to use the latest 1.x version of Avatica with the JDBC Protector. The server can be downloaded from Maven Central.

The following snippet shows an example command-line for running the Avatica standalone server with the JDBC Protector:

java -cp "protegrity-jdbc-<version>.jar:protegrity-protect-linux64-<version>.jar:avatica-standalone-server.jar:mysql-connector-java.jar" -Dprotegrity.cloud.protect.uri=rps://esa.example.com:443 org.apache.calcite.avatica.standalone.StandaloneServer -p 8765 -u "jdbc:iap:mysql://localhost:3306/database"

The following is a list of clients that support Avatica:

• Microsoft .NET driver for Apache Phoenix Query Server
  • Home page
  • Language: C#
  • License: Apache License 2.0
• Apache Phoenix/Avatica SQL Driver
  • Home page
  • Language: Go
  • License: Apache License 2.0
• Apache Phoenix database adapter for Python
  • Home page
  • Language: Python
  • License: Apache License 2.0

For more information on Apache Avatica, refer to the Avatica documentation.

Connection Properties

The following table describes the JDBC connection properties accepted by the JDBC Protector. The protector uses Apache Calcite and supports Calcite’s configuration properties. The Apache Calcite documentation can be referenced to better understand the options for any of these configuration properties.

Java Properties

The following table describes Java system properties that can be used to modify the behavior of the JDBC Protector.

Model Configuration

The model.json file allows customizing the database schemas exposed by the JDBC Protector and how they map to an underlying JDBC connection.

The example below defines a schema called sales that matches the name of a database in a MySQL server. The defaultSchema property defines which schema to search if a SQL query uses a table that is not fully qualified. The jdbcDriver, jdbcUrl, user, and password properties describe the JDBC connection to a MySQL database that will be protected by the JDBC Protector. The ptyCatalogUri described in Connection Properties can be defined in the operand object.

{
    "version": "1.0",
    "defaultSchema": "sales",
    "schemas": [
        {
            "name": "sales",
            "factory": "com.protegrity.jdbc.JdbcSchemaFactory",
            "operand": {
                "jdbcDriver": "com.mysql.jdbc.Driver",
                "jdbcUrl": "jdbc:mysql://localhost:3306/sales",
                "user": "admin",
                "password": "",
                "ptyCatalogUri": "file:/opt/schema.json"
            },
            "type": "custom"
        }
    ]
}

Modify your application’s JDBC URL to use only properties defined in Connection Properties. For example:. For example:

jdbc:iap:model=path/to/model.json;lex=mysql;conformance=mysql_5

Schema Configuration

The schema.json file describes the database schema and maps columns to data elements from your Protegrity security policy. At a high level, the file lists the databases, tables, and columns that need to be protected. When a query is executed, the JDBC Protector will scan the schema.json file for a matching database, table, and column name ignoring case. If a match is found, the JDBC Protector will use the specified data element to transparently protect and unprotect that column.

The ptyCatalogUri connection property tells the JDBC Protector where to look for the schema.json file. Use a file URI when the schema.json file is a local file. For example, file:/path/dbschema.json indicates the schema configuration is in the local file /path/dbschema.json. Use a classpath URI when the schema.json file is in an application configuration directory or a JAR file that has been added to the JVM classpath. For example, classpath:dbschema.json causes the JDBC Protector to search the classpath for the dbschema.json file.

Each database entry in the schema.json file should contain the name of a database in your database server and a list of table entries. The database name may also refer to a schema name or catalog name if supported by your database server. Each table entry should contain the name of a database table and a list of column entries. Each column entry should contain the name of a table column and the name of a data element within your Protegrity security policy.

The following JSON document shows how the catalog should be constructed:

{"schema":
  {"database": [
    {"name": "SCHEMA NAME or DATABASE NAME", "table": [
      {"name": "TABLE NAME", "column": [
        {"name": "COLUMN NAME", "dataelement": "DATA ELEMENT NAME"}
      ]}
    ]}
  ]}
}

The input and output parameters for stored procedures can also be defined. The following example shows a stored procedure with two input parameters and one output parameter. The input parameters will be protected before the stored procedure is called, and the output parameter will be unprotected before its value is returned to the application.

{"schema":
    {"function": [
        {"name": "SetLastNameForId", "parameter": [
            {"dataelement": null},
            {"dataelement": "deTokName"},
            {"dataelement": "deTokName"}
        ]}
    ]}
}

PostgreSQL

When used with PostgreSQL, the database name must be the PostgreSQL schema name. The PostgreSQL catalog name is not used. The following example shows a JSON catalog for a sales catalog in PostgreSQL containing employees and customers tables.

{"schema":
  {"database": [
    {"name": "public", "table": [
      {"name": "employees", "column": [
        {"name": "firstName", "dataelement": "deTokName"},
        {"name": "lastName", "dataelement": "deTokName"},
        {"name": "email", "dataelement": "deTokEmail"}
      ]},
      {"name": "customers", "column": [
        {"name": "customerName", "dataelement": "deTokName"},
        {"name": "city", "dataelement": "deTokCity"},
        {"name": "postalCode", "dataelement": "deTokPostCD"}
      ]}
    ]}
  ]}
}

SQL Server

When used with SQL Server, the database name must be the SQL Server schema name. The SQL Server database name is not used. The following example shows a catalog for a sales database containing employees and customers tables.

{"schema":
  {"database": [
    {"name": "dbo", "table": [
      {"name": "employees", "column": [
        {"name": "firstName", "dataelement": "deTokName"},
        {"name": "lastName", "dataelement": "deTokName"},
        {"name": "email", "dataelement": "deTokEmail"}
      ]},
      {"name": "customers", "column": [
        {"name": "customerName", "dataelement": "deTokName"},
        {"name": "city", "dataelement": "deTokCity"},
        {"name": "postalCode", "dataelement": "deTokPostCD"}
      ]}
    ]}
  ]}
}

Stored Procedures

The JDBC Protector supports data protection for stored procedures when used with CallableStatement and dynamic parameters. The input and output parameters should be defined in schema.json along with their associated data elements.

The following Java code provides an example of calling a stored procedure with two input parameters and one output parameter. The value of the second parameter will be protected before the stored procedure is called, and the value of the third parameter will be unprotected before its value is returned to the application.

CallableStatement stmt = connection.prepareCall("call SetLastNameForId(4, ?, ?)");
stmt.setString(1, "Smith");
stmt.registerOutParameter(2, Types.VARCHAR);
stmt.execute();
String fullName = stmt.getString(2);

Here is the schema.json used for the above example:

{"schema":
    {"function": [
        {"name": "SetLastNameForId", "parameter": [
            {"dataelement": null},
            {"dataelement": "deTokName"},
            {"dataelement": "deTokName"}
        ]}
    ]}
}

Configure Protegrity XA Data Source

This topic describes how to configure JDBC Application Protector for XA transactions.

Prerequisites

• Protegrity JDBC driver installed. See the installation section for driver setup.
• XA is supported since JDBC Application Protector v9.1.0.12.

JDBC Application Protector provides com.protegrity.jdbc.xa.ProtegrityXADataSource for support of XA transactions.

This data source must be configured with the following properties:

• url - JDBC URL data source is connected to
• user - JDBC username (optional)
• password - JDBC password (optional)
• catalogUri - Protegrity catalog URI. For information about configuring catalog URI, see Protegrity Connection Properties.
• protectUri - Protegrity protection service URI. For information about configuring protect URI, see Protegrity Security Policy.
• delegateClassName - Native XA data source class name. Protegrity XA data source will delegate all operations to this class.
• policyUser - Policy user for security operations (optional)

Configuration properties may be initialized using any of the methods described below. The initialization follows a specific precedence order: if the property is not initialized using class fields, it will then be looked up from the JDBC URL. If not found there, initialization will attempt to retrieve the property from java.util.Properties. If still not found, it will check Java system properties, and finally, it will fall back to environment variables.

Configuration options in precedence order:

1. Using class fields
2. Using JDBC URL
3. Using java.util.Properties
4. Using Java system properties
5. Using environment variables

Once initialized, see the following examples for using XA data source. Alternatively, XA data source can be configured using JNDI.

Method 1: Initialize Using Class Fields

public static void main(String[] args) {
    var xaDataSource = new ProtegrityXADataSource();
    xaDataSource.setUsername("username");
    xaDataSource.setPassword("password");
    xaDataSource.setDelegateClassName("org.postgresql.xa.PGXADataSource");
    xaDataSource.setCatalogUri("file:/pty-schema.json");
    xaDataSource.setProtectUri("rps://esa.example.com:443");
    xaDataSource.setUrl("jdbc:postgresql://host:port/database");
    xaDataSource.setPolicyUser("policy-user");
}

Method 2: Initialize Using a JDBC URL

The following parameters are available:

• user
• password
• ptyCatalogUri
• ptyProtectUri
• delegateClassName
• ptyPolicyUser

public static void main(String[] args) {
    var xaDataSource = ProtegrityXADataSource.fromJdbcUrl(
        "jdbc:postgresql://host:port/database" +
        "?user=user" +
        "&password=password" +
        "&ptyCatalogUri=file:/pty-schema.json" +
        "&ptyProtectUri=rps://esa.example.com:443" +
        "&delegateClassName=org.postgresql.xa.PGXADataSource" +
        "&ptyPolicyUser=policy-user"
    );
}

Method 3: Initialize Using java.util.Properties

import java.util.Properties;

public static void main(String[] args) {
    var properties = new Properties();
    properties.put(ProtegrityXADataSource.PropertyNames.URL, "jdbc:postgresql://host:port/database");
    properties.put(ProtegrityXADataSource.PropertyNames.USER, "username");
    properties.put(ProtegrityXADataSource.PropertyNames.PASSWORD, "password");
    properties.put(ProtegrityXADataSource.PropertyNames.CATALOG_URI, "file:/pty-schema.json");
    properties.put(ProtegrityXADataSource.PropertyNames.PROTECT_URI, "rps://esa.example.com:443");
    properties.put(ProtegrityXADataSource.PropertyNames.DELEGATE_CLASS_NAME, "org.postgresql.xa.PGXADataSource");
    properties.put(ProtegrityXADataSource.PropertyNames.POLICY_USER, "policy-user");

    var xaDataSource = new ProtegrityXADataSource(properties);
}

Method 4: Initialize Using Java System Properties

public static void main(String[] args) {
    System.setProperty("com.protegrity.jdbc.url", "jdbc:postgresql://host:port/database");
    System.setProperty("com.protegrity.jdbc.user", "username");
    System.setProperty("com.protegrity.jdbc.password", "password");
    System.setProperty("com.protegrity.jdbc.delegateClassName", "org.postgresql.xa.PGXADataSource");
    System.setProperty("com.protegrity.jdbc.ptyCatalogUri", "file:/pty-schema.json");
    System.setProperty("com.protegrity.jdbc.ptyProtectUri", "rps://esa.example.com:443");
    System.setProperty("com.protegrity.jdbc.ptyPolicyUser", "policy-user");

    var xaDataSource = new ProtegrityXADataSource();
}