Privacy protection - ApsaraDB RDS - Alibaba Cloud Documentation Center

The Always confidential database feature can be used to protect privacy data and prevent privacy data leaks. This topic describes the best practices for privacy data protection by using the Always confidential database feature.

Scenarios

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

Name (name)	Phone number (phone)	ID card number (ID)	Bank card number (debit_card)	Address (address)
Xiaobao TAO	13900001111	111222190002309999	6225888888888888	No. 888, ABC Road, Hangzhou, Zhejiang
Sanduo DING	13900002222	111222190002308888	6225666666666666	No. 666, DEF Road, Hangzhou, Zhejiang

Note

Data in the preceding table is for reference only.

The sensitive information about a policyholder is encrypted by the data department for protection after the information is transmitted from the mobile device of the policyholder. Personnel in the sales department, users of involved applications, and users of databases can view only the ciphertext data. This prevents privacy data from being leaked.

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. However, the sales department can view only the ciphertext data but not the plaintext data.

Prerequisites

You understand how to use the Always confidential database feature. For more information, see Overview.
The client SDK or Java Database Connectivity (JDBC) that is provided for the Always confidential database feature is downloaded and installed. For more information, see Configure the Always confidential database feature on an Intel SGX-based security-enhanced ApsaraDB RDS for PostgreSQL instance.
The EncDB extension is downloaded. This 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 of an ApsaraDB RDS for PostgreSQL instance.

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, you can run the following command to generate configuration files to 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 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 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
```

Example of privacy protection

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;

Create a user for the data department.

Note

The EncDB extension automatically generates SQL statements based on the configured 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.
# The MEK ID 178079820457738240 is used as an example.
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

The following statements must be executed by using the ins_data user.
In the following SQL statements, encdb.dek_xxx() and encdb.keyname_xxx() are data conversion functions. For more information, see Data conversion functions.

-- Create a data encryption key (DEK) and record the value of groupid. Example value: 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.

Edit a BCL to update the authorization scope (including groupid) of the required DEK to the BCL.
```
./setGroupIdBCL.sh -d b6785611-0c49-4f13-87a9-13f151de9b4d
```
Note
The 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

Create a user for the sales department.

Note

The EncDB extension automatically generates SQL statements based on the configured 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.
# The MEK ID 2715553450389700608 is used as an example.
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: permission denied for table person error occurs, execute the GRANT SELECT ON person TO ins_sale; statement to authorize the sales department to access the table.

Use the ins_sale user to execute the following statement. After the execution, an error indicating that the BCL is not obtained is reported.

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