Overview of column encryption

Column encryption is a data protection mechanism provided by OceanBase Database. It returns encrypted (ciphertext) results according to specified encryption rules, helping to protect sensitive data stored in the database. This topic explains the column encryption feature, how it workd, and provides examples.

How it works

A database administrator or a user with the CREATE SENSITIVE RULE privilege at the user level can create column-level sensitive data protection rules on existing tables. If a column A is specified as a protected column by a data protection rule R, then when a user executes a SELECT statement that includes column A in the projection, the database checks whether the user has plaintext access privilege for rule R:

• If the user has plaintext access privilege for rule R: The projection expression containing column A returns the plaintext result as normal.

• If the user does not have plaintext access privilege for rule R: The projection expression containing column A is encrypted as specified by the rule and then returned to the user.

Data protection rules

You can create a sensitive data protection 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:

• Data protection rule: Currently, only rules of the ENCRYPTION type are supported. You can specify the encryption algorithm when creating a rule.
• Rule scope: Each rule can be configured to protect one or more columns, and may span across different databases and tables. However, each column can be protected by no more than one data protection rule.
• Privilege requirements: To create a rule, the user must have the CREATE SENSITIVE RULE privilege at the user level, as well as full access privileges (SELECT, INSERT, UPDATE, and REFERENCE) on the specified columns. The creator of the rule automatically receives plaintext access privilege for that rule.

Privilege mechanism

• User-level plaintext access privilege: If a user has the user-level plaintext access privilege, the user is not affected by data protection rules and can access data in plaintext.
• Rule-level plaintext access privilege: If a user has the plaintext access privilege for a rule, the user is not affected by that rule and can access the data protected by it in plaintext.
• Users without privileges: If a user does not have the user-level plaintext access privilege and does not have the plaintext access privilege for any rule, the user can only view the encrypted data when querying the columns protected by the rules.

Differences from function-based encryption

The ENHANCED_AES_ENCRYPT function is an AES encryption method provided by OceanBase Database.

References

• Create a column encryption rule
• Manage column encryption privileges