Privacy data protection - PolarDB - Alibaba Cloud Documentation Center

You can use an always-confidential database to protect privacy data and prevent privacy data leaks. This topic describes the best practices for privacy data protection by using an always-confidential database.

Scenarios

For example, an insurance company wants to protect privacy data that is stored in a policyholder information table named person.

name	phone	ID	debit_card	address
Xiaobao TAO	13900001111	111222190002309999	6225888888888888	No. 888, ABC Road, Hangzhou, Zhejiang
Sanduo DING	13900002222	111222190002308888	6225666666666666	No. 666, DEF Road, Hangzhou, Zhejiang

Note

The data in the preceding table is used for reference only.

The data department encrypts sensitive information about a policyholder for protection after the information is transmitted from the corresponding mobile device. Personnel in the sales department, users of involved applications, and database users can view only the ciphertext data. This prevents privacy data leaks.

The policyholder can use the mobile app of the insurance company to encrypt data and then write or update personal information.
The sales department of the insurance company can connect to the database to query data. The sales department can view only the ciphertext data and cannot view the plaintext data.

Prerequisites

You understand how to use an always-confidential database. For more information, see Overview.
The client SDK or Java Database Connectivity (JDBC) required to use an always-confidential database is downloaded and installed. For more information, see Use the always-confidential database feature from a client.
The EncDB extension is downloaded. The extension is used to import master encryption keys (MEKs), register certificates or public and private key pairs, and issue and revoke behavior control lists (BCLs).
The EncDB extension generates SQL statements that can be executed in a database based on the configured parameters. After you download and decompress the EncDB extension package, you can run the ./genEncdbSQLCommand.sh --help command to view more information.
Note
For more information about BCLs, see Grant access permissions on multi-user data.

Preparations

Note

In the following example, a CentOS operating system is used to run commands.

Configure a cipher suite.
1. Configure variables for the cipher suite.
```
cipher_suite=RSA_WITH_AES_128_CBC_SHA256
```
2. Generate a file template.
  Note
  In this example, the following command is run to generate configuration files in the sample directory based on the file template. The generated configuration files, such as key files and authorization files, are required for subsequent use.
```
./generateSampleKeyAndBCLs.sh -c ${cipher_suite}
```
Initialize the database.
1. Create a database.
```
CREATE DATABASE demo;
```
  Note
  In this example, a database named demo is created. You can create a database based on your business requirements. You must use the actual database name in subsequent commands.
2. Create the extension that is required by the always-confidential database feature in the created database.
```
CREATE EXTENSION encdb;
```
3. Obtain the public key certificate of the always-confidential database and add the certificate content to a local file.
```
SELECT encode(db_process_msg_api('{"request_type":0,"version":"1.2.8"}'),'escape')::json->'server_info'->'public_key'
```
  Note
  In this example, the local file named default_enclave_public_key.pem is used. You can change the file name based on your business requirements. You must use the actual file name in subsequent commands.
  If the content of the public key certificate starts and ends with double quotation marks (""), you do not need to add double quotation marks to the local file.
4. Convert the format of the public key certificate into a standard format.
  1. Run the following command to replace \\n with a line feed \n:
```
sed -i 's/\\\\n/\n/g' default_enclave_public_key.pem
```
  2. Run the following command to remove all empty lines:
```
sed -i '/^[ ]*$/d' default_enclave_public_key.pem
```

Examples

Data department of an insurance company

Prepare information about policyholders.

-------- Create a user. --------
-- Create a user for the data department, which is responsible for user data.
CREATE USER ins_data;
-- Create a user for the sales department.
CREATE USER ins_sale;

-------- Create a table. --------
-- Create a policyholder information table for the data department.
CREATE TABLE person (
    name enc_text,
    phone enc_text,
    ID enc_text,
    debit_card enc_text,
    address enc_text
);
-- Authorize the data department to access the policyholder information table.
GRANT ALL ON person TO ins_data;

