Re: SQL Encryption -- MIDRANGE-L

target of encryption should be CHAR ( nn) FOR BIT DATA


    Alexei
always speaking for myself only





"Mike Pantzopoulos - \(H/O\)" <mpantzopoulos@xxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
06/02/2005 05:57 PM
Please respond to
Midrange Systems Technical Discussion


To
<midrange-l@xxxxxxxxxxxx>
cc

Subject
SQL Encryption






I found an article in the May 2005 copy of iSeries News regarding the SQL 
ENCRYPT and DECRYPT functions ad they seem to be the answer to a privacy 
issue I'm trying to address.
I set up a small test scenario to get some hands-on experience but end up 
having trouble with the DECRYPT function. Here is the sequence of events:

*                I created a simple table with the increased field sizes 
as defined in the article  -

                  CREATE TABLE ANVMXP/ENCRYPT (CARD CHAR ( 28), PNUMBER 
DEC ( 15, 2), 
                  ZNUMBER NUMERIC ( 15, 2))  
                  Table ENCRYPT in ANVMXP created but could not be 
journaled. 
 
*                I inserted 3 records and encrypted the values placed into 
the CARD column (note that 'abcdef' is the passsword) -

                 INSERT INTO ANVMXP/ENCRYPT  
                 VALUES(encrypt('mickey mouse','abcdef'), 7654321, 
8765432) 
                 1 rows inserted in ENCRYPT in ANVMXP.  
                 INSERT INTO ANVMXP/ENCRYPT  
                 VALUES(encrypt('donald duck','abcdef'), 7654321, 8765432) 
 
                 1 rows inserted in ENCRYPT in ANVMXP.  
                 INSERT INTO ANVMXP/ENCRYPT  
                 VALUES(encrypt('pluto dog','abcdef'), 7654321, 8765432)   

                 1 rows inserted in ENCRYPT in ANVMXP.  

*                When I view the contents, I can see that the field CARD 
has been encrypted -

                 select * from anvmxp/encrypt 
                 SELECT statement run complete. 

                  CARD                                       PNUMBER   
ZNUMBER 
                  *ÃÃÅÅÅNx=L***fJÃc*ÃÂ**m0              7,654,321.00  
8,765,432.00 
                  *ÃÂÅÅÅNÂ{gju/*EÃÃLÃÂVÃÃ               
7,654,321.00  
8,765,432.00 
                  **UÅÅÅNsÅ**|o8Ã*****Ã*f~              7,654,321.00  
8,765,432.00 

*                When I try to view the CARD column in unencrypted form I 
get -

SELECT decrypt_char(card,'abcdef'), PNUMBER, ZNUMBER FROM 
anvmxp/encrypt 

 Argument 01 of function DECRYPT_CHAR not valid. 

This error translates to SQL0171, but I can't see anything that helps me 
there. 

Any one else tried this and succeeded? 


Thanks.
 

* 
Mike Pantzopoulos

EIG-Ansvar Limited
Tel  : 61 (3) 9614 3535
Fax: 61 (3) 9614 2740



*********************************************************************************************************
This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. 
If you are not the intended recipient, any use, disclosure or copying of 
this message is unauthorised. If you have received this message in error, 
please reply using the sender's email address. 

This footnote confirms that this email message has been scanned for 
computer viruses. EIG-Ansvar Limited does not accept liability for any 
loss or damage, whether caused by our own negligence or not, that results 
from a computer virus or defect in the transmission of this email or any 
attached file. 

EIG-Ansvar Limited - Australia (A.B.N. 21 007 216 506)
Email : insure@xxxxxxxxxxxxxxxx

Eig-Ansvar Limited - New Zealand
Email : insure@xxxxxxxxxxxxxxxx

*********************************************************************************************************