PolarDB:Grant access permissions on multi-user data

Use case

An advertising platform wants to identify users under 30 from a data platform's encrypted user profile table. The data platform owns the DEK and the applicant (the advertising platform) holds its own DEK. Without authorization, the applicant can only see ciphertext and cannot use the data.

To proceed:

1. The applicant submits a BCL authorization request specifying which DEK groups and operations are needed.

2. The data platform reviews and signs the BCL to approve access.

3. After approval, the advertising platform can query the user profile data within the authorized scope.

Step 1: Initialize certificates

Both parties must register their certificates in the application code before calling any BCL APIs. The following code registers the participant's certificate with the KeyManager. Key registration is required only once per session.

// The private key of the participant's certificate.
String userPriPemString = "*****";
// The public key of the participant's certificate.
String userPukPemString = "*****";

// Initialize the KeyManager and register the certificate.
KeyManager km = sdk.getKeyManager();
km.registerCertificate(userPriPemString, userPukPemString);

Step 2: Collect required parameter values

Before defining the BCL JSON body, collect the following values. Both the applicant and data owner need these values.

Certificate thumbprints (`issuer_pukid` and `subject_pukid`)

A certificate thumbprint is the SHA-256 hash of the certificate content, encoded in Base64:

1. Compute the SHA-256 hash of the full certificate content, including the -----BEGIN CERTIFICATE----- and -----END CERTIFICATE----- markers. You can specify a different hash algorithm if needed.

2. Base64-encode the hash.

3. Use the resulting string as the thumbprint value.

DEK group values (`groupid`, `min`, `max`)

Run the following SQL statement to retrieve the groupid and DEK ID (dekid) for a given encrypted column:

SELECT encdb_get_cc_entry_by_name(<keyname>);

Set <keyname> to the name of the column on which the applicant needs access. The result contains the groupid and the DEK IDs that define the range (min and max).

Step 3: Define the BCL JSON body

Construct the BCL JSON body with the values collected in Step 2. The issueBCL method uses this JSON body for both the applicant's request and the data owner's approval.

String bclBodyJsonString = """
{
  "version": 1,
  "serial_num": "a121bac0-5cb3-4463-a2b2-1155ff29f4c8",

  "issuer_pukid": "dYJ3Wfj/n0eZbuqgQjv8bnBdPXGyWGOlxE/uMy16NXo=",
  "subject_pukid": "+Gg4vXehPt9BWlCPdR83wKDn6E1b/XddNhKQ5mVbKVQ=",

  "validity": {
    "not_before": "20220820111111+0800",
    "not_after":  "20230820111111+0800"
  },
  "policies": {
    "issuer_dek_group": [
      { "min": 1, "max": 100000, "groupid": "b6785611-0c49-4f13-87a9-13f151de9b4d" }
    ],
    "subject_dek_group": [
      { "min": 1, "max": 100000, "groupid": "5e19cfa7-c001-4e44-acab-2e5de4b97dcb" }
    ],
    "result_dek": "SUBJECT",
    "operation": ["*"],
    "preproc":  "NULL",
    "postproc": "NULL"
  }
}
""";

The following table describes each parameter.

Step 4: Applicant — submit the authorization request

The applicant calls issueBCL with isIssuer set to false. This signs the BCL JSON body with the applicant's private key and submits the authorization request to the data owner.

boolean isIssuer = false;
bclBodyJsonString = km.issueBCL(bclBodyJsonString, userPukPemString, userPriPemString, isIssuer);

Step 5: Data owner — approve and issue the BCL

The data owner reviews the BCL JSON body and, if approved, calls issueBCL with isIssuer set to true. This signs the BCL with the data owner's private key and issues the authorization.

boolean isIssuer = true;
bclBodyJsonString = km.issueBCL(bclBodyJsonString, userPukPemString, userPriPemString, isIssuer);

After this step, the applicant can query the data owner's ciphertext within the scope defined by the BCL.

Step 1: Define the revocation list

Construct the behavior revocation list (BRL) JSON body. The serial_num value must match the serial number in the BCL you want to revoke.

String brlBodyJsonString = """
{
  "version": 1,
  "pukid": "dYJ3Wfj/n0eZbuqgQjv8bnBdPXGyWGOlxE/uMy16NXo=",
  "this_update": "20220819111128+0800",
  "next_update": "20220919111128+0800",
  "revoked": [
    {
      "revocation_date": "20220819111128+0800",
      "serial_num": "a121bac0-5cb3-4463-a2b2-1155ff29f4c8"
    }
  ]
}
""";

The following table describes each parameter.

Step 2: Data owner — issue the revocation

The data owner calls revokeBCL to sign and submit the BRL. After this call, the applicant loses access to the data owner's ciphertext.

brlBodyJsonString = km.revokeBCL(brlBodyJsonString, userPukPemString, userPriPemString);