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:

Returns:
This UDF returns the unprotected value as the VARCHAR(8000) datatype.

Attention: If the user does not have access to the data in the policy, this UDF returns ‘NULL’.

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(64)
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
SELECT @data = <database_name>.dbo.pty_select (@outputdata, 'AES256',null,0)
PRINT @data 

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

pty_selectunicode

This function unprotects the data that is protected by Unicode Gen2, and FPE Unicode data elements.

Note: This UDF does not support masking.

Signature:

pty_selectunicode (data NVARCHAR, dataelement VARCHAR, def INT, scid INT)

Parameters:

Returns:
This UDF returns the unprotect value as the NVARCHAR(4000) datatype. This UDF returns protected value, if the option is configured in policy and user does not have access to the data.

Attention: If the user does not have access to the data in the policy, this UDF returns ‘NULL’.

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
SELECT @data = <database_name>.dbo.pty_selectunicode (@outputdata,
'TE_UG2_SLT13_L2R2_UTF16LE_Latin1_Supplement_ASTYES',null,0)
PRINT @data

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
SELECT @data = <database_name>.dbo.pty_selectunicode (@outputdata, 'fpe_unicode',null,0)
PRINT @data

pty_select2

This function unprotects the data that is protected by a type-preserving data element, such as, Tokens and No Encryption, for access control.

Signature:

pty_select2(data VARCHAR, dataelement VARCHAR, def VARCHAR, scid INT)

Parameters:

Returns:
This UDF returns the unprotected value as the VARCHAR(8000) datatype.

Attention: If the user does not have access to the data in the policy, this UDF returns ‘NULL’.

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 VARCHAR64,
 @data VARCHAR64
SET @data='232432423432'
EXEC @result= <database_name>.dbo.xp_pty_tpe_insert @outputdata output,@data, 'TE_N_S16_L0R0_Y',0
IF @result=0
 PRINT 'OK'
ELSE 
 PRINT 'ERROR'
PRINT @outputdata
SELECT @data = <database_name>.dbo.pty_select2(@outputdata,'TE_N_S16_L0R0_Y',null,0)
PRINT @data

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

pty_selectint

This function unprotects the data that is protected by an integer-tokenization data element.

Signature:

pty_selectint(data INT, dataelement VARCHAR, def INT, scid INT)

Parameters:

Returns:
This UDF returns the unprotected value as the INT datatype. This UDF returns protected value, if the option is configured in policy and user does not have access to the data.

Attention: If the user does not have access to the data in the policy, this UDF returns ‘NULL’.

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(64),
 @data INT
 SET @data= 2324
 EXEC @result= <database_name>.dbo.xp_pty_tpe_insert @outputdata output, @data,
'TE_INT_4', 0
 IF @result = 0
 PRINT 'OK'
 ELSE 
 PRINT 'ERROR'
 PRINT @outputdata
 SELECT @data = <database_name>.dbo.pty_selectint(@outputdata,'TE_INT_4',null,0)
 PRINT @data

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

xp_pty_select

This function unprotects the data that is protected by an encryption data element. It can also be used when the Security Coordinate ID is not defined.

Signature:

xp_pty_select(data VARBINARY, dataelement VARCHAR, def VARCHAR)

Parameters:

Returns:
This UDF returns the unprotected value as the VARBINARY(8000) datatype.

Attention: If the user does not have access to the data in the policy, this UDF returns ‘NULL’.

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(64) 
@data VARBINARY(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
SELECT @data = <database_name>.dbo.xp_pty_select(@outputdata,'AES256',null)
PRINT @data

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