Using Data Encryption Functions (IDS)

Home | Previous Page | Next Page Using Functions in SELECT Statements >

Using Data Encryption Functions (IDS)

You can use the SET ENCRYPTION PASSWORD statement with built-in SQL encryption functions that use Advanced Encryption Standard (AES) and Triple DES (3DES) encryption to secure your sensitive data. When you use encryption, only those users who have the correct password will be able to read, copy, or modify the data.

Use the SET ENCRYPTION PASSWORD statement with the following built-in encryption and decryption functions:

ENCRYPT_AES

ENCRYPT_AES(data-string-expression
[, password-string-expression [, hint-string-expression ]])

ENCRYPT_TDES

ENCRYPT_TDES (data-string-expression
 [, password-string-expression [, hint-string-expression ]])

DECRYPT_CHAR

DECRYPT_CHAR(EncryptedData [, PasswordOrPhrase])

DECRYPT_BINARY

DECRYPT_BINARY(EncryptedData [, PasswordOrPhrase])

GETHINT
```
GETHINT(EncryptedData)
```

Use ENCRYPT_AES and ENCRYPT_TDES to define encrypted data and use DECRYPT_CHAR and DECRYPT_BINARY to query encrypted data. Use GETHINT to display the password hint string, if set, on the server.

You can use these SQL built-in functions to implement column-level or cell-level encryption.

Use column-level encryption to encrypt all values in a given column with the same password.
Use cell-level encryption to encrypt data within the column with different passwords.

The following example uses column-level encryption to secure credit card data.

To use column-level data encryption to secure credit card data

Create the table.

create table customer (id char(30), creditcard lvarchar(67));

Insert the encryption data.

Set session password.

    SET ENCRYPTION PASSWORD "credit card number is encrypted";

Encrypt data.

    INSERT INTO customer VALUES
("Alice",  encrypt_aes("1234567890123456"));
    INSERT INTO customer VALUES
("Bob", encrypt_aes("2345678901234567"));

Query encryption data with decryption function:

    SET ENCRYPTION PASSWORD "credit card number is encrypted";
    SELECT id FROM customer 
    WHERE DECRYPT_CHAR(creditcard) = "2345678901234567"

For more information on encryption security, see IBM Informix: Administrator's Guide.

For more information on the syntax of built-in encryption and decryption functions, see IBM Informix: Guide to SQL Syntax.