Note

The EncDB extension automatically generates SQL statements based on the input parameters. You must execute the generated SQL statements in the database.

./genEncdbSQLCommand.sh -r MEK_PROVISION --mek sample/default_mek_data.bin -e default_enclave_public_key.pem -c ${cipher_suite}

# Execute the SELECT encdb_get_current_mek_id(); statement to obtain the MEK ID of the user that is created for the data department.
# In this example, the MEK ID 178079820457738240 is used.
mekid_data=178079820457738240 

./genEncdbSQLCommand.sh -r BCL_REGISTER --mekid ${mekid_data} --mek sample/default_mek_data.bin --puk sample/usr_puk_data.pem --pri sample/usr_pri_data.pem -c ${cipher_suite}

Create an encryption key for the policyholder information table.

Note

You must execute the following statements as the ins_data user.
encdb.dek_xxx() and encdb.keyname_xxx() in the following statements are data conversion functions. For more information, see Data conversion functions.

-- Create a data encryption key (DEK) and record the group ID, such as b6785611-0c49-4f13-87a9-13f151de9b4d.
SELECT encdb.dek_generate(encdb.keyname_generate('ins_data','demo','public','person','name'));

-- This is only an example. All columns share the same DEK.
SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','phone'),encdb.keyname_generate('ins_data','demo','public','person','name'));
SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','ID'),encdb.keyname_generate('ins_data','demo','public','person','name'));
SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','debit_card'),encdb.keyname_generate('ins_data','demo','public','person','name'));
SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','address'),encdb.keyname_generate('ins_data','demo','public','person','name'));

In most cases, database users cannot directly encrypt data by using SQL statements due to security reasons. Database users must be explicitly authorized to encrypt data by using SQL statements.

Configure a BCL to update the authorization scope (including the group ID) of the DEK.
```
./setGroupIdBCL.sh -d b6785611-0c49-4f13-87a9-13f151de9b4d
```
Note
The value b6785611-0c49-4f13-87a9-13f151de9b4d is used for reference only. To obtain the actual value, you can execute the following statement:
```
SELECT encdb.dek_generate(encdb.keyname_generate('ins_data','demo','public','person','name'));
```

Issue the BCL.

./genEncdbSQLCommand.sh -r BCL_ISSUE --subject_sign --spriv sample/usr_pri_data.pem --spuk sample/usr_puk_data.pem --ipuk sample/usr_puk_data.pem --bcl sample/bcl_for_data_insert.txt -c ${cipher_suite}
./genEncdbSQLCommand.sh -r BCL_ISSUE --issuer_sign --ipriv sample/usr_pri_data.pem --spuk sample/usr_puk_data.pem --ipuk sample/usr_puk_data.pem --bcl sample/bcl_for_data_insert.txt -c ${cipher_suite}

Write data.

Note

You must execute the following statements as the ins_data user.

INSERT INTO person VALUES(encdb.enc_text_encrypt ('Xiaobao TAO',encdb.keyname_generate ('ines_data','demo','public','person','name'))),
                          encdb.enc_text_encrypt('13900001111',encdb.keyname_generate('ins_data','demo','public','person','phone')),
                          encdb.enc_text_encrypt('111222190002309999',encdb.keyname_generate('ins_data','demo','public','person','ID')),
                          encdb.enc_text_encrypt('6225888888888888',encdb.keyname_generate('ins_data','demo','public','person','debit_card')),
                          encdb.enc_text_encrypt('No. 888, ABC Road, Hangzhou, Zhejiang',encdb.keyname_generate('ins_data','demo','public','person','address')));
