ApsaraDB RDS:Multi-party data integration and computing

Scenarios

The insurance company wants to use the data that is collected by the data platform company to develop potential users. For example, the insurance company wants to push vehicle insurance to car owners.

Prerequisites

In this topic, the insurance company that is described in the "Privacy protection" topic is used to illustrate multi-party data integration and computing. You must complete the configurations based on the instructions provided in Privacy protection.

Examples

Data platform company

1. Create a user and a table for a database as the database administrator (DBA).

   -------- Create a user. --------
   -- Data platform company
   CREATE USER ly;
   -- Create a user portrait table for the data platform company.
   CREATE TABLE portrait (
       phone enc_text,
       age enc_int4,
       have_car enc_text,
       annual_consume enc_int8
   );
   -- Authorize the data platform company to access data tables.
   GRANT ALL ON portrait TO ly;

2. Register an account with the data platform company.

   Note

   The EncDB tool 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_ly.bin -e default_enclave_public_key.pem -c ${cipher_suite}
   
   # Obtain the ID of the master encryption key (MEK) of the data platform company. You can execute the SELECT encdb_get_current_mek_id(); statement to obtain the ID.
   # In this example, 6953973016013340672 is used.
   mekid_ly = 6953973016013340672
   ./genEncdbSQLCommand.sh -r BCL_REGISTER --mekid ${mekid_ly} --mek sample/default_mek_ly.bin --puk sample/usr_puk_ly.pem --pri sample/usr_pri_ly.pem -c ${cipher_suite}

3. The data platform company creates an encryption key for the user portrait table.

   Note

   • You must execute the following statements as the ly user.

   • The encdb.dek_xxx() and encdb.keyname_xxx() functions in the following statements are used to convert data types. For more information, see Perform conversions between plaintext and ciphertext.

   -- Create a data encryption key (DEK) and record the group ID, such as fd89d386-ee00-4e0e-9e5f-66efb4c124aa.
   SELECT encdb.dek_generate(encdb.keyname_generate('ly','demo','public','portrait','phone'));
   
   -- This is only an example. All columns share the same DEK.
   SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ly','demo','public','portrait','age'),encdb.keyname_generate('ly','demo','public','portrait','phone'));
   SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ly','demo','public','portrait','have_car'),encdb.keyname_generate('ly','demo','public','portrait','phone'));
   SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ly','demo','public','portrait','annual_consume'),encdb.keyname_generate('ly','demo','public','portrait','phone'));

   In most cases, you cannot execute SQL statements to encrypt data. This helps ensure security. You must be authorized to encrypt data.

   1. Edit the BCL to update the authorization scope (including the group ID) of the DEK to the BCL.

      ./setGroupIdBCL.sh -l fd89d386-ee00-4e0e-9e5f-66efb4c124aa

      Note

      fd89d386-ee00-4e0e-9e5f-66efb4c124aa is used for reference only. To obtain the actual value, you can execute the following statement:

      SELECT encdb.dek_generate(encdb.keyname_generate('ly','demo','public','portrait','phone'));

   2. Issue the BCL.

      ./genEncdbSQLCommand.sh -r BCL_ISSUE --subject_sign --spriv sample/usr_pri_ly.pem --spuk sample/usr_puk_ly.pem --ipuk sample/usr_puk_ly.pem --bcl sample/bcl_for_ly_insert.txt -c ${cipher_suite}
      ./genEncdbSQLCommand.sh -r BCL_ISSUE --issuer_sign --ipriv sample/usr_pri_ly.pem --spuk sample/usr_puk_ly.pem --ipuk sample/usr_puk_ly.pem --bcl sample/bcl_for_ly_insert.txt -c ${cipher_suite}

4. The data platform company writes data.

   Note

   You must execute the following statements as the ly user.

   INSERT INTO portrait VALUES(encdb.enc_text_encrypt('13900001111',encdb.keyname_generate('ly','demo','public','portrait','phone')),
                               encdb.enc_int4_encrypt('29',encdb.keyname_generate('ly','demo','public','portrait','age')),
                               encdb.enc_text_encrypt('N',encdb.keyname_generate('ly','demo','public','portrait','have_car')),
                               encdb.enc_int8_encrypt('2',encdb.keyname_generate('ly','demo','public','portrait','annual_consume')));
   INSERT INTO portrait VALUES(encdb.enc_text_encrypt('13900002222',encdb.keyname_generate('ly','demo','public','portrait','phone')),
                               encdb.enc_int4_encrypt('34',encdb.keyname_generate('ly','demo','public','portrait','age')),
                               encdb.enc_text_encrypt('Y',encdb.keyname_generate('ly','demo','public','portrait','have_car')),
                               encdb.enc_int8_encrypt('1',encdb.keyname_generate('ly','demo','public','portrait','annual_consume')));

