Column encryption is a data protection feature provided by OceanBase AI Database. It allows you to return ciphertext based on encryption rules when results are returned, protecting sensitive data stored in the database. This topic describes the column encryption feature and its principles, and provides examples.
How it works
A database administrator or a user with the user-level CREATE SENSITIVE RULE privilege can create a column-level data protection rule on an existing table. If a column A is specified as a protected column in the data protection rule R, the database checks whether the user has plaintext access privilege on rule R when the user performs a SELECT operation and column A is projected:
The user has plaintext access privilege on rule
R: The projection expression containing columnAreturns the plaintext result.The user does not have plaintext access privilege on rule
R: The projection expression containing columnAis encrypted according to the rule and then returned to the user.
Data protection rules
You can create a sensitive rule by using the CREATE SENSITIVE RULE statement. The syntax is as follows:
CREATE SENSITIVE RULE <rule_name>
ON <sensitive_field_list>
USING ENCRYPTION [ = <encryption_method>];
Details are as follows:
- Data protection rule: The only supported data protection rule type is
ENCRYPTION. When you create the rule, you can specify the encryption algorithm. - Rule scope: Each rule can protect one or more columns and supports cross-database and cross-table protection. A column can be protected by only one data protection rule.
- Privileges required: The user who creates the rule must have the user-level
CREATE SENSITIVE RULEprivilege and full access (including theSELECT,INSERT,UPDATE, andREFERENCESprivileges) to the relevant columns. Note that the user who creates a rule does not automatically have plaintext access to the rule. You need to explicitly grant the access.
Privilege mechanism
- User-level plaintext access privilege: If a user has the plaintext access privilege at the user level, the user can access plaintext data without being affected by data protection rules.
- Rule-level plaintext access privilege: If a user has the plaintext access privilege on a particular rule, the user is not affected by the rule and can access the data protected by the rule in plaintext.
- Users without privileges: If a user does not have the plaintext access privilege at the user level or on a particular rule, the user can only view encrypted data when querying the columns protected by the rule.
Differences from function encryption
The ENHANCED_AES_ENCRYPT function is an AES encryption method of OceanBase AI Database.
Feature |
Column encryption |
ENHANCED_AES_ENCRYPT function |
|---|---|---|
| How to use | Configure rules by using DDL | Manually call in SQL statements |
| Privilege control | Fine-grained rule-based privileges | Requires the ENCRYPT privilege. |
| Query experience | Automatically return plaintext or ciphertext based on privileges | Always returns ciphertext, which must be decrypted manually. |
| Index support | Encrypted column indexes are supported. | Supported, but requires special processing. |
| Application compatibility | Fully compatible with existing applications | Requires application code changes. |