Ciphertext data O&M and masking - ApsaraDB RDS - Alibaba Cloud Documentation Center

After data is encrypted, unauthorized database administrators (DBAs) can view only the ciphertext data and cannot perform O&M operations on the ciphertext data. The Always confidential database feature allows DBAs who are authorized by using a behavior control list (BCL) to perform O&M operations on ciphertext data. The feature also supports data masking, which ensures that DBAs cannot obtain the plaintext data during ciphertext data O&M.

Scenarios

Assume that you accidentally delete personal data in a database. After you submit a ticket, a DBA restores the accidentally deleted data by using data tracking, and verifies whether the data is successfully restored by performing data sampling. However, the DBA cannot determine whether the restored data is consistent with the original data because the restored data is encrypted.

If the DBA is unauthorized, the DBA fails to decrypt the encrypted data.
If the DBA is authorized by using a BCL, the DBA can decrypt the encrypted data to view the plaintext data after data masking. The DBA can verify data accuracy based on the plaintext data after data masking. This ensures the security of sensitive data.

Prerequisites

In this example, the person table that is created in the Privacy protection topic is used to perform O&M operations on ciphertext data and protect privacy.

Example on ciphertext data O&M and data masking

A DBA does not obtain the required permissions. As a result, the DBA cannot view the data, and the privacy data is protected.

Note

You must use the ins_data user to execute the following statement:

SELECT encdb.decrypt(id) FROM person;

Sample result:

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

The data department grants the DBA the permissions to view the data after data masking.

Edit a BCL to update the authorization scope (including groupid) of the required data encryption key (DEK) to the BCL.
```
./setGroupIdBCL.sh -d <groupid>
```
Note
You can execute the following statement to obtain the group ID:
SELECT encdb_get_cc_entry_by_name(encdb.keyname_generate('<user_name>', '<database_name>', '<schema_name>', '<table_name>', '<column_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_data_for_dba_select.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_data_for_dba_select.txt -c ${cipher_suite}

After the BCL is issued, the DBA can view the masked data and perform O&M operations.

SELECT encdb.decrypt(id) FROM person;
       decrypt
 --------------------
  11122*********9999
  11122*********8888
 (2 rows)

After the O&M operations are complete, the data department revokes the permissions from the DBA at the earliest opportunity.

# Issue the BCL to revoke the permissions
./genEncdbSQLCommand.sh -r BCL_REVOKE --puk sample/usr_puk_data.pem --pri sample/usr_pri_data.pem --brl sample/brl_data_for_dba_select.txt -c ${cipher_suite}

After the permissions are revoked, the DBA fails to execute the following statement to perform O&M operations.

Note

You must use the ins_data user to execute the following statements:

SELECT encdb.decrypt(id) FROM person;

Sample results:

NOTICE:  -- encdb -- -- Enclave log -- 4 - src/core/trusted/src/key_mgmt.cpp,576,encdb_trusted_import_bcl: BCL (aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa) was invalidated by BRL (1dec8190-3fd6-4de1-9ab7-7c1456933b28)
NOTICE:  -- encdb -- -- Enclave log -- 4 - src/core/trusted/src/key_mgmt.cpp,596,encdb_trusted_import_bcl: no BCL authorization
WARNING:  -- encdb -- -- Untrusted log -- 4 - src/core/untrusted/src/encdb_untrusted_enclave.cpp,250,encdb_ecall: Import BCL (subject_mekid: 178079820457738240, issuer_mekid: 178079820457738240) to enclave failed - returned 0xfa020000
ERROR:  encdb_ext_enc_text_decrypt: enc_text decrypt errno:fa020000