#IBMiSample
This is a wery simple way to store passwords in a secure way in a DB2 SQL Server database.
First of all create a table with only two fileds, user (UTENTE), and password (PWD).
CREATE TABLE A01AWPWD (UTENTE varchar(128), PWD VARCHAR(124) FOR BIT DATA)
Establish an encryption key. My encryption key for this example is skjdjuiu%Tr&$*+[be%_tr=\?
Fill the table with users and passwords:
INSERT INTO A01AWPWD (UTENTE, PWD)
VALUES ('User01', ENCRYPT('Passw01',
'skjdjuiu%Tr&$*+[be%_tr=\?'))
INSERT INTO A01AWPWD (UTENTE, PWD)
VALUES ('User02', ENCRYPT('Passw02',
'skjdjuiu%Tr&$*+[be%_tr=\?'))
INSERT INTO A01AWPWD (UTENTE, PWD)
VALUES ('JohnS', ENCRYPT('ert%9ii',
'skjdjuiu%Tr&$*+[be%_tr=\?'))
Now we have a table A01AWPWD with ENCRYPTED passwords.
Try this:
SELECT PWD FROM A01AWPWD WHERE UTENTE = 'JohnS'
As you can see, the password is ENCRYPTED.
To read (DECRYPT) the passwords you need to know the encryption key:
SELECT DECRYPT_CHAR(PWD,'skjdjuiu%Tr&$*+[be%_tr=\?')
FROM A01AWPWD
WHERE UTENTE = 'JohnS'
Another way with SQL embedded:
**free
************************************************
* Encrypting/Decrypting data with SQL embedded *
* Encrypt/decript example with *
* ENCRYPT_AES *
* ENCRYPT_RC2 *
* ENCRYPT_TDES *
* *
* DECRYPT_BIT *
************************************************
ctl-opt
option(*nodebugio) dftactgrp(*no) actgrp(*new);
dcl-s encryptionPassword VARCHAR(128);
dcl-s p0 CHAR(10);
dcl-s p1 CHAR(10);
dcl-s p2 CHAR(10);
dcl-s p3 CHAR(10);
// The EXEC SQL is never executed. It is used at compile time.
exec sql Set Option Commit = *none;
// Create File
exec sql
create or replace table TABLE1
(F0TEXT VARCHAR(010),
F1_AES VARCHAR(128) FOR BIT DATA,
F2_RC2 VARCHAR(128) FOR BIT DATA,
F3TDES VARCHAR(128) FOR BIT DATA,
PRIMARY KEY(F0TEXT))
ON REPLACE DELETE ROWS ;
encryptionPassword = 'kljhasdflkjhasdf';
exec sql SET ENCRYPTION PASSWORD = :encryptionPassword ;
// Insert a row
EXEC SQL INSERT INTO TABLE1
VALUES ('aaaa', ENCRYPT_AES( '111111111') ,
ENCRYPT_RC2( '222222222') ,
ENCRYPT_TDES('333333333')) ;
// Insert another row
EXEC SQL INSERT INTO TABLE1
VALUES ('bbbb', ENCRYPT_AES( 'xxxxxxxxx') ,
ENCRYPT_RC2( 'yyyyyyyyy') ,
ENCRYPT_TDES('zzzzzzzzz')) ;
exec sql close C1;
exec sql open C1;
// Read recordset without decription
dow Sqlcode = 0;
exec sql fetch C1 into :p0, :p1, :p2, :p3;
if sqlcode = 0;
dsply p1;
dsply p2;
dsply p3;
endif;
enddo;
exec sql close C1;
exec sql close C2;
exec sql open C2;
// Read recordset with decription
dow Sqlcode = 0;
exec sql fetch C2 into :p0, :p1, :p2, :p3;
if sqlcode = 0;
dsply p1;
dsply p2;
dsply p3;
endif;
enddo;
exec sql close C2;
*inlr = *on;
// Read recordset without decription
exec sql declare C1 cursor for
SELECT F0TEXT,
F1_AES,
F2_RC2,
F3TDES
FROM TABLE1;
exec sql declare C2 cursor for
SELECT F0TEXT,
DECRYPT_BIT(F1_AES),
DECRYPT_BIT(F2_RC2),
DECRYPT_BIT(F3TDES)
FROM TABLE1;
That's it
I appreciate all the comments made on this blog.
Passwords should never be stored in a reversible encryption schema, but hashed in a one-way non-reversible direction !
ReplyDeleteIf your main encryption password leaks... all your passwords are on the street !
I just came here to say that. Thanks.
DeleteThank you, Paul.
DeleteIn a case when I was asked to store password in DB2 table first I created the MD5 checksum of the entered password, and then added this checksum to the fix part of the encryption key. Using this key I encrypted the password and stored in the table.
DeleteThe same process has been executed at checking the entered passwod: MD5 checksum -> key creation -> password encryption.
The encrypted password have been compared...
@Paul Nicolay
DeleteIf you need to logon to a remote server with user and password from an IBM i, how would you store the credentials?
If you have a better way, please share it.