INSERT INTO person VALUES(encdb.enc_text_encrypt ('Sanduo DING ',encdb.keyname_generate('ins_data','demo','public','person','name'))),
                          encdb.enc_text_encrypt('13900002222',encdb.keyname_generate('ins_data','demo','public','person','phone')),
                          encdb.enc_text_encrypt('111222190002308888',encdb.keyname_generate('ins_data','demo','public','person','ID')),
                          encdb.enc_text_encrypt('6225666666666666',encdb.keyname_generate('ins_data','demo','public','person','debit_card')),
                          encdb.enc_text_encrypt ('No. 666, DEF Road, Hangzhou, Zhejiang',encdb.keyname_generate ('ines_data','demo','public','person','address')));

Query data.

Query the policyholder information table. The query result is in ciphertext.

SELECT name,phone FROM person;
                                  name                                    |                                  phone                                   
--------------------------------------------------------------------------+--------------------------------------------------------------------------
 \xdf4901df087c6a3e0325175bb76942c684191a8dda2a8d0c35f295dc1e30eaeaa0c0e3 | \x315102ea5ab8a659066ab672e6dfbfd89a3a2b360bf6efe3787931e00f61af05f7408c 
 \xed4903dfd1bda1a89ad6aa7e8905c0e6305e15db4bc9ce2d2cfac9e25094d2a3ed367d | \xd75bb76942c682a8d0c35f295dc5ab8a659066ab672e6de00f61af0a1a89ad6aa7e890  
(2 rows)

Execute the following statement as the ins_data user. The query result is in plaintext.

SELECT encdb.decrypt(name) FROM person WHERE name LIKE encdb.enc_text_encrypt('TAO%',encdb.keyname_generate('ins_data','demo','public','person','name'));
 decrypt
---------
 Xiaobao TAO
(1 row)

Sales department of the insurance company

Note

The EncDB extension automatically generates SQL statements based on the input parameters. You must execute the generated SQL statements in the database.

./genEncdbSQLCommand.sh -r MEK_PROVISION --mek sample/default_mek_sale.bin -e default_enclave_public_key.pem -c ${cipher_suite}

# Execute the SELECT encdb_get_current_mek_id(); statement to obtain the MEK ID of the user that is created for the sales department.
# In this example, the MEK ID 2715553450389700608 is used.
mekid_sale=2715553450389700608

./genEncdbSQLCommand.sh -r BCL_REGISTER --mekid ${mekid_sale} --mek sample/default_mek_sale.bin --puk sample/usr_puk_sale.pem --pri sample/usr_pri_sale.pem -c ${cipher_suite}

Query data.

The sales department is not authorized by the data department to view policyholder information. This protects privacy data.

Query the policyholder information table. The query result is in ciphertext.

SELECT name,phone FROM person;
                                  name                                    |                                  phone                                   
--------------------------------------------------------------------------+--------------------------------------------------------------------------
 \xdf4901df087c6a3e0325175bb76942c684191a8dda2a8d0c35f295dc1e30eaeaa0c0e3 | \x315102ea5ab8a659066ab672e6dfbfd89a3a2b360bf6efe3787931e00f61af05f7408c 
 \xed4903dfd1bda1a89ad6aa7e8905c0e6305e15db4bc9ce2d2cfac9e25094d2a3ed367d | \xd75bb76942c682a8d0c35f295dc5ab8a659066ab672e6de00f61af0a1a89ad6aa7e890  
(2 rows)

Note

If the error message ERROR: permission denied for table person is displayed, execute the GRANT SELECT ON person TO ins_sale; statement to authorize the sales department to access the table.

Execute the following statement as the ins_sale user. An error message is displayed, indicating that the required permissions are not granted by using a BCL.

SELECT encdb.decrypt(name) FROM person;
 WARNING:  -- encdb -- -- Untrusted log -- 4 - src/core/untrusted/src/encdb_untrusted_enclave.cpp,256,encdb_ecall: Select BCL (subject_mekid: 2715553450389700608, issuer_mekid: 178079820457738240) from table fail - returned 0xfa030000
 ERROR:  encdb_ext_enc_text_decrypt: enc_text decrypt errno:fa030000