• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Using encryption for two columns on a DB2 database and PB17.

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Using encryption for two columns on a DB2 database and PB17.

  1. How-to
0
Votes
Undo
  1. Dana Willey
    Dana Willey
  2. PowerBuilder
  3. Wednesday, 4 March 2020 21:57 PM UTC

We have to encrypt two columns on one DB2.  We have set it up as a varbinary with a with of 32 characters.

Using an insert statement we added the column not problem;

INSERT INTO YCCFSSC5.BANK_ACCOUNT (TX_PRVD_ACCTN_NMBR)

     VALUES(ENCRYPT('586729745', 'CTKIND'));

Accessing using a select statement works also 

SELECT DECRYPT_CHAR(TX_PRVD_ACCTN_NMBR, 'CTKIND')

FROM YCCFSSC5.BANK_ACCOUNT;

How do we incorporate this into a DataWindow so we can retrieve, insert and update the columns

 

Thank you.

  1. Encryption
Call to SOAP web service from PB application can't...
How to implement Access Control List for menu obje...
Responses (4)
David Peace
David Peace Accepted Answer Pending Moderation
  1. Thursday, 5 March 2020 16:35 PM UTC
  2. PowerBuilder
  3. # 1

You will have to encrypt the values just before you update the DW and decrypt the values just after the DW retrieve.

That's probably the simplest way to do it.

Regards

David

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 5 March 2020 16:45 PM UTC
  2. PowerBuilder
  3. # 2

Hi,

I don't know DB2, but is it not possible to simply define the column or table as an encrypted one in the database?

That way you wouldn't have to do anything when inserting or retrieving the data.

regards,

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 6 March 2020 15:33 PM UTC
FYI: https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/seca/src/tpc/db2z_definecolencryption.html

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 6 March 2020 15:42 PM UTC
Thanks. So it seems that no .. you always have to call the functions. :(
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Thursday, 5 March 2020 20:01 PM UTC
  2. PowerBuilder
  3. # 3

Since you need DB2 code to encrypt/decrypt I would consider using stored procedures INSERT/UPDATE but allow SELECT statement to feed the DataWindow as usual.

Alternative, consider using stored procedures DW where each CRUD operation has its own stored procedure  call.

Comment
Dana Willey
  1. Dana Willey
  2. Thursday, 5 March 2020 20:31 PM UTC
Amazingly enough we are not allowed to use stored procedures. Thanks for your input.
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Thursday, 5 March 2020 20:44 PM UTC
Sorry!

Couple of months ago I was told: The security recommendation is "Prefer stored procedures over any direct access like SELECT/INSERT/UPDATE/DELETE." So I mentioned DataWindows using stored procedures for data access.

Another option for you is to capture the INSERT/UPDATE statements in SQLPreview and for each call consider whether you need to change the syntax to handle encrypted columns. Help on DataWindow's SQLPreview event describes return values.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 6 March 2020 15:43 PM UTC
  2. PowerBuilder
  3. # 4

Quote:

Amazingly enough we are not allowed to use stored procedures. Thanks for your input.

 

Are you allowed to use "Triggers" then? That would be another way of "automatically" doing the encryption.

regards

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.