If you want to encrypt only portions of your database, you can do so with the ENCRYPT function. The ENCRYPT function uses the same AES strong encryption algorithm that is used for database encryption to encrypt values that are passed to it.
The key for the ENCRYPT function is case sensitive, even in case-insensitive databases. As with most passwords, it is best to choose a key value that cannot be easily guessed. It is recommended that you choose a value for your key that is at least 16 characters long, contains a mix of upper and lowercase, and includes numbers, letters and special characters. You will require this key each time you want to decrypt the data.
Protect your key. Be sure to store a copy of your key in a safe location. A lost key will result in the encrypted data becoming completely inaccessible, from which there is no recovery.
Encrypted values can be decrypted with the DECRYPT function. You must use the same key that was specified in the ENCRYPT function. Both of these functions return LONG BINARY values. If you require a different data type, you can use the CAST function to convert the value to the required data type. The example below shows how to use the CAST function to convert a decrypted value to the required data type. See CAST function [Data type conversion].
If database users need to access the data in decrypted form, but you do not want them to have access to the encryption key, you can create a view that uses the DECRYPT function. This allows users to access the decrypted data without knowing the encryption key. If you create a view or stored procedure that uses the table, you can use the SET HIDDEN parameter of the ALTER VIEW and ALTER PROCEDURE statements to ensure that users cannot access the encryption key by looking at the view or procedure definition. See ALTER PROCEDURE statement and ALTER VIEW statement.
The following example uses triggers to encrypt a column that stores passwords in a table called user_info. The user_info table is defined as follows:
CREATE TABLE user_info ( employee_ID INTEGER NOT NULL PRIMARY KEY, user_name CHAR(80), user_pwd CHAR(80) );
Two triggers are added to the database to encrypt the value in the user_pwd column, either when a new user is added or an existing user's password is updated.
The encrypt_new_user_pwd trigger fires each time a new row is added to the user_info_table:
CREATE TRIGGER encrypt_new_user_pwd BEFORE INSERT ON user_info REFERENCING NEW AS new_pwd FOR EACH ROW BEGIN SET new_pwd.user_pwd=ENCRYPT(new_pwd.user_pwd, '8U3dkA'); END;
The encrypt_updated_pwd trigger fires each time the user_pwd column is updated in the user_info table:
CREATE TRIGGER encrypt_updated_pwd BEFORE UPDATE OF user_pwd ON user_info REFERENCING NEW AS new_pwd FOR EACH ROW BEGIN SET new_pwd.user_pwd=ENCRYPT(new_pwd.user_pwd, '8U3dkA'); END;
Add a new user to the database:
INSERT INTO user_info VALUES ( '1', 'd_williamson', 'abc123');
If you issue a SELECT statement to view the information in the user_info table, the value in the user_pwd column is binary data (the encrypted form of the password) and not the value abc123 that was specified in the INSERT statement.
If this user's password is changed:
UPDATE user_info SET user_pwd='xyz' WHERE employee_ID='1';
the encrypt_updated_pwd trigger fires and the encrypted form of the new password appears in the user_pwd column.
The original password can be retrieved by issuing the following SQL statement. This statement uses the DECRYPT function and the encryption key to decrypt the data, as well as the CAST function to convert the value from a LONG BINARY to a CHAR value:
SELECT CAST (DECRYPT(user-pwd, '8U3dkA') AS CHAR(100)) FROM user_info WHERE employee_ID = '1';