ApsaraDB RDS：授權管理

package org.example;

import com.alibaba.encdb.EncdbSDK;
import com.alibaba.encdb.crypto.EncdbSDKBuilder;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        Main main = new Main();
        main.testSelfBcl();
    }

    private String readPemFile(String filename) throws IOException {
        return new String(Files.readAllBytes(Paths.get(filename)));
    }

    public void testSelfBcl() {
        // 串連參數請使用實際執行個體資訊及參數進行替換
        String hostname = "pgm-****.pg.rds.aliyuncs.com";
        String port = "5432";
        String dbname = "testdb";
        String username = "testdbuser";
        String password = "****";
        String mek = "00112233445566778899aabbccddeeff";  // 只是樣本，建議用更複雜的密鑰
        String prikeyFilename = "D:\\test\\Issuer\\pri_key_pkcs8.pem"; // 只是樣本，請使用實際路徑替換
        String pubkeyFilename = "D:\\test\\Issuer\\pub_key.pem"; // 只是樣本，請使用實際路徑替換
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://" + hostname + ":" + port + "/" + dbname + "?binaryTransfer=true", username, password)) {
            System.out.println("connect success");
            String tblname = "tbl_alter_" + conn.getMetaData().getUserName();
            // 建立表
            conn.createStatement().executeUpdate("DROP TABLE IF EXISTS " + tblname);
            conn.createStatement().executeUpdate("CREATE TABLE " + tblname + " (id int)");
            // 寫入明文資料
            try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO " + tblname + " VALUES(?)")) {
                stmt.setInt(1, 231);
                stmt.executeUpdate();
            }
            // （可選）清理歷史BCL授權記錄
            conn.createStatement().executeUpdate("DELETE FROM encdb.encdb_internal_bcl_table");
            conn.createStatement().executeUpdate("DELETE FROM encdb.encdb_internal_brl_map_table");
            conn.createStatement().executeUpdate("DELETE FROM encdb.encdb_internal_brl_table");
            // 初始化SDK，並分發MEK
            EncdbSDK sdk = EncdbSDKBuilder.newInstance()
                    .setDbConnection(conn)
                    .setMek(mek)
                    .build();
            System.out.println("init success");
            // 從檔案讀取公私密金鑰內容
            String userPrkPemString = readPemFile(prikeyFilename);
            String userPukPemString = readPemFile(pubkeyFilename);
            // 註冊公私密金鑰資訊
            sdk.getKeyManager().registerCertificate(userPrkPemString, userPukPemString);
            System.out.println("register certificate success");

            // 使用指定密鑰將明文列變更成密文列，例如通過encdb.dek_generate產生
            // 此處使用使用者的預設密鑰，實際可使用encdb.keyname_generate擷取目標keyname
            String keyname = "'|" + username + "|" + dbname + "|'";
            String alterStmString = "ALTER TABLE " + tblname + " ALTER COLUMN id SET DATA TYPE enc_int4 USING encdb.enc_int4_encrypt(id, " + keyname + ")";

            // 未授權前，執行明密文變更失敗
            try {
                conn.createStatement().executeUpdate(alterStmString);
            } catch (SQLException exception) {
                if (exception.getMessage().contains("fa030000") || exception.getMessage().contains("fa020000")) {
                    System.out.println("alter column to enc_int4 failed without authorized");
                } else {
                    throw exception; // 重新拋出其他異常
                }
            }
            // 執行多使用者授權
            String bclBodyJsonString = """
                    {
                        "version": 1,
                        "serial_num": "fdbed057-7fe5-4c31-97ae-afdd615732fe",
                        "issuer_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "subject_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "validity": {
                            "not_after": "20250920111111+0800",
                            "not_before": "20240920111111+0800"
                        },
                        "policies": {
                            "issuer_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "93165651-609f-47db-91aa-c3a2ab7084c4"
                                }
                            ],
                            "result_dek": "SUBJECT",
                            "subject_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "93165651-609f-47db-91aa-c3a2ab7084c4"
                                }
                            ],
                            "operation": [
                                "*"
                            ],
                            "postproc": "NULL",
                            "preproc": "NULL"
                        }
                    }
                    """;
            // 簽發授權
            {
                bclBodyJsonString = sdk.getKeyManager().issueBCL(bclBodyJsonString, userPukPemString, userPrkPemString, true);
                bclBodyJsonString = sdk.getKeyManager().issueBCL(bclBodyJsonString, userPukPemString, userPrkPemString, false);
                System.out.println("issue bcl success");
            }
            // 授權後，執行明密文變更成功
            conn.createStatement().executeUpdate(alterStmString);
            System.out.println("alter column to enc_int4 success after authorized");
        } catch (SQLException |
                 IOException e) {
            e.printStackTrace(); // 列印異常
        }
    }
}

