Use the built-in ENCRYPT_AES() and ENCRYPT_TDES() encryption functions to encrypt data in columns containing the following character data types or smart large object data types:
After encrypting sensitive data, such as credit card numbers, only users who can provide a secret password can decrypt the data.
You can also use the SET ENCRYPTION PASSWORD statement to set an encryption password for a session. If you do this, only users who can provide a secret password can view, copy, or modify encrypted data.
The built-in ENCRYPT_AES(), ENCRYPT_TDES(), DECRYPT_CHAR(), and DECRYPT_BINARY() encryption and decryption functions can use the session-level password if the password is not explicitly specified in the encryption or decryption function. If you use the SET ENCRYPTION PASSWORD statement, you do not need to provide the same password in every encryption or decryption function.
When you set an encryption password for a session, you can also specify a password hint. If you specify a hint, you can store the hint with the encrypted password or in another location. The password must be a minimum of 6 bytes and can be a maximum of 128 bytes. The password used for decryption must match the password used for encryption.
When you set encryption passwords for column data, you can specify these types of encryption:
Passwords and hints that you declare with SET ENCRYPTION PASSWORD are not stored as plain text in any table of the system catalog. To prevent other users from accessing the plain text of encrypted data or of a password, you must avoid actions that might compromise the secrecy of a password:
When you set a password, Dynamic Server transfers the password and any hint to a 128-bit key that is used to encrypt the password and hint. Passwords and hints are not stored as clear text. The key is a time-based random value per instance. The database server starts the key when the server starts; the key is destroyed when the database server shuts down.
Dynamic Server includes an Encrypt Virtual Processor. If the encrypt option of the VPCLASS parameter is not defined in the ONCONFIG configuration file, the database server starts one Encrypt VP the first time that any encryption or decryption functions defined for column-level encryption are called. You can define multiple Encrypt VPs if necessary to decrease the time needed to start the database server. For more information, see Encrypt Virtual Processors and the configuration parameters chapter in the IBM Informix Dynamic Server Administrator's Reference.
When the database server is in online mode, you can use the onmode -p command to add or drop Encrypt VPs. For example, to add four more Encrypt VPs, use:
onmode -p 4 encrypt
To drop three Encrypt VPs, use:
onmode -p -3 encrypt
For more information, see the onmode utility chapter in the IBM Informix Dynamic Server Administrator's Reference.
An encrypted value uses more storage space than the corresponding plain text value. This occurs because all of the information needed to decrypt the value, except the encryption key, is stored with the value. Therefore, embedding zero bytes in the encrypted result is not recommended.
Before you set the encryption password and encrypt data, you must be sure the encrypted data can fit in the column.
To encrypt a column:
For examples of two methods for calculating the size of an encrypted column, see Example Showing How to Determine the Size of an Encrypted Column.
For an example of how to insert a password into your code and use the ENCRYPT function, see Example Showing How to Encrypt a Column.
Use the DECRYPT_BINARY(), and DECRYPT_CHAR() functions to query encrypted data. For an example of querying encrypted data, see Example Showing How to Query Encrypted Data.
See the IBM Informix Guide to SQL: Syntax for more informations about:
The following example shows how the size of a Credit Card column is calculated:
DATA SIZE 16 bytes
ENCRYPTED DATA SIZE = (DATA SIZE + blocksize8) / blocksize8 *
blocksize8 = 24 bytes (integer operation)
OR ENCRYPTED DATA SIZE = (DATA SIZE - DATA SIZE% blocksize8 +
blocksize8 ) = 24 bytes
(For ENCRYPT_TDES, round up to (N + 1) * 8 bytes, for example
13 bytes round up to 16 bytes, 16 bytes to 24 bytes)
HEADER SIZE = 11 bytes (for Base64 encoding)
IV SIZE = 8 bytes (fixed size)
HINT SIZE = 32 bytes (maximum size)
ENCRYPED HINT SIZE = 40 bytes (maximum size)
BASE64 SIZE = ((INPUT DATA SIZE + 2) / 3) * 4
(integer operation)
OR BASE64 SIZE = ((INPUT DATA SIZE + 2) -
(INPUT DATA SIZE + 2) % 3) / 3 * 4
TOTAL SIZE = HEADER SIZE
+ BASE64(IV SIZE + ENCRYPTED DATA SIZE + ENCRYPTED HINT)
= 11 + BASE64(8 + 24 + 40)
= 11 + (72 + 2) / 3 * 4
= 11 + 96 = 107
In the previous example, Initialization Vector (IV) is a pseudo-random series of bytes that is used to initiate encryption when using some cipher modes. IV size is the number of random series of bytes; for Dynamic Server, this is 8 bytes.
If the hint is not stored in the column, the total size in the previous example is 55 bytes.
SELECT LENGTH(ENCRYPT_TDES
("1234567890123456",
"password", "long....hint"))
FROM "informix".systables WHERE tabid = 1Without the hint, you can calculate as follows:
SELECT LENGTH(ENCRYPT_TDES("1234567890123456",
"password", ""))
FROM "informix".systables WHERE tabid = 1
The following example shows how to use the encryption password in a column that contains a social security number:
create table emp
( name char(40),
salary money,
ssn lvarchar(67)
);
set encryption password "one two three 123";
insert into emp values ("Alice", 50000, encrypt_aes
('123-456-7890'));
insert into emp values ("Bob", 65000, encrypt_aes
('213-656-0890'));
select name, salary, decrypt_char(ssn, "one two three 123")
from emp where name = 'Bob';
The following example shows how to use the decrypt function to query encrypted data:
select name, decrypt_char(ssn, "one two three 123") from emp;
or
set encryption password "one two three 123";
select name, salary, decrypt_char(ssn) from emp where name = 'Bob';
Home |
[ Top of Page | Previous Page | Next Page | Contents |
Index ]