业务无感知的列加密功能特性说明-OceanBase数据库使用指南

OceanBase 数据库 V4.3.5 BP3 版本新增业务无感知的数据保护功能，数据库管理员可以在表上创建列级别的数据保护规则，当普通用户执行 SELECT 操作，且投影的列中包含了加密列时，数据库会检查该用户是否具有明文访问权限，有明文访问权限则包含此加密列的投影表达式会正常返回明文结果，否则会按照数据保护规则描述的方式加密后再返回给用户。本文主要介绍该特性的详细内容及使用方法和注意事项。

详细说明

前提条件

OceanBase 版本： V4.3.5 BP3（oceanbase-4.3.5.3-103000102025071821）及更高的版本（仅 MySQL 模式租户）。
目前仅 MySQL 模式租户支持列加密。
需要先为该租户开启 TDE 透明加密。

如果没有开启租户 TDE 透明加密的话，会遇到报错：
```
MySQL [test]> select * from tbl1;
ERROR 4360 (HY000): the keystore opened with dont have a master key
```

测试验证

Step 1）为业务 MySQL 租户开启透明表空间加密参数。

$ 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)

Step 2）以 root 超级管理员用户登陆，创建测试表并插入数据。

$ 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)

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]> 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)

Step 3）以 root 超级管理员用户登陆，创建敏感规则。

-- 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; 来赋权一下。

Step 4）创建普通用户，并赋予 all privileges 权限。

注：CREATE SENSITIVE RULE/DROP SENSITIVE RULE 权限会被包含在 all privileges 中，但 PLAINACCESS 不会：

$ 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)

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]> 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)

Step 5）使用 user1 用户登陆，尝试对加密列数据进行读写操作。

$ 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)

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]> 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'@'%'

Step 6）使用 root 超级管理员用户对 user1 赋予 PLAINACCESS 明文访问的权限。

$ 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)

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]> grant PLAINACCESS on *.* to user1;
Query OK, 0 rows affected (0.04 sec)

Step 7）再次使用 user1 用户登陆，尝试对加密列数据进行读写操作。

$ 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)

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]> select * from tbl1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

MySQL [test]> select * from tbl2;
+------+------+
| b    | c    |
+------+------+
|    4 |    4 |
|    5 |    5 |
|    6 |    6 |
+------+------+
3 rows in set (0.00 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)

Step 8）从敏感规则 r1 中删除列 tbl2(c)，同时撤回 user1 用户级的明文访问权限。

$ 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)

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]> 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)

Step 9）再次使用 user1 用户登陆，测试是否可以读取 tbl2 表列数据的明文。

$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)

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]> 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 模式租户）。

业务无感知的列加密功能特性说明

详细说明

前提条件

测试验证

适用版本

相关文档