Data Warehouse Protectors

• 1: Deploying the Data Warehouse Protectors
• 2: Teradata Data Warehouse Protector
• 2.1: Understanding the Architecture
• 2.2: System Requirements
• 2.3: Preparing the Environment
• 2.3.1: Extracting the Teradata Installation Package
• 2.3.2: Installing the Log Forwarder
• 2.3.3: Installing the Resilient Package Agent
• 2.4: Installing the Protector
• 2.4.1: Installing the Teradata Objects
• 2.4.2: Creating the Teradata User Defined Functions (UDFs)
• 2.4.3: Installing the Teradata User Defined Types (UDTs)
• 2.4.4: Creating the Teradata User Defined Types (UDTs)
• 2.5: Configuring the Protector
• 2.5.1: Working with the config.ini file
• 2.5.1.1: Accessing the config.ini File
• 2.5.1.2: Understanding the Parameters in the config.ini File
• 2.5.2: Updating the Output Buffer for the Unicode UDFs
• 2.5.2.1: Updating the dbpuserconf.ini file
• 2.5.2.2: Updating the createvarcharunicode.sql file
• 2.6: Uninstalling the Protector
• 2.6.1: Uninstalling the Log Forwarder
• 2.6.2: Uninstalling the RPAgent
• 2.6.3: Uninstalling the User Defined Functions (UDFs)
• 2.6.4: Removing the Installation Directory
• 3: User Defined Functions and APIs
• 3.1: Teradata UDFs
• 3.1.1: General UDFs
• 3.1.2: Access Check UDFs
• 3.1.3: Varchar Latin UDFs
• 3.1.4: Varchar Unicode UDFs
• 3.1.5: Float UDFs
• 3.1.6: Small Integer UDFs
• 3.1.7: Integer UDFs
• 3.1.8: Big Integer UDFs
• 3.1.9: Date UDFs
• 3.1.10: 8-Byte and 16-Byte Decimal UDFs
• 3.1.11: JSON UDFs
• 3.1.12: XML UDFs
• 3.1.13: Float UDFs for No Encryption
• 3.1.14: Date UDFs for No Encryption
• 3.1.15: 8-Byte AND 16-Byte Decimal UDFs for No Encryption
• 3.2: Trino User Defined Functions and Procedures
• 3.2.1: General UDFs
• 3.2.2: VarChar UDFs
• 3.2.3: BigInt UDFs
• 3.2.4: SmallInt UDFs
• 3.2.5: Integer UDFs
• 3.2.6: Date UDFs
• 3.2.7: DateTime UDFs
• 3.2.8: VarChar Encryption UDFs
• 3.2.9: Unicode UDFs
• 3.2.10: Decimal UDFs
• 3.2.11: Double UDFs
• 3.2.12: VarBinary Encryption UDFs
• 4: Appendix
• 4.1: Additional references for the Protectors
• 4.1.1: Additional references for the Teradata Protector
• 4.1.1.1: Configuring access to execute queries
• 4.1.1.2: Teradata Query Bands and Trusted Sessions
• 4.2: Data Warehouse Sample Scripts
• 4.2.1: Teradata Database Data Warehouse Sample Scripts
• 4.3: Return Codes for Data Warehouse Protectors
• 4.4: Supported Data Warehouse Protectors Matrix
• 5: Trino Data Warehouse Protector
• 5.1: Understanding the Architecture
• 5.2: System Requirements
• 5.3: Preparing the Environment
• 5.3.1: Extracting the Files from the Installation Package
• 5.3.2: Executing the Configurator Script
• 5.4: Installing the Trino Protector
• 5.5: Configuring the Trino Protector
• 5.5.1: Working with Cluster Utilities
• 5.5.1.1: Log Forwarder Control Script
• 5.5.1.2: RPAgent Control Script
• 5.5.1.3: Sync Config.ini
• 5.5.1.4: Sync RPAgent
• 5.5.1.5: Sync Log Forwarder
• 5.6: Uninstalling the Trino Protector

1 - Deploying the Data Warehouse Protectors

This page discusses the deployment process for the Protegrity Data Warehouse Protector.

Deploying the Protegrity Data Warehouse Protector involves the following key steps:

1. The customer installs and initializes the required Data Warehouse Protector.
2. The configurations that are required for the initialization process, are passed to the protector by using the config.ini file.
3. The RPAgent synchronizes with the RP Proxy or ESA at regular intervals and checks for any changes in the policy. If there is a change in policy, then the RPAgent downloads the updated policy package over a TLS channel and stores in the shared memory.
4. The protector synchronizes with the shared memory using the cadence value set in the config.ini file. Any updates in the policy are fetched in the policy package. The policy is available in the shared memory and the policy package is available in the process memory. The updated policy package is read from the process memory and is used to perform the data security operations, such as, protect and unprotect.
5. The Audit logs from the Data Warehouse Protector are forwarded to the Audit Store using the Log Forwarder. The Audit logs generated by the RPAgent are forwarded to the Audit Store using the Log Forwarder.

The following are the two main components of Data Warehouse Protector:

• Log Forwarder - is a log processing tool that collects the data security operation logs from the Data Warehouse Protector and forwards them to the Audit Store (Insight) in the ESA.

• Resilient Package Agent - synchronizes with the RPProxy or ESA at regular intervals of 60 seconds and checks for any changes in the policy. If there is a change in policy, then it downloads the updated policy package over a TLS channel and stores in the shared memory.

2 - Teradata Data Warehouse Protector

The Protegrity Teradata Data Warehouse Protector has been optimized to work with the fast, parallel, and multi-node Teradata systems. This protector is the fastest protection point available on the market for Teradata databases.

This page discusses about the Teradata Data Warehouse Protector architecture, components, and the protector usage in detail.

2.1 - Understanding the Architecture

The architecture for the Teradata distribution of the Data Warehouse Protector is depicted in the image below.

2.2 - System Requirements

Ensure that the following prerequisites are met, before installing the Teradata Data Warehouse Protector:

• The ESA appliance, v10.0.x or higher, is installed, configured, and running.
• The ports that are configured on the ESA and the nodes in the cluster, which will run the Data Warehouse Protector, are listed in the following table:

• Additional requirements for each of the Teradata node:

  • Approximately 40 MB of free hard drive space should be available.
  • Every node must have network connectivity. This means that you should be able to access the node through the network using TCP/IP.
  • Ensure that you have the DBA rights in the Teradata database.
  • Ensure that you have the root access to the operating system.
  • The Database Server must be up and running.
  • The C-compiler must be installed. It is used to install the UDFs in the Teradata database.

  Note: For more information about configuring access roles to execute the DBA queries, refer to Additional references for the Teradata Protector.

  Note: If the Teradata Parallel Upgrade Tool (PUT) is unavailable, then the Data Warehouse Protector packages must be manually transferred and installed on each node.

The following table lists the minimum hardware configuration for the Data Warehouse Protector on Teradata distribution.

Note: In v10.0.0, the RPAgent loads the policy package into the shared memory. Every individual service process on a node that initializes the protector will load a copy of the policy package into the process heap memory. Therefore, the RAM requirement on each node depends on the policy size and the number of protector instances (number of processes).

2.3 - Preparing the Environment

2.3.1 - Extracting the Teradata Installation Package

You must extract the Teradata Data Warehouse Protector package to access the Teradata Protector components required for the installation process.

To extract the files from the installation package:

1. Log in to the server as the user with the required permissions.

2. Navigate to the directory where you have saved the Teradata Data Warehouse protector package.
   For example, /opt/protegrity/.

3. To extract the contents of the Teradata Data Warehouse Protector package, run the following command:

   tar -xvf DatabaseProtector_SLES-ALL-64_x86-64_Teradata-ALL-64_10.0.0+x.tgz
   

4. Press ENTER.
   The commands extracts the installation package and signature file from the Teradata Data Warehouse Protector package:

   DatabaseProtector_SLES-ALL-64_x86-64_Teradata-ALL-64_10.0.0+x.tgz
   signatures/DatabaseProtector_SLES-ALL-64_x86-64_Teradata-ALL-64_10.0.0+x.sig
   

   For more information about the steps to verify the signed Teradata Data Warehouse protector build, refer to Verification of Signed Protector Build.

5. To extract the contents of the installation package, run the following command:

   tar -xvf DatabaseProtector_SLES-ALL-64_x86-64_Teradata-ALL-64_10.0.0+x.tgz
   

