This topic describes the syntax, parameters, and limitations for creating column encryption rules, and provides examples to help you configure them.
Notice
This feature is only available in MySQL-compatible mode.
Prerequisites
Before using sensitive rules, you must enable Transparent Data Encryption (TDE) and create a master key:
-- Set the encryption method for transparent tablespaces to 'internal' or 'obcloud'.
ALTER SYSTEM SET tde_method = '<encryption_method>';
ALTER INSTANCE ROTATE INNODB MASTER KEY; -- The first execution requires about 20 seconds for the key to take effect.
For more information about the tde_method parameter, see tde_method.
Syntax
Before you create a column encryption rule, you must understand the related syntax. OceanBase Database provides DDL statements to manage column encryption rules, including creating, dropping, and modifying them.
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
To remove a column encryption rule, use the following syntax:
DROP SENSITIVE RULE <rule_name>;
For more information, see DROP SENSITIVE RULE.
Modify a column encryption rule
In practice, you may need to adjust column encryption rules based on business requirements. OceanBase Database provides flexible syntax to modify these rules:
-- 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 describe how to create, drop, and modify column encryption rules in common scenarios.
Create
-- Create a sensitive rule named r1 to protect tbl1(a), tbl2(b, c), and db2.tbl3(e,f).
-- Use the default encryption algorithm.
CREATE ENCRYPTION RULE r1 on tbl1(a), tbl2(b, c), db2.tbl3(e,f)
USING ENCRYPTION;
-- Create a sensitive rule named r2 to protect tbl4(x, y) and tbl5(z).
-- Use the sm4-cbc encryption algorithm.
CREATE ENCRYPTION RULE r2 on tbl4(x, y), tbl5(z)
USING ENCRYPTION = 'sm4-cbc';
Drop
DROP ENCRYPTION RULE r1;
Modify
-- Add tbl2(c), db2.tbl3(x) to sensitive rule r1.
ALTER SENSITIVE RULE r1 ADD COLUMN tbl2(c), db2.tbl3(x);
-- Drop tbl2(c) from sensitive rule r1.
ALTER SENSITIVE RULE r1 DROP COLUMN tbl2(c);
-- Enable sensitive rule r1.
ALTER SENSITIVE RULE r1 ENABLE;
-- Disable sensitive rule 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';
References
- Overview of column encryption
- Manage column encryption privileges
- For more information about the definition and attributes of sensitive rules, see DBA/CDB_OB_SENSITIVE_RULES.