PolarDB: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.

Examples

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.

Data platform company

1. Create a user and a table 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 the table.
   GRANT ALL ON portrait TO ly;

2. Register the data platform company user.

   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_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 Convert data 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, 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.

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

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

      Note

      The value 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. Configure a BCL to update the authorization scope (including the group ID) of the DEK.

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

      Note

      The value 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 message ERROR: permission denied for table portrait 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 permissions from the data platform company. The data platform company reviews and approves the application and then grants the permissions.

        # The sales department applies for permissions: 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 grants the permissions by using a BCL: 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 permissions from the data department of the data platform company. The data department reviews and approves the application and then grants the permissions.

        # The sales department applies for permissions: 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 grants the permissions by using a BCL: 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.