Equality Query in a Fully-encrypted Database

Availability

This feature is available since V500R001C20.

Introduction

The encrypted database aims to protect privacy throughout the data lifecycle. In this way, data is always in ciphertext during transmission, computing, and storage regardless of the service scenario and environment. After the data owner encrypts data on the client and sends the encrypted data to the server, the attacker cannot obtain valuable information even if the attacker steals user data by exploiting system vulnerabilities. In this way, data privacy is protected.

Benefits

The entire service data flow is in ciphertext during data processing, so the following can be implemented by using a fully-encrypted database:

1. Protect data privacy and security throughout the lifecycle on the cloud. Attackers cannot obtain valid information from the database server regardless of the data status.
2. Help cloud service providers obtain third-party trust. Users, including service administrators and O&M administrators in enterprise service scenarios and application developers in consumer cloud services, can keep keys by themselves so that users with high permissions cannot obtain valid data.
3. Enable cloud databases to better comply with personal privacy protection laws and regulations with the help of the fully-encrypted database.

Description

From the perspective of users, the encrypted equality query functions are divided into three parts, which are implemented by the newly added KeyTool and the enhanced openGauss gsql client.

First, this feature provides the client key management function. Users can use KeyTool to generate, destroy, and update CMKs, and import and export keys. With the import and export functions of KeyTool, CMKs can be transmitted between different clients. In addition, the KeyTool implements key management on a single client. By configuring management files, you can store and update keys.

In addition, this feature provides the key creation and encrypted table creation functions. The SQL syntax CREATE CLINET MASTER KEY and CREATE COLUMN ENCRYPTION KEY are added to record and manage CMK and CEK metadata in the database. The CMK and CEK information is recorded in the new system catalog. The CREATE TABLE syntax is extended to specify a column encryption key and encryption algorithm for each sensitive information column in a table, facilitating subsequent ciphertext data storage.

This feature supports the encrypted equality query function, which is the core of the entire feature. Although users are unaware of the ciphertext query, the query of sensitive data is restricted by the specifications of the current encrypted equality query.

From the overall perspective, this feature is used to store and manage data based on sensitive data protection requirements and implement query tasks based on ciphertext data.

Enhancements

None

Constraints

• Data is encrypted at the column level, and encryption policies cannot be differentiated by row level.

• Except the RENAME operation, the ALTER TABLE syntax cannot be used to change columns in an encrypted table (including the conversion between encrypted and unencrypted columns). The ADD and DROP operations can be used to add and delete encrypted columns, respectively.

• The CHECK(COLUMN IS NOT NULL) syntax can be used, but most check syntax cannot be set for encrypted columns.

• When **support_extended_features **is set to off, primary key and unique cannot be used for encrypted columns. When **support_extended_features **is set to on, only primary key and unique can be used for encrypted columns.

• Different data types cannot be implicitly converted.

• The set operation cannot be performed between ciphertexts of different data types.

• Range partitioning cannot be created for encrypted columns.

• Only the repeat and empty_blob() functions can be used to encrypt columns.

• The current version supports only the gsql client. Other clients such as JDBC and ODBC are not supported.

• Data can only be imported to the encrypted table by running copy from stdin, \copy, or insert into values (…) on the client.

• The system does not support encrypted queries, such as sorting, range query, and fuzzy query, except equality query.

• The PROCEDURE STATEMENT syntax cannot be used for stored procedures.

• Non-encrypted table data cannot be inserted into encrypted table data using the INSERT INTO… SELECT… syntax.

• An error is reported if the two attribute conditions used for comparison in the query operation use different data encryption keys.

• Encrypted equality query supports the following data types:

  Category	Type	Description
  Integer types	tinyint/tinyint(n)	Tiny integer, which is the same as int1.
  	smallint	Small integer, which is the same as int2.
  	int4	Common integer.
  	binary_integer	Oracle compatibility type. Generally, the value is an integer.
  	bigint/bigint(n)	Big integer, which is the same as int8.
  Numeric data types	numeric(p,s)	A number with the precision p.
  	number	Oracle compatibility type, which is the same as numeric(p,s).
  Floating point types	float4	Single-precision floating point.
  	float8	Double-precision floating point.
  	double precision	Double-precision floating point.
  Character data types	char/char(n)	Fixed-length character string. If the length is insufficient, add spaces. The default precision is 1.
  	varchar(n)	Variable-length character string, where n indicates the maximum number of bytes.
  	text	Text type.
  	varchar2(n)	Oracle compatibility type, which is the same as varchar(n).
  	clob	Character large object.
  Binary data types	bytea	Variable-length binary string.
  	blob	Binary large object.

Dependencies

None