Data Value encryption in DB2

Comments: Over the years, the database has been able to prevent unauthorized people from seeing the data, which is usually achieved through the privileges and permissions in the Database Manager. In the current environment, the demand for data storage confidentiality is growing. This means that even if DBA has full access permissions to the data in the table, there may be some messages in the table that the data owner does not want others to see for many years, the database has been able to prevent unauthorized people from seeing the data, which is usually achieved through the privileges and permissions in the Database Manager. In the current environment, the demand for data storage confidentiality is growing. This means that even if the DBA has full access permissions to the data in the table, there may be some information that the data owner does not want others to see in the table. Especially for Web-based applications, this issue is even more obvious. In such applications, user input data (such as credit card numbers) must be saved, this application can be used by the same user later. In addition, the user owner wants to ensure that no one else can access the data. To implement this function, DB2 has built-in SQL functions that allow applications to encrypt and decrypt data. When you insert data into a database, you can use the encrypted password provided by the user to encrypt the data. When retrieving the data, you must provide the same password to decrypt the data. If you want to use the same PASSWORD multiple times, you can use a value assignment statement to set the encryption password value and make it valid during a connection period.

This article describes these SQL functions and provides examples of how to use these encryption functions. We will also discuss the design and performance of using encrypted data in relational databases.

Implementation
The signature of these new SQL functions is shown below. The SQL Reference section of the DB2 document contains more detailed documents. (To ensure that the encrypted data is of the correct data type and length, read the "Table Column Definition" section under the ENCRYPT function in SQL Reference .)

Encrypt (StringDataToEncrypt, PasswordOrPhrase, PasswordHint)
Decrypt_Char (EncryptedData, PasswordOrPhrase)
GetHint (EncryptedData)
Set Encryption Password

The algorithm used to encrypt data is an RC2 block cipher with a 128-bit key. The 128-bit key is obtained from the password through the message digest. The encrypted password is not related to DB2 Authentication and is only used for data encryption and decryption.