package org.example;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;

import com.alibaba.encdb.Cryptor;
import com.alibaba.encdb.EncdbSDK;
import com.alibaba.encdb.common.Constants.EncAlgo;
import com.alibaba.encdb.crypto.EncdbSDKBuilder;

public class BlcTest {
    private String readPemFile(String filename) throws IOException {
        return new String(Files.readAllBytes(Paths.get(filename)));
    }

    private void prepareData(Connection conn, EncdbSDK sdk) throws SQLException {
        int id = 1;
        String name = "name";
        int price = 1234;
        float miles = 12.34f;
        String secret = "aliyun";

        String tblname = "tbl_" + conn.getMetaData().getUserName();
        // 建立表
        conn.createStatement().executeUpdate("DROP TABLE IF EXISTS " + tblname);
        conn.createStatement().executeUpdate("CREATE TABLE " + tblname
                + " (id INTEGER, name VARCHAR, price enc_int4, miles enc_float4, secret enc_text, PRIMARY KEY (id))");

        // 寫入資料
        PreparedStatement stmt = conn.prepareStatement(
                "INSERT INTO " + tblname + " (id, name, price, miles, secret) VALUES(?,?,?,?,?)");
        stmt.setInt(1, id);
        stmt.setString(2, name);
        // 對資料進行加密並寫入
        Cryptor cryptor = sdk.getCryptor();
        stmt.setBytes(3, cryptor.encrypt(tblname, "price", price));
        stmt.setBytes(4, cryptor.encrypt(tblname, "miles", miles));
        stmt.setBytes(5, cryptor.encrypt(tblname, "secret", secret));
        stmt.execute();
    }

    private void validateResult(ResultSet rs, EncdbSDK sdk) throws SQLException {
        int id = 1;
        String name = "name";
        int price = 1234;
        float miles = 12.34f;
        String secret = "aliyun";

        Cryptor cryptor = sdk.getCryptor();
        while (rs.next()) {
            int idRs = rs.getInt(1);
            Assertions.assertEquals(id, idRs);
            String nameRs = rs.getString(2);
            Assertions.assertEquals(name, nameRs);
            int priceRs = cryptor.decryptInt(rs.getBytes(3));
            Assertions.assertEquals(price, priceRs);
            float milesRs = cryptor.decryptFloat(rs.getBytes(4));
            Assertions.assertEquals(miles, milesRs, 0.000001f);
            String secretRs = cryptor.decryptString(rs.getBytes(5));
            Assertions.assertEquals(secret, secretRs);
        }
    }

    private void simpleQuery(Connection conn, EncdbSDK sdk) throws SQLException {
        String tblname = "tbl_" + conn.getMetaData().getUserName();
        String sqlCmd = "SELECT * FROM " + tblname + " WHERE  price > ?";
        PreparedStatement stmt = conn.prepareStatement(sqlCmd);
        // 加密查詢內容
        Cryptor cryptor = sdk.getCryptor();
        stmt.setBytes(1, cryptor.encrypt(tblname, "price", 100));
        ResultSet rs = stmt.executeQuery();

        validateResult(rs, sdk);
    }

    private void subjectJoinQuery(Connection subjectConn, EncdbSDK subjectSdk, Connection issuerConn)
            throws SQLException {
        String issuerTblname = "tbl_" + issuerConn.getMetaData().getUserName();
        String subjectTblname = "tbl_" + subjectConn.getMetaData().getUserName();

        // 以 Subject 身份發起 JOIN 查詢 Issuer 表中資料
        String sqlCmd = "SELECT subject.id as id, subject.name as name, subject.price as price, subject.miles as miles, subject.secret as secret "
                + "FROM " + issuerTblname + " issuer, " + subjectTblname + " subject "
                + "WHERE issuer.price = subject.price and subject.price > ?";

        Connection conn = subjectConn;
        EncdbSDK sdk = subjectSdk;
        PreparedStatement stmt = conn.prepareStatement(sqlCmd);
        // 加密查詢內容
        Cryptor cryptor = sdk.getCryptor();
        stmt.setBytes(1, cryptor.encrypt(subjectTblname, "price", 100));
        ResultSet rs = stmt.executeQuery();

        validateResult(rs, sdk);
    }

