Skip to main content

OTF Decryption

For On-The-Fly (OTF) decryption, data is decrypted on request from any user or application while reading data from physical storage. The data is always stored in encrypted form.

In PK Protect, OTF decryption is supported only in RDBMS for the following three databases: Redshift, SQL Server, and DB2 for I. After successfully executing an encryption task, you must provide access rights for sensitive types to the database user(s) by assigning roles to them under the Access Control screen. After getting the permissions, user will be able to see the decrypted data i.e., plain data. Otherwise, the user will see the encrypted data.

*Note: For OTF decryption, there is no need to run any decryption task from PK Protect user interface.

OTF decryption is supported through:

  1. UDF

  2. View

UDFs

UDF stands for User Defined Function. The user can directly call a UDF to decrypt data instead of using PK Protect user interface. The UDF will be created on the target database. You can add the below mentioned UDF calls in the query and can perform OTF decryption:

  • For DB2 for I:

    1. DG_FPE_DECRYPT_OTF(<Input data>, <Target database name>, <Target table name>, <Target column name>)

    2. DG_AES_DECRYPT_OTF(<Input data>, <Target database name>, <Target table name>, <Target column name>)

  • For SQL Server & Redshift:

    1. DG_AES_DECRYPT_V2(<Column name>, '<schema.table.column>')

    2. DG_FPE_DECRYPT_V2(<Column name>, '<schema.table.column>')

  • Sample Function Call:

    • For DB2 for I:
      SELECT SSNO, DG.DG_FPE_DECRYPT_OTF(SSNO, 'DG_MASK', 'INFO2', 'SSNO'),
      EMAIL, DG.DG_AES_DECRYPT_OTF(EMAIL, 'DG_MASK', 'INFO2', 'EMAIL')
      FROM TARGET_DB.TARGET_TABLE;

    • For SQL Server & Redshift:
      SELECT DG.DG_AES_DECRYPT_V2(SSNO, 'DG_MASK.INFO2.SSNO’) FROM DG_MASK.INFO2;

*Note: All the input parameters are case-sensitive. Also, there is a security flaw in UDF approach. Hence, it is recommended to use OTF decryption views.

View

To create a view, you need to check the Create Decryption View checkbox and provide the Database and Schema name while defining an encryption task. The Masker IDP will create a decryption view in the specified schema under the specified database. Using view, users that have access in, can view decrypted data and others would see encrypted data.

*Note: The Create Decryption View checkbox will appear only when you select connections established for: Redshift, SQL Server, and DB2 for I.

To create view in the specified schema, provide the following grant:

GRANT CREATEIN ON SCHEMA <VIEW_SCHMEA> TO <DSM_USER>;

The naming convention for view names will be:

  • For DB2 for I:
    <TABLE_NAME> suffixed by _OTFV. For example, if a table name is EMP, then the view name will be “EMP_OTFV”.

  • For SQL Server:
    <target_database>_<target_schema>_<target_table>_decryption_view

  • For Redshift:
    <target_schema>_<target_table>_decryption_view

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.