一、 密态等值查询概述
随着企业数据上云，数据的安全隐私保护面临越来越严重的挑战。密态数据库将解决数据整个生命周期中的隐私保护问题，涵盖网络传输、数据存储以及数据运行态；更进一步，密态数据库可以实现云化场景下的数据隐私权限分离，即实现数据拥有者和实际数据管理者的数据读取能力分离。密态等值查询将优先解决密文数据的等值类查询问题。密态等值查询目前支持客户端工具gsql和JDBC。接下来分别介绍如何使用客户端工具执行密态等值查询的相关操作。
二、使用gsql操作密态数据库
1. 操作步骤
(1) 以操作系统用户omm登录CN所在主机。
(2) 使用gsql连接数据库服务器时，需使用“-C”参数或“--enable-client-encryption”，打开全密态数据库的开关。执行以下命令打开密态开关，连接密态数据库。
```sql gsql -p PORT postgres -r -C ```
(3) 创建客户端主密钥CMK和列加密密钥CEK。创建CMK的语法请参考[CREATE CLIENT MASTER KEY](https://docs.mogdb.io/zh/mogdb/v3.0/CREATE-CLIENT-MASTER-KEY)、创建的CEK的语法请参考[CREATE COLUMN ENCRYPTION KEY](https://docs.mogdb.io/zh/mogdb/v3.0/CREATE-COLUMN-ENCRYPTION-KEY)。
```sql --创建客户端加密主密钥(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 KEY ```
查询存储密钥信息的系统表结果如下。
```sql 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) ```
(4) 创建加密表。
```sql 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 TABLE ```
查询表的详细信息如下，Modifiers值为encrypted则表示该列是加密列。
```sql MogDB=\d creditcard_info Table "public.creditcard_info" Column | Type | Modifiers -------------+-------------------+------------ id_number | integer | name | text | encrypted credit_card | character varying | encrypted ```
(5) 向加密表插入数据并进行等值查询。
```sql 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) ```
(6) （可选）对加密表进行alter和update操作。
```sql 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) ```