Insurance company

SELECT * FROM person ps JOIN portrait pt ON ps.phone = pt.phone WHERE hava_car = 'Y';

1. Grant encryption permissions to the sales department of the insurance company.

   1. Edit the BCL to update the authorization scope (including the group ID) of the DEK to the BCL.

      ./setGroupIdBCL.sh -s 7903d109-f3e0-4f3e-b815-3682cb8bd6db

      Note

      7903d109-f3e0-4f3e-b815-3682cb8bd6db is used for reference only. The sales department uses the default key to encrypt data. To obtain the group ID, you can execute the following statement:

      Obtain the MEK ID of the sales department account, such as 2715553450389700608.
      SELECT encdb_get_current_mek_id(); 
      
      # Obtain the group ID.
      SELECT groupid FROM encdb.encdb_internal_dek_table WHERE mekid = 2715553450389700608;

   2. Issue the BCL.

   ./genEncdbSQLCommand.sh -r BCL_ISSUE --subject_sign --spriv sample/usr_pri_sale.pem --spuk sample/usr_puk_sale.pem --ipuk sample/usr_puk_sale.pem --bcl sample/bcl_for_sale_insert.txt -c ${cipher_suite}
   ./genEncdbSQLCommand.sh -r BCL_ISSUE --issuer_sign --ipriv sample/usr_pri_sale.pem --spuk sample/usr_puk_sale.pem --ipuk sample/usr_puk_sale.pem --bcl sample/bcl_for_sale_insert.txt -c ${cipher_suite}

2. The sales department of the insurance company queries data.

   • The sales department is not authorized by the data platform company to use the data of the data platform company.

     Note

     You must execute the following statements as the ins_sale user.

     SELECT * FROM person ps JOIN portrait pt ON ps.phone = pt.phone WHERE pt.have_car = encdb.enc_text_encrypt('Y',encdb.keyname_generate('ins_sale','demo', Null, Null, Null));
      WARNING:  -- encdb -- -- Untrusted log -- 4 - src/core/untrusted/src/encdb_untrusted_enclave.cpp,256,encdb_ecall: Select BCL (subject_mekid: 2715553450389700608, issuer_mekid: 6953973016013340672) from table fail - returned 0xfa030000
      ERROR:  pg_enc_cmp_eq: encrypted type equal errno: fa030000

     Note

     If the ERROR: permission denied for table portrait error message is displayed, you can execute the GRANT SELECT ON portrait TO ins_sale; statement to grant access permissions on the table.

   • The sales department of the insurance company obtains the required permissions from the data platform company and the data department of the data platform company. Then, The sales department of the insurance company uses the data of the data platform company.

     1. The sales department applies for authorization from the data platform company. The data platform company reviews and approves the application and then issues the authorization.

        # The sales department applies for authorization: Subject signature.
        ./genEncdbSQLCommand.sh -r BCL_ISSUE --subject_sign --spriv sample/usr_pri_sale.pem --spuk sample/usr_puk_sale.pem --ipuk sample/usr_puk_ly.pem --bcl sample/bcl_ly_for_sale_select.txt -c ${cipher_suite}
        # The data platform company reviews and approves the application and then issues the BCL authorization: Issuer signature.
        ./genEncdbSQLCommand.sh -r BCL_ISSUE --issuer_sign --ipriv sample/usr_pri_ly.pem --spuk sample/usr_puk_sale.pem --ipuk sample/usr_puk_ly.pem --bcl sample/bcl_ly_for_sale_select.txt -c ${cipher_suite}

     2. The sales department applies for authorization from the data department of the data platform company. The data department reviews and approves the application and then issues the authorization.

        # The sales department applies for authorization: Subject signature.
        ./genEncdbSQLCommand.sh -r BCL_ISSUE --subject_sign --spriv sample/usr_pri_sale.pem --spuk sample/usr_puk_sale.pem --ipuk sample/usr_puk_data.pem --bcl sample/bcl_data_for_sale_select.txt -c ${cipher_suite}
        # The data department reviews and approves the application and then issues the BCL authorization: Issuer signature.
        ./genEncdbSQLCommand.sh -r BCL_ISSUE --issuer_sign --ipriv sample/usr_pri_data.pem --spuk sample/usr_puk_sale.pem --ipuk sample/usr_puk_data.pem --bcl sample/bcl_data_for_sale_select.txt -c ${cipher_suite}

     3. The sales department can correctly run the joint query and obtain the query result in ciphertext.

        Note

        You must execute the following statements as the ins_sale user.

        SELECT * FROM person ps JOIN portrait pt ON ps.phone = pt.phone WHERE pt.have_car = encdb.enc_text_encrypt('Y',encdb.keyname_generate('ins_sale','demo', Null, Null, Null));
        -- Run the joint query and obtain the query result in ciphertext.