    @Test
    public void testBcl() throws SQLException, IOException {
        // 網域名稱（hostname）、連接埠（port）、資料庫執行個體名（dbname）等串連資訊需要更新為您的執行個體資訊
        String hostname = "pgm-****.pg.rds.aliyuncs.com";
        String port = "5432";
        String dbname = "testdb";

        // Issuer 資訊，即授權方
        Connection issuerConn;
        EncdbSDK issuerSdk;
        String issuerPriKeyPemString;
        String issuerPubKeyPemString;
        // Subject 資訊，即申請方
        Connection subjectConn;
        EncdbSDK subjectSdk;
        String subjectPriKeyPemString;
        String subjectPubKeyPemString;

        // 初始化資料庫連接，並完成MEK分發。請參考 EncDB SDK
        {
            // 初始化 Issuer 資訊
            {
                // 使用者名稱（username）、密碼（password）等串連資訊需要更新為您的執行個體資訊
                String username = "testdbuser";
                String password = "****";
                String mek = "00112233445566778899aabbccddeeff"; // 只是樣本，建議用更複雜的密鑰

                // 建立資料庫連接
                String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
                issuerConn = DriverManager.getConnection(dbUrl, username, password);
                System.out.println("issuer connect success");

                // 初始化SDK，並分發MEK，請參考 EncDB SDK
                issuerSdk = EncdbSDKBuilder.newInstance()
                        .setDbConnection(issuerConn)
                        .setMek(mek)
                        .setEncAlgo(EncAlgo.SM4_128_CBC)
                        .build();
                System.out.println("issuer init success");
            }
            // 初始化 Subject 資訊
            {
                // 使用者名稱（username）、密碼（password）等串連資訊需要更新為您的執行個體資訊
                String username = "testdbuser02";
                String password = "****";
                String mek = "ffeeddccbbaa99887766554433221100"; // 只是樣本，建議用更複雜的密鑰

                // 建立資料庫連接
                String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
                subjectConn = DriverManager.getConnection(dbUrl, username, password);
                System.out.println("subject connect success");

                // 初始化SDK，並分發MEK，請參考 EncDB SDK
                subjectSdk = EncdbSDKBuilder.newInstance()
                        .setDbConnection(subjectConn)
                        .setMek(mek)
                        .setEncAlgo(EncAlgo.SM4_128_CBC)
                        .build();
                System.out.println("subject init success");
            }

            // 插入測試資料，並驗證資料所有者可正確訪問並查看密文欄位解密後內容
            {
                // Issuer 寫入資料並驗證
                {
                    Connection tmpConnection = issuerConn;
                    EncdbSDK tmpSdk = issuerSdk;

                    // 以 Issuer 身份，準備測試資料，其中部分欄位為加密欄位
                    prepareData(tmpConnection, tmpSdk);
                    System.out.println("issuer prepare data success");

                    // 以 Issuer 身份查詢資料，可以正常訪問並查看密文欄位解密後內容
                    Assertions.assertDoesNotThrow(() -> {
                        simpleQuery(tmpConnection, tmpSdk);
                    });
                    System.out.println("issuer query own data success");
                }
                // Subject 寫入資料並驗證
                {
                    Connection tmpConnection = subjectConn;
                    EncdbSDK tmpSdk = subjectSdk;

                    // 以 Subject 身份，準備測試資料，其中部分欄位為加密欄位
                    prepareData(tmpConnection, tmpSdk);
                    System.out.println("subject prepare data success");

                    // 以 Subject 身份查詢資料，可以正常訪問並查看密文欄位解密後內容
                    Assertions.assertDoesNotThrow(() -> {
                        simpleQuery(tmpConnection, tmpSdk);
                    });
                    System.out.println("subject query own data success");
                }
                // 授予資料表存取權限，但並非資料內容存取權限
                {
                    String tblname = "tbl_" + issuerConn.getMetaData().getUserName();
                    issuerConn.createStatement().execute(
                            "GRANT SELECT ON TABLE " + tblname + " TO " + subjectConn.getMetaData().getUserName());

                    // 清理歷史BCL授權記錄
                    subjectConn.createStatement().execute("DELETE FROM encdb.encdb_internal_bcl_table");
                    subjectConn.createStatement().execute("DELETE FROM encdb.encdb_internal_brl_map_table");
                    subjectConn.createStatement().execute("DELETE FROM encdb.encdb_internal_brl_table");
                }
            }
        }

        // 註冊 Subject 和 Issuer 公開金鑰和私密金鑰。僅需初始化首次一次性操作，不需要重複執行
        {
            // Issuer 註冊公私密金鑰
            {
                // 從檔案讀取公私密金鑰內容
                String prikeyFilename = "D:\\test\\Issuer\\pri_key_pkcs8.pem"; // 只是樣本，請使用實際路徑替換
                String pubkeyFilename = "D:\\test\\Issuer\\pub_key.pem"; // 只是樣本，請使用實際路徑替換
                issuerPriKeyPemString = readPemFile(prikeyFilename);
                issuerPubKeyPemString = readPemFile(pubkeyFilename);
                // 註冊公私密金鑰資訊
                issuerSdk.getKeyManager().registerCertificate(issuerPriKeyPemString, issuerPubKeyPemString);
                System.out.println("issuer register certificate success");

            }

            // Subject 註冊公私密金鑰
            {
                // 從檔案讀取公私密金鑰內容
                String prikeyFilename = "D:\\test\\Subject\\pri_key_pkcs8.pem"; // 只是樣本，請使用實際路徑替換
                String pubkeyFilename = "D:\\test\\Subject\\pub_key.pem"; // 只是樣本，請使用實際路徑替換
                subjectPriKeyPemString = readPemFile(prikeyFilename);
                subjectPubKeyPemString = readPemFile(pubkeyFilename);
                // 註冊公私密金鑰資訊
                subjectSdk.getKeyManager().registerCertificate(subjectPriKeyPemString, subjectPubKeyPemString);
                System.out.println("subject register certificate success");
            }
        }

        // 未多使用者授權前， Subject 嘗試訪問 Issuer 寫入的資料，報錯提示拒絕訪問
        {
            Connection tmpConnection = subjectConn;
            EncdbSDK tmpSdk = subjectSdk;
            Connection tmpIssuerConn = issuerConn;
            SQLException exception = Assertions.assertThrows(SQLException.class, () -> {
                subjectJoinQuery(tmpConnection, tmpSdk, tmpIssuerConn);
            });
            Assertions.assertEquals("ERROR: encdb_get_hash_from_bytea: errno:fa030000", exception.getMessage());
            System.out.println("subject query issuer data failed without authorized");
        }

        // 執行多使用者授權。請參考文檔中對步驟的描述，確保授權資訊正確
        {
            // 授權 Subject 訪問 Issuer 指定列的密鑰，其中，Subject 和 Issuer 的密鑰分別在
            // `issuer_dek_group`、`subject_dek_group` 指定
            String bclBodyJsonString = """
                    {
                        "version": 1,
                        "serial_num": "a121bac0-5cb3-4463-a2b2-1155ff29f4c8",
                        "issuer_pukid": "p81x+WqYb7BR0yP0LK0qiEaxgLDqwuIjfJhgC0mMJcE=",
                        "subject_pukid": "qIPPfgTJEEG/9WkjP0E5LLAijZ14h/Qgb2EfmBZCWSo=",
                        "validity": {
                            "not_after": "20250820111111+0800",
                            "not_before": "20240820111111+0800"
                        },
                        "policies": {
                            "issuer_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "e4a92b05-f64d-4665-aadd-cd1336d0c0cc"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "1010b43b-50da-4473-81ac-ce84657eb4f9"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "b99e70cf-c6b0-444f-a404-81a721e38734"
                                }
                            ],
                            "result_dek": "SUBJECT",
                            "subject_dek_group": [
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "d1cbe5a6-49f0-42e0-ba07-572e2a5e2f5f"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "9c772fa3-4712-4034-9447-0f1e9e18fbeb"
                                },
                                {
                                    "min": 1,
                                    "max": 100000,
                                    "groupid": "e2541c31-891e-4f8d-8389-09c5631f1e66"
                                }
                            ],
                            "operation": [
                                "*"
                            ],
                            "postproc": "NULL",
                            "preproc": "NULL"
                        }
                    }
                    """;

            // 以下兩個步驟先後順序可調整，即：1）Issuer 主動授權，或者 2）Subject 申請授權，均可
            // Issuer 簽發授權，允許 Subject 訪問被 BCL 中指定密鑰DEK加密的資料
            {
                bclBodyJsonString = issuerSdk.getKeyManager().issueBCL(bclBodyJsonString, issuerPubKeyPemString,
                        issuerPriKeyPemString, true);
                System.out.println("issuer issue bcl success");
            }
            // Subject 簽發請求，申請訪問被 BCL 中指定密鑰DEK加密的資料
            {
                bclBodyJsonString = subjectSdk.getKeyManager().issueBCL(bclBodyJsonString, subjectPubKeyPemString,
                        subjectPriKeyPemString, false);
                System.out.println("subject issue bcl success");
            }
        }

        // 多使用者授權後， Subject 嘗試訪問 Issuer 寫入的資料，成功訪問並查看解密後內容
        {
            Connection tmpConnection = subjectConn;
            EncdbSDK tmpSdk = subjectSdk;
            Connection tmpIssuerConn = issuerConn;
            Assertions.assertDoesNotThrow(() -> {
                subjectJoinQuery(tmpConnection, tmpSdk, tmpIssuerConn);
            });
            System.out.println("subject query issuer data success after authorized");
        }
    }
}