评论

收藏

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

游戏开发 游戏开发 发布于:2022-05-29 10:00 | 阅读数:396 | 评论:0

目录
1. 密态等值查询概述
2. 使用gsql操作密态数据库
操作步骤
3. 使用JDBC操作密态数据库
连接密态数据库
调用isValid方法刷新缓存示例
执行密态等值查询相关的创建密钥语句
执行密态等值查询相关的创建加密表的语句
执行加密表的预编译SQL语句
执行加密表的批处理操作
   1. 密态等值查询概述
随着企业数据上云,数据的安全隐私保护面临越来越严重的挑战。密态数据库将解决数据整个生命周期中的隐私保护问题,涵盖网络传输、数据存储以及数据运行态;更进一步,密态数据库可以实现云化场景下的数据隐私权限分离,即实现数据拥有者和实际数据管理者的数据读取能力分离。密态等值查询将优先解决密文数据的等值类查询问题。密态等值查询目前支持客户端工具gsql和JDBC。接下来分别介绍如何使用客户端工具执行密态等值查询的相关操作。
2. 使用gsql操作密态数据库
  操作步骤
      
  • 以操作系统用户omm登录主节点。  
  • 执行以下命令打开密态开关,连接密态数据库。
    gsql -p PORT postgres -r -C
    这里,PORT需要替换为实际值。
      
  • 创建客户端主密钥CMK和列加密密钥CEK。创建CMK的语法请参考CREATE CLIENT MASTER KEY、创建的CEK的语法请参考CREATE COLUMN ENCRYPTION KEY。
    --创建客户端加密主密钥(CMK)
    openGauss=# CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value1", ALGORITHM = RSA_2048);
    openGauss=# CREATE CLIENT MASTER KEY ImgCMK WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value2", ALGORITHM = RSA_2048);
    openGauss=# CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
    CREATE COLUMN ENCRYPTION KEY
    openGauss=# CREATE COLUMN ENCRYPTION KEY ImgCEK WITH VALUES (CLIENT_MASTER_KEY = ImgCMK, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
    CREATE COLUMN ENCRYPTION KEY
    查询存储密钥信息的系统表结果如下。
    openGauss=# 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)
    openGauss=# 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)

  • 创建加密表。
    openGauss=# 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则表示该列是加密列。
    openGauss=# \d creditcard_info
        Table "public.creditcard_info"
       Column  |     Type    | Modifiers
    -------------+-------------------+------------
     id_number   | integer       |
     name    | text        |  encrypted
     credit_card | character varying |  encrypted

  • 向加密表插入数据并进行等值查询。
    openGauss=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');
    INSERT 0 1
    openGauss=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033');
    INSERT 0 1
    openGauss=# select * from creditcard_info where name = 'joe';
     id_number | name |   credit_card
    -----------+------+---------------------
         1 | joe  | 6217986500001288393
    (1 row)
    注意:使用非密态客户端查看该加密表数据时是密文
    openGauss=# select id_number,name from creditcard_info;
     id_number |                                     name
    -----------+------------------------------------------------------------------------------------------------------------------------------------------------------
         1 | \x011aefabd754ded0a536a96664790622487c4d366d313aecd5839e410a46d29cba96a60e4831000000ee79056a114c9a6c041bb552b78052e912a8b730609142074c63791abebd0d38
         2 | \x011aefabd76853108eb406c0f90e7c773b71648fa6e2b8028cf634b49aec65b4fcfb376f3531000000f7471c8686682de215d09aa87113f6fb03884be2031ef4dd967afc6f7901646b
    (2 rows)

  • (可选)对加密表进行alter和update操作。
    openGauss=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ImgCEK, ENCRYPTION_TYPE = DETERMINISTIC);
    ALTER TABLE
    openGauss=# \d creditcard_info
        Table "public.creditcard_info"
       Column  |     Type    | Modifiers
    -------------+-------------------+------------
     id_number   | integer       |
     name    | text        |  encrypted
     credit_card | character varying |  encrypted
     age     | integer       |  encrypted
    openGauss=# ALTER TABLE creditcard_info DROP COLUMN age;
    ALTER TABLE
    openGauss=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy';
    UPDATE 1
    openGauss=# select * from creditcard_info  where name = 'joy';
     id_number | name |  credit_card
    -----------+------+-------------------
         2 | joy  | 80000000011111111
    (1 row)

  3. 使用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方法保证connection能够持有变更后的密态配置数据,此时需要保证参数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();

关注下面的标签,发现更多相似文章