设置密态等值查询
随着企业数据上云，数据的安全隐私保护面临越来越严重的挑战。密态数据库将解决数据整个生命周期中的隐私保护问题，涵盖网络传输、数据存储以及数据运行态；更进一步，密态数据库可以实现云化场景下的数据隐私权限分离，即实现数据拥有者和实际数据管理者的数据读取能力分离。密态等值查询将优先解决密文数据的等值类查询问题。密态等值查询目前支持客户端工具gsql和JDBC。接下来分别介绍如何使用客户端工具执行密态等值查询的相关操作。
操作步骤
以操作系统用户omm登录CN所在主机。
使用gsql连接数据库服务器时，需使用“-C”参数或“--enable-client-encryption”，打开全密态数据库的开关。执行以下命令打开密态开关，连接密态数据库。
gsql -p PORT postgres -r -CCopy
如果需要使用密态计算功能，打开-Z开关即可。
gsql -p PORT postgres -r –C -ZCopy
创建客户端主密钥CMK和列加密密钥CEK。创建CMK的语法请参考CREATE CLIENT MASTER KEY、创建的CEK的语法请参考CREATE COLUMN ENCRYPTION KEY。
--创建客户端加密主密钥(CMK) MogDB=# CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value1", ALGORITHM = RSA_2048); MogDB=# CREATE CLIENT MASTER KEY ImgCMK WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value2", ALGORITHM = RSA_2048); MogDB=# CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256); CREATE COLUMN ENCRYPTION KEY MogDB=# CREATE COLUMN ENCRYPTION KEY ImgCEK WITH VALUES (CLIENT_MASTER_KEY = ImgCMK, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256); CREATE COLUMN ENCRYPTION KEYCopy
查询存储密钥信息的系统表结果如下。
MogDB=# 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) MogDB=# 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)Copy
创建加密表。
MogDB=# 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 TABLECopy
查询表的详细信息如下，Modifiers值为encrypted则表示该列是加密列。
MogDB=# \d creditcard_info Table "public.creditcard_info" Column | Type | Modifiers -------------+-------------------+------------ id_number | integer | name | text | encrypted credit_card | character varying | encryptedCopy
向加密表插入数据并进行等值查询。
MogDB=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393'); INSERT 0 1 MogDB=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033'); INSERT 0 1 MogDB=# select * from creditcard_info where name = 'joe'; id_number | name | credit_card -----------+------+--------------------- 1 | joe | 6217986500001288393 (1 row) -- 注意：使用非密态客户端查看该加密表数据时是密文 MogDB=# select id_number,name from creditcard_info; id_number | name -----------+-------------------------------------- 1 | \x011aefabd754ded0a536a96664790622487c4d366d313aecd5839e410a46d29cba96a60e4831000000ee79056a114c9a6c041bb552b78052e912a8b730609142074c63791abebd0d38 2 | \x011aefabd76853108eb406c0f90e7c773b71648fa6e2b8028cf634b49aec65b4fcfb376f3531000000f7471c8686682de215d09aa87113f6fb03884be2031ef4dd967afc6f7901646b (2 rows)Copy
（可选）对加密表进行alter和update操作。
MogDB=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ImgCEK, ENCRYPTION_TYPE = DETERMINISTIC); ALTER TABLE MogDB=# \d creditcard_info Table "public.creditcard_info" Column | Type | Modifiers -------------+-------------------+------------ id_number | integer | name | text | encrypted credit_card | character varying | encrypted age | integer | encrypted MogDB=# ALTER TABLE creditcard_info DROP COLUMN age; ALTER TABLE MogDB=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy'; UPDATE 1 MogDB=# select * from creditcard_info where name = 'joy'; id_number | name | credit_card -----------+------+------------------- 2 | joy | 80000000011111111 (1 row)Copy
注意：目前仅支持linux。
连接密态数据库需要使用驱动包gsjdbc4.jar，具体JDBC连接参数参考基于JDBC开发章节介绍。JDBC支持密态数据库相关操作，需要设置enable_ce=1，示例如下。
public static Connection getConnect(String username, String passwd)
{
//驱动类。
String driver = "org.opengauss.Driver";
//数据库连接描述符。
String sourceURL = "jdbc:opengauss://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;
};Copy
说明:
- 【建议】使用JDBC操作密态数据库时，一个数据库连接对象对应一个线程，否则，不同线程变更可能导致冲突。
- 【建议】使用JDBC操作密态数据库时，不同connection对密态配置数据有变更，由客户端调用isvalid方法保证connection能够持有变更后的密态配置数据，此时需要保证参数refreshClientEncryption为1(默认值为1)，在单客户端操作密态数据场景下，refreshClientEncryption参数可以设置为0。
// 创建客户端主密钥
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();
}Copy
// 创建客户端主密钥
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);Copy
// 创建列加密密钥
int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);");Copy
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();Copy
// 调用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();Copy
// 调用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();Copy
密态支持函数/存储过程当前版本只支持sql和PL/pgsql两种语言。由于密态支持存储过程中创建和执行函数/存储过程对用户是无感知的，因此语法和非密态无区别。
密态等值查询支持函数存储过程新增系统表gs_encrypted_proc，用于存储参数返回的原始数据类型。
创建密钥，详细步骤请参考使用gsql操作密态数据库和使用JDBC操作密态数据库。
创建加密表。
MogDB=# CREATE TABLE creditcard_info ( MogDB(# id_number int, MogDB(# name text, MogDB(# credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC) MogDB(# ) with (orientation=row); CREATE TABLECopy
插入数据。
MogDB=# insert into creditcard_info values(1, 'Avi', '1234567890123456'); INSERT 0 1 MogDB=# insert into creditcard_info values(2, 'Eli', '2345678901234567'); INSERT 0 1Copy
创建函数支持密态等值查询。
MogDB=# CREATE FUNCTION f_encrypt_in_sql(val1 text, val2 varchar(19)) RETURNS text AS 'SELECT name from creditcard_info where name=$1 or credit_card=$2 LIMIT 1' LANGUAGE SQL; CREATE FUNCTION MogDB=# CREATE FUNCTION f_encrypt_in_plpgsql (val1 text, val2 varchar(19)) mogdb-# RETURNS text AS $$ mogdb$# DECLARE mogdb$# c text; mogdb$# BEGIN mogdb$# SELECT into c name from creditcard_info where name=$1 or credit_card =$2 LIMIT 1; mogdb$# RETURN c; mogdb$# END; $$ mogdb-# LANGUAGE plpgsql; CREATE FUNCTIONCopy
执行函数。
MogDB=# SELECT f_encrypt_in_sql('Avi','1234567890123456'); f_encrypt_in_sql ------------------ Avi (1 row) MogDB=# SELECT f_encrypt_in_plpgsql('Avi', val2=>'1234567890123456'); f_encrypt_in_plpgsql ---------------------- Avi (1 row)Copy
- 函数/存储过程中的“执行动态查询语句”中的查询是在执行过程中编译，因此函数/存储过程中的表名、列名不能在创建阶段未知，输入参数不能用于表名、列名或以任何方式连接。
- 在RETURNS、IN和OUT的参数中，不支持混合使用加密和非加密类型参数。虽然参数类型都是原始数据类型，但实际类型不同。
- 对高级包接口中，服务端打印输出的接口不会做解密操作，加密数据类型在强转成明文原始数据类型时会打印出该数据类型的默认值。
- 当前版本函数/存储过程的LANGUAGE只支持SQL和PL/pgSQL，不支持C和JAVA等其他过程语言。
- 不支持在函数/存储过程中执行其他查询加密列的函数/存储过程。
- 当前版本不支持default、DECLARE中为变量赋予默认值，且不支持对DECLARE中的返回值进行解密，用户可以用执行函数时用输入参数，输出参数来代替使用。
- 不支持gs_dump对涉及加密列的function进行备份。
- 不支持在函数/存储过程中创建密钥。
- 该版本密态函数/存储过程不支持触发器。
- 密态等值查询函数/存储过程不支持对plpgsql语言对语法进行转义，对于语法主体带有引号的语法CREATE FUNCTION AS ‘语法主体'， 可以用CREATE FUNCTION AS '语法主体' 代替。
- 不支持在密态等值查询函数/存储过程中执行修改加密列定义的操作，包括对创建加密表，添加加密列，由于执行函数是在服务端，客户端没法判断是否是需要刷新缓存，得断开连接后或触发刷新客户端加密列缓存才可以对该列做加密操作。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