Here we can provide an optional parameter PasswordHint, which is a string that helps you remember the PasswordOrPhrase used for data encryption. (For example, you can use 'George 'as a reminder to remember 'Washington .)

Column-level encryption
Column-level encryption means that all values in a given column are encrypted using the same password. This type of encryption can be used in the view or with a public password. Encryption password is useful when the same key is used for all rows in one or more tables.

Example 1: Use the encryption password value to save the encrypted PASSWORD. It encrypts the employee's social security number and stores it in the EMP table in encrypted form.

Create table emp (ssn varchar (124) for bit data );
Set encryption password = 'ben123 ';
Insert into emp (ssn) values (encrypt ('2017-46-8832 '));
Insert into emp (ssn) values (encrypt ('2017-46-1904 '));
Insert into emp (ssn) values (encrypt ('2017-23-3221 '));
Select decrypt_char (ssn) from emp;

Example 2: Use the encryption password value in combination with the view to save the encrypted PASSWORD. The following statement declares a view of the emp table:

Create view clear_ssn (ssn) as select decrypt_char (ssn) from emp;
In the application code, we set the encryption password to 'ben123'. Now we can use the clear_ssn view.

Set encryption password = 'ben123 ';
Select ssn from clear_ssn;

Row-column (cell) or set-column-level encryption
Row-Column (cell) or Set-Column (Set-Column) encryption means that multiple passwords are used in an encrypted data Column. For example, a website may need to save the customer's credit card number (ccn ). In this database, each customer can use his or her own password or phrase to encrypt ccn.

Example 3: Web applications collect user information about customers. This information includes the customer name (stored in the host variable custname), credit card number (stored in the host variable cardnum), and password (stored in the host variable userpswd ). The application performs the insertion of customer information as follows.

Insert into customer (ccn, name) values (encrypt (: cardnum,: userpswd),: custname)
When the application needs to re-display a customer's credit card information, the customer needs to enter a password, which is also stored in the host variable userpswd. Then, you can retrieve the ccn as follows:

Select decrypt_char (ccn,: userpswd) from customer where name =: custname;
Example 4: Use a prompt to help customers remember their passwords. Here we use the same application as Example 3, which will prompt to save it to the host variable pswdhint. Assume that userpswd is 'chamonix 'and pswdhint is 'ski Holiday '.

Insert into customer (ccn, name)
Values (encrypt (: cardnum,: userpswd,: pswdhint),: custname)
If you request a prompt about the password, you can use the following query.

Select gethint (ccn) into: pswdhint from customer where name =: custname;
The value of pswdhint is set to "Ski Holiday ".

Encrypt non-character values
Data Encryption of numeric and date/time types is indirectly supported by forced type conversion. Non-character SQL types can be encrypted by forcibly converting to "varchar" or "char. For more information about forced type conversion, see the "Casting Between Data Types" section in the SQL reference document.

Example 5: the forced type conversion function used to encrypt and decrypt TIMESTAMP data.

-- Create a table to store our encrypted value
Create table etemp (c1 varchar (124) for bit data );
Set encryption password 'Next password ';
-- Store encrypted timestamp
Insert into etemp values encrypt (char (current timestamp ));
-- Select & decrypt timestamp
Select timestamp (decrypt_char (c1) from etemp;
Example 6: encrypt/decrypt double data.

Set encryption password 'Next password ';
Insert into etemp values encrypt (char (1.11111002E5 ));
Select double (decrypt_char (c1) from etemp;
Performance
Encryption, in essence, slows down most SQL statements. However, if you pay more attention and make more judgments, You can minimize the overhead. In addition, data encryption has a great impact on database design. Generally, you need to encrypt some sensitive data elements in a model, such as the social insurance number, credit card number, patient name, and so on. Some data values are not so suitable for encryption-such as boolean values (true and false), or other small collections such as integers 1 to 10. These values are easily guessed together with the column name, so you need to determine whether encryption is really useful.

In some cases, creating an index on encrypted data is a good idea. The correct matching and connection of encrypted data will use the index you created. Because the encrypted data is essentially binary data, you need to scan the table when performing a range check on the encrypted data. Range check needs to decrypt the values of a column in all rows. Therefore, you should avoid range check and at least perform proper optimization.

The following scenario illustrates our discussion. A common master-detail mode can be used by programmers in many projects. We will implement column-level encryption for Employee Social Security numbers (ssn. In the master table emp and slave table empProject, ssn is stored in encrypted form.

-- Define Tables and Indexes for encrypted data
Create table emp (ssn varchar (48) for bit data,
Name varchar (48 ));
Create unique index idxEmp on emp (ssn) includes (name );
Create table empProject (ssn varchar (48) for bit data,
ProjectName varchar (48 ));
Create index idxEmpPrj on empProject (ssn );
-- Add some data
Set encryption password = 'ssnpassword ';
Insert into emp values (encrypt ('2017-93-7558 '), 'Super Programmer ');
Insert into emp values (encrypt ('2017-23-2678 '), 'novice Programmer ');
Insert into empProject values (encrypt ('2017-93-7558 '), 'uddi Project ');
Insert into empProject values (encrypt ('2017-23-2678 '), 'uddi Project ');
Insert into empProject values (encrypt ('2017-93-7558 '), 'db2 UDB Version 10 ');
-- Find the programmers working on UDDI select a. name, decrypt_char (a. ssn)
From emp a, empProject B
Where
A. ssn = B. ssn
And B. project = 'uddi project ';
-- Build a list of the projects that the programmer with ssn
-- '1970-93-7558 'is working on
Select projectName
From empProject
Where ssn = encrypt ('2017-93-7558 ');

Compared with the above example, the following two examples are the opposite typical examples that should not be used. Although these queries can return the correct answers, they need to decrypt the ssn for all rows. When the table is large, this problem becomes prominent.

Select a. name, decrypt_char (a. ssn)
From emp a, empProject B
Where
Decrypt_char (a. ssn) = decrypt_char (B. ssn)
And B. project = 'uddi project ';

This query requires that each row of the emp table and each 'uddi project' row of the empProject table be decrypted to execute the connection.

Select projectName
From empProject
Where decrypt_char (ssn) = '2017-93-7558 ';

This query requires that each row in the empProject table be decrypted.

Conclusion
In this article, we demonstrate how the encryption functions in IBM DB2 Universal Database provide a simple way to encrypt sensitive data. These functions can be used to implement column-level and row-column-level encryption. During the design and implementation, developers should examine important performance-related issues. Data Encryption adds a new available tool for hiding private data, which can be used for confidentiality even for administrators.