OceanBase Database offers column encryption as a data protection mechanism. This feature ensures that sensitive data stored in the database is returned as encrypted values in query results, based on defined encryption rules. Column encryption is supported for both MySQL- and Oracle-compatible tenants. This topic explains how column encryption works and provides practical examples.
Applicability
OceanBase Database Community Edition does not support column encryption.
How it works
As a database administrator or a user who has the CREATE SENSITIVE RULE privilege at the user level, you can create column-level sensitive data protection rules for existing tables. Assume that a column named A is specified as a protected column by a rule named R. When a user executes a SELECT statement whose projection list contains column A, the database checks whether the user has the plaintext access privilege on rule R:
User has plaintext access to rule
R: The projection expression that contains column A returns plaintext data.User does not have plaintext access to rule
R: The projection expression that contains column A returns the value of column A encrypted as defined by the rule.
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>];
The details are as follows:
- Data protection rules: Currently, only the
ENCRYPTIONtype is supported for data protection rules. You can specify the encryption algorithm when you create a rule. - Scope of the rule: Each rule can specify the protection of one or more columns across different databases and tables. A column can be protected by at most one data protection rule.
- Privileges required: The user who creates a rule must have both the
CREATE SENSITIVE RULEprivilege at the user level and the full access privilege on the specified columns (including theSELECT,INSERT,UPDATE, andREFERENCEprivileges). The user who creates the rule does not automatically inherit the plaintext access privilege for the rule; it must be separately authorized.
Privilege mechanism
- User-level plaintext access: A user with user-level plaintext access can access plaintext data and will not be affected by data protection rules.
- Rule-level plaintext access: If a user has plaintext access on a specific rule, this rule does not affect the user, and the user can access the plaintext data protected by this rule.
- Users without privileges: If a user has neither user-level plaintext access nor rule-level plaintext access, the user can only see encrypted data for columns protected by the rule.
Differences with function encryption
The ENHANCED_AES_ENCRYPT function is an AES encryption method in OceanBase Database.
| Feature | Column encryption | ENHANCED_AES_ENCRYPT function |
|---|---|---|
| Usage | Configure rules through DDL | Manually invoke in SQL statements |
| Access control | Fine-grained rule-based access | Requires the ENCRYPT privilege |
| Query experience | Automatically returns plain or encrypted text based on privileges | Always returns encrypted text, requires manual decryption |
| Index support | Encrypted column indexes | Supported but require special handling |
| Application compatibility | Fully compatible with existing applications | Requires modification of application code |