6. Press ENTER.
   The commands extracts the following files:

   LogforwarderSetup_Linux_x64_10.0.0+x.sh
   RPAgentSetup_Linux_x64_10.0.0+x.sh
   PepTeradataSetup_Linux_x64_10.0.0+x.sh
   PepTeradata_UDTSetup_Linux_x64_10.0.0+x.sh
   U.S.Patent.No.6,321,201.Legend.txt
   

2.3.2 - Installing the Log Forwarder

1. Log in to the server as the user with the required permissions.

2. Navigate to the directory where you have extracted the Teradata data warehouse protector package.
   For example, /opt/protegrity/.

3. To install the Log Forwarder, run the following command:

   ./LogforwarderSetup_Linux_x64_10.0.0+x.sh
   

4. Press ENTER.
   The prompt to enter the Audit Store endpoint appears.

   Enter the audit store endpoint (host:port):
   

5. Enter the IP address of the Audit Store.

   Important: If you fail to specify an IP address, then the script will terminate the installation process.

6. Press ENTER.
   The installer script appends the port number to the IP address and the prompt to enter an additional Audit Store appears.

   Audit store endpoints: x.x.x.x:9200
   
   Do you want to add another audit store endpoint? [y/n]:
   

   The default value for the port is 9200. If you want to skip adding an additional Audit Store endpoint, then type n.

   Do you want to add another audit store endpoint? [y/n]: n
   

   To proceed with the installation, without adding an additional endpoint, skip to step 10.

7. To enter additional Audit Store endpoints, type y.

8. Press ENTER. The prompt to enter an additional Audit Store appears.

   Do you want to add another audit store endpoint? [y/n]: y
   Enter the audit store endpoint (host), alternative (host:port) to use another port than the default port 9200 :
   

   For every additional Audit Store point that you want to add, repeat steps 6 and step 7.

9. Press ENTER.
   The script displays list of the Audit Store endpoints and the prompt to accept or abort the installation appears.

   These audit store endpoints will be added:
   x.x.x.x:9200
   Type 'y' to accept or 'n' to abort installation:
   

10. To continue with the installation process, type y.

11. Press ENTER.
    The script extracts the files and a confirmation message appears.

    Type 'y' to accept or 'n' to abort installation: y
    Unpacking...
    Extracting files...
    Protegrity Log Forwarder installed in /opt/protegrity/logforwarder.
    

    To abort the installation process, type n. The installer aborts the installation and the following message appears:

    Type 'y' to accept or 'n' to abort installation: n
    The logforwarder installation is aborted.
    

12. Navigate to the /opt/protegrity/logforwarder/bin/ directory.

13. To start the Log Forwarder, run the following command:

    ./logforwarderctrl start
    

14. Press ENTER.
    The command starts the Log Forwarder.

    [ info] switching to background mode (PID=8329)
    Logforwarder started, PID (<process_ID>) written to PID file /opt/protegrity/logforwarder/
    bin/fluent-bit.pid
    

    For more information about changing the authentication, refer to Updating the Configuration Parameters for the Log Forwarder.

2.3.3 - Installing the Resilient Package Agent

The Resilient Package (RP) Agent downloads the certificates. These certificates are further used to authenticate the login credentials, public or private keys, and certify the code reliability.

To install the RPAgent:

1. Log in to the server as the user with the required permissions.

2. Navigate to the directory where you have extracted the Teradata Data Warehouse protector package. For example, /opt/protegrity/.

3. To install the RPAgent, run the following command:

   ./RPAgentSetup_Linux_x64_10.0.0+x.sh
   

4. Press ENTER.
   The prompt to enter the host name or the IP address of the ESA appears.

   Please Enter ESA host name or IP address []:
   

5. Enter the IP address of the ESA.

   If you fail to specify an IP address, then the installation script will terminate the installation process.

6. Press ENTER.
   The prompt to enter the username for downloading the certificate appears.

   Please enter the user name for downloading certificates[]:
   

7. Enter the username to download the certificates.

8. Press ENTER.
   The prompt to enter the password for downloading the certificate appears.

    Please enter the password for downloading certificates []:
   

9. Enter the password to download the certificates.

10. Press ENTER.
    The installer extracts the files and downloads the certificates.

    Unpacking...
    Extracting files...
    Obtaining token from x.x.x.x:25400...
    Downloading certificates from x.x.x.x:25400...
    % Total % Received % Xferd Average Speed Time Time Time Current
    Dload Upload Total Spent Left Speed
    100 8704 100 8704 0 0 209k 0 --:--:-- --:--:-- --:--:-- 212k
    Extracting certificates...
    Certificates successfully downloaded and stored in /opt/protegrity/rpagent/data
    Protegrity RPAgent installed in /opt/protegrity/rpagent.
    

    If the JWT token is not specified while downloading the certificates, then the RPAgent fetches the token automatically from the ESA.

11. Navigate to the /opt/protegrity/rpagent/bin/ directory.

12. To start the RPAgent, run the following command:

    ./rpagentctrl start
    

13. Press ENTER.
    The command starts the RPAgent successfully and a confirmation message appears.

    Starting rpagent
    

14. To verify the status of the RPAgent, run the following command:

    ./rpagentctrl status
    

15. Press ENTER.
    The status of the RPAgent service appears.

    rpagent is running (pid=10817)
    

2.4 - Installing the Protector

This section outlines the installation process for the Protegrity Teradata Data Warehouse Protector.

The following figure shows a complete task flow to install the Protegrity Teradata Data Warehouse Protector.

2.4.1 - Installing the Teradata Objects

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/ directory.

3. To install the Teradata objects, run the following command:

   ./PepTeradataSetup_Linux_x64_10.0.0+x.sh
   

4. Press ENTER.
   The prompt to continue installing the Teradata objects appears.

   *****************************************************
   Welcome to the Database Protector Setup Wizard
   *****************************************************
   This will install the teradata objects on your computer
   Do you want to continue? [yes or no]
   

5. To proceed with the installation of the Teradata objects, type yes.

6. Press ENTER.
   The prompt to enter the name of the database to install the UDFs appears.

   Enter name of database where the UDFs will be installed.
   

7. Enter the database name to continue.

8. Press ENTER
   The prompt to mention the maximum size of the VARCHAR allocated by the UDFs appears.

9. Enter the maximum size of the VARCHAR to be allocated by the UDFs.

   The default value is 500 characters. You must modify the default value in this step, as per your requirement, for maximum character length. The mentioned VARCHAR size is the maximum value allocated by the UDFs for UNICODE character set.

10. Press ENTER.
    The script installs the Teradata objects in the /opt/protegrity/databaseprotector/teradata/ directory.

    [500]:
    1000
    ***********BUFFER LENGTH INITIALIZATION**************
    UDF VARCHAR MAX INPUT BUFFER LENGTH (TOKENIZATION) : 1000 Latin characters
    UDF VARCHAR MAX OUTPUT BUFFER LENGTH (TOKENIZATION) : 1351 Latin characters
    UDF VARCHAR MAX INPUT BUFFER LENGTH (ENCRYPTION) : 1000 Latin characters
    UDF VARCHAR MAX OUTPUT BUFFER LENGTH (ENCRYPTION) : 1038 Bytes
    UDF VARCHAR_UNICODE MAX INPUT BUFFER LENGTH (TOKENIZATION) : 1000 UNICODE characters
    UDF VARCHAR_UNICODE MAX OUTPUT BUFFER LENGTH (TOKENIZATION) : 2706 UNICODE characters
    UDF VARCHAR_UNICODE MAX INPUT BUFFER LENGTH (ENCRYPTION) : 1000 UNICODE characters
    UDF VARCHAR_UNICODE MAX OUTPUT BUFFER LENGTH (ENCRYPTION) : 2038 Bytes
    teradata objects installed in /opt/protegrity/databaseprotector/teradata.
    Permission for /opt/protegrity/databaseprotector is successfully set.
    

    Important: By default, all the configurations provided for the UDFs are stored in the dbpuserconf.ini file within the /etc/protegrity/ directory.
    The Teradata Data Warehouse Protector uses the dbpuserconf.ini file for internal purposes only.

2.4.2 - Creating the Teradata User Defined Functions (UDFs)

Before creating the UDFs, ensure that the following prerequisites are met:

• You have installed the Teradata Data Warehouse Protector on all the nodes.

