--- title: 业务无感知的列加密功能特性说明-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 业务无感知的列加密功能特性说明相关的常见问题和使用技巧，帮助您快速解决 业务无感知的列加密功能特性说明的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 业务无感知的列加密功能特性说明 更新时间：2026-05-09 09:36 适用版本： V4.3.x、V4.4.x 内容类型：TechNote OceanBase 数据库 V4.3.5 BP3 版本新增业务无感知的数据保护功能，数据库管理员可以在表上创建列级别的数据保护规则，当普通用户执行 SELECT 操作，且投影的列中包含了加密列时，数据库会检查该用户是否具有明文访问权限，有明文访问权限则包含此加密列的投影表达式会正常返回明文结果，否则会按照数据保护规则描述的方式加密后再返回给用户。 本文主要介绍该特性的详细内容及使用方法和注意事项。 ## 详细说明 ### 前提条件 - **OceanBase 版本：** V4.3.5 BP3（oceanbase-4.3.5.3-103000102025071821）及更高的版本（仅 MySQL 模式租户）。 - 目前仅 MySQL 模式租户支持列加密。 - 需要先为该租户开启 TDE 透明加密。 如果没有开启租户 TDE 透明加密的话，会遇到报错： ```shell MySQL [test]> select * from tbl1; ERROR 4360 (HY000): the keystore opened with dont have a master key ``` ### 测试验证 1. **Step 1）为业务 MySQL 租户开启透明表空间加密参数**。 ```shell $ mysql -h127.0.0.1 -P2881 -uroot@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221633648 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [test]> show parameters like 'tde_method'\G *************************** 1. row *************************** zone: zone1 svr_type: observer svr_ip: 11.166.87.226 svr_port: 2882 name: tde_method data_type: STRING value: none info: none : transparent encryption is none, none means cannot use tde, internal : transparent encryption is in the form of internal tables, bkmi : transparent encryption is in the form of external bkmi section: OBSERVER scope: TENANT source: DEFAULT edit_level: DYNAMIC_EFFECTIVE default_value: none isdefault: 1 1 row in set (0.00 sec) MySQL [test]> alter system set tde_method='internal'; -- 首次执行需要等待约 20 秒使秘钥生效 Query OK, 0 rows affected (0.01 sec) ``` 2. **Step 2）以 root 超级管理员用户登陆，创建测试表并插入数据**。 ```shell $ mysql -h127.0.0.1 -P2881 -uroot@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221682741 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) MySQL [test]> create table tbl1 (a int); Query OK, 0 rows affected (0.09 sec) MySQL [test]> create table tbl2 (b int, c int); Query OK, 0 rows affected (0.10 sec) MySQL [test]> insert into tbl1 values (1),(2),(3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 MySQL [test]> insert into tbl2 values (4,4),(5,5),(6,6); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 MySQL [test]> select * from tbl1; +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) MySQL [test]> select * from tbl2; +------+------+ | b | c | +------+------+ | 4 | 4 | | 5 | 5 | | 6 | 6 | +------+------+ 3 rows in set (0.01 sec) ``` 3. **Step 3）以 root 超级管理员用户登陆，创建敏感规则**。 ```shell -- root 用户默认就拥有 CREATE SENSITIVE RULE/DROP SENSITIVE RULE 的权限，且不能被 revoke MySQL [test]> create sensitive rule r1 on tbl1(a), tbl2(b, c) using encryption; Query OK, 0 rows affected (0.06 sec) -- root 用户默认就可以访问所有加密列的明文数据，不受数据保护规则的影响，且不能被 revoke MySQL [test]> select * from tbl1; +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) MySQL [test]> select * from tbl2; +------+------+ | b | c | +------+------+ | 4 | 4 | | 5 | 5 | | 6 | 6 | +------+------+ 3 rows in set (0.00 sec) ``` **注意：如果是从低版本升级上来的租户，目前升级流程中没有为 root 用户赋予 `CREATE SENSITIVE RULE` 权限，需要先执行 `grant CREATE SENSITIVE RULE on *.* to root;` 来赋权一下**。 4. **Step 4）创建普通用户，并赋予 `all privileges` 权限**。 **注：`CREATE SENSITIVE RULE/DROP SENSITIVE RULE` 权限会被包含在 `all privileges` 中，但 `PLAINACCESS` 不会**： ```shell $ mysql -h127.0.0.1 -P2881 -uroot@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221494119 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) MySQL [test]> create user user1 identified by "xxx"; Query OK, 0 rows affected (0.09 sec) MySQL [test]> grant all privileges on *.* to user1; Query OK, 0 rows affected (0.07 sec) ``` 5. **Step 5）使用 user1 用户登陆，尝试对加密列数据进行读写操作**。 ```shell $ mysql -h127.0.0.1 -P2881 -uuser1@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221622439 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) MySQL [test]> select current_user(); +----------------+ | current_user() | +----------------+ | user1@% | +----------------+ 1 row in set (0.00 sec) -- 当用户没有规则 R1 的明文访问权限时，只能读取到加密列的密文 MySQL [test]> select * from tbl1; +--------------------------+ | a | +--------------------------+ | 0▒ ▒m|▒OLG7▒▒ ▒ ▒ | | 0▒ UM▒▒▒M▒▒ǐ[ | | 0▒ ▒▒▒▒H▒SI▒▒2▒ | +--------------------------+ 3 rows in set (0.00 sec) MySQL [test]> select * from tbl2; +--------------------------+--------------------------+ | b | c | +--------------------------+--------------------------+ | 0▒ X▒)B|**▒▒T▒R | 0▒ X▒)B|**▒▒T▒R | | 0▒ ▒▒▒@w▒▒▒ކ▒▒▒ | 0▒ ▒▒▒@w▒▒▒ކ▒▒▒ | | 0▒ Ö\▒p▒▒C▒<▒[▒ | 0▒ Ö\▒p▒▒C▒<▒[▒ | +--------------------------+--------------------------+ 3 rows in set (0.00 sec) -- 子查询或视图也会受到数据保护规则的影响 MySQL [test]> select * from (select a as c from tbl1); +------------------------------------------+ | c | +------------------------------------------+ ▒▒e▒▒Y▒▒b▒Hi▒"▒▒+▒ | | 0▒ ▒.P▒▒▒▒F_▒ ▒uI▒▒}▒7▒ȮF6▒▒▒▒H | | 0▒ ▒▒▒▒91▒|▒▒В0j▒j׽I▒▒*▒ | < +------------------------------------------+ 3 rows in set (0.00 sec) MySQL [test]> select c from (select a as c from tbl1); +------------------------------------------+ | c | +------------------------------------------+ ▒▒e▒▒Y▒▒b▒Hi▒"▒▒+▒ | | 0▒ ▒.P▒▒▒▒F_▒ ▒uI▒▒}▒7▒ȮF6▒▒▒▒H | | 0▒ ▒▒▒▒91▒|▒▒В0j▒j׽I▒▒*▒ | < +------------------------------------------+ 3 rows in set (0.00 sec) -- 查询 tbl1.a + 1，这个投影列会整体加密后再返回给用户 -- 注意不是先对 tbl1.a 加密，再做加法 MySQL [test]> SELECT a + 1 FROM tbl1; +--------------------------+ | a + 1 | +--------------------------+ | 0▒ UM▒▒▒M▒▒ǐ[ | | 0▒ ▒▒▒▒H▒SI▒▒2▒ | | 0▒ X▒)B|**▒▒T▒R | +--------------------------+ 3 rows in set (0.00 sec) -- 用户也不能对没有明文访问权限的列做 insert/update 等操作 MySQL [test]> insert into tbl1 values (4); ERROR 11098 (HY000): Access sensitive field protected by sensitive rule 'r1' denied to user 'user1'@'%' MySQL [test]> update tbl1 set a = a + 1; ERROR 11098 (HY000): Access sensitive field protected by sensitive rule 'r1' denied to user 'user1'@'%' ``` 6. **Step 6）使用 root 超级管理员用户对 user1 赋予 `PLAINACCESS` 明文访问的权限**。 ```shell $ mysql -h127.0.0.1 -P2881 -uroot@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221544737 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) MySQL [test]> grant PLAINACCESS on *.* to user1; Query OK, 0 rows affected (0.04 sec) ``` 7. **Step 7）再次使用 user1 用户登陆，尝试对加密列数据进行读写操作**。 ```shell $ mysql -h127.0.0.1 -P2881 -uuser1@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221570080 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) MySQL [test]> select * from tbl1; +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.01 sec) MySQL [test]> update tbl1 set a = a + 1; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 MySQL [test]> select * from tbl1; +------+ | a | +------+ | 2 | | 3 | | 4 | +------+ 3 rows in set (0.00 sec) ``` 8. **Step 8）从敏感规则 r1 中删除列 tbl2(c)，同时撤回 user1 用户级的明文访问权限**。 ```shell $ mysql -h127.0.0.1 -P2881 -uroot@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221643045 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) MySQL [test]> alter sensitive rule r1 drop column tbl2(c); Query OK, 0 rows affected (0.06 sec) MySQL [test]> show sensitive rules; +-----------+-------------------+---------+---------+----------------------------+ | rule_name | protection_policy | method | enabled | protected_columns | +-----------+-------------------+---------+---------+----------------------------+ | r1 | ENCRYPTION | aes-256 | YES | test.tbl1(a), test.tbl2(b) | +-----------+-------------------+---------+---------+----------------------------+ 1 row in set (0.05 sec) MySQL [test]> revoke PLAINACCESS on *.* from user1; Query OK, 0 rows affected (0.04 sec) MySQL [test]> show sensitive rules; +-----------+-------------------+---------+---------+----------------------------+ | rule_name | protection_policy | method | enabled | protected_columns | +-----------+-------------------+---------+---------+----------------------------+ | r1 | ENCRYPTION | aes-256 | YES | test.tbl1(a), test.tbl2(b) | +-----------+-------------------+---------+---------+----------------------------+ 1 row in set (0.02 sec) ``` 9. **Step 9）再次使用 user1 用户登陆，测试是否可以读取 tbl2 表列数据的明文**。 ```shell $mysql -h127.0.0.1 -P2881 -uuser1@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221541245 Server version: 5.7.25 OceanBase 4.3.5.3 (r103000102025071821-4b8c513fcc2194bad9eb2f93c789040f6dd01f11) (Built Jul 18 2025 21:13:03) MySQL [test]> select current_user(); +----------------+ | current_user() | +----------------+ | user1@% | +----------------+ 1 row in set (0.01 sec) MySQL [test]> select b from tbl2; +--------------------------+ | b | +--------------------------+ | 0▒ X▒)B|**▒▒T▒R | | 0▒ ▒▒▒@w▒▒▒ކ▒▒▒ | | 0▒ Ö\▒p▒▒C▒<▒[▒ | +--------------------------+ 3 rows in set (0.01 sec) MySQL [test]> select c from tbl2; +------+ | c | +------+ | 4 | | 5 | | 6 | +------+ 3 rows in set (0.00 sec) ``` ## 适用版本 **OceanBase 数据库 V4.3.x 系列：** V4.3.5 BP3（oceanbase-4.3.5.3-103000102025071821）及更高的版本（仅 MySQL 模式租户）。 ## 相关文档 - 有关列加密权限管理详情说明，参见：[列加密权限管理](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000003568970)。 - 有关列加密功能常见问题详情说明，参见：[列加密常见问题](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000003568971)。 Previous [执行超大 SQL，审计日志无法审计到，OBServer 日志报错 ret=-4024 的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001953378) Next [Oracle 租户授权 FORCE ANY TRANSACTION 出现语法错误](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002395806) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线