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.