更新时间:2024-05-20 GMT+08:00
使用gsql操作密态数据库
执行SQL语句
在执行本节的SQL语句之前,请确保已完成前两阶段:准备阶段、配置阶段。
本节以完整的执行流程为例,介绍如何使用密态数据库语法,包括三个阶段:使用DDL阶段、使用DML阶段、清理阶段。
# 1 连接数据库,并通过-C参数开启全密态开关 [terminal] # gsql -p PORT gaussdb -h HOST -U USER -W PASSWORD -r -C -- 2 创建主密钥 -- KEY_PATH格式请参考:《开发者指南》中“SQL参考 > SQL语法 > CREATE CLIENT MASTER KEY”章节, -- 华为云场景下,KEY_PATH中需使用项目ID与密钥ID,在准备阶段已介绍如何获取密钥ID,配置阶段已介绍如何获取项目ID gaussdb=# CREATE CLIENT MASTER KEY cmk1 WITH ( KEY_STORE = huawei_kms , KEY_PATH = 'https://kms.cn-north-4.myhuaweicloud.com/v1.0/00000000000000000000000000000000/kms/00000000-0000-0000-0000-00000000000', ALGORITHM = AES_256); -- 3 创建列密钥,列密钥由上一步创建的主密钥加密。详细语法参考:《开发者指南》中“SQL参考 > SQL语法 > CREATE COLUMN ENCRYPTION KEY”章节 gaussdb=# CREATE COLUMN ENCRYPTION KEY cek1 WITH VALUES (CLIENT_MASTER_KEY = cmk1, ALGORITHM = AES_256_GCM); -- 4 创建加密表,并通过语法指定表中name和credit_card为加密列。 gaussdb=# CREATE TABLE creditcard_info ( id_number int, name text encrypted with (column_encryption_key = cek1, encryption_type = DETERMINISTIC), credit_card varchar(19) encrypted with (column_encryption_key = cek1, 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 -- 5 向加密表写入数据 gaussdb=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393'); INSERT 0 1 gaussdb=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033'); INSERT 0 1 -- 6 从加密表中查询数据 gaussdb=# select * from creditcard_info where name = 'joe'; id_number | name | credit_card -----------+------+--------------------- 1 | joe | 6217986500001288393 -- 7 更新加密表中数据 gaussdb=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy'; UPDATE 1 -- 8 其他操作:向表中新增一列加密列 gaussdb=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC); ALTER TABLE -- 9 其他操作:从表中删除一列加密列 gaussdb=# ALTER TABLE creditcard_info DROP COLUMN age; ALTER TABLE -- 10 其他操作:从系统表中查询主密钥信息 gaussdb=# SELECT * FROM gs_client_global_keys; global_key_name | key_namespace | key_owner | key_acl | create_date -----------------+---------------+-----------+---------+---------------------------- cmk1 | 2200 | 10 | | 2021-04-21 11:04:00.656617 (1 rows) -- 11 其他操作:从系统表中查询列密钥信息 gaussdb=# 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 -----------------+---------------------------+---------------+----------- cek1 | 760411027 | 16392 | 10 (1 rows) -- 12 其他操作:查看表中列的元信息 gaussdb=# \d creditcard_info Table "public.creditcard_info" Column | Type | Modifiers -------------+-------------------+------------ id_number | integer | name | text | encrypted credit_card | character varying | encrypted -- 13 删除加密表 gaussdb=# DROP TABLE creditcard_info; DROP TABLE -- 14 删除列密钥 gaussdb=# DROP COLUMN ENCRYPTION KEY cek1; DROP COLUMN ENCRYPTION KEY -- 15 删除主密钥 gaussdb=# DROP CLIENT MASTER KEY cmk1; DROP CLIENT MASTER KEY
父主题: 设置密态等值查询