Overview of column encryption

Column encryption is a data protection feature provided by OceanBase Database. It allows you to return ciphertext based on encryption rules when results are returned. It protects sensitive data stored in the database. This feature is supported in both MySQL and Oracle tenants. This topic describes the column encryption feature and provides an example.

How it works

A database administrator or a user with the CREATE SENSITIVE RULE privilege can create a column-level data protection rule for an existing table. If column A is specified as a protected column in the data protection rule R, the database checks whether the user has plaintext access privileges on rule R when the SELECT operation is performed and column A is projected:

• The user has plaintext access to the rule R: Projections that include column A will return plaintext results.

• The user does not have the plaintext access privilege for rule R: Projection expressions containing column A will be encrypted according to the rule and returned to the user.

Data Protection Rules

You can create a sensitive rule by using the CREATE SENSIVE 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 rules: The only supported data protection rule type is ENCRYPTION. When creating the rule, you can specify the encryption algorithm.
• Rule scope: Each rule can be applied to one or more columns and supports cross-database and cross-table application. A column can be protected by only one data protection rule.
• Permissions required: The user who creates the rule must have the CREATE SENSITIVE RULE permission at the user level and full access (which includes the SELECT, INSERT, UPDATE, and REFERENCE permissions) to the relevant columns. Note that a user who creates a rule does not automatically have plaintext access to the rule itself. You need to explicitly grant access.

Permissions mechanism

• Plaintext Access at User Level: If a user has plaintext access at the user level, they can access plaintext data without being affected by data protection rules.
• Rule-level explicit privileges: A user with explicit privileges on a rule is not affected by that rule and can access the data protected by the rule in plaintext.
• Users without privileges: Users without explicit privileges at the user level or for a specific rule can only view encrypted data when querying columns protected by that rule.

Differences with functional encryption

The ENHANCED_AES_ENCRYPT function is an encryption method of OceanBase Database.

Related documents

• Create a column encryption rule (MySQL mode)
• Create a column encryption rule (Oracle mode)
• Manage column encryption permissions (MySQL mode)
• Column encryption permission management (Oracle mode) in the Azure portal