This topic describes the syntax, parameters, and restrictions of creating a column encryption rule, and provides examples to help you quickly configure column encryption rules.
Prerequisites
Before you use sensitive rules, enable Transparent Data Encryption (TDE) and create a master key:
-- Set the TDE method for transparent tablespace encryption. For valid values, see tde_method (for example, internal, obcloud, and ocp).
ALTER SYSTEM SET tde_method = '<encryption_method>';
ALTER INSTANCE ROTATE INNODB MASTER KEY; -- The first execution requires about 20 seconds for the master key to take effect.
For more information about the tde_method configuration item, see tde_method.
Syntax
Before you create a column encryption rule, you must understand the syntax. OceanBase AI Database provides complete DDL statements to manage column encryption rules, including create, drop, and alter operations.
Create a column encryption rule
CREATE SENSITIVE RULE <rule_name>
ON <sensitive_field_list>
USING ENCRYPTION [ = <encryption_method>];
For more information, see CREATE SENSITIVE RULE.
Drop a column encryption rule
You can use the following syntax to drop a column encryption rule:
DROP SENSITIVE RULE <rule_name>;
For more information, see DROP SENSITIVE RULE.
Modify a column encryption rule
In actual use, you may need to adjust column encryption rules based on business requirements. OceanBase AI Database provides flexible modification syntax to meet these requirements:
-- Add sensitive columns.
ALTER SENSITIVE RULE <rule_name> ADD COLUMN <sensitive_field_list>;
-- Drop sensitive columns.
ALTER SENSITIVE RULE <rule_name> DROP COLUMN <sensitive_field_list>;
-- Enable the rule.
ALTER SENSITIVE RULE <rule_name> ENABLE;
-- Disable the rule.
ALTER SENSITIVE RULE <rule_name> DISABLE;
-- Change the encryption algorithm.
ALTER SENSITIVE RULE <rule_name> USING ENCRYPTION [= <encryption_method>];
For more information, see ALTER SENSITIVE RULE.
Examples
The following examples help you understand the operations of column encryption rules. These examples cover the main scenarios of creating, dropping, and modifying rules, as well as a complete example.
Create
-- Create a sensitive rule named r1 to protect the a column of the tbl1 table, the b and c columns of the tbl2 table, and the e and f columns of the db2.tbl3 table.
-- When you do not specify an encryption algorithm, the default algorithm AES-256-ECB is used. This is equivalent to USING ENCRYPTION = 'aes-256'.
CREATE SENSITIVE RULE r1 on tbl1(a), tbl2(b, c), db2.tbl3(e,f)
USING ENCRYPTION;
-- Create a sensitive rule named r2 to protect the x and y columns of the tbl4 table and the z column of the tbl5 table.
-- Use the SM4-CBC encryption algorithm.
CREATE SENSITIVE RULE r2 on tbl4(x, y), tbl5(z)
USING ENCRYPTION = 'sm4-cbc';
Delete
DROP SENSITIVE RULE r1;
Modify
-- Add the c column of the tbl2 table and the x column of the db2.tbl3 table to the sensitive rule named r1.
ALTER SENSITIVE RULE r1 ADD COLUMN tbl2(c), db2.tbl3(x);
-- Drop the c column of the tbl2 table from the sensitive rule named r1.
ALTER SENSITIVE RULE r1 DROP COLUMN tbl2(c);
-- Enable the sensitive rule named r1.
ALTER SENSITIVE RULE r1 ENABLE;
-- Disable the sensitive rule named r1.
ALTER SENSITIVE RULE r1 DISABLE;
-- Change the encryption algorithm of the sensitive rule named r1 to the default algorithm.
ALTER SENSITIVE RULE r1 USING ENCRYPTION;
-- Change the encryption algorithm of the sensitive rule named r1 to SM4-CBC.
ALTER SENSITIVE RULE r1 USING ENCRYPTION = 'sm4-cbc';
Complete example
To help you get started, this section provides an end-to-end example that includes the Prerequisites section.
-- Note! Only new test tenants without a master key require the following initialization. If an active master key already exists, skip this code block.
-- Administrator: root@mysql.
ALTER SYSTEM SET tde_method = 'internal';
-- Wait for about 20 seconds. After the tde_method takes effect, run the following statement to rotate the master key.
ALTER INSTANCE ROTATE INNODB MASTER KEY;
The following steps use two connections: Session A uses the administrator root@mysql, and Session B uses the regular user sr_demo@mysql (the password is ******). Replace these with your actual tenant name and connection address, and switch sessions as instructed.
When starting the OBClient client, add --binary-as-hex to display ciphertext as hexadecimal strings starting with 0x. The following ciphertext is for illustration only; 0x... indicates omitted ciphertext, and the actual value varies with the tenant's master key.
Session A: Prepare data and grant the rule management privilege.
CREATE DATABASE sr_demo; CREATE USER sr_demo IDENTIFIED BY '******'; CREATE TABLE sr_demo.customer ( id INT PRIMARY KEY, phone VARCHAR(20), email VARCHAR(40) ); INSERT INTO sr_demo.customer VALUES (1, '13800000000', 'alice@example.com'); -- The privilege to manage rules and the access privilege on the target columns required to create a rule. GRANT CREATE SENSITIVE RULE ON *.* TO sr_demo; GRANT SELECT, INSERT, UPDATE, REFERENCES ON sr_demo.customer TO sr_demo;Session B: Create a rule. Rules are enabled by default after creation.
CREATE SENSITIVE RULE sr_demo_rule ON sr_demo.customer(phone) USING ENCRYPTION = 'aes-256';The creator must separately obtain the
PLAINACCESSprivilege. Thephonecolumn is returned as ciphertext in the current query.SELECT id, phone, email FROM sr_demo.customer ORDER BY id;ID | PHONE | EMAIL 1 | 0x8B22B347127C399C8909760000F43EA4 | alice@example.comThe filter condition uses a plaintext value.
SELECT id FROM sr_demo.customer WHERE phone = '13800000000';ID 1Session A: View the rule and grant plaintext access privilege on it.
SHOW SENSITIVE RULES FROM sr_demo.customer;RULE_NAME | PROTECTION_POLICY | METHOD | ENABLED | PROTECTED_COLUMNS sr_demo_rule | ENCRYPTION | aes-256 | YES | sr_demo.customer(phone)GRANT PLAINACCESS ON SENSITIVE RULE sr_demo_rule TO sr_demo; SELECT RULE_NAME, USER_NAME, USER_TYPE FROM oceanbase.DBA_OB_SENSITIVE_RULE_PLAINACCESS_USERS WHERE RULE_NAME = 'sr_demo_rule';RULE_NAME | USER_NAME | USER_TYPE sr_demo_rule | sr_demo | USERSession B: Execute the same query to confirm that plaintext is returned.
SELECT id, phone, email FROM sr_demo.customer ORDER BY id;ID | PHONE | EMAIL 1 | 13800000000 | alice@example.comSession A: Revoke plaintext access.
REVOKE PLAINACCESS ON SENSITIVE RULE sr_demo_rule FROM sr_demo;Session B: Execute the same query and confirm that
phoneis returned as ciphertext.SELECT id, phone, email FROM sr_demo.customer ORDER BY id;ID | PHONE | EMAIL 1 | 0x... | alice@example.comSession B: Add
emailto the same rule and confirm that both columns return ciphertext.ALTER SENSITIVE RULE sr_demo_rule ADD COLUMN sr_demo.customer(email); SELECT id, phone, email FROM sr_demo.customer ORDER BY id;ID | PHONE | EMAIL 1 | 0x... | 0x...Session B: Disable the rule. Even without the
PLAINACCESSprivilege, plaintext is returned.ALTER SENSITIVE RULE sr_demo_rule DISABLE; SELECT id, phone, email FROM sr_demo.customer ORDER BY id;ID | PHONE | EMAIL 1 | 13800000000 | alice@example.comSession B: Enable the rule and remove the protection for
email. Theemailcolumn in the table remains.ALTER SENSITIVE RULE sr_demo_rule ENABLE; ALTER SENSITIVE RULE sr_demo_rule DROP COLUMN sr_demo.customer(email); SELECT id, phone, email FROM sr_demo.customer ORDER BY id;ID | PHONE | EMAIL 1 | 0x... | alice@example.comSession B: Drop the rule, then close session B.
DROP SENSITIVE RULE sr_demo_rule;Session A: Clean up the sample user and data.
DROP USER sr_demo; DROP DATABASE sr_demo;
References
- Column encryption overview
- Column encryption privilege management
- For more information about the definitions and attributes of sensitive rules, see DBA/CDB_OB_SENSITIVE_RULES