Data value Encryption in DB2

"Guide" This article describes how to easily integrate data encryption into a database application using the new functions provided in IBM DB2 Universal Database Version (Unix & Windows).

Over the years, the database has been able to prevent unauthorized people from seeing the data, which is usually done through privileges and permissions in the database manager. In the current environment, the need for confidentiality of stored data is growing. This means that even if the DBA has full access to the data in the table, there may be some information in the table that the data owner does not want anyone else to see. This is especially the case for web-based applications in which data entered by a user, such as a credit card number, needs to be saved for the same user to use the application later. At the same time, user owners want to be able to ensure that no one else has access to this data. To implement this functionality, DB2 has built-in SQL functions that allow applications to encrypt and decrypt data. When you insert data into a database, you can encrypt it by using a user-supplied cryptographic password. When retrieving this data, you must provide the same password to decrypt the data. For situations where you want to use the same password more than once, you can use an assignment statement to set the encryption PASSWORD value and make it valid during a connection.

This article describes these SQL functions and gives some examples of how to use these cryptographic functions. We will also discuss the design and performance-related aspects of using encrypted data in a relational database.

Realize

The signatures of these new SQL functions are shown below. More detailed documentation is available in the SQL Reference section of the DB2 document. (To ensure that you use the correct data type and length for the encrypted data, be sure to 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 the data is a RC2 grouping cipher (block cipher) with a 128-bit key. This 128-bit key is derived from the password from the message digest. Encrypted passwords are independent of DB2 authentication and are used only for encryption and decryption of data.

Here you can provide an optional parameter passwordhint, which is a string that can help the user to remember the passwordorphrase used to encrypt the data. (For example, you can use ' George ' as a reminder to remember ' Washington '.) ）

Column-Level encryption

Column-level encryption (encryption) means that all values in a given column are encrypted using the same password. This type of encryption can be used in a view or in the case of a public password. Encryption PASSWORD Private registers are useful when you use the same key for all rows in one or more tables.

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

CREATE TABLE EMP (SSN varchar (124) for bit data);

Set Encryption password = ' Ben123 ';

INSERT INTO EMP (SSN) VALUES (Encrypt (' 289-46-8832 '));

INSERT INTO EMP (SSN) VALUES (Encrypt (' 222-46-1904 '));

INSERT INTO EMP (SSN) VALUES (Encrypt (' 765-23-3221 '));

Select Decrypt_char (SSN) from EMP;