• When installing the Teradata objects, you must specify the maximum data size to be allocated by the UDFs. This value should not exceed 500 MB.

  • When you calculate the data size, ensure that you also consider the space for the overheads.
    For example:
    • For the data that would be tokenized using non-length preserving tokens, you must add an overhead of approximately 6% to the original data size.
    • For the AES-encrypted data, with the blocks of 16 bytes, you must add an overhead of an additional 16 bytes to include CRC or IV.

• The database user that installs the UDFs must have the following privileges:

  • GRANT CREATE FUNCTION ON PROTEGRITY to USER1;
    

  • GRANT ALTER FUNCTION ON PROTEGRITY to USER1;
    

    • USER1 is the database user who install the UDFs.
    • PROTEGRITY is the name of the database where the UDFs are installed.
    • ROLE1 is the group to which the USER1 belongs.
      Ensure that the database user who installs the UDFs is part of the ROLE1 group.

• To grant privileges to a database user to perform database administration functions, run the following query:

  GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE, STATISTICS, DUMP, RESTORE, CHECKPOINT, SHOW, EXECUTE PROCEDURE, ALTER PROCEDURE, EXECUTE FUNCTION, ALTER FUNCTION, ALTER EXTERNAL PROCEDURE, CREATE OWNER PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE MACRO, CREATE TRIGGER, CREATE PROCEDURE, CREATE FUNCTION, DROP TABLE, DROP VIEW, DROP MACRO, DROP TRIGGER, DROP PROCEDURE, DROP FUNCTION ON TESTDB TO ROLE1;
  

