This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Appendix

1: Additional references for the Protectors

1.1: Additional references for the Teradata Protector

1.1.1: Configuring access to execute queries
1.1.2: Teradata Query Bands and Trusted Sessions

2: Data Warehouse Sample Scripts

2.1: Teradata Database Data Warehouse Sample Scripts

3: Return Codes for Data Warehouse Protectors
4: Supported Data Warehouse Protectors Matrix

This page consists of the Additional References, Return Codes, Sample Scripts, and Supported Platforms Matrix for the Data Warehouse Protectors.

1 - Additional references for the Protectors

This page consists of the the additional references for all the Data Warehouse Protectors v10.0.0.

1.1 - Additional references for the Teradata Protector

This page consists of the the additional references for the Teradata Data Warehouse Protector.

1.1.1 - Configuring access to execute queries

When configuring a Teradata Member Source in ESA, the following grants must be given to the user that will be connecting to the database. This is required in order to retrieve policy users and groups.

The following is a list of privilege rights that are required for the access configuration:

Select access to DBC.DBASE
Select access to DBC.ROLEINFO
Select access to DBC.RoleMembers

The privilege rights must be granted in the member source configuration on the ESA when you are defining a database user with the roles.

There are three basic types of queries performed in Teradata:

Retrieving the database users
```
SELECT DBASE.DatabaseNameI FROM DBC.DBASE DBASE WHERE DBASE.ROWTYPE = 'U' ORDER BY 1;
```
For more information about fetching the users, refer to Additional References for Teradata.

Retrieving the database roles/groups

SELECT RoleName,UPPER(GRANTEE) FROM DBC.RoleMembers ORDER BY RoleName;

Retrieving the database users that are members of a role/group

SELECT UPPER(GRANTEE) FROM DBC.RoleMembers ORDER BY GRANTEE;

1.1.2 - Teradata Query Bands and Trusted Sessions

When a middle-tier application is used together with the Teradata database, it typically logs on to the database as a permanent database user (application user) and establishes a connection pool. End-users that access the database through the middle-tier application are given all authorized database privileges and are audited based on that single application user.

For the sites that require users to be individually identified, authorized, and audited, the middle-tier application can be configured to offer trusted sessions. Application users that access the database through a trusted session must be set up as proxy users and assigned one or more database roles, which determine their access rights in the database. When a proxy user requests database access, the application forwards the user identity and applicable role information to the database.

For more information about Teradata trusted sessions, refer to https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Security-Administration/Introduction-to-Security-Administration.

The system uses a proxy user if the query band contains the reserved name PROXYUSER. In order for the proxy user to access sensitive data, UDFs and UDTs need to know the requestor of the data. They obtain this information from the query band parameters.

For more information about query bands, refer to https://docs.teradata.com/r/Teradata-VantageCloud-Lake/SQL-Reference/SQL-Data-Definition-Language

If a proxy user is found among the query band parameters, then it is used in the authorization process instead of the regular data user (which could be a different user). This means that only the proxy user’s permissions apply. This is similar to how the Teradata permissions work for trusted sessions. The database permissions for the proxy user are used, and not the application user’s permissions.

Before such a user can access the database, a Grant Connect through Access right should be given by the database administrator to the user. The following example provides the query to ensure that the user ‘JSMITH’ can connect through.

The application My_App is confiured to connect to Teradata with a service account My_App_User that is not part of the Protegrity security policy. However, in case the app user JSMITH which does not exist in Teradata needs to see the data in the clear. Then, the database administrator must first Grant Connect access to the user, JSMITH.

GRANT CONNECT THROUGH My_App_User
TO JSMITH
WITH ROLE AppRole;

The user JSMITH can now access the database. However, since JSMITH does not exist in the database, Teradata needs to know what role it needs to inherit. This can be any role already configured within Teradata.

Then, every time JSMITH wants to run a SQL command through My_App, the following query band statement needs to be executed first: SET QUERY_BAND=‘PROXYUSER=JSMITH;’ FOR SESSION;

The UDF getqueryband is provided by Teradata.

select getqueryband();
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
getqueryband()
---------------------------------------------------------------------------

select pty_varcharlatinenc('abcd','AES',123,0,0);
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
pty_varcharlatinenc('abcd','AES',123,0,0)
---------------------------------------------------------------------------
E3AE49B5C44E4CE64CC7AB3A20F82325

SET QUERY_BAND='PROXYUSER=JSMITH;' FOR SESSION;
*** Set QUERY_BAND accepted.
*** Total elapsed time was 1 second.

select getqueryband();
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
getqueryband()
---------------------------------------------------------------------------
=S> PROXYUSER=JSMITH;

select pty_varcharlatinenc('abcd','AES',123,0,0);
*** Failure 7504 in UDF/XSP/UDM SYSLIB.pty_varcharlatinenc: SQLSTATE U0001:
No such user
Statement# 1, Info =0
*** Total elapsed time was 1 second.
AUDIT TRACE:
Thu Dec 30 01:21:53.530 2010 JSMITH AES 0 1 0 0 Insert, unknown user dbp 1

SET QUERY_BAND=NONE FOR SESSION;
*** Set QUERY_BAND accepted.
*** Total elapsed time was 1 second.

select pty_varcharlatinenc('abcd','AES',123,0,0);
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
pty_varcharlatinenc('abcd','AES',123,0,0)
---------------------------------------------------------------------------
E3AE49B5C44E4CE64CC7AB3A20F82325

Important: The Data Warehouse Protector supports user names that are up to 255 characters in length. However, the Teradata platform supports user name lengths of 128 characters only. Hence, the user name is limited to the value supported by the Teradata platform.

2 - Data Warehouse Sample Scripts

This page provides sample scripts for the operations performed using the Data Warehouse Protectors.

2.1 - Teradata Database Data Warehouse Sample Scripts

This page consists of the sample scripts for encryption and tokenization by the Teradata Data Warehouse Protector. It is recommended to view them and replace the values if required.
The following sample scripts are also included in the installation package.

Encryption sample script
Tokenization sample script

Encryption sample script

---------------------------------------------------------------------
-- Protegrity User Defined Functions sample script.
--
-- NOTE: Please change the following 'tags' before executing the script:
--      - REPLACE_DATABASE- Specify the testdatabase.
--      - <data element1> - dataelement used for protecting varchar
--      - <data element2> - dataelement used for protecting integer
--      - <data element3> - dataelement used for protecting date
--
-- This script should be run in BTEQ.
--
-- Copyright (c) 2025 Protegrity USA, Inc. All rights reserved
--
---------------------------------------------------------------------
DATABASE REPLACE_DATABASE;
.IF ERRORCODE != 0 THEN .QUIT 99

BT;
  DROP TABLE SAMPLE1_BAK;
ET;

BT;
  DROP TABLE SAMPLE1_PTY;
ET;

DROP VIEW SAMPLE1;

-----------------------------------------------------------------------
--
-- SAMPLE1: Table with no protection. Contains sample data.
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1 (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARCHAR(32)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    INTEGER                   NOT NULL,
    BIRT    DATE FORMAT 'YYYY/MM/DD'  NOT NULL,
    LUPD    DATE FORMAT 'YYYY/MM/DD'  NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1 ('4000567834561233', 'PTY_IVP_FPRTEST_LNAME', 123456789, 987654321, CAST( '2013/02/15' AS DATE ), CAST( '2013/02/15' AS DATE ));
ET;

BT;
RENAME TABLE SAMPLE1 to SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
-- SAMPLE1_PTY: This table is similar to SAMPLE1 will contain protected data.
--              The 'LNAM','REFN', and 'LUPD' columns now are of type VARBYTE.
--              Data is migrated from the 'SAMPLE1_BAK' table using UDF calls.
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1_PTY (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARBYTE(48)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    VARBYTE(16)               NOT NULL,
    BIRT    DATE FORMAT 'YYYY/MM/DD'  NOT NULL,
    LUPD    VARBYTE(16)               NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1_PTY("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") SELECT
    "CCN",
    TESTDB.PTY_VARCHARLATINENC("LNAM",'<data element1>',50,0,0),
    "RATING", 
    TESTDB.PTY_INTEGERENC("REFN",'<data element2>',34,0,0),
    "BIRT",
    TESTDB.PTY_DATEENC("LUPD",'<data element3>',34,0,0)
  FROM SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
--  SAMPLE1: This is a view that shows how data is unprotected using the UDFs.
--           Data is selected from the 'SAMPLE1_PTY' table.
--           The name of this view is the same as the original table
--
-----------------------------------------------------------------------
BT;
  CREATE VIEW SAMPLE1 ("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") AS SELECT
    "CCN",
    CAST(TESTDB.PTY_VARCHARLATINDEC("LNAM",'<data element1>',32,0,0) AS VARCHAR(32)),
    "RATING",
    CAST(TESTDB.PTY_INTEGERDEC("REFN",'<data element2>',0,0) AS INTEGER),
    "BIRT"
    ,
    CAST(TESTDB.PTY_DATEDEC("LUPD",'<data element3>',0,0) AS DATE)
  FROM SAMPLE1_PTY;
ET;

Tokenization sample script

---------------------------------------------------------------------
-- Protegrity User Defined Functions.
-- Copyright (c) 2025 Protegrity USA, Inc. All rights reserved
--
-- This script should be run in BTEQ
--
-- NOTE: Please change the following 'tags' before executing the script:
--      - TESTDB - database where the protegrity UDF's are installed
--      - REPLACEDB - Database where you have testdata
--      - <data element1> - dataelement used for protecting varchar
--      - <data element2> - dataelement used for protecting integer
--      - <data element3> - dataelement used for protecting date
--NOTE: Use datetime dataelement to protect and unprotect YMD date data
---------------------------------------------------------------------
DATABASE REPLACE_DATABASE;
.IF ERRORCODE != 0 THEN .QUIT 99

-----------------------------------------------------------------------
--
-- SAMPLE - Two tables and one view is created as follows:
--
-- Run the sample_tok.sql job to verify protect / unprotect of datatypes
-- VARCHAR, DATE and INTEGER.
--
-----------------------------------------------------------------------
BT;
  DROP TABLE SAMPLE1_BAK;
ET;

BT;
  DROP TABLE SAMPLE1_PTY;
ET;

DROP VIEW SAMPLE1;

-----------------------------------------------------------------------
--
-- SAMPLE1  Base table with no protection
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1 (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARCHAR(32)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    INTEGER                   NOT NULL,
    BIRT    DATE FORMAT 'yyyy-mm-dd'  NOT NULL,
    LUPD    DATE FORMAT 'yyyy-mm-dd'  NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1 ('4000567834561233', 'PTY_IVP_FPRTEST_LNAME', 123456789, 987654321, CAST( '2013/02/15' AS DATE ), CAST( '2013/02/15' AS DATE ));
ET;

BT;
RENAME TABLE SAMPLE1 to SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
-- SAMPLE1_PTY Same as SAMPLE1 but with protection added fo 
--             columns, which are encrypted / tokenized when the 
--             table is loaded from SAMPLE1_BAK.
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1_PTY (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARCHAR(32)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    INTEGER                   NOT NULL,
    BIRT    DATE FORMAT 'yyyy-mm-dd'  NOT NULL,
    LUPD    DATE FORMAT 'yyyy-mm-dd'  NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1_PTY("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") SELECT
    TESTDB.PTY_VARCHARLATININS("CCN",'<data element1>',32,0,0),
    "LNAM",
    TESTDB.PTY_INTEGERINS("RATING",'<data element2>',32,0,0),
    "REFN",
    CAST(TESTDB.PTY_VARCHARLATININS(CAST("BIRT" AS VARCHAR(32)),'<data element3>',32,0,0) AS DATE),
    "LUPD"
  FROM SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
--  SAMPLE1 Same as SAMPLE1_PTY. But data is decrypted /detokenized
--              when SAMPLE1 is loaded from SAMPLE1_PTY.
--
-----------------------------------------------------------------------
BT;
  CREATE VIEW SAMPLE1 ("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") AS SELECT
    TESTDB.PTY_VARCHARLATINSEL("CCN",'<data element1>',32,0,0),
    "LNAM",
    TESTDB.PTY_INTEGERSEL ("RATING",'<data element2>',0,0),
    "REFN",
    CAST(TESTDB.PTY_VARCHARLATINSEL(CAST("BIRT" AS VARCHAR(32)),'<data element3>',32,0,0) AS DATE),
    "LUPD"
  FROM SAMPLE1_PTY;
ET;

3 - Return Codes for Data Warehouse Protectors

This page includes the list of return codes for the Data Warehouse Protectors.

Return Code	Description
1	The username could not be found in the policy
2	The data element could not be found in the policy
3	The user does not have the appropriate permissions to perform the requested operation
4	Tweak is null
5	Integrity check failed
6	Data protect operation was successful
7	Data protect operation failed
8	Data unprotect operation was successful
9	Data unprotect operation failed
10	The user has appropriate permissions to perform the requested operation but no data has been protected/unprotected
11	Data unprotect operation was successful with use of an inactive keyid
12	Input is null or not within allowed limits
13	Internal error occurring in a function call after the provider has been opened
14	Failed to load data encryption key
15	Tweak input is too long
19	Unsupported tweak action for the specified fpe data element
20	Failed to allocate memory
21	Input or output buffer is too small
22	Data is too short to be protected/unprotected
23	Data is too long to be protected/unprotected
26	Unsupported algorithm or unsupported action for the specific data element
31	Policy not available
44	The content of the input data is not valid
49	Unsupported input encoding for the specific data element
50	Data reprotect operation was successful
51	Failed to send logs, connection refused

4 - Supported Data Warehouse Protectors Matrix

The below table lists the Data Warehouse protectors with the supported Data Warehouse version and platform details:

Protector	Supported Data Warehouse Version	Supported Platforms
Teradata Data Warehouse Protector	Teradata 17.05	SLES 12
	Teradata 17.10	SLES 12
	Teradata 17.20	SLES 12
		SLES 15
	Vantage Cloud Lake Teradata 20	SLES 15 SP4