Someone asked me the following question:

"How do I keep passwords in the database so nobody can get them?"

It means that we cannot keep the the passwords in plain text in the database. Informix has a few functions that can be used for encryption: ENCRYPT_AES and ENCRYPT_TDS. It would be easy to create a table and encrypt the column that contains the passwords.

The next statement that came up was: "..but, if someone has the encryption password, he can get all the passwords. We need to protect the passwords from internal access".

This means that we need to use a different password to protect each password in the table. The solution I proposed was to use the password to encrypt itself. Let's look at an example:

CREATE TABLE passwd (
  col1 int,
  col2 varchar(50)
);
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Jacques", "Jacques"));
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Lance", "Lance0"));
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Daniel", "Daniel"));
INSERT INTO TABLE passwd VALUES(1, ENCRYPT_AES("Umut", "Umut01"));

The values inserted look as follow:

SELECT * FROM passwd
  col1  col2
     1  0hHv/AAAAEA/WSA...
     2  0hXr/AAAAEAxvWo...
     3  0c4z/AAAAEA7prn...
     4  0Bfr/AAAAEa4NXw...

I can now test f someone has the right password for user 1 by using the password value to decrypt itself:

SELECT col1, DECRYPT_CHAR(col2, "Jacques") FROM passwd WHERE col1 = 1;
  col1  (expression)
     1  Jacques

If I use the improper password, I receive an error:

SELECT col1, DECRYPT_CHAR(col2, "Jacques") FROM passwd WHERE col1 = 3;

26008: The internal decryption function failed

One more thing. Note that the encryption password must be at least six-character long. This is why in the example I padded some encryption passwords. An easy way to work around it would be to always add padding to make sure we meet that minimum size. Keep in mind that the maximum size of an encryption key is 128 bytes.

With this approach, we can keep passwords in the database and keep them secret.