• To distribute the installation on all the nodes while installing the UDF in a multi-node environment, you can run either of the following commands:

  • UNIX commands:

    psh mkdir /opt/protegrity/
    

  • PUT utility:

    pcl -send /opt/protegrity/* /opt/protegrity/
    

To create the UDFs for Teradata:

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/databaseprotector/teradata/sqlscripts/ directory.

3. To view the .sql queries, run the following command:

   /opt/protegrity/databaseprotector/teradata/sqlscripts/ # ls -ltr
   

4. Press ENTER.
   The list of available queries in the .sql file format appears.

   total 164
   -rw-r----- 1 tdatuser tdtrusted 8939 createdecimalobjects.sql
   -rw-r----- 1 tdatuser tdtrusted 2560 dropobjects.sql
   -rw-r----- 1 tdatuser tdtrusted 781 dropvarcharunicode.sql
   -rw-r----- 1 tdatuser tdtrusted 67128 createobjects.sql
   -rw-r----- 1 tdatuser tdtrusted 10294 createvarcharunicode.sql
   -rw-r----- 1 tdatuser tdtrusted 8401 createdecimalobjects_a.sql
   -rw-r----- 1 tdatuser tdtrusted 793 dropvarcharunicode_a.sql
   -rw-r----- 1 tdatuser tdtrusted 1875 dropobjects_a.sql
   -rw-r----- 1 tdatuser tdtrusted 19643 createobjects_a.sql
   -rw-r----- 1 tdatuser tdtrusted 5078 createvarcharunicode_a.sql
   -rw-r----- 1 tdatuser tdtrusted 5300 testscript.sql
   -rw-r----- 1 tdatuser tdtrusted 3558 sample_tok.sql
   -rw-r----- 1 tdatuser tdtrusted 3324 sample_enc.sql
   

5. To start the bteq, run the following command:

   /opt/protegrity/databaseprotector/teradata/sqlscripts/ # bteq
   

6. Press ENTER.
   The prompt to log in to the database appears.

   Enter your logon or BTEQ command:
   

7. To log in to the database, run the following command:

   .logon < username >
   

8. Press ENTER.
   The prompt to enter the database password appears.

   Password:
   

9. Enter the database password.

10. Press ENTER.
    The connection to the Teradata database is completed successfully.

    *** Logon successfully completed.
    

11. To create the UDFs, execute the following query:
    

    .run file=createobjects.sql
    

12. Press ENTER.
    The script creates the UDFs and the following message for each of the created UDF appears.

    *** Function has been created.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

13. To create the Varchar Unicode UDFs, execute the following query:

    .run file=createvarcharunicode.sql
    

14. Press ENTER.
    The script creates the UDFs and the following message for each of the created UDF appears.

    *** Function has been created.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

15. To create the Decimal UDFs, execute the following query:

    .run file=createdecimal.sql
    

16. Press ENTER.
    The script creates the Decimal UDFs and the following message for each of the created UDF appears.

    *** Function has been created.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

For more information about the User Defined Functions (UDFs) for Teradata, refer to User Defined Functions and API.

2.4.3 - Installing the Teradata User Defined Types (UDTs)

The UDTs allows you to create the data-types that can be used as pre-defined data-types.

To install the UDT for Teradata:

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/ directory.

3. To install the UDT setup for Teradata, run the following command:

   ./PepTeradata_UDTSetup_Linux_x64_10.0.0+x.sh
   

4. Press ENTER.
   The prompt to continue the installation appears.

   *****************************************************
   Welcome to the Database Protector Setup Wizard
   *****************************************************
   This will install the teradata user defined types on your computer
   Do you want to continue? [yes or no]
   

5. To proceed, type yes.

6. Press ENTER.
   The script extracts the files and installs the data types in the default directory. The script also sets the permissions for the data types.

   [/opt/protegrity]:
   Unpacking...
   To get started with UDTs, please run /opt/protegrity/databaseprotector/teradata
   generate_udt_scripts.sh.
   Teradata UDTs installed in /opt/protegrity/databaseprotector/teradata.
   Permission for /opt/protegrity/databaseprotector/teradata is successfully set.
   

For more information about the Teradata User Defined Types (UDTs), refer to Teradata User Defined Types (UDTs).

2.4.4 - Creating the Teradata User Defined Types (UDTs)

The Teradata Data Warehouse Protector automatically creates the To-SQL and From-SQL transform, the ordering, and the necessary casts for a distinct UDT once the CREATE TYPE statement is issued.

On the Data Warehouse Protector installation, the /databaseprotector/teradata/udt/ directory is created with the following files:

• generate_udt_scripts.sh is an executable file that generates UDT scripts
• pepteradataudt.plm is a library that contains protect and unprotect functions for UDT usage.

The generate_udt_scripts.sh generates UDT scripts using the following command:

/opt/protegrity/databaseprotector/teradata/udt # ./generate_udt_scripts.sh --help

Protegrity Data Security Platform - Teradata UDT Scripts
Usage: generate_udt_scripts udtname dataelement scid dbtype
udtname    : UDT Name
dataelement: Data Element
scid       : Security Coordinate ID
dbtype     : Database data type, must be one of: bigint,date,float,integer,varchar

The following are some limitations for the UDT arguments:

• Udtname – any applicable name
• Dataelement – DE deployed
• Scid – applicable security coordinate (0 by default)
• Dbtype – one of the data types bigint, date, float, integer, varchar.

Important: The scid parameter is no longer used and is retained for compatibility purpose only.

To create the User Defined Types:

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/databaseprotector/teradata/udt/ directory.

3. To view the files and directories in the ../udt/ directory, run the following command:

   /opt/protegrity/databaseprotector/udt # ls 
   

4. Press ENTER.

   The list of available content appears.

   /opt/protegrity/databaseprotector/teradata/udt # ls
   generate_udt_scripts.sh  pepteradataudt.plm  sqlscripts
   

5. To generate the UDT scripts required for creating the UDTs, run the following command:

   ./generate_udt_scripts.sh <udtname> <dataelement> <scid> <dbtype>
   

   For example:

   ./generate_udt_scripts.sh UDT_VARCHAR AES128 0 varchar
   

6. Press ENTER.

   The script generates the following .sql queries for the UDTs in the /opt/protegrity/databaseprotector/teradata/udt directory.

   create_UDT_VARCHAR.sql
   drop_UDT_VARCHAR.sql
   

   It is recommended to use the data element names in the upper-case.

   You can modify the .sql queries using the bteq utility for error handling.

7. To start the bteq utility, run the following command:

   /opt/protegrity/databaseprotector/teradata/sqlscripts # bteq
   

8. Press ENTER.

   The prompt to log in to the database appears.

   Enter your logon or BTEQ command:
   

9. To log in to the database, run the following command:

   .logon <username>
   

10. Press ENTER.

    The prompt to enter the database password appears.

    Password: 
    

11. To proceed, type the database password.

12. Press ENTER.

    The connection to the Teradata Data Warehouse is completed successfully.

    *** Logon successfully completed.
    

13. To create the UDTs, run the following query:

    .run file=create_UDT_VARCHAR.sql
    

14. Press ENTER.

    The query creates the UDTs and the following message for each of the created UDT appears.

    *** Function has been created. 
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

    It is recommended to create only one UDT for each data type.
    Creating an additional UDT, with a basic data type that is used by an existing UDT, results in a linked error.

15. To grant the access permissions to the UDTs, execute the following SQL statements using the bteq utility.

    1. To provide the execute access to the UDTs, run the following command:

       chmod 755 create_UDT_VARCHAR.sql
       

    2. Press ENTER.

    3. To provide the UDTUSAGE access for the UDTs to public with a GRANT option, run the following query:

       GRANT UDTUSAGE ON SYSUDTLIB TO PUBLIC WITH GRANT OPTION;
       

    4. Press ENTER.

    5. To provide the execute function for all the UDTs to public with a GRANT option, run the following query:

       GRANT ALL ON TYPE SYSUDTLIB.UDT_VARCHAR TO PUBLIC WITH GRANT OPTION;
       

       The protect/unprotect operations for the UDTs must be performed using the bteq utility.

    6. Press ENTER.

    The script creates the UDTs and grants access permissions using the SQL statements.

2.5 - Configuring the Protector

2.5.1 - Working with the config.ini file

This page discusses about the config.ini file for the Teradata Data Warehouse Protector.

By default, this file is located in the /opt/protegrity/databaseprotector/teradata/data/ directory.

2.5.1.1 - Accessing the config.ini File

1. Log in to the server as the user with the required permissions.

2. Navigate to the directory where you have downloaded the installation package.
   For example, /opt/protegrity/databaseprotector/teradata/data/

3. To view the contents within the directory, run the following command:

   /opt/protegrity/databaseprotector/teradata/data #  ls -ltr
   

4. Press ENTER.
   The list of available configurable files appears.

   total 4
   -rw-r----- 1 tdatuser tdtrusted 1058 Oct 14 01:27 config.ini
   

5. To open the config.ini file, run the following command:

   /opt/protegrity/databaseprotector/teradata/data # vim config.ini
   

6. Press ENTER.
   The vim utility starts and the contents of the config.ini file appears.

   ###############################################################################
   # Log Provider Config
   ###############################################################################
   [log]
   
   # In case that connection to fluent-bit is lost, set how audits/logs are handled
   # 
   # drop  : (default) Protector throws logs away if connection to the fluentbit is lost
   # error : Protector returns error without protecting/unprotecting 
   #         data if connection to the fluentbit is lost
   mode = drop
   
   # Host/IP to fluent-bit where audits/logs will be forwarded from the protector
   #
   # Default localhost
   host = localhost
   
   ###############################################################################
   # Protector Config
   ###############################################################################
   [protector]
   
   # cadence is used to decide whether deployment is dynamic or immutable.
   #
   # '0' is used for immutable deployment.
   # Non-negative values other than '0' is used as policy sync interval for dynamic deployment.
   # default cadence value is '60'.
   cadence = 60
   

   For more information about parameters in the config.ini file, refer to Parameters in the config.ini file.

7. To close the config.ini file, run the following command:

   # :q
   

   Important: To reflect any changes made to the config.ini file, you must restart the Teradata Database.

8. To restart the Teradata Database, run the following command:

   # tpareset -f <reason for restart>
   

9. Press ENTER.
   A prompt to continue with restarting the database appears.

   You are about to restart the database
     on the system
        'localhost'
   Do you wish to continue (default: n) [y,n]
   

10. To continue with restarting the database, type y.
    The Teradata Database restarts successfully.

2.5.1.2 - Understanding the Parameters in the config.ini File

The following table consists of the config.ini parameters along with the descriptions:

2.5.2 - Updating the Output Buffer for the Unicode UDFs

This page discusses the process to update the output buffer length for the Varchar Unicode UDFs.

By default, the value of the output buffer length is 500 characters. This value can be modified during the installation of the Teradata objects.

After completing the installation process, you may need to manually update the output buffer length values if necessary. For instance, if you need to protect strings longer than 500 bytes, adjust the buffer length to accommodate the largest string size. Be aware that a big buffer size slows the overall performance. Additionally, each protection method has a size limitations. For example, tokenization has a maximum size limit of 4096 bytes. The output buffer sizes for all the UDFs are stored in both, the dbpuserconf.ini and createvarcharunicode.sql files.

The process to update the output buffer length for the Varchar Unicode UDFs involves the following steps:

1. To update the output buffer length in the dbpuserconf.ini file, refer to Updating the dbpuserconf.ini file.
2. To update the output buffer length in the createvarcharunicode.sql file, refer to Updating the createvarcharunicode.sql file.
3. To uninstall the Varchar Unicode UDFs using the dropvarcharunicode.sql file, refer to Uninstalling the UDFs.
4. To re-create the Varchar Unicode UDFs using the createvarcharunicode.sql file, refer to Creating the UDFs.

2.5.2.1 - Updating the dbpuserconf.ini file

1. Log in to the server as the user with the required permissions.

2. Navigate to the directory where you have downloaded the dbpuserconf.ini file.
   For example, /etc/protegrity/

3. To view the contents within the directory, run the following command:

   /etc/protegrity/ #  ls -ltr
   

4. Press ENTER.
   The list of available configurable files appears.

   total 4
   -rw-r----- 1 tdatuser tdtrusted 1058 Jan 28 01:27 dbpuserconf.ini
   

5. To open the dbpuserconf.ini file, run the following command:

   /etc/protegrity/ # vim dbpuserconf.ini
   

6. Press ENTER.
   The vim utility starts and the contents of the dbpuserconf.ini file appears.

   ###############################################################################
   # Config ini
   ###############################################################################
   [config_ini]
   # path points to database protector installation directory
   path = /opt/protegrity/databaseprotector/teradata/data/config.ini
   
   ###############################################################################
   # Protector Varchar Sizes (set by user during installation)
   ###############################################################################
   [varchar_sizes]
   UDF_VARCHAR_MAX = 500
   UDF_VARCHAR_OVERHEADMAX = 500
   VARCHAR_MAX_IN_BUF_LEN_TOKEN_LATIN = 500
   VARCHAR_MAX_OUT_BUF_LEN_TOKEN_LATIN = 676
   VARCHAR_MAX_IN_BUF_LEN_ENC_LATIN = 500
   VARCHAR_MAX_OUT_BUF_LEN_ENC_BYTES = 538
   VARCHAR_MAX_IN_BUF_LEN_TOKEN_UNICODE = 500
   VARCHAR_MAX_OUT_BUF_LEN_TOKEN_UNICODE = 1356
   VARCHAR_MAX_IN_BUF_LEN_ENC_UNICODE = 500
   VARCHAR_UNICODE_MAX_OUT_BUF_LEN_ENC_BYTES = 1038
   TdvmDev2:/etc/protegrity/ #
   

   Important: You must update the VARCHAR_MAX_OUT_BUF_LEN_TOKEN_UNICODE parameter with the required output buffer length.

7. To save the changes to the dbpuserconf.ini file, run the following command:

   # :wq
   

2.5.2.2 - Updating the createvarcharunicode.sql file

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/databaseprotector/teradata/sqlscripts/ directory.

3. To update the output buffer length in the createvarcharunicode.sql file, run the following command:

   vim createvarcharunicode.sql
   

4. Press ENTER.
   The vim utility starts and the contents of the createvarcharunicode.sql file appears.

   Ensure to update the value of the output buffer length for the PTY_VARCHARUNICODEINS, PTY_VARCHARUNICODESEL, and PTY_VARCHARUNICODESELEX UDFs as per your requirements.

5. To save changes to the createvarcharunicode.sql file, run the following command:

   # :wq
   

   Important: To reflect any changes made to the createvarcharunicode file, you must restart the Teradata Database.

6. To restart the Teradata Database, run the following command:

   # tpareset -f <reason for restart>
   

   Important: Updating the createvarcharunicode.sql file does not require a tpareset.

7. Press ENTER.
   A prompt to continue with restarting the database appears.

   You are about to restart the database
     on the system
        'localhost'
   Do you wish to continue (default: n) [y,n]
   

8. To continue with restarting the database, type y.
   The Teradata Database restarts successfully.

2.6 - Uninstalling the Protector

This page discusses the uninstallation process for the Protegrity Teradata Data Warehouse Protector.

2.6.1 - Uninstalling the Log Forwarder

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/logforwarder/bin/ directory.

3. To stop the Log Forwarder, run the following command:

   ./logforwarderctrl stop
   

4. Press ENTER.
   The command stops the Log Forwarder.

   Stopping Logforwarder with PID: 20658
   Please Wait
   

5. To verify the status of Log Forwarder, run the following command:

   ./logforwarderctrl status
   

6. Press ENTER.
   The status of the Log Forwarder appears.

   Logforwarder is not running
   

7. Navigate to the /opt/protegrity/ directory.

8. To remove the /logforwarder/ directory, run the following command.

   rmdir logforwarder
   

9. Press ENTER.
   The command removes the the /logforwarder/ directory and completes the uninstallation for the Log Forwarder.

2.6.2 - Uninstalling the RPAgent

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/rpagent/bin/ directory.

3. To stop the RPAgent, run the following command:

   ./rpagentctrl stop
   

4. Press ENTER.
   The command stops the RPAgent.

   Stopping RP Agent (PID: 10856)
   Please Wait
   

5. To verify the status of RPAgent, run the following command:

   ./rpagentctrl status
   

6. Press ENTER.
   The status of the RPAgent appears.

   RP Agent is not running
   

7. Navigate to the /opt/protegrity/ directory.

8. To remove the /rpagent/ directory, run the following command:

   rmdir rpagent
   

9. Press ENTER.
   The command removes the /rpagent/ directory and completes the uninstallation for the RPAgent.

2.6.3 - Uninstalling the User Defined Functions (UDFs)

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/databaseprotector/teradata/sqlscripts/ directory.

3. To start the bteq utility, run the following command:

   /opt/protegrity/databaseprotector/teradata/sqlscripts/ # bteq
   

4. Press ENTER.
   The prompt to log in to the database appears.

   Enter your logon or BTEQ command:
   

5. To log in to the database, run the following command:

   .logon <username>
   

6. Press ENTER.
   The prompt to enter the database password appears.

7. To proceed with the removal of the UDFs, type the database password.

8. Press ENTER.
   The connection to the Teradata database is completed successfully.

   *** Logon successfully completed.
   

9. To remove the installed UDFs from the Teradata Data Warehouse Protector, run the following query:

   .run file=dropobjects.sql
   

10. Press ENTER.
    The script removes each of the UDFs and the following message for each of the removed UDF appears.

    *** Function has been dropped.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

11. To remove the Varchar Unicode UDFs installed on the Teradata Data Warehouse Protector, run the following query:

    .run file=dropvarcharunicode.sql
    

12. Press ENTER.
    The script removes each of the UDFs and the following message for each of the removed UDF appears.

    *** Function has been dropped.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

13. To remove the Decimal UDFs installed on the Teradata Data Warehouse Protector, run the following query:

    .run file=dropdecimalobjects.sql 
    

14. Press ENTER.
    The script removes each of the UDFs and the following message for each of the removed UDF appears.

    *** Function has been dropped.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

2.6.4 - Removing the Installation Directory

You must delete the installation directory to complete the process of uninstalling the Teradata Data Warehouse Protector.
To remove the installation directory:

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/ directory.

3. To delete the installation directory, run the following command:

   rm -rf /databaseprotector/
   

4. Press ENTER.
   The command deletes the files and the sub-directories within the specified directory.

   This step completes the uninstallation process of the Teradata Data Warehouse Protector.

3 - User Defined Functions and APIs

The Data Warehouse Protector contains User Defined Functions (UDF), which perform the following:

• Fetches the policy related information from the shared memory
• Applies the access control settings that are derived on the basis of policy settings
• Encrypts or tokenizes the data based on the policy settings
• Generates Audit logs

To avoid any performance issues resulting due to casting of the data, a general best practice is to protect the data and present the decryption related API/UDFs/commands, as applicable, in the tables as views to authorized users only. This eliminates the unauthorized user’s access to the decryption API/UDFs/commands by limiting the access to the protected data only.
The decryption process is limited to authorized users and thus, does not cause any performance impact as the API/UDFs/commands are executed restrictively.

Warning: With the Data Warehouse protector, you cannot use different data elements for different rows in the same query because of the caching feature. The caching feature will cache the data element that you pass and it will use the same data element for protect or unprotect actions in the column.

3.1 - Teradata UDFs

This page provides a detailed list of User Defined Functions (UDFs) for general information, protection, and unprotection of data with different data types.

It is recommended to run the sample queries in BTEQ (Basic Teradata Query). For more information, refer to Sample Scripts provided in the Teradata Data Warehouse Protector package at the default location, /opt/protegrity/databaseprotector/teradata/sqlscripts/.

Protegrity UDFs can support the JSON format for protection and unprotection. It is not possible to mask data stored in XML or JSON (JavaScript Object Notation) formats. While executing the Unprotect UDFs for these formats, clear data is returned with an error message. Masking is supported only with the Varchar UDFs.

Teradata UDFs for Protection

This section provides a detailed list of User Defined Functions (UDFs) for general information, and protection, unprotection, and tokenization of data with different data types.

Teradata UDFs - Deterministic and Non-deterministic clauses

Teradata supports the following two optional clauses to categorize if the UDF returns identical results for identical inputs or not.

• DETERMINISTIC - specifies that the UDF function returns the same results for identical inputs. The de-tokenization and decryption UDFs are defined with the DETERMINISTIC clause.
• NOT DETERMINISTIC - specifies that the UDF function returns non-identical results for identical inputs. This is the default option. The tokenization and encryption UDFs are defined with the NOT DETERMINISTIC clause.

Risk

In case of a query with constant arguments to the DETERMINISTIC UDF call, Teradata may cache the result of the evaluated UDF, as designed. During subsequent query execution, the results may be fetched from the Teradata internal cache without evaluating the UDF.

This is a risk because it can cause unauthorized access to the protected data due to lack of authorization check during the UDF execution. In addition, altering the clause to NOT DETERMINISTIC may cause performance issues as the UDFs defined with the DETERMINISTIC clause execute faster in comparison to the UDFs defined with the NOT DETERMINISTIC clause.

As per usage, if you are not using any constants in the UDF call, then you can recreate the UDF with the DETERMINISTIC clause to ensure faster performance.

Important: For all the Teradata UDFs, the communicationid and scid parameters are no longer used and are retained for compatibility purposes only. It is recommended to set the values for these parameters as zero.

• General UDFs
• Access Check UDFs
• Varchar Latin UDFs
• Varchar Unicode UDF
• Float UDFs
• Small Integer UDFs
• Integer UDFs
• Big Integer UDFs
• Date UDFs
• 8-Byte AND 16-Byte Decimal UDFs
• JSON UDFs
• XML UDFs

Teradata UDFs for No Encryption

This section provides a list of User Defined Functions (UDFs) that can be used with No Encryption data elements.

• Float UDFs for No Encryption
• Date UDFs for No Encryption
• 8-Byte and 16-Byte Decimal UDFs for No Encryption

3.1.1 - General UDFs

This section includes the general UDFs that can be used to retrieve the Teradata Protector version and the current user.

pty_whoami

This UDF returns the name of the user who is currently logged in.

Signature:

pty_whoami()

Parameters:
None

Returns:
The function returns the name of user logged in to the database.

Example:

select pty_whoami();

pty_getversion

This UDF returns the version of the installed Teradata Data Warehouse Protector.

Signature:

pty_getversion()

Parameters:
None

Returns:
The function returns the version of the product as a string

Example:

select pty_getversion();

pty_getdbsinfo

This UDF returns the Teradata session, statement, and request numbers. These parameters are captured in audit logs and can be cross-referenced in the ESA Forensics View.

Signature:

pty_getdbsinfo

Parameters:
None

Returns:
The function returns the following parameters in a string.

Example:

select pty_getdbsinfo();

3.1.2 - Access Check UDFs

This section includes list of UDFs that can be used to check select access-related information.

pty_checkselaccess

This UDF checks whether a database user has unprotect access for a set of data elements. To run this UDF, the database user should be granted access rights for protection.

Signature:

pty_checkselaccess(dataelement<n> VARCHAR, resultlen INTEGER, communicationid INTEGER)

Parameters:

Returns:

The function returns a 3-CHARACTER string.

• Position 1: Value 1 indicates select permissions on dataelement1, value 0 indicates no select permissions
• Position 2: Value 1 indicates select permissions on dataelement2, value 0 indicates no select permissions
• Position 3: Value 1 indicates select permissions on dataelement3, value 0 indicates no select permissions

Exception:
None

Example:

select pty_checkselaccess('AES256', 'AES128', 'AES128_IV_CRC_KID', 3, 0);

3.1.3 - Varchar Latin UDFs

The Varchar Latin UDFs accept the string data encoded in the Latin character set.

Important: Do not exceed the maximum output buffer length when using the result length parameter (resultlen) in the Varchar Latin UDFs.
For more information about the maximum output buffer length, for each Varchar Latin UDF, refer to Installing the Teradata Objects.

pty_varcharlatinenc

This UDF protects the string data using an Encryption data element.

Signature:

pty_varcharlatinenc(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_varcharlatinenc ('Any character value! ', 'AES256',500,0,0);

pty_varcharlatindec

This UDF unprotects the protected string data.

Signature:

pty_varcharlatindec(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_varcharlatindec(pty_varcharlatinenc('Any character value! ', 'dataelement',500,0,0 ), 'dataelement',500,0,0 );

pty_varcharlatindecex

This UDF unprotects the protected string data.

Signature:

pty_varcharlatindecex(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns an error instead of NULL, if the user does not have access rights.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_varcharlatindecex(PTY_VARCHARLATINENC('ProtegrityProt', 'AES256',100,0,0 ), 'AES256',100,0,0 );

pty_varcharlatinins

This UDF protects the string data using type-preserving data elements, such as, tokens, and No Encryption for access control.

Signature:

pty_varcharlatinins(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the protected VARCHAR value.
• The function returns NULL when user has no access to the data in the policy.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharlatinins('Any character value! ', 'dataelement',500,0,0 );

• Email Tokenization:
  This UDF can be used to tokenize email input type.
  In the following example, email is a token element created in the ESA of email type.

  pty_varcharlatinins('email@protegrity.com','email',32,0,0);
  

• Timestamp Tokenization:
  This UDF can be used to tokenize timestamp data.
  The following example displays a sample of timestamp tokenization:

  select pty_varcharlatinins(cast('22-09-1990' as varchar(32)),'alphanum',64,0,0);
  

pty_varcharlatinsel

This UDF unprotects the protected string data.

Signature:

pty_varcharlatinsel(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to data.
• The function returns NULL when user has no access to the data in the policy.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Note: If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small 

Example:

SELECT pty_varcharlatinsel(pty_varcharlatinins('Any character value! ', 'dataelement',500,0,0 ), 'dataelement',500,0,0 );

• Email De-tokenization:
  This UDF can be used to de-tokenize email input type tokenized using the PTY_VARCHARLATININS UDF.
  In the following example, email is a token element created in the ESA of email type.

  pty_varcharlatinsel('F00CJ@protegrity.com','email',32,0,0);
  

• Timestamp Data De-tokenization:
  This UDF can be used to de-tokenize timestamp data tokenized using the PTY_VARCHARLATININS UDF. The following example displays a sample of timestamp data de-tokenization.

  sel cast(pty_varcharlatinsel(pty_varcharlatinins(cast('2019-04-14 08:30:41-04:00' as varchar(64)),'TE_N_S16_L3R1_ASTYES',64,0,0),'TE_N_S16_L3R1_ASTYES',64,0,0) AS TIMESTAMP(0));
  

pty_varcharlatinselex

This UDF unprotects the protected string data.

Signature:

pty_varcharlatinselex(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Note: If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

 Input or output buffer is too small 

.

Example:

SELECT pty_varcharlatinselex(pty_varcharlatinins('Any character value! ', 'dataelement',500,0,0 ), 'dataelement',500,0,0 );

pty_varcharlatinhash

This UDF calculates the hash value of a string data.

Attention: This is a one-way function and you cannot unprotect the data.

Signature:

pty_varcharlatinhash(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the hash value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Caution: Starting from the version 10.0.x, the HMAC-SHA1 protection method is deprecated.
It is recommended to use the HMAC-SHA256 protection method instead of the HMAC-SHA1 protection method.
For assistance in switching to a different protection method, contact Protegrity Support.

Example:

SELECT pty_varcharlatinhash ('ProtegrityProt', 'HMAC_SHA256', 100,0,0);

3.1.4 - Varchar Unicode UDFs

The Varchar Unicode UDFs accept the string data encoded in the UNICODE character set.

Important: Do not exceed the maximum output buffer length when using the result length parameter (resultlen) in the Varchar Unicode UDFs.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

pty_varcharunicodeenc

This UDF protects the Unicode string using an Encryption data element for encryption.

Signature:

pty_varcharunicodeenc(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodeenc (TRANSLATE(CAST('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'AES_128',100,0,0 );

pty_varcharunicodedec

This UDF unprotects the protected Unicode string data.

Signature:

pty_varcharunicodedec(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected Unicode character value.
• The function returns NULL when user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodedec(pty_varcharunicodeenc(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE, 'AES256',100,0,0), 'AES256',100,0,0 ));

pty_varcharunicodedecex

This UDF unprotects the protected Unicode string data.

Signature:

pty_varcharunicodedecex(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodedecex(pty_varcharunicodeenc(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'AES256', 100, 0,0), 'AES256', 100, 0,0);

pty_varcharunicodeins

This UDF protects Unicode string data using type-preserving data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Signature:

pty_varcharunicodeins(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Note: For pty_varcharunicodeins, set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:
The function returns the protected VARCHAR value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example for Unicode Gen2:

The Unicode Gen2 data elements supports the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer.
For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

SELECT pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0);

SELECT pty_varcharunicodeins(TRANSLATE(CAST ('ϠϡϢϣϥϦ' AS VARCHAR(1000)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0);

pty_varcharunicodesel

This UDF unprotects Unicode string data protected by data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Warning: This UDF does not support masking.

Signature:

pty_varcharunicodesel(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

For pty_varcharunicodesel, you must set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:

• The function returns an unprotected character value.
• The function returns a protected value if this option is configured in the policy and the user does not have access to data.
• The function returns NULL when the user has no access to data in the policy.

Exception:

• If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small

Example for Unicode Gen2:

The Unicode Gen2 data elements support the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer.
For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

select pty_varcharunicodesel(pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE),'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0),'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0);

select pty_varcharunicodesel(pty_varcharunicodeins(TRANSLATE(CAST ('ϠϡϢϣϥϦ' AS VARCHAR(1000)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0);

pty_varcharunicodeselex

This UDF unprotects Unicode string data protected by data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Warning: This UDF does not support masking.

Signature:

pty_varcharunicodeselex(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

For pty_varcharunicodeselex, set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:

• The function returns an unprotected character value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small

.

Example:

select pty_varcharunicodeselex(pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'NoEncryption', 100, 0,0), 'NoEncryption', 100, 0,0);

3.1.5 - Float UDFs

pty_floatenc

This UDF protects the float value using an Encryption data element.

Signature:

pty_floatenc(col FLOAT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatenc(26656.0, 'AES256', 100, 0,0); 

pty_floatdec

This UDF unprotects the protected float value.

Signature:

pty_floatdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected FLOAT value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatdec(pty_floatenc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_floatdecex

This UDF unprotects the protected float value.

Signature:

pty_floatdecex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected FLOAT value.
• The function returns an error instead of NULL if the user does not have access

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatdecex(pty_floatenc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_floathash

This UDF calculates the hash value for a float value.

Attention: This is a one-way function and you cannot unprotect the data.

Signature:

pty_floathash(col FLOAT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the hash value.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Caution: Starting from the version 10.0.x, the HMAC-SHA1 protection method is deprecated.
It is recommended to use the HMAC-SHA256 protection method instead of the HMAC-SHA1 protection method.
For assistance in switching to a different protection method, contact Protegrity Support.

Example:

select pty_floathash(26656.0, 'HMAC_SHA256', 100, 0,0);

3.1.6 - Small Integer UDFs

pty_smallintenc

This UDF protects the small integer value using an Encryption data element.

Signature:

pty_smallintenc(col SMALLINT, dataelement VARCHAR, resultlen INTEGER, communicationidINTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintenc(12345,'AES256',100,0,0);

pty_smallintdec

This UDF unprotects the small integer value.

Signature:

pty_smallintdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected SMALLINT value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintdec(pty_smallintenc(12345,'AES256',100,0,0),'AES256',0,0);

pty_smallintdecex

This UDF unprotects the protected small integer value.

Signature:

pty_smallintdecex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected SMALLINT value.
• The function returns an error instead of NULL if the user does not have access

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintdecex(pty_smallintenc(12345,'AES256',100,0,0),'AES256',0,0);

pty_smallintins

This UDF protects the small integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_smallintins(col SMALLINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected SMALLINT value.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintins(12345, 'TE_INT_2', 100, 0,0);

pty_smallintsel

This UDF unprotects the small integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_smallintsel(col SMALLINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected SMALLINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintsel(pty_smallintins(12345, 'TE_INT_2', 100, 0,0), 'TE_INT_2',0,0);

pty_smallintselex

This UDF unprotects the protected small integer value.

Signature:

pty_smallintselex(col SMALLINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the SMALLINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintselex(pty_smallintins(12345, 'TE_INT_2', 100, 0,0), 'TE_INT_2',0,0);

pty_smallinthash

This UDF calculates the hash value for a SMALLINT value. This is a one-way function and you cannot unprotect the data.

Signature:

pty_smallinthash(col SMALLINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the hash value.

Exception:
If you configure an exception in the policy and the user does not have access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY_SMALLINTHASH(1234, 'HMAC_SHA256', 100, 0,0);

3.1.7 - Integer UDFs

pty_integerenc

This UDF protects integer value using an Encryption data element.

Signature:

pty_integerenc(col INTEGER, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerenc(1234, 'AES256', 100, 0,0);

pty_integerdec

This UDF unprotects the protected integer value.

Signature:

pty_integerdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected INTEGER value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerdec(pty_integerenc(1234, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_integerdecex

This UDF unprotects the protected integer value.

Signature:

pty_integerdecex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected INTEGER value.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_integerdecex(pty_integerenc(1234, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_integerins

This UDF protects the integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_integerins(col INTEGER, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected INTEGER value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerins(1234, 'TE_INT_4', 100, 0,0);

pty_integersel

This UDF unprotects the protected integer value.

Signature:

pty_integersel(col INTEGER, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected INTEGER value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integersel(pty_integerins(1234, 'TE_INT_4', 100, 0,0), 'TE_INT_4', 0,0);

pty_integerselex

This UDF unprotects the protected integer value.

Signature:

pty_integerselex(col INTEGER, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected INTEGER value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If you configure an exception in the policy and the user does not have the access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_integerselex(pty_integerins(1234, 'TE_INT_4', 100, 0,0), 'TE_INT_4', 0,0);

pty_integerhash

This UDF calculates the hash value for integer value.

Attention: This is a one-way function and you cannot unprotect the data.

Signature:

pty_integerhash(col INTEGER, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the hash value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerhash(1234, 'HMAC_SHA256', 100, 0,0);

Caution: Starting from the version 10.0.x, the HMAC-SHA1 protection method is deprecated.
It is recommended to use the HMAC-SHA256 protection method instead of the HMAC-SHA1 protection method.
For assistance in switching to a different protection method, contact Protegrity Support.

3.1.8 - Big Integer UDFs

pty_bigintenc

This UDF protects the Big Integer value using a data element for encryption.

Signature:

pty_bigintenc(col BIGINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

The function returns the protected VARBYTE value.

Exception:

If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintenc(12345678,'AES256',100,0,0);

pty_bigintdec

This UDF unprotects the Big Integer value.

Signature:

select pty_bigintdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected BIGINT value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintdec(pty_bigintenc(12345678,'AES256',100,0,0),'AES256',0,0);

pty_bigintdecex

This UDF unprotects the protected Big Integer value.

Signature:

pty_bigintdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected BIGINT value.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_bigintdec(pty_bigintenc(12345678,'AES256',100,0,0),'AES256',0,0);

pty_bigintins

This UDF protects the Big Integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_bigintins(col BIGINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected BIGINT value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintins(12345678, 'TE_INT_8', 100, 0,0);

pty_bigintsel

This UDF unprotects the Big Integer value.

Signature:

pty_bigintsel(col BIGINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected BIGINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintsel(pty_bigintins(12345678, 'TE_INT_8', 100, 0,0), 'TE_INT_8',0,0);

pty_bigintselex

This UDF unprotects the protected Big Integer value and returns an error instead of NULL if user does not have access.

Signature:

pty_bigintselex(col BIGINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected BIGINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintselex(PTY_BIGINTINS(12345678, 'TE_INT_8', 100, 0,0), 'TE_INT_8',0,0);

3.1.9 - Date UDFs

The dates can be protected using encryption and tokenization as the data protection method. The native UDFs, such as, pty_dateenc and pty_datedec, can be used for encryption and decryption respectively. To tokenize the date formats using the date data element, the data must be cast to VARCHAR type and then protected/unprotected with PTY_VARCHARLATININS/PTY_VARCHARLATINSEL UDFs.

To avoid any performance issues resulting due to casting of the data, a general best practice is to protect the data and present the decryption-related UDFs in the tables as views to authorized users only. This eliminates the unauthorized user’s access to the decryption UDFs and has the protected data only. The decryption process is limited to authorized users and thus, doesn’t cause any performance impact as the UDFs are executed restrictively.

pty_dateenc

This UDF protects the date value using an Encrytion data element.

Signature:

pty_dateenc(col DATE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_dateenc('1990-11-22', 'AES256', 100, 0,0);

pty_datedec

This UDF unprotects the protected date value.

Signature:

pty_datedec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected DATE value.

  The function returns the output as per the system date format.

• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF will terminate with an error message explaining what went wrong.

Example:

select pty_datedec(pty_dateenc('1990-10-22', 'AES256', 100, 0,0), 'AES256', 0,0);

pty_datedecex

This UDF unprotects the protected date value and returns an error instead of NULL if the user does not have access.

Signature:

pty_datedecexex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected DATE value.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_datedecex(pty_dateenc(CAST ('22 Sep 90' AS DATE FORMAT 'DD-MMM-YY'), 'AES256', 100, 0,0), 'AES256', 0,0);

3.1.10 - 8-Byte and 16-Byte Decimal UDFs

These UDFs work on the Decimal data types that are either 8 or 16 bytes in size. The 8-byte Decimal data types have a precision between 10 and 18 digits, while the 16-byte Decimals have a precision between 19 and 38 digits.

Note: Only one set of Decimal UDFs can be created for each range. The user must provide the UDF name. It is recommended that you replace with, for example, 10_2 if the target data type is Decimal(10,2) to get a function pty_decimal_10_2enc, or 22_3 if the target data type is Decimal(22,3) to get pty_decimal_22_3enc.

pty_decimalenc

This UDF protects the decimal value with a data element for encryption.

Signature:

pty_decimal<n>enc(col DECIMAL<n>, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1enc(26656.0, 'AES256', 100, 0,0);

pty_decimaldec

This UDF unprotects the protected decimal value.

Signature:

pty_decimal<n>dec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected DECIMAL value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1dec(pty_decimal37_1enc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_decimaldecex

This UDF unprotects the protected decimal value and returns an error instead of NULL if the user does not have access.

Signature:

pty_decimal<n>decex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected DECIMAL value.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1decex(pty_decimal37_1enc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

3.1.11 - JSON UDFs

These UDFs are used to protect and unprotect data for JSON data type. These UDFs have been introduced to support LOB or Large Objects that can be loaded to or extracted from the Teradata Database tables. Depending on the data element chosen, the data is tokenized or encrypted. The data in JSON are protected as CLOBs.

The examples provided for protection and unprotection are for single queries.

pty_jsonins

This UDF protects the JSON value using the type-preserving data elements, such as, token and No Encryption data element for access control.

Signature:

pty_jsonins(col JSON, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected JSON CLOB (Character Large Objects) value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Note: Tokenizing a JSON format data with a Printable tokenization data element will not return a valid JSON format output.

Example:

SELECT pty_jsonins(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'TE_A_N_S23_L2R2_Y', 500, 0, 0);

pty_jsonsel

This UDF unprotects the protected JSON CLOBs.

Signature:

pty_jsonsel(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected JSON values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_jsonsel(pty_jsonins(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'TE_A_N_S23_L2R2_Y', 500, 0, 0), 'TE_A_N_S23_L2R2_Y', 500, 0, 0);

pty_jsonselex

This UDF unprotects the JSON CLOBs that are protected using a tokenization data element.

Signature:

pty_jsonselex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected JSON values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error explaining what went wrong.

Example:

SELECT pty_jsonselex(pty_jsonins(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'TE_A_N_S23_L2R2_Y', 500, 0, 0), 'TE_A_N_S23_L2R2_Y', 500, 0, 0);

pty_jsonenc

This UDF protects the JSON value using an encrytion data element.

Signature:

pty_jsonenc(col JSON, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected JSON CLOB (Character Large Objects) value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_jsonenc(pty_jsonenc(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'AES256', 500, 0, 0), 'AES256', 500, 0, 0);

pty_jsondec

This UDF unprotects the CLOB value that are protected using an encryption data element.

Signature:

pty_jsondec(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected JSON values.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_jsondec(pty_jsonenc(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'AES256', 500, 0, 0), 'AES256', 500, 0, 0);

3.1.12 - XML UDFs

These UDFs support the XML data type. The XML content is stored in compact binary form or CLOBs that preserve the information set of the XML document. These UDFs have been introduced to support the XML files that can be loaded to or extracted from the Teradata Database tables. Depending on the data element chosen, the data is either tokenized or encrypted.

pty_xmlins

This UDF protects the XML value using type-preserving data elements, such as, token and No Encryption for access control.

Signature:

pty_xmlins(col XML, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected CLOB value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Caution: Tokenizing XML data with Printable tokenization does not return a valid XML format output.

Example:

select pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0) "Protected Data";

pty_xmlsel

This UDF unprotects the protected CLOB value.

Signature:

pty_xmlsel(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected XML values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

sel
pty_xmlsel( 
pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0),'TE_A_N_S23_L2R2_Y',1500,0,0) "UnProtected Data";

pty_xmlselex

This UDF unprotects the protected CLOB value with strong encryption.

Signature:

pty_xmlselex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected XML values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

sel
pty_xmlselex( 
pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0),'TE_A_N_S23_L2R2_Y',1500,0,0) "UnProtected Data";

pty_xmlenc

This UDF protects the XML data using an Encryption data element.

Signature:

pty_xmlenc(col XML, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected CLOB value.

Exception:
If the user does not have protect access rights in the policy, UDF terminates with an error message explaining what went wrong.

Example:

sel 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0) "Protected Data";

pty_xmldec

This UDF unprotects the protected CLOB values.

Signature:

pty_xmldec(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected XML value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select
pty_xmldec( 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0),'AES256',1500,0,0) "UnProtected Data";

pty_xmldecex

This UDF unprotects the protected CLOB value with strong encryption.

Signature:

pty_xmldecex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected XML value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select
pty_xmldecex( 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0),'AES256',1500,0,0) "UnProtected Data";

3.1.13 - Float UDFs for No Encryption

pty_floatins

This UDF can be used only with the No Encryption data element.

Signature:

pty_floatins(col FLOAT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the input value as it is.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatins(26656.0, 'NoEncryption', 100, 0,0);

pty_floatsel

This UDF unprotects the float value for a No Encryption data element.

Signature:

pty_floatsel(col FLOAT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as it is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatsel(pty_floatins(26656.0, 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

pty_floatselex

This UDF unprotects the float value protected with a No Encryption data element.

Signature:

pty_floatselex(col FLOAT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as it is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatselex(pty_floatins(26656.0, 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

3.1.14 - Date UDFs for No Encryption

This section provides DATE UDFs that are applicable for No Encryption data elements.

pty_dateins

This UDF protects a date value with a No Encryption data element to impose access control.

Signature:

pty_dateins(col DATE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the input value as is.

The function returns the output as per the system date format.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_dateins(CAST ('22-09-1990' AS DATE FORMAT 'DD-MM-YYYY'), 'NoEncryption', 100, 0,0);

pty_datesel

This UDF unprotects the date value that is protected using a No Encryption data element.

Signature:

pty_datesel(col DATE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_datesel(pty_dateins(CAST ('22-09-1990' AS DATE FORMAT 'DD-MM-YYYY'), 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

pty_dateselex

This UDF unprotects the date value that is protected with a No Encryption data element and returns an error instead of NULL if the user does not have access.

Signature:

pty_dateselex(col DATE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_dateselex(pty_dateins(CAST ('22-09-1990' AS DATE FORMAT 'DD-MM-YYYY'), 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

3.1.15 - 8-Byte AND 16-Byte Decimal UDFs for No Encryption

These UDFs work on the Decimal data types that are either 8 or 16 bytes in size. The 8-byte Decimals have a precision between 10 and 18 digits, while the 16-byte Decimals have a precision between 19 and 38 digits. These UDFs apply to the No Encryption data elements only.

pty_decimalins

This UDF protects the decimal value using a No Encryption data element.

Signature:

pty_decimal<n>ins(col DECIMAL<M,N>, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the input value as is.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1ins(26656.0, 'NoEncryption', 100, 0,0);

pty_decimalsel

This UDF unprotects the decimal value that is protected using a No Encryption data element.

Signature:

pty_decimal<n>sel(col DECIMAL<M,N>, dataelement VARCHAR, communicationid INTEGER, SCID INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1sel(pty_decimal37_1ins(26656.0, 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

pty_decimalselex

This UDF unprotects the decimal value that is protected using a No Encryption data element.

Signature:

pty_decimal<n>selex(col DECIMAL(m,n), dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1selex(pty_decimal37_1ins(26656.0, 'NoEncryption', 100, 0,0),'NoEncryption', 0,0);

3.2 - Trino User Defined Functions and Procedures

This section provides a detailed list of User Defined Functions (UDFs) for general information, and protection and unprotection of different data types.

3.2.1 - General UDFs

This section includes list of general UDFs that can be used to retrieve the Trino Protector version and the current user.

ptyWhoAmI()

This function returns the name of the user.

Signature:

ptyWhoAmI()

Parameters:
None

Returns:
This UDF returns the name of the user logged in to the database as VARCHAR.

Example:

SELECT ptyWhoAmI();

ptyGetVersion()

This UDF returns the JpepLite version used in Trino UDFs.

Signature:

ptyGetVersion()

Parameters:
None

Returns:
This UDF returns the JpepLite version used in Trino UDFs.

Example:

select ptyGetVersion();

ptyGetVersionExtended()

The UDF returns the extended version information.

Signature:

pty_getversionextended();

Parameters:

• None

Returns:
The UDF returns a string in the following format:

JpepLite: <1>; CORE: <2>;

where,

• 1. Is the JpepLite version
• 2. Is the Core library version

Example:

select pty_getversionextended();

3.2.2 - VarChar UDFs

This section provides a list of Varchar UDFs for the protect, unprotect, and reprotect operations.

Consider a Trino session where you impersonate a user using the –user parameter as shown in the following example.

./TrinoCLI --server localhost:8080 --catalog hive --schema default --user=<sample_user>

If you execute any UDF after impersonating a user, then the query execution happens for the impersonated user <sample_user>. This is a limitation of Trino.

ptyProtectStr()

This UDF protects the varchar values.

Signature:

ptyProtectStr(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected varchar value.

Example:

select ptyProtectStr('ProtegrityProt','Varchar_DE');

Supported Protection Methods:

ptyUnprotectStr()

This UDF unprotects the existing protected varchar value.

Signature:

ptyUnprotectStr(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected varchar value.

Example:

select ptyUnProtectStr(PtyProtectStr('ProtegrityProt','Varchar_DE'),'Varchar_DE');

Supported Protection Methods:

ptyReprotect() - Str

This UDF reprotects the varchar protected data, which was earlier protected using the ptyProtectStr UDF, with a different data element.

Signature:

ptyReprotect(varchar input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected varchar value.

Example:

select ptyReprotect(PtyProtectStr('ProtegrityProt','Varchar_DE'),'Varchar_DE','new_Varchar_DE');

Supported Protection Methods:

3.2.3 - BigInt UDFs

This section provides a list of the BigInt UDFs for the protect, unprotect, and reprotect operations.

ptyProtectBigInt()

This UDF protects the BigInt value.

Signature:

ptyProtectBigInt(bigint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected BigInt value.

Example:

select PtyProtectBigInt(1234567, 'BigInt_DE');

Supported Protection Methods:

ptyUnprotectBigInt()

This UDF unprotects the protected BigInt value.

Signature:

ptyUnProtectBigInt(bigint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected BigInt value.

Example:

select PtyUnProtectBigInt(PtyProtectBigInt(1234567, 'BigInt_DE'), 'BigInt_DE');

Supported Protection Methods: