User Defined Functions and APIs

• 1: Oracle User Defined Functions and APIs
• 1.1: General UDFs
• 1.2: Access Check Procedures
• 1.3: Insert Encryption UDFs
• 1.4: Insert No-Encryption, Token, and FPE UDFs
• 1.5: Multiple Insert Encryption Procedures
• 1.6: Select Decryption UDFs
• 1.7: Select No-Encryption, Token, and FPE UDFs
• 1.8: Update Encryption UDFs
• 1.9: Update No-Encryption, Token, and FPE UDFs
• 1.10: Multiple Update Encryption Procedures
• 1.11: Hash UDFs
• 1.12: Blob UDFs
• 1.13: Clob UDFs
• 1.14: Bulk UDFs
• 1.15: Oracle Input Datatype to UDF Mapping
• 2: MSSQL User Defined Functions and APIs
• 2.1: General Functions
• 2.2: Insert Procedures
• 2.3: UPDATE Extended Stored Procedures
• 2.4: Access Check Procedures
• 2.5: Select Functions and Procedures
• 2.6: VARCHAR UDFs
• 2.7: NVARCHAR UDFs
• 2.8: Integer UDFs
• 2.9: BLOB UDFs
• 2.10: CLOB UDFs

1 - Oracle User Defined Functions and APIs

1.1 - General UDFs

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

pty.whoami

The UDF returns the name of the user who is currently logged in to the database.

Signature:

pty.whoami()

Parameters:
None

Returns:
This UDF returns the name of the user as the VARCHAR2 string.

Exception:
None

Example:

select pty.whoami() ”Test of WhoAmI” from dual;
Test of WhoAmI
---
USER1

pty.getversion

This UDF returns the version of the protector.

Signature:

pty.getversion()

Parameters:
None

Returns:
This UDF returns the version of the protector as the VARCHAR2 string.

Example:

select pty.getversion() ”Test of GetVersion” from dual;

Test of GetVersion
---
x.x.x.x

1.2 - Access Check Procedures

The procedures listed here check whether the user is granted access permissions to the data element. The procedures will pass if the user has access. Otherwise, it casts an exception with the reason for failure.

The permissions for protect, unprotect, and reprotect are defined based on the roles assigned to the user. For more information about how to grant these permissions and assign roles, refer to Policy Management.

pty.ins_check

This procedure determines if the user has insert(protect) access to the data element.

Signature:

pty.ins_check(dataelement VARCHAR)

Parameters:

Returns:
The procedure returns the value as Success, if the user can insert data.

Example:

declare
begin
  dbms_output.put_line('Test of INSERT check procedure');
  dbms_output.put_line('------------------------------');
  pty.ins_check('DE_AES256');
end;

pty.sel_check

The procedure determines whether the user has select(unprotect) access to a data element.

Signature:

pty.sel_check(dataelement VARCHAR)

Parameters:

Returns:
The procedure returns the value as success, if the user has access.

Example:

declare
begin
  dbms_output.put_line('Test of SELECT check procedure');
  dbms_output.put_line('------------------------------');
  pty.sel_check('DE_AES256');
end;

pty.upd_check

This procedure determines if the user has update(reprotect) access to the data element.

Signature:

pty.upd_check(dataelement VARCHAR)

Parameters:

Returns:
The procedure returns the value as Success, if the user has update permissions.

Example:

declare
begin
  dbms_output.put_line('Test of UPDATE check procedure');
  dbms_output.put_line('------------------------------');
  pty.upd_check('DE_AES256');
end;

1.3 - Insert Encryption UDFs

These UDFs encrypt the data.

Note: The permissions for protect, unprotect, and reprotect are defined based on the roles assigned to the user. For more information about how to grant these permissions and assign roles, refer to Policy Management.

pty.ins_encrypt

This UDF encrypts data with a data element for encryption.

Signature:

pty.ins_encrypt (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW 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.ins_encrypt('DE_AES256', 'Original data', 0) "Test of INSERT encrypt func" from dual;

pty.ins_encrypt_char

This UDF encrypts the CHAR data with a data element for encryption.

Signature:

pty.ins_encrypt_char (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW 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.ins_encrypt_char('DE_AES256', 'Original data', 0) "Test of INSERT enc CHAR func" from dual;

pty.ins_encrypt_varchar2

This UDF encrypts the VARCHAR2 data with a data element for encryption.

Signature:

pty.ins_encrypt_varchar2(dataelement CHAR, inval VARCHAR2, scid1 BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the LONG RAW 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.ins_encrypt_varchar2('DE_AES256', 'Original data', 0) "Test INSERT enc VARCHAR2 func" from dual;

pty.ins_encrypt_date

This UDF encrypts the DATE data with a data element for encryption.

Note: To protect the Oracle input data type DATE, use the UDFs as described in Oracle Input Data Type to UDF Mapping to identify the appropriate UDF as per requirements.

Signature:

pty.ins_encrypt_date(dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW 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.ins_encrypt_date('DE_AES256', '23-OCT-14', 0) "Test of INSERT enc DATE func" from dual;

pty.ins_encrypt_integer

This UDF encrypts the INTEGER data with a data element for encryption.

Signature:

pty.ins_encrypt_integer (dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW 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.ins_encrypt_integer('DE_AES256', 12345, 0) "Test of INSERT enc INT func" from dual;

pty.ins_encrypt_real

This UDF encrypts the REAL data with a data element for encryption.

Signature:

pty.ins_encrypt_real (dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW 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.ins_encrypt_real('DE_AES256', 1234.1234, 0) "Test of INSERT enc REAL func" from dual;

pty.ins_encrypt_float

This UDF encrypts the FLOAT data with a data element for encryption.

Signature:

pty.ins_encrypt_float (dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW 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.ins_encrypt_float('DE_AES256', 1234.1234, 0) "Test of INSERT enc FLOAT func" from dual;

pty.ins_encrypt_number

This UDF encrypts the NUMBER data with a data element for encryption.

Signature:

pty.ins_encrypt_number (dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW 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.ins_encrypt_number('DE_AES256', 12345, 0) "Test of INSERT enc NUMBER func" from dual;

pty.ins_encrypt_raw

This UDF encrypts the RAW data, which is variable length binary data of maximum size 2000 bytes, with a data element for encryption.

Signature:

pty.ins_encrypt_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted values as the RAW 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.ins_encrypt_raw('DE_AES256', 'FFDD12345', 0) "Test of INSERT enc RAW func" from dual;

1.4 - Insert No-Encryption, Token, and FPE UDFs

These UDFs are used with Tokenization, Format Preserving Encryption (FPE) and, No Encryption data elements.

pty.ins_char

This UDF protects the CHAR data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.ins_char (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the CHAR data type.

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.ins_char('DE_CHAR', 'Original data', 0) "Test of INSERT CHAR func" from dual;

pty.ins_varchar2

This UDF protects the VARCHAR data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

CAUTION: For Date type of data elements, the pty.ins_varchar2 UDF returns an invalid date format error if the input value falls between the non-existent date range from 05-OCT-1582 to 14-OCT-1582 of the Gregorian Calendar. For more information about the tokenization and de-tokenization of the cutover dates of the Proleptic Gregorian Calendar, refer to the section Date Tokenization for cutover Dates of the Proleptic Gregorian Calendar in Protection Methods Reference.

Signature:

pty.ins_varchar2 (dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the VARCHAR2 data type.

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.ins_varchar2('DE_VARCHAR2', 'Original data', 0) "Test of INSERT VARCHAR2 func" from dual; 

pty.ins_unicodenvarchar2

This UDF encrypts data with a data element.

Note: This UDF does not support masking.

Signature:

pty.ins_unicodenvarchar2 (dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the NVARCHAR2 datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message that explains what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful protection without returning any error, but corruption of data can occur.

Example:

select pty.ins_unicodenvarchar2('fpe_unicode', 'Original data', 0) "Test of INSERT encrypt func" from dual;

pty.ins_unicodevarchar2_tok

This UDF protects the VARCHAR2 data with a Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.ins_unicodevarchar2_tok(dataelement IN CHAR, inval IN VARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the VARCHAR2 datatype.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message that explains what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful protection without returning any error, but corruption of data can occur.

Example for Unicode Gen2:

```
select pty.ins_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0) from dual;
```

```
select pty.ins_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0) from dual;
```

pty.ins_unicodenvarchar2_tok

This UDF protects the NVARCHAR2 data with a Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.ins_unicodenvarchar2_tok(dataelement IN CHAR, inval IN NVARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the NVARCHAR2 data type.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message that explains what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful protection without returning any error, but corruption of data can occur.

Example for Unicode Gen2:
select pty.ins_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0) from dual;

```
select pty.ins_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0) from dual;
```

pty.ins_date

This UDF protects the DATE data with a tokenization and No Encryption data element.

Signature:

pty.ins_date (dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected DATE value, when No Encryption data element is used.
  
• This UDF returns the protected DATE value, when a tokenization data element is used and if the data element date format and the NLS_DATE_FORMAT environment variable for an Oracle session is the same as mentioned in the note above.

Exception:

• No Encryption Date Element: If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.
• Tokenization Date Element: Tokenization fails and the UDF terminates with an error message explaining what went wrong.

Example for No Encryption:

select PTY.ins_date('DE_NoEnc', '10-23-2014', 0) "Test of INSERT DATE func" from dual;

Example for Tokenization:

select PTY.ins_date('DE_DATE', '10-23-2014', 0) "Test of INSERT DATE func" from dual;

pty.ins_integer

This UDF protects the INTEGER data with a tokenization and No Encryption data element.

Signature:

pty.ins_integer(dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the protected value as the INTEGER datatype.

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.ins_integer('DE_Integer', 12345, 0) "Test of INSERT INT func" from dual;

pty.ins_real

This UDF protects the REAL data with a No Encryption data element.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the REAL datatype.

Signature:
pty.ins_real(dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as the REAL datatype.

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: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_real('DE_NoEnc', 1234.1234, 0) "Test of INSERT REAL func" from dual;

pty.ins_float

This UDF protects the FLOAT data with a No Encryption data element.

Signature:

pty.ins_float (dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as the FLOAT datatype.

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: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_float('DE_NoEnc', 1234.1234, 0) "Test of INSERT FLOAT func" from dual;

pty.ins_number

This UDF protects the NUMBER data with tokenization and No Encryption data elements.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the NUMBER datatype.

Signature:

pty.ins_number (dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as the NUMBER datatype.

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: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_number('DE_Integer', 12345, 0) "Test of INSERT NUMBER func" from dual;

pty.ins_raw

This UDF protects the RAW data with a No Encryption data element.

Signature:

pty.ins_raw (dataelement CHAR, inval RAW, scid BINARY_INTEGER\)

Parameters:

Returns:
This UDF returns the unprotected value as RAW 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. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, then the UDF returns the following error:
character to number conversion error

Example:

select PTY.ins_raw('DE_NoEnc', 'FFDD12345', 0) "Test of INSERT RAW func" from dual;

1.5 - Multiple Insert Encryption Procedures

These procedures encrypt one to four values of data with one procedure call. The user must be granted Protect access for the data element that will be used to execute these procedures. You can use the ins_check procedure to check whether the user has Protect access.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.encInsert

This procedure encrypts one value of VARCHAR2 data with one data element for encryption.

Signature:

pty.encInsert(dataelement VARCHAR2, cdata VARCHAR2, rdata RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

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

Example:

declare 
  raw_out raw(2000); 
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 1 
    COLUMN');
  dbms_output.put_line('----------------------------------------------');
  pty.encInsert('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data: ' || raw_out);
end;

pty.ins_encryptx2

This procedure encrypts two values of VARCHAR2 data with two data elements for encryption.

Signature:

pty.ins_encryptx2 (dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted values as RAW data.

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

Example:

Encrypted values are the output parameters 
declare 
  raw_out1 raw(2000);
  raw_out2 raw(2000);
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 2 
    COLUMNS');
  dbms_output.put_line('---------------------------------------------');
  pty.ins_encryptx2('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
end;

pty.ins_encryptx3

This procedure encrypts three values of VARCHAR2 data with three data elements for encryption.

Signature:

pty.ins_encryptx3(dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted values as RAW data.

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

Example:

declare 
  raw_out1 raw(2000);
  raw_out2 raw(2000);
  raw_out3 raw(2000);
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 3 
    COLUMNS');
  dbms_output.put_line('---------------------------------------------');
  pty.ins_encryptx3('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199', 
    raw_out3, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
end;

pty.ins_encryptx4

This procedure encrypts four values of VARCHAR2 data with four data elements for encryption.

Signature:

pty.ins_encryptx4(dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER, dataelement4 VARCHAR2, cdata4 VARCHAR2, rdata4 RAW, scid4 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

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

Example:

declare 
  raw_out1 raw(2000);
  raw_out2 raw(2000);
  raw_out3 raw(2000);
  raw_out4 raw(2000);
begin 
  dbms_output.put_line('Test of INSERT multi encryption procedure for 4 
    COLUMNS');
  dbms_output.put_line('---------------------------------------------');
  pty.ins_encryptx4('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199', 
    raw_out3, 0, 'DE_AES256', 'fhgdADGHSJddeg', raw_out4, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out4);
end;

1.6 - Select Decryption UDFs

The UDFs in this section decrypt the encrypted data. Unprotect access is required to use these procedures.

pty.sel_decrypt

This UDF decrypts the RAW data with an encryption data element.

Signature:

pty.sel_decrypt(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt('DE_AES256', PTY.ins_encrypt('DE_AES256', 'Original data', 0),0) "Test of SELECT dec func" from dual;

pty.sel_decrypt_char

This UDF decrypts the CHAR data with an encryption data element.

Signature:

pty.sel_decrypt_char(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_char('AES256', PTY.ins_encrypt_char('AES256', 'Original data', 0),0) "Test of SELECT dec CHAR func" from dual; 

pty.sel_decrypt_varchar2

This UDF decrypts the VARCHAR2 data with an encryption data element.

Signature:

pty.sel_decrypt_varchar2(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the VARCHAR2 datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_varchar2('AES256', PTY.ins_encrypt_varchar2('AES256','Original data', 0),0) "Test of SELECT dec VARCHAR2 func" from dual;

pty.sel_decrypt_date

This UDF decrypts the DATE data with an encryption data element.

Signature:

pty.sel_decrypt_date(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the DATE datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_date('DE_AES256', PTY.ins_encrypt_date('DE_AES256', '23-OCT-14', 0),0) "Test of SELECT dec DATE func" from dual;

pty.sel_decrypt_integer

This UDF decrypts the INTEGER data with an encryption data element.

Signature:

pty.sel_decrypt_integer(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the INTEGER datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_integer('DE_AES256', PTY.ins_encrypt_integer('DE_AES256', 12345, 0),0) "Test of SELECT dec INT func" from dual;

pty.sel_decrypt_real

This UDF decrypts the REAL data with an encryption data element.

Signature:

pty.sel_decrypt_real(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the REAL datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_real('AES256', PTY.ins_encrypt_real('AES256',1234.1234,0),0) “Test of SELECT dec REAL func” from dual;

pty.sel_decrypt_float

This UDF decrypts the FLOAT data with an encryption data element.

Signature:

pty.sel_decrypt_float(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the FLOAT datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_float('DE_AES256', PTY.ins_encrypt_float('DE_AES256', 1234.1234, 0),0) "Test of SELECT dec FLOAT func" from dual;

pty.sel_decrypt_number

This UDF decrypts the NUMBER data with an encryption data element.

Signature:

pty.sel_decrypt_number(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NUMBER datatype.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_number('DE_AES256', PTY.ins_encrypt_number('DE_AES256', 12345, 0),0) "Test of SELECT dec NUMBER func" from dual;

pty.sel_decrypt_raw

This UDF decrypts the RAW data with an encryption data element.

Signature:

pty.sel_decrypt_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the RAW data.
• This UDF returns the unprotected value as the NULL, when the user has no access to data in the policy.

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

Example:

select PTY.sel_decrypt_raw('AES256', PTY.ins_encrypt_raw('AES256', 'FFDD12345', 0),0) "Test of SELECT dec RAW func" from dual;

1.7 - Select No-Encryption, Token, and FPE UDFs

These UDFs unprotect the protected data. Unprotect access is required to use these UDFs.

pty.sel_char

This UDF unprotects the CHAR data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.sel_char(dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the CHAR datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

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

Example:

select PTY.sel_char('DE_DTP2_AES256_AN', PTY.ins_char('DE_DTP2_AES256_AN', 'Original data', 0),0) "Test of SELECT CHAR func" from dual;

pty.sel_varchar2

This UDF unprotects the VARCHAR2 data with tokenization and No Encryption data elements.

Note: This UDF supports masking.

Signature:

pty.sel_varchar2(dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the VARCHAR2 datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

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

Example:

select PTY.sel_varchar2('DE_DTP2_AES256_AN', PTY.ins_varchar2('DE_DTP2_AES256_AN', 'Original data', 0),0) "Test of SELECT VARCHAR2 func" from dual;

pty.sel_unicodenvarchar2

This UDF unprotects the protected NVARCHAR data.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodenvarchar2(dataelement CHAR, inval NVARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NVARCHAR2 datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example:

select pty.sel_unicodenvarchar2('fpe_unicode', PTY.ins_unicodenvarchar2('fpe_unicode', 'Original data', 0),0) "Test of SELECT NVARCHAR2 func" from dual;

pty.sel_unicodevarchar2_tok

This UDF unprotects the VARCHAR2 data protected by a Unicode Base64 and Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodevarchar2_tok(dataelement IN CHAR, inval IN VARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as VARCHAR2.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example for Unicode Base64:

select pty.sel_unicodevarchar2_tok('TE_UNICODE_BASE64_SLT13_ASTYES', pty.ins_unicodevarchar2_tok('TE_UNICODE_BASE64_SLT13_ASTYES', 'Protegrity123',0),0) from dual;

Example for Unicode Gen2:
select pty.sel_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',pty.ins_unicodevarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0),0) from dual; select pty.sel_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',pty.ins_unicodevarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0),0) from dual;

pty.sel_unicodenvarchar2_tok

This UDF unprotects the NVARCHAR2 data protected by a Unicode Gen2 data element.

Note: This UDF does not support masking.

Signature:

pty.sel_unicodenvarchar2_tok(dataelement IN CHAR, inval IN NVARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the unprotected value as NVARCHAR2.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful unprotection without returning any error, but corruption of data can occur.

Example for Unicode Gen2:
select pty.sel_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',pty.ins_unicodenvarchar2_tok('TE_UG2_UTF16LE_LL1AN_SLT13_L2R0_ASTYES',N'xyzÀÁÂÃÄÅÆÇÈÉÊ',0),0) from dual;

```
select 
pty.sel_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',pty.ins_unicodenvarchar2_tok('TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE',N'ϠϡϢϣϥϦ',0),0) from dual;
```

pty.sel_date

This UDF unprotects the DATE data with a No Encryption data element.

Signature:

pty.sel_date(dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the DATE datatype.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

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

Example:

select PTY.sel_date('DE_NoEnc', PTY.ins_date('DE_NoEnc', '23-OCT-14', 0),0) "Test of SELECT DATE func" from dual;

pty.sel_integer

This UDF unprotects the INTEGER data with tokenization and No Encryption data elements.

Signature:

pty.sel_integer(dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the INTEGER datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

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

Example:

select PTY.sel_integer('Integer4',PTY.ins_integer('integer',12344567,0),0) “Test of SELECT INT func” from dual;

pty.sel_real

This UDF unprotects the REAL data with a No Encryption data element.

Signature:

pty.sel_real(dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the REAL datatype.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_real('DE_NoEnc', PTY.ins_real('DE_NoEnc', 1234.1234, 0),0) "Test of SELECT REAL func" from dual;

pty.sel_float

This UDF unprotects the FLOAT data with a No Encryption data element.

Signature:

pty.sel_float(dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the FLOAT datatype.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_float('DE_NoEnc', PTY.ins_float('DE_NoEnc', 1234.1234, 0),0) "Test of SELECT FLOAT func" from dual; 

pty.sel_number

This UDF unprotects the NUMBER data with tokenization and No Encryption data elements.

Signature:

pty.sel_number(dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the NUMBER datatype.
• This UDF returns the protected value, if this option is configured in the policy and user does not have access to data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_number('DE_Integer', PTY.ins_number('DE_Integer', 123455667, 0),0) "Test of SELECT NUMBER func" from dual; 

pty.sel_raw

This UDF unprotects the RAW data with a No Encryption data element.

Signature:

pty.sel_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the unprotected value as the RAW data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.
• This UDF returns the unprotected value as NULL, when the user is not specified in the policy.

Exception:
If configured in policy and user does not have unprotect access rights, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.sel_raw('DE_NoEnc', PTY.ins_raw('DE_NoEnc', 'FFDD12345', 0),0) "Test of SELECT RAW func" from dual;

1.8 - Update Encryption UDFs

These UDFs update the data. Protect access is required to use these functions.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.encUpdate

This procedure updates and encrypts one value of the VARCHAR2 data with one data element for encryption.

Signature:

pty.encUpdate(dataelement VARCHAR2, cdata VARCHAR2, rdata RAW, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

declare 
  raw_out raw(2000); 
begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 1
    COLUMN');
  dbms_output.put_line('------------------------------------------------
    ------');
  pty.encUpdate('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data: ' || raw_out);
end;

pty.upd_encrypt_char

This UDF re-encrypts the CHAR protected data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_char(dataelement CHAR, inval CHAR, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_char('DE_AES256', 'Original data', 0) "Test of UPDATE enc CHAR func" from dual;

pty.upd_encrypt_varchar2

This UDF re-encrypts the VARCHAR2 data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_varchar2(dataelement CHAR, inval VARCHAR2, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_varchar2('DE_AES256', 'Original data', 0) "Test of UPDATE enc VARCHAR2 func" from dual;

pty.upd_encrypt_date

This UDF re-encrypts the DATE data that has been updated, with a data element for encryption.

Note: When you use the pty.ins_encrypt_date UDF to protect date, the data is not protected. If you want to protect the Oracle input data type DATE, you must use the UDFs as described in Oracle Input Data Type to UDF Mapping to identify the appropriate UDF as per your requirement.

Signature:

pty.upd_encrypt_date(dataelement CHAR, inval DATE, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_date('DE_AES256', '23-OCT-14', 0) "Test of UPDATE enc DATE func" from dual;

pty.upd_encrypt_integer

This UDF re-encrypts the INTEGER data that has been updated, with a data element for encryption.

Signature:
pty.upd_encrypt_integer(dataelement CHAR, inval INTEGER, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_integer('DE_AES256', 12345, 0) "Test of UPDATE enc INT func" from dual;

pty.upd_encrypt_real

This UDF re-encrypts the REAL data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_real(dataelement CHAR, inval REAL, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_real('DE_AES256', 1234.1234, 0) "Test of UPDATE enc REAL func" from dual;

pty.upd_encrypt_float

This UDF re-encrypts the FLOAT data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_float(dataelement CHAR, inval FLOAT, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_float('DE_AES256', 1234.1234, 0) "Test of UPDATE enc FLOAT func" from dual;

pty.upd_encrypt_number

This UDF re-encrypts the NUMBER data that has been updated, with a data element in encryption.

Signature:

pty.upd_encrypt_number(dataelement CHAR, inval NUMBER, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_number('DE_AES256', 12345, 0) "Test of UPDATE enc NUMBER func" from dual;

pty.upd_encrypt_raw

This UDF re-encrypts the RAW data that has been updated, with a data element for encryption.

Signature:

pty.upd_encrypt_raw(dataelement CHAR, inval RAW, scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as RAW data.

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

Example:

select PTY.upd_encrypt_raw('DE_AES256', 'FFDD12345', 0) "Test of UPDATE enc RAW func" from dual; 

1.9 - Update No-Encryption, Token, and FPE UDFs

These UDFs are used to update the data for tokenization and Format Preserving Encryption (FPE). The user must have Protect access to use these procedures.

Note: For reprotect operations, the Audit logs are generated as Protect Logs instead of Reprotect Logs.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.upd_char

This UDF re-protects the CHAR data with tokenization and No Encryption data elements.

Signature:

pty.upd_char(dataelement CHAR, inval CHAR, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the output value as the CHAR datatype.

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

Example:

select PTY.upd_char('DE_DTP2_AES256_AN', 'Original data', 0) "Test of UPDATE CHAR func" from dual;

pty.upd_varchar2

This UDF reprotects the VARCHAR2 data with tokenization and No Encryption data elements.

Signature:

pty.upd_varchar2(dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the output value as the VARCHAR2 datatype.

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

Example:

select PTY.upd_varchar2('DE_DTP2_AES256_AN', 'Original data', 0) "Test of UPDATE VARCHAR2 func" from dual;

pty.upd_unicodenvarchar2

This UDF re-encrypts the NVARCHAR2 data that has been updated, with a data element.

Signature:

pty.upd_unicodenvarchar2(dataelement CHAR, inval NVARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as the NVARCHAR2 data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful reprotection without returning any error, but corruption of data can occur.

Example:

select PTY.upd_unicodenvarchar2('fpe_unicode', 'Original data', 0) "Test of UPDATE encrypt NVARCHAR2 func" from dual;

pty.upd_unicodevarchar2_tok

This UDF re-encrypts the VARCHAR2 data that has been updated with a Unicode Base64 and Unicode Gen2 data element.

Signature:

pty.upd_unicodevarchar2_tok (dataelement IN CHAR, inval IN VARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as VARCHAR2 data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful reprotection without returning any error, but corruption of data can occur.

Example:

select pty.upd_unicodevarchar2_tok('TE_UG2_S13_PL_N_BASCYR_AN_UTF8','‎защита данных‎',0) from dual;

pty.upd_unicodenvarchar2_tok

This UDF re-encrypts the NVARCHAR2 data that has been updated with a Unicode Base64 and Unicode Gen2 data element.

Signature:
pty.upd_unicodenvarchar2_tok(dataelement IN CHAR, inval IN NVARCHAR2, SCID IN BINARY_INTEGER)

Parameters:

Returns:
This UDF returns an encrypted value as NVARCHAR2 data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.
>Note: Ensure to use the supported data element only. Using an unsupported data element might result in successful reprotection without returning any error, but corruption of data can occur.

Example:

select pty.upd_unicodenvarchar2_tok('TE_UG2_S13_PL_N_BASCYR_AN_UTF8','‎защита данных‎',0) from dual;

pty.upd_date

This UDF reprotects the DATE data with a No Encryption data element.

Note: When you use the pty.ins_encrypt_date UDF to protect date, the data is not protected. If you want to protect the Oracle input data type DATE, you must use the UDFs as described in Oracle Input Data Type to UDF Mapping to identify the appropriate UDF as per your requirement.

Signature:

pty.upd_date (dataelement CHAR, inval DATE, scid BINARY_INTEGER)

Parameters:

Returns:
The UDF returns the original value as DATE.

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

Example:

select PTY.upd_date('DE_NoEnc', '23-OCT-14', 0) "Test of UPDATE DATE func" from dual; 

pty.upd_integer

This UDF re-protects the INTEGER data with tokenization and No Encryption data elements.

Signature:

pty.upd_integer(dataelement CHAR, inval INTEGER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the INTEGER datatype.

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

Example:

select PTY.upd_integer('DE_Integer', 12345, 0) "Test of UPDATE INT func" from dual;

pty.upd_real

This UDF reprotects the REAL data with a No Encryption data element.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the REAL datatype.

Signature:

pty.upd_real(dataelement CHAR, inval REAL, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the REAL datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_real('DE_NoEnc', 1234.1234, 0) "Test of UPDATE REAL func" from dual;

pty.upd_float

This UDF reprotects the FLOAT data with a No Encryption data element.

Signature:

pty.upd_float(dataelement CHAR, inval FLOAT, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the FLOAT datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure that you use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_float('DE_NoEnc', 1234.1234, 0) "Test of UPDATE FLOAT func" from dual;

pty.upd_number

This UDF reprotects the NUMBER data with tokenization and No Encryption data elements.

Note: Data corruption occurs when the input length exceeds 10 decimal digits in the NUMBER datatype.

Signature:

pty.upd_number(dataelement CHAR, inval NUMBER, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the NUMBER datatype.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure that you use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_number('DE_Integer', 12345, 0) "Test of UPDATE NUMBER func" from dual;

pty.upd_raw

This UDF re-protects the RAW data with a No Encryption data element.

Signature:

pty.upd_raw(dataelement CHAR, inval RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This UDF returns the original value as the RAW data.

Exception:
If the user does not have reprotect access rights in the policy, then the UDF terminates with an error message explaining what went wrong. >Note: Ensure to use the supported data element only. If an unsupported data element is passed, the following error is returned: character to number conversion error.

Example:

select PTY.upd_raw('DE_NoEnc', 'FFDD12345', 0) "Test of UPDATE RAW func" from dual;

1.10 - Multiple Update Encryption Procedures

These procedures encrypt one to four values of data with one procedure call. The user must be granted Protect access for the data element that will be used to execute these procedures. You can use the upd_check procedure to check whether the user has Protect access.

Note: These UDFs are marked for deprecation and will be removed from the future releases. Protegrity recommends to use the standard Insert or Protect UDFs.

pty.encUpdate

This procedure updates and encrypts one value of the VARCHAR2 data with one data element for encryption.

Signature:

pty.encUpdate (dataelement VARCHAR2, cdata VARCHAR2, rdata RAW, scid BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

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

Example:

declare 
  raw_out raw(2000); 
begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 1
    COLUMN');
  dbms_output.put_line('------------------------------------------------
    ------');
  pty.encUpdate('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data: ' || raw_out);
end;

pty.upd_encryptx2

This procedure updates and encrypts two values of VARCHAR2 data with two data elements for encryption.

Signature:

pty.upd_encryptx2(dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

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

Example:

begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 2 
    COLUMNS');
  dbms_output.put_line('------------------------------------------------
    -------');
  pty.upd_encryptx2('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
end;

pty.upd_encryptx3

This procedure updates and encrypts three values of VARCHAR2 data with three data elements for encryption.

Signature:

pty.upd_encryptx3 (dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

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

Example:

begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 3 
    COLUMNS');
  dbms_output.put_line('-----------------------------------------------
    --------');
  pty.upd_encryptx3('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0,
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199',
    raw_out3, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
end;

pty.upd_encryptx4

This procedure updates and encrypts four values of VARCHAR2 data with four data elements for encryption.

Signature:

pty.upd_encryptx4 (dataelement1 VARCHAR2, cdata1 VARCHAR2, rdata1 RAW, scid1 BINARY_INTEGER, dataelement2 VARCHAR2, cdata2 VARCHAR2, rdata2 RAW, scid2 BINARY_INTEGER, dataelement3 VARCHAR2, cdata3 VARCHAR2, rdata3 RAW, scid3 BINARY_INTEGER, dataelement4 VARCHAR2, cdata4 VARCHAR2, rdata4 RAW, scid4 BINARY_INTEGER)

Parameters:

Returns:
This procedure returns the encrypted value as RAW data.

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

Example:

begin 
  dbms_output.put_line('Test of UPDATE multi encryption procedure for 4 
    COLUMNS');
  dbms_output.put_line('------------------------------------------------
    -------');
  pty.upd_encryptx4('DE_AES256', 'ASFGFGghg5577fFFyu', raw_out1, 0, 
    'DE_AES256', 'IyutGGg76hg8h1', raw_out2, 0, 'DE_AES256', 'AAaazzZZ1199',
    raw_out3, 0 , 'DE_AES256', ' ASFGFGghg5577fFFyu; AblnQEWsw0129NGku; 
    BINKUcrc8749lLLnx; CAESYwiw0098mMMns; FEORLkjk2323kKKmn; 
    LAENILmcm6677kBBop; MOIRNAzlz9876lMMyu;  MUBMIARAR6087kUUmn; 
   NIASAlziz2398hTTuv; PATRHXuru9898hFFns; ROYNESgog7802gMMus;
   SIRSHAuna9049kKKjn; TOTALSlol7843mWWqa; TUSFAVopo8080tTTnx; 
   TUHSRAknk8108mKKdw; VAENSAJJBJ6712fFFGH; VEPSIMdsd9898kSDnm; 
   URDPLAghg7676LLyu; UNBAKERkik2233lLLmu; YANMRAlsl9090fFFyu; 
   YASTURhom0123hHHmn; XAOILDghg0987fFFmn; ZABCDEmom5577bHHyy; 
  ZOHRASghg5297nNNcd ', raw_out4, 0);
  DBMS_OUTPUT.PUT_LINE('Encrypted data1: ' || raw_out1);
  DBMS_OUTPUT.PUT_LINE('Encrypted data2: ' || raw_out2);
  DBMS_OUTPUT.PUT_LINE('Encrypted data3: ' || raw_out3);
  DBMS_OUTPUT.PUT_LINE('Encrypted data4: ' || raw_out4);
end;

1.11 - Hash UDFs

These UDFs protect the data as a hash value.

pty.ins_hash_varchar2

This UDF uses the hash function to protect the VARCHAR data with a data element for hashing to return a protected value.

Signature:

pty.ins_hash_varchar2(dataelement CHAR, cdata VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the Hash value as the RAW data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.

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

Example:

SELECT PTY.ins_hash_varchar2('DE_Hash', ' ASertcv2013; CUxdcs3675; ccNNddfF9084; hjMjCS0123',0) "Test of INSERT HASH function" from dual; 

pty.upd_hash_varchar2

This UDF uses the hash function to protect the VARCHAR data with a data element for hashing to return a protected value.

Signature:

pty.upd_hash_varchar2(dataelement CHAR, inval VARCHAR2, scid BINARY_INTEGER)

Parameters:

Returns:

• This UDF returns the Hash value as the RAW data.
• This UDF returns the unprotected value as NULL, when the user has no access to data in the policy.

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

Example:

SELECT PTY.upd_hash_varchar2('DE_Hash', 'ASertcv2013; CUxdcs3675; ccNNddfF9084; hjMjCS0123;',0) "Test of UPDATE HASH function" from dual; 

1.12 - Blob UDFs

These UDFs can be used to encrypt and decrypt the data stored in the BLOB data type.

pty.ins_encrypt_blob

This function is used to encrypt the data stored in a BLOB with an encryption data element.

Signature:

pty.ins_encrypt_blob(dataelement CHAR, input_data BLOB , scid INTEGER)

Parameters:

Returns:
This UDF returns the encrypted value as the BLOB data. > Note: If you perform a protect operation with the input data as null or empty, then the output will be an empty_blob.

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

Example:

select pty.ins_encrypt_blob('AES256',TO_BLOB('691F89CD2BCBF055EFD4F3B51470AEF6'),0) from dual;

Caution: A maximum of 1.5 GB of input data can be protected using the pty.ins_encrypt_blob UDF. The pty.ins_encrypt_blob UDF will return an unexpected behaviour if you exceed the maximum input data limit of 1.5 GB. For example: ORA-28579: network error during callback from external procedure agent.

pty.sel_decrypt_blob

This function is used to decrypt the encrypted data stored in a BLOB with an encryption data element.

Signature:

pty.sel_decrypt_blob (dataelement CHAR, input_data BLOB, scid INTEGER)

Parameters:

Returns:

• This UDF returns the decrypted value as the BLOB data.
• This UDF returns the decrypted value as an EMPTY_BLOB, when the user has no access to the database.

Note: If you perform a protect operation with the input data as null or empty, then the output will be an empty_blob.

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

Example:

select pty.sel_decrypt_blob('AES256',pty.ins_encrypt_blob('AES256',TO_BLOB('691F89CD2BCBF055EFD4F3B51470AEF6'),0),0) from dual;

1.13 - Clob UDFs

These UDFs can be used to encrypt and decrypt the data stored in the CLOB data type.

pty.ins_encrypt_clob

This function is used to encrypt the data stored in a CLOB with an encryption data element.

Signature:

pty.ins_encrypt_clob(dataelement CHAR, input_data CLOB, scid INTEGER)

CAUTION: Ensure that the input data stored in the CLOB data type does not contain multibyte characters. If you pass data containing multibyte characters to the CLOB UDF, then an unexpected behaviour is observed.
For example: An error 'ORA-28579: network error during callback from external procedure agent' is returned or the input data is corrupted. For more information about CLOB data type, refer to the Oracle Help Center.

Parameters:

Returns:
This UDF returns the encrypted value as the CLOB data. >Note: If you perform a protect operation with the input data as null or empty, then the output will be an empty_blob.

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.ins_encrypt_clob('AES256','John',0) from dual;

Note: A maximum of 500 MB of input data can be protected using the pty.ins_encrypt_clob UDF.

pty.sel_decrypt_clob

This function is used to decrypt the encrypted data stored in a BLOB with an encryption data element.

Signature:

pty.sel_decrypt_clob(dataelement CHAR, input_data BLOB, scid INTEGER)

Parameters:

Returns:

• This UDF returns the decrypted value as the CLOB data.
• This UDF returns the decrypted value as an EMPTY_CLOB, when the user has no access to the database.

  Note: If you perform a unprotect operation with the input data as null or empty, then the output will be an EMPTY_CLOB.

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

Example:

select pty.sel_decrypt_clob('AES256',pty.ins_encrypt_clob('AES256','John',0),0) from dual;

1.14 - Bulk UDFs

Bulk User-Defined Functions (UDFs) in Oracle are designed to process multiple rows in a single call, rather than operating on one row at a time like scalar UDFs. They are typically used for batch operations such as tokenization, encryption, or transformation of large datasets. In Oracle v10.0.0, bulk UDFs are implemented to improve efficiency when working with large tables or columns containing sensitive data.

The features of the bulk UDFs are listed below.

• Accept table name, source column(s), and data element name as arguments.
• Read multiple records, prepare batches, and process them collectively.
• Return results for all rows in one execution cycle.

The advantages of bulk UDFs over scalar UDFs are listed below.

Note: When ‘NULL’ is passed as a column name, it will be treated a standard SQL term and be processed appropriately. For example, the following query will return NULL under the result column.

select * from pty.ins_varchar2_bulk('tbl_tok_varchar_bulk_positive','NULL','cid','TE_A_S13_L0R0_ASTYES',NULL,0);

Note: In case of an error in executing the bulk UDFs, it is observed that failed queries return the audit log count based on the internal batch size. The range for the batch size ranges from a minimum of 1 to a maximum of 1000 entries.

Note: The source and primary key column names in the tables will be processed and executed as per SQL’s standard behavior.

pty.ins_encrypt_varchar2_bulk

This function is used to encrypt a column of VARCHAR2 data in bulk, returning a table of results with the primary key and encrypted value.

Note: The column_name data must be in the varchar format.

Signature:

pty.ins_encrypt_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:
This UDF returns a pipelined table of type raw_4000_table, where each row contains the primary key and the encrypted value for the column. If the input data is null or empty, the output will be NULL.

Example:

SELECT * FROM TABLE(
  pty.ins_encrypt_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'ID',
    'AES256',
    'WHERE status = ''ACTIVE''',
    0
  )
);

pty.sel_decrypt_varchar2_bulk

This function is used to decrypt a column of RAW (encrypted VARCHAR2) data in bulk, returning a table of results with the primary key and decrypted value.

Note: The source column data must be in the RAW format.

Signature:

pty.sel_decrypt_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:

This UDF returns a pipelined table of type result_table_type, where each row contains the primary key and the decrypted value for the column. If the input data is null or empty, the output will be NULL.

Example:

SELECT * FROM TABLE(
  pty.sel_decrypt_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'ID',
    'AES256',
    'WHERE status = ''ACTIVE''',
    0
  )
);

pty.ins_varchar2_bulk

This function is used to tokenize (protect) a column of VARCHAR2 data in bulk, returning a table of results with primary key and tokenized value.

Note: The column_name data must be in the varchar format.

Signature:

pty.ins_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:
This UDF returns a pipelined table of type result_table_type, where each row contains the primary key and the tokenized value for the column. If the input data is null or empty, the output will NULL.

Example:

SELECT * FROM TABLE(
  pty.ins_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'id',
    'TE_A_S13_L1R2_Y',
    'WHERE status = ''ACTIVE''',
    0
  )
);

Example of table to table insert with Bulk UDF:

insert into <target_table>(col1,col2,col3,col4,col5) 
select p.pk_value,e.col2,e.col3,e.col4,p.result
from <source_table> e join table(pty.ins_varchar2_bulk('<source_table>','col5','col1','de_TokName',NULL,0))
on e.col1 = p.pk_value; 

pty.sel_varchar2_bulk

This function is used to detokenize (unprotect) a column of VARCHAR2 data in bulk, returning a table of results with primary key and detokenized value.

Note: The column_name data must be in the VARCHAR2 format.

Signature:

pty.sel_varchar2_bulk(
    source_table_name IN VARCHAR2,
    column_name IN VARCHAR2,
    pk_column_name IN VARCHAR2,
    dataelement IN CHAR,
    where_clause IN VARCHAR2,
    SCID IN BINARY_INTEGER
)

Parameters:

Returns:

This UDF returns a pipelined table of type result_table_type, where each row contains the primary key and the detokenized value for the column. If the input data is null or empty, the output will NULL.

Example:

SELECT * FROM TABLE(
  pty.sel_varchar2_bulk(
    '<table_name>',
    '<input_column>',
    'id',
    'TE_A_S13_L1R2_Y',
    'WHERE status = ''ACTIVE''',
    0
  )
);

1.15 - Oracle Input Datatype to UDF Mapping

This section provides tables with the Oracle input data type to the appropriate UDF mapping. It also provides the data element information that must be considered when creating a policy.

CAUTION: Starting from version 10.0.0, the 3DES, CUSP 3DES, and HMAC-SHA1 protection methods have been deprecated based on NIST recommendations around weak ciphers. It is recommended to use the following protection methods instead of the deprecated methods:

For assistance in switching to a different protection method, contact Protegrity.

CAUTION: Starting from version 10.0.0, the Date YYYY-MM-DD, Date DD/MM/YYYY, Date MM/DD/YYYY, Printable, Unicode, and Unicode Base64 tokenization types have been deprecated. It is recommended to use the following tokenization types instead of the deprecated tokenization types:

For assistance in switching to a different tokenization type, contact Protegrity.

2 - MSSQL User Defined Functions and APIs

This section provides a detailed list of functions and extended stored procedures for general functions, and protection and unprotection of different data types.

Warning:
Ensure to use the supported data element only. While using an unsupported data element, the decryption will complete successfully without any error. However, this can result in data corruption.

Note:
In the case of MSSQL Database Protector, if a data element greater than 55 characters long is passed to the UDF, then the UDF terminates with the error message: Data element name too long.

2.1 - General Functions

pty_getVersion

This function returns the version of the installed protector.

Signature:

pty_getVersion()

Parameters:
None

Returns:
This UDF returns the version number as the NVARCHAR datatype.

Example:

DECLARE
@data NVARCHAR(64)
SELECT @data = <database_name>.dbo.pty_getVersion()
PRINT @data;

pty_whoAmI

This function returns the name of the logged in user.

Signature:

pty_whoAmI()

Parameters:
None

Returns:
This UDF returns the name of the user as the NVARCHAR datatype.

Example:

DECLARE
@data NVARCHAR
SELECT @data = <database_name>.dbo.pty_whoAmI()
PRINT @data

2.2 - Insert Procedures

These extended procedures are used while protecting data using Insert queries.

xp_pty_insert

This stored procedure protects the data using an encryption data element.

Signature:

xp_pty_insert(outputdata VARBINARY OUTPUT, data VARCHAR,dataelement VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
@result INT,
 @outputdata VARBINARY(8000),
 @data VARCHAR(8000)
SET @data='232432423432'
EXEC @result= <database_name>.dbo.xp_pty_insert @outputdata output,@data, 'AES256', 0
IF @result = 0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata

In the Example, value=‘AES256’, is the name of the data element.

xp_pty_tpe_unicode_insert

This stored procedure protects the data using the FPE Unicode, and Unicode Gen2 data elements.

Note:
This UDF does not support masking.

Signature:

xp_pty_tpe_unicode_insert(outputdata NVARCHAR OUTPUT, data NVARCHAR, dataelement VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example for Unicode Gen2:

DECLARE 
 @result INT,
 @outputdata NVARCHAR(4000),
 @data NVARCHAR(4000)
SET @data= N'¢€ÃÂÃÄÅÊËÌÃÃŽÃÃÑÒÓÃÃ'
EXEC @result= <database_name>.dbo.xp_pty_tpe_unicode_insert @outputdata output,@data, 
'TE_UG2_SLT13_L2R2_UTF16LE_Latin1_Supplement_ASTYES', 0
IF @result = 0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata

In the Example, value=‘TE_UG2_SLT13_L2R2_UTF16LE_Latin1_Supplement_ASTYES’, is the name of the data element.

Example for FPE Unicode:

DECLARE 
 @result INT,
 @outputdata NVARCHAR(4000),
 @data NVARCHAR(4000)
SET @data= N'232432423432'
EXEC @result= <database_name>.dbo.xp_pty_tpe_unicode_insert @outputdata output,@data, 
'fpe_unicode', 0
IF @result = 0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata

xp_pty_tpe_insert

This stored procedure protects the data using type-preserving data element, such as Tokens and No Encryption for access control.

Signature:

xp_pty_tpe_insert(outputdata VARCHAR OUTPUT, data VARCHAR, dataelement VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
 @result INT,
 @outputdata VARCHAR(8000),
 @data VARCHAR(8000)
SET @data='How are you'
EXEC @result= <database_name>.dbo.xp_pty_tpe_insert @outputdata output,@data, 'TE_A_S13_L0R0_Y', 
0
IF @result = 0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata

xp_pty_tpe_int_insert

This stored procedure protects the integer data by using an integer-tokenization data element.

Signature:

xp_pty_tpe_int_insert(outputdata INT OUTPUT, data INT, dataelement VARCHAR, scid INT)

Note: This UDF does not support no-encryption integer-tokenization data element.

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
 @result INT,
 @outputdata INT,
 @data INT
SET @data='1234'
EXEC @result= <database_name>.dbo.xp_pty_tpe_int_insert @outputdata output,@data, 'Integer', 0
IF @result=0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata

xp_pty_insert_hash

This stored procedure calculates the hash value of the input data using a HMAC_SHA256 data element.

Note: HMAC_SHA1 is deprecated. Protegrity recommends transitioning to HMAC_SHA256. Starting with v10.0.0, creating new data elements under HMAC_SHA1 is disabled. However, existing data elements under HMAC_SHA1 can be used.

Signature:

xp_pty_insert_hash(hash VARBINARY OUTPUT, data VARCHAR, dataelement VARCHAR, scid INT)

Parameters

Returns:
This UDF returns either of the following values as a result of the hash operation:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
 @result INT,
 @hash VARBINARY(8000),
 @data VARCHAR(8000)
SET @data='232432423432'
EXEC @result= <database_name>.dbo.xp_pty_insert_hash @hash output, @data, 'HMAC_SHA256', 0
IF @result=0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @hash

In the Example, value=‘HMAC_SHA256’, is the name of the data element.

2.3 - UPDATE Extended Stored Procedures

These stored procedures are used while protecting data using Update statements.

xp_pty_update

This stored procedure protects the data using an encryption data element.

Signature:

xp_pty_update(outputdata VARBINARY OUTPUT, data VARCHAR, dataelement VARCHAR, status CHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE
 @result INT,
 @outputdata VARBINARY(8000),
 @data VARCHAR(8000)
SET @data = '232432423432'
EXEC @result = <database_name>.dbo.xp_pty_update @outputdata output, @data, 'AES256', 'T', 0
IF @result = 0
 PRINT 'OK'
ELSE
 PRINT 'ERROR'
PRINT @outputdata

In the Example, value=‘AES256’, is the name of the data element.

xp_pty_tpe_unicode_update

This stored procedure protects the data using the FPE Unicode data element only.

Note: This UDF does not support masking.

Signature:

xp_pty_tpe_unicode_update(outputdata NVARCHAR OUTPUT, data NVARCHAR, dataelement VARCHAR, status CHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE
 @result INT,
 @outputdata NVARCHAR(4000),
 @data NVARCHAR(4000)
SET @data = N'232432423432'
EXEC @result = <database_name>.dbo.xp_pty_tpe_unicode_update @outputdata output, @data, 
'fpe_unicode', 'T', 0
IF @result = 0
 PRINT 'OK'
ELSE
 PRINT 'ERROR'
 PRINT @outputdata

xp_pty_tpe_update

This stored procedure protects the data using type-preserving data element, such as Tokens and No Encryption, for access control.

Signature:

xp_pty_tpe_update(outputdata VARCHAR OUTPUT, data VARCHAR, dataelement VARCHAR, status CHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE
 @result INT,
 @outputdata VARCHAR(8000),
 @data VARCHAR(8000)
SET @data = 'How are you'
EXEC @result = <database_name>.dbo.xp_pty_tpe_update @outputdata output, @data, 
'TE_A_S13_L0R0_Y', 'T', 0
IF @result = 0
 PRINT 'OK'
ELSE
 PRINT 'ERROR'
PRINT @outputdata

xp_pty_tpe_int_update

This stored procedure protects the input data (integer) using an integer-tokenization data element.

Signature:

xp_pty_tpe_int_update(outputdata INT OUTPUT, data INT, dataelement VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as protected data in the output parameter:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
 @result INT,
 @outputdata INT,
 @data INT
SET @data ='1234'
EXEC @result= <database_name>.dbo.xp_pty_tpe_int_update @outputdata output, @data, 'Integer', 0
IF @result = 0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata

xp_pty_update_hash

This stored procedure calculates the hash value of the input data using a HMAC_SHA256 data element.

Note:
New data elements under HMAC_SHA1 cannot be created.
Existing data elements under HMAC_SHA1 remain supported and can continue to be used.

^*HMAC_SHA1 is deprecated. We recommend transitioning to HMAC_SHA256, which continues to be supported.

Signature:

xp_pty_update_hash(hash VARBINARY OUTPUT, data VARCHAR, dataelement VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns either of the following values as a result of the hash operation:

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

DECLARE 
 @result INT,
 @hash VARBINARY(64),
 @data VARCHAR(64)
SET @data='232432423432'
EXEC @result= <database_name>.dbo.xp_pty_update_hash @hash output,@data, 'HMAC_SHA256', 0
IF @result=0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @hash

2.4 - Access Check Procedures

These functions check access permissions allowed to the user for protecting or unprotecting the data. Depending on the permitted access, a bit value 0 (zero) or 1 is returned.

xp_pty_select_check

This stored procedure determines whether the user has select (unprotect) access to the data element.

Signature:

xp_pty_select_check(dataelement VARCHAR)

Parameters:

Returns:
This UDF returns either of the following values:

Example:

DECLARE 
 @result BIT
SELECT @result = master.dbo.xp_pty_select_check ('AES256')
PRINT @result 

In the Example, value=‘AES256’, is the name of the data element.

xp_pty_update_check

This stored procedure determines whether the user has update access to the data element.

Signature:

xp_pty_update_check (dataelement VARCHAR)

Parameters:

Returns:
This UDF returns either of the following values:

Example:

DECLARE
 @result BIT
SELECT @result = master.dbo.xp_pty_update_check('AES256')
PRINT @result

In the Example, value=‘AES256’, is the name of the data element.

xp_pty_insert_check

This stored procedure determines whether the user has insert (protect) access to the data element.

Signature:

xp_pty_insert_check(dataelement VARCHAR)

Parameters:

Returns:
This UDF returns either of the following values:

Example:

DECLARE
 @result BIT
SELECT @result = master.dbo.xp_pty_insert_check('AES256')
PRINT @result

In the Example, value=‘AES256’, is the name of the data element.

2.5 - Select Functions and Procedures

These functions and stored procedures unprotect the data and return the unprotected value.

pty_select

This function unprotects the data that is protected by an encryption data element.

Signature:

pty_select (data VARBINARY, dataelement VARCHAR, def VARCHAR, scid INT)

Parameters: