Using gsql to Operate an Encrypted Database
Executing SQL Statements
Before running the SQL statements in this section, ensure that the preparation and configuration phases are complete.
This section uses a complete execution process as an example to describe how to use the encrypted database syntax, including three phases: DDL statement execution, DML statement execution, and cleanup.
# 1. Connect to the database and use the -C parameter to enable the full encryption function. [terminal] # gsql -p PORT gaussdb -h HOST -U USER -W PASSWORD -r -C -- 2. Create a master key. -- For details about the KEY_PATH format, see "SQL Reference > SQL Syntax > CREATE CLIENT MASTER KEY" in Developer Guide. -- In the Huawei Cloud scenario, the project ID and key ID are required in KEY_PATH. For details about how to obtain the key ID, see the preparation phase. For details about how to obtain the project ID, see the configuration phase. 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. Create a column key. The column key is encrypted by the master key created in the previous step. For details about the syntax, see "SQL Reference > SQL Syntax > CREATE COLUMN ENCRYPTION KEY " in Developer Guide. gaussdb=# CREATE COLUMN ENCRYPTION KEY cek1 WITH VALUES (CLIENT_MASTER_KEY = cmk1, ALGORITHM = AES_256_GCM); -- 4. Create an encrypted table and use syntax to specify name and credit_card in the table as encrypted columns. 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 key by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution key. CREATE TABLE -- 5. Write data to the encrypted table. 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. Query data from the encrypted table. gaussdb=# select * from creditcard_info where name = 'joe'; id_number | name | credit_card -----------+------+--------------------- 1 | joe | 6217986500001288393 -- 7. Update data in the encrypted table. gaussdb=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy'; UPDATE 1 -- 8. Other operations: Add an encrypted column to a table. gaussdb=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC); ALTER TABLE -- 9. Other operations: Delete an encrypted column from a table. gaussdb=# ALTER TABLE creditcard_info DROP COLUMN age; ALTER TABLE -- 10. Other operations: Query master key information from the system catalog. 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. Other operations: Query column key information from the system catalog. 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. Other operations: View the metadata of a column in a table. gaussdb=# \d creditcard_info Table "public.creditcard_info" Column | Type | Modifiers -------------+-------------------+------------ id_number | integer | name | text | encrypted credit_card | character varying | encrypted -- 13. Delete an encrypted table. gaussdb=# DROP TABLE creditcard_info; DROP TABLE -- 14. Delete a column key. gaussdb=# DROP COLUMN ENCRYPTION KEY cek1; DROP COLUMN ENCRYPTION KEY -- 15. Delete a master key. gaussdb=# DROP CLIENT MASTER KEY cmk1; DROP CLIENT MASTER KEY
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot