Implement multi-party data integration and computing with joint queries - ApsaraDB RDS

Always Confidential Database lets data owners grant third parties access to encrypted data for joint computing without exposing the underlying plaintext. This page walks through a complete multi-party data integration scenario using a data platform company and an insurance company as examples.

Key concepts

Familiarize yourself with the following terms before running the examples:

Term	Description
Behavior control list (BCL)	A permission policy that controls which parties can query or decrypt encrypted columns. Issued jointly by a subject (the requesting party) and an issuer (the authorizing party).
Master encryption key (MEK)	A root key associated with each registered user account. Identified by a unique MEK ID.
Data encryption key (DEK)	A column-level encryption key derived from the MEK. Identified by a group ID.
EncDB tool	A command-line tool that generates SQL statements for EncDB operations based on input parameters. Run the generated statements in the database to apply them.
Joint query	A SQL query that joins tables owned by different parties. Requires BCL authorization to access encrypted columns across party boundaries.

How BCL authorization affects joint queries

A joint query produces one of three outcomes depending on the BCL state:

BCL state	Query result
No BCL authorization	Query fails with a permission error
BCL query authorization issued	Query succeeds; result is returned in ciphertext
BCL decryption authorization issued	Authorized party can decrypt the result to plaintext

Who does what

This example involves three roles. Use the table below to find the steps relevant to your role:

Role	Account	Steps
Database administrator (DBA)	DBA account	Set up users and tables — step 1
Data platform company	`ly`	Register account, create DEKs, write data — steps 2–4
Insurance company (sales dept.)	`ins_sale`	Grant encryption permissions, run joint queries — steps 1–2

Prerequisites

Before you begin, ensure that you have:

Completed the configuration steps in Privacy protection. The insurance company accounts described in that topic are used throughout this example.
Access to the EncDB tool (genEncdbSQLCommand.sh and setGroupIdBCL.sh)
The key material files for each party (sample/ directory with .pem and .bin files)

Data platform company setup

Step 1: Create users and tables (run as DBA)

-- Create the data platform company user
CREATE USER ly;

-- Create the user portrait table with encrypted column types
CREATE TABLE portrait (
    phone          enc_text,
    age            enc_int4,
    have_car       enc_text,
    annual_consume enc_int8
);

-- Grant the data platform company full access to the table
GRANT ALL ON portrait TO ly;

Step 2: Register the data platform company account

The EncDB tool generates the SQL statements. Run each generated statement in the database.

# Provision the MEK for the data platform company
./genEncdbSQLCommand.sh -r MEK_PROVISION \
  --mek sample/default_mek_ly.bin \
  -e default_enclave_public_key.pem \
  -c ${cipher_suite}

# Get the MEK ID (run in the database after provisioning)
# SELECT encdb_get_current_mek_id();
# Example output: 6953973016013340672
mekid_ly=6953973016013340672

# Register the BCL for the data platform company
./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}

Step 3: Create DEKs for the portrait table (run as `ly`)

-- Generate a DEK for the phone column and record the group ID
-- Example group ID: fd89d386-ee00-4e0e-9e5f-66efb4c124aa
SELECT encdb.dek_generate(
    encdb.keyname_generate('ly', 'demo', 'public', 'portrait', 'phone')
);

-- Share the same DEK across all columns in this example
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')
);

The encdb.dek_xxx() and encdb.keyname_xxx() functions handle type conversions between plaintext and ciphertext. For details, see Perform conversions between plaintext and ciphertext.

Encryption is blocked by default until BCL authorization is in place. Add the group ID to the BCL and issue it:

# Add the DEK group ID to the BCL
# Replace fd89d386-ee00-4e0e-9e5f-66efb4c124aa with your actual group ID from the SELECT above
./setGroupIdBCL.sh -l fd89d386-ee00-4e0e-9e5f-66efb4c124aa

# Issue the BCL — subject signature (data platform company signs as both subject and issuer here)
./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}

# Issue the BCL — issuer signature
./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}

Step 4: Write encrypted data (run as `ly`)

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'))
);

The sample data inserted (reference only):

phone	age	have_car	annual_consume
13900001111	29	N	20000
13900002222	34	Y	10000

Insurance company setup

The sales department wants to run the following joint query to identify car owners for a vehicle insurance campaign:

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));

Step 1: Grant encryption permissions to the sales department

# Get the sales department MEK ID (run in the database as ins_sale)
# SELECT encdb_get_current_mek_id();
# Example output: 2715553450389700608

# Get the group ID for the sales department's default key
# SELECT groupid FROM encdb.encdb_internal_dek_table WHERE mekid = 2715553450389700608;
# Example output: 7903d109-f3e0-4f3e-b815-3682cb8bd6db

# Add the group ID to the BCL
./setGroupIdBCL.sh -s 7903d109-f3e0-4f3e-b815-3682cb8bd6db

# Issue the BCL — 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_sale.pem \
  --bcl sample/bcl_for_sale_insert.txt \
  -c ${cipher_suite}

# Issue the BCL — issuer signature
./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}

Step 2: Run the joint query (run as `ins_sale`)

Without data platform company authorization

Running the joint query before the data platform company grants access fails:

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));

Expected error:

WARNING:  -- encdb -- -- Untrusted log -- 4 - ...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

This error means ins_sale does not yet have BCL authorization from the data platform company (ly). The enclave cannot find a valid BCL entry linking the two parties.

If you see ERROR: permission denied for table portrait instead, grant table-level read access first: GRANT SELECT ON portrait TO ins_sale;

With data platform company authorization

The data platform company grants the sales department cross-party query access through a two-step authorization process.

The data platform company authorizes query access. The sales department initiates the request (subject signature), and the data platform company approves and issues the authorization (issuer signature):

# Subject signature — sales department applies for authorization
./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}

# Issuer signature — data platform company approves and issues
./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}

The data department of the data platform company also authorizes query access using the same pattern:

# Subject signature — sales department applies for authorization
./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}

# Issuer signature — data department approves and issues
./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}

Run the joint query again. With BCL query authorization in place, the query succeeds and returns the matching rows in ciphertext:

-- Run as ins_sale
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));
-- Result: rows returned in ciphertext

To read the plaintext values, the data platform company must also issue BCL decryption authorization.

What's next

Privacy protection — configure the accounts and base encryption setup required before running this example
Perform conversions between plaintext and ciphertext — reference for encdb.dek_xxx() and encdb.keyname_xxx() functions