Creating views with column-level encryption

You can create a view that uses column-level encryption and selects decrypted data from a table.

About this task

You can define the view with a decryption function in the defining fullselect. If the correct password is provided when the view is queried, Db2 will return decrypted data. Suppose that you want to create a view that contains decrypted employee ID numbers from the EMP table.

Procedure

To create a view that uses column-level encryption and selects decrypted data:

  1. Create a view on the EMP table by using the following statement:

    Begin general-use programming interface information.

    CREATE VIEW CLR_EMP (EMPNO) AS SELECT DECRYPT_CHAR(EMPNO) FROM EMP;
    End general-use programming interface information.
  2. Set the encryption password so that the fullselect in the view definition can retrieve decrypted data.

    Use the following statement:

    Begin general-use programming interface information.
    SET ENCRYPTION PASSWORD = :hv_pass;
    End general-use programming interface information.
  3. Select data from the view by using the following statement:

    Begin general-use programming interface information.

    SELECT EMPNO FROM CLR_EMP;
    End general-use programming interface information.