数据库密态等值查询概述及操作

数据库密态等值查询概述及操作

一、密态等值查询概述

随着企业数据上云，数据的安全隐私保护面临越来越严重的挑战。密态数据库将解决数据整个生命周期中的隐私保护问题，涵盖网络传输、数据存储以及数据运行态;更进一步，密态数据库可以实现云化场景下的数据隐私权限分离，即实现数据拥有者和实际数据管理者的数据读取能力分离。密态等值查询将优先解决密文数据的等值类查询问题。密态等值查询目前支持客户端工具 gsql 和 JDBC。接下来分别介绍如何使用客户端工具执行密态等值查询的相关操作。

二、使用 gsql 操作密态数据库

操作步骤

以操作系统用户 omm 登录主节点。

执行以下命令打开密态开关，连接密态数据库。

gsql -p PORT postgres -r -C

这里，PORT需要替换为实际值。

创建客户端主密钥 CMK 和列加密密钥 CEK。

--创建客户端加密主密钥(CMK)***=# CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value1", ALGORITHM = RSA_2048);***=# CREATE CLIENT MASTER KEY ImgCMK WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value2", ALGORITHM = RSA_2048);***=# CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);CREATE COLUMN ENCRYPTION KEY***=# CREATE COLUMN ENCRYPTION KEY ImgCEK WITH VALUES (CLIENT_MASTER_KEY = ImgCMK, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);CREATE COLUMN ENCRYPTION KEY

查询存储密钥信息的系统表结果如下：

***=# SELECT * FROM gs_client_global_keys; global_key_name | key_namespace | key_owner | key_acl | create_date-----------------+---------------+-----------+---------+---------------------------- imgcmk1 | 2200 | 10 | | 2021-04-21 11:04:00.656617 imgcmk | 2200 | 10 | | 2021-04-21 11:04:05.389746(2 rows)***=# SELECT column_key_name,column_key_distributed_id ,global_key_id,key_owner FROM gs_column_keys; column_key_name | column_key_distributed_id | global_key_id | key_owner-----------------+---------------------------+---------------+----------- imgcek1 | 760411027 | 16392 | 10 imgcek | 3618369306 | 16398 | 10(2 rows)

创建加密表：

***=# CREATE TABLE creditcard_info (id_number int, name text encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC),credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'id_number' as the distribution column by default.HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CREATE TABLE

查询表的详细信息如下，Modifiers 值为 encrypted 则表示该列是加密列。

***=# \d creditcard_info Table "public.creditcard_info" Column | Type | Modifiers-------------+-------------------+------------ id_number | integer | name | text | encrypted credit_card | character varying | encrypted

向加密表插入数据并进行等值查询。

***=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');INSERT 0 1***=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033');INSERT 0 1***=# select * from creditcard_info where name = 'joe'; id_number | name | credit_card-----------+------+--------------------- 1 | joe | 6217986500001288393(1 row)注意：使用非密态客户端查看该加密表数据时是密文***=# select id_number,name from creditcard_info; id_number | name-----------+------------------------------------------------------------------------------------------------------------------------------------------------------ 1 | \x011aefabd754ded0a536a96664790622487c4d366d313aecd5839e410a46d29cba96a60e4831000000ee79056a114c9a6c041bb552b78052e912a8b730609142074c63791abebd0d38 2 | \x011aefabd76853108eb406c0f90e7c773b71648fa6e2b8028cf634b49aec65b4fcfb376f3531000000f7471c8686682de215d09aa87113f6fb03884be2031ef4dd967afc6f7901646b(2 rows)

（可选）对加密表进行 alter 和 update 操作。

***=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ImgCEK, ENCRYPTION_TYPE = DETERMINISTIC);ALTER TABLE***=# \d creditcard_info Table "public.creditcard_info" Column | Type | Modifiers-------------+-------------------+------------ id_number | integer | name | text | encrypted credit_card | character varying | encrypted age | integer | encrypted***=# ALTER TABLE creditcard_info DROP COLUMN age;ALTER TABLE***=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy';UPDATE 1***=# select * from creditcard_info where name = 'joy'; id_number | name | credit_card-----------+------+------------------- 2 | joy | 80000000011111111(1 row)

三、使用 JDBC 操作密态数据库

连接密态数据库

连接密态数据库需要使用驱动包 gsjdbc4.jar，具体 JDBC 连接参数参考基于 JDBC 开发章节介绍。JDBC 支持密态数据库相关操作，需要设置

enable_ce=1，示例如下：

public static Connection getConnect(String username, String passwd) { //驱动类。 String driver = "org.postgresql.Driver"; //数据库连接描述符。 String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres?enable_ce=1"; Connection conn = null; try { //加载驱动。 Class.forName(driver); } catch( Exception e ) { e.printStackTrace(); return null; } try { //创建连接。 conn = DriverManager.getConnection(sourceURL, username, passwd); System.out.println("Connection succeed!"); } catch(Exception e) { e.printStackTrace(); return null; } return conn; };

说明：

【建议】使用 JDBC 操作密态数据库时，一个数据库连接对象对应一个线程，否则，不同线程变更可能导致冲突。【建议】使用 JDBC 操作密态数据库时，不同 connection 对密态配置数据有变更，由客户端调用 isvalid 方法保证连接能够持有变更后的密态配置数据，此时需要保证参数 refreshClientEncryption 为 1 （默认值为 1），在单客户端操作密态数据场景下，refreshClientEncryption 参数可以设置为 0。

调用 isValid 方法刷新缓存示例

// 创建客户端主密钥Connection conn1 = DriverManager.getConnection("url","user","password");// conn1通过调用isValid刷新缓存try { if (!conn1.getConnection().isValid(60)) { conn1.getFileWriter().writeLine("isValid Failed for connection 1"); }} catch (SQLException e) { conn1.getFileWriter().writeLine("isValid Failed with error"); e.printStackTrace();}

执行密态等值查询相关的创建密钥语句

// 创建客户端主密钥Connection conn = DriverManager.getConnection("url","user","password");Statement stmt = conn.createStatement();int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = gs_ktool, KEY_PATH = \"gs_ktool/1\", ALGORITHM = AES_256_CBC;"); int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = localkms, KEY_PATH = \"key_path_value\" , ALGORITHM = RSA_2048);

说明： 创建密钥之前需要使用 gs_ktool 工具提前生成密钥，才能创建 CMK 成功。

// 创建列加密密钥int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);");

执行密态等值查询相关的创建加密表的语句

int rc3 = stmt.executeUpdate("CREATE TABLE creditcard_info (id_number int, name varchar(50) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC),credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));");// 插入数据int rc4 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');");// 查询加密表ResultSet rs = null;rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';");// 关闭语句对象stmt.close();

执行加密表的预编译 SQL 语句

// 调用Connection的prepareStatement方法创建预编译语句对象。PreparedStatement pstmt = con.prepareStatement("INSERT INTO creditcard_info VALUES (?, ?, ?);");// 调用PreparedStatement的setShort设置参数。pstmt.setInt(1, 2);pstmt.setString(2, "joy");pstmt.setString(3, "6219985678349800033");// 调用PreparedStatement的executeUpdate方法执行预编译SQL语句。int rowcount = pstmt.executeUpdate();// 调用PreparedStatement的close方法关闭预编译语句对象。pstmt.close();

执行加密表的批处理操作

// 调用Connection的prepareStatement方法创建预编译语句对象。Connection conn = DriverManager.getConnection("url","user","password");PreparedStatement pstmt = conn.prepareStatement("INSERT INTO batch_table (id, name, address) VALUES (?,?,?)");// 针对每条数据都要调用setShort设置参数，以及调用addBatch确认该条设置完毕。int loopCount = 20; for (int i = 1; i < loopCount + 1; ++i) { statemnet.setInt(1, i); statemnet.setString(2, "Name " + i); statemnet.setString(3, "Address " + i); // Add row to the batch. statemnet.addBatch();}// 调用PreparedStatement的executeBatch方法执行批处理。int[] rowcount = pstmt.executeBatch();// 调用PreparedStatement的close方法关闭预编译语句对象。pstmt.close();

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。