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.

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:

   

   CREATE VIEW CLR_EMP (EMPNO) AS SELECT DECRYPT_CHAR(EMPNO) FROM EMP;

   
2. Set the encryption password so that the fullselect in the view definition can retrieve decrypted data.

   Use the following statement:

   

   SET ENCRYPTION PASSWORD = :hv_pass;

   
3. Select data from the view by using the following statement:

   

   SELECT EMPNO FROM CLR_EMP;