日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
數(shù)據(jù)庫密態(tài)等值查詢概述及操作

一、密態(tài)等值查詢概述

隨著企業(yè)數(shù)據(jù)上云，數(shù)據(jù)的安全隱私保護(hù)面臨越來越嚴(yán)重的挑戰(zhàn)。密態(tài)數(shù)據(jù)庫將解決數(shù)據(jù)整個(gè)生命周期中的隱私保護(hù)問題，涵蓋網(wǎng)絡(luò)傳輸、數(shù)據(jù)存儲(chǔ)以及數(shù)據(jù)運(yùn)行態(tài);更進(jìn)一步，密態(tài)數(shù)據(jù)庫可以實(shí)現(xiàn)云化場(chǎng)景下的數(shù)據(jù)隱私權(quán)限分離，即實(shí)現(xiàn)數(shù)據(jù)擁有者和實(shí)際數(shù)據(jù)管理者的數(shù)據(jù)讀取能力分離。密態(tài)等值查詢將優(yōu)先解決密文數(shù)據(jù)的等值類查詢問題。密態(tài)等值查詢目前支持客戶端工具 gsql 和 JDBC。接下來分別介紹如何使用客戶端工具執(zhí)行密態(tài)等值查詢的相關(guān)操作。

創(chuàng)新互聯(lián)公司是一家專業(yè)提供和靜企業(yè)網(wǎng)站建設(shè),專注與做網(wǎng)站、網(wǎng)站設(shè)計(jì)、H5高端網(wǎng)站建設(shè)、小程序制作等業(yè)務(wù)。10年已為和靜眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計(jì)公司優(yōu)惠進(jìn)行中。

二、使用 gsql 操作密態(tài)數(shù)據(jù)庫

操作步驟

以操作系統(tǒng)用戶 omm 登錄主節(jié)點(diǎn)。

執(zhí)行以下命令打開密態(tài)開關(guān)，連接密態(tài)數(shù)據(jù)庫。

gsql -p PORT postgres -r -C

這里，PORT需要替換為實(shí)際值。

創(chuàng)建客戶端主密鑰 CMK 和列加密密鑰 CEK。

--創(chuàng)建客戶端加密主密鑰(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

查詢存儲(chǔ)密鑰信息的系統(tǒng)表結(jié)果如下：

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)

創(chuàng)建加密表：

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

查詢表的詳細(xì)信息如下，Modifiers 值為 encrypted 則表示該列是加密列。

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

向加密表插入數(shù)據(jù)并進(jìn)行等值查詢。

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)
注意：使用非密態(tài)客戶端查看該加密表數(shù)據(jù)時(shí)是密文
openGauss=# select id_number,name from creditcard_info;
 id_number |                                                                         name
-----------+------------------------------------------------------------------------------------------------------------------------------------------------------
         1 | \x011aefabd754ded0a536a96664790622487c4d366d313aecd5839e410a46d29cba96a60e4831000000ee79056a114c9a6c041bb552b78052e912a8b730609142074c63791abebd0d38
         2 | \x011aefabd76853108eb406c0f90e7c773b71648fa6e2b8028cf634b49aec65b4fcfb376f3531000000f7471c8686682de215d09aa87113f6fb03884be2031ef4dd967afc6f7901646b
(2 rows)

（可選）對(duì)加密表進(jìn)行 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)

三、使用 JDBC 操作密態(tài)數(shù)據(jù)庫

連接密態(tài)數(shù)據(jù)庫

連接密態(tài)數(shù)據(jù)庫需要使用驅(qū)動(dòng)包 gsjdbc4.jar，具體 JDBC 連接參數(shù)參考基于 JDBC 開發(fā)章節(jié)介紹。JDBC 支持密態(tài)數(shù)據(jù)庫相關(guān)操作，需要設(shè)置

enable_ce=1，示例如下：

public static Connection getConnect(String username, String passwd)
    {
        //驅(qū)動(dòng)類。
        String driver = "org.postgresql.Driver";
        //數(shù)據(jù)庫連接描述符。
        String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres?enable_ce=1";
        Connection conn = null;
        
        try
        {
            //加載驅(qū)動(dòng)。
            Class.forName(driver);
        }
        catch( Exception e )
        {
            e.printStackTrace();
            return null;
        }
        
        try
        {
             //創(chuàng)建連接。
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("Connection succeed!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        }
        
        return conn;
    };

說明：

  • 【建議】使用 JDBC 操作密態(tài)數(shù)據(jù)庫時(shí)，一個(gè)數(shù)據(jù)庫連接對(duì)象對(duì)應(yīng)一個(gè)線程，否則，不同線程變更可能導(dǎo)致沖突。
  • 【建議】使用 JDBC 操作密態(tài)數(shù)據(jù)庫時(shí)，不同 connection 對(duì)密態(tài)配置數(shù)據(jù)有變更，由客戶端調(diào)用 isvalid 方法保證連接
    能夠持有變更后的密態(tài)配置數(shù)據(jù)，此時(shí)需要保證參數(shù) refreshClientEncryption 為 1 （默認(rèn)值為 1），在單客戶端操作密態(tài)數(shù)據(jù)場(chǎng)景下，refreshClientEncryption 參數(shù)可以設(shè)置為 0。

調(diào)用 isValid 方法刷新緩存示例

// 創(chuàng)建客戶端主密鑰
Connection conn1 = DriverManager.getConnection("url","user","password");

// conn1通過調(diào)用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();
}

執(zhí)行密態(tài)等值查詢相關(guān)的創(chuàng)建密鑰語句

// 創(chuàng)建客戶端主密鑰
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);

說明： 創(chuàng)建密鑰之前需要使用 gs_ktool 工具提前生成密鑰，才能創(chuàng)建 CMK 成功。

// 創(chuàng)建列加密密鑰
int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);");

執(zhí)行密態(tài)等值查詢相關(guān)的創(chuàng)建加密表的語句

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));");
// 插入數(shù)據(jù)
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';");
// 關(guān)閉語句對(duì)象
stmt.close();

執(zhí)行加密表的預(yù)編譯 SQL 語句

// 調(diào)用Connection的prepareStatement方法創(chuàng)建預(yù)編譯語句對(duì)象。
PreparedStatement pstmt = con.prepareStatement("INSERT INTO creditcard_info VALUES (?, ?, ?);");
// 調(diào)用PreparedStatement的setShort設(shè)置參數(shù)。
pstmt.setInt(1, 2);
pstmt.setString(2, "joy");
pstmt.setString(3, "6219985678349800033");
// 調(diào)用PreparedStatement的executeUpdate方法執(zhí)行預(yù)編譯SQL語句。
int rowcount = pstmt.executeUpdate();
// 調(diào)用PreparedStatement的close方法關(guān)閉預(yù)編譯語句對(duì)象。
pstmt.close();

執(zhí)行加密表的批處理操作

// 調(diào)用Connection的prepareStatement方法創(chuàng)建預(yù)編譯語句對(duì)象。
Connection conn = DriverManager.getConnection("url","user","password");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO batch_table (id, name, address) VALUES (?,?,?)");
// 針對(duì)每條數(shù)據(jù)都要調(diào)用setShort設(shè)置參數(shù)，以及調(diào)用addBatch確認(rèn)該條設(shè)置完畢。
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();
}
// 調(diào)用PreparedStatement的executeBatch方法執(zhí)行批處理。
int[] rowcount = pstmt.executeBatch();
// 調(diào)用PreparedStatement的close方法關(guān)閉預(yù)編譯語句對(duì)象。
pstmt.close();

新聞名稱：數(shù)據(jù)庫密態(tài)等值查詢概述及操作
鏈接分享：http://www.dlmjj.cn/article/dpcgdhc.html