云数据库 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");
        }
    }
}