承接前文 OceanBase 安全審計的《傳輸加密》，本文主要實踐資料透明加密，並驗證加密是否有效。
作者：張乾，外星人2號，兼任四位喵星人的鏟屎官。
愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。
本文約 1200 字，預計閱讀需要 4 分鐘。
環境
版本：OceanBase 4.1.0.0 企業版
加密配置
詳細的 加密步驟 略過，本次使用 MySQL 租戶。
開啟透明加密並建立表空間
管理員使用者登入到叢集的 MySQL 租戶。
# 開啟 internal 方式的透明加密
# tde_method 預設值為 none，表示關閉透明表空間加密
obclient [oceanbase]> ALTER SYSTEM SET tde_method='internal';
Query OK, 0 rows affected (0.022 sec)
obclient [oceanbase]> SHOW PARAMETERS LIKE 'tde_method';
+-------+----------+-------------+----------+------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+-------------+----------+------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+
| zone1 | observer | 172.17.0.13 | 2882 | tde_method | NULL | internal | 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 | OBSERVER | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-------------+----------+------------+-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+
1 row in set (0.017 sec)
# 執行該語句，生成主金鑰
obclient [oceanbase]> ALTER INSTANCE ROTATE INNODB MASTER KEY;
Query OK, 0 rows affected (0.028 sec)
# 建立表空間並指定加密演演算法，其中 'y' 表示預設使用 aes-256 演演算法
obclient [oceanbase]> CREATE TABLESPACE sectest_ts1 encryption = 'y';
Query OK, 0 rows affected (0.021 sec)在加密表空間內建立新表
普通使用者登入到資料庫的 MySQL 租戶，建立新表 t1。
# 建立表並指定表空間
obclient [sysbenchdb]> CREATE TABLE t1 (id1 int, id2 int) TABLESPACE sectest_ts1;
Query OK, 0 rows affected (0.076 sec)
# 確認表空間內的表是否標記為加密
# encryptionalg 為 aes-256，且 encrypted 為 YES 則表示表加密配置成功
obclient [oceanbase]> SELECT table_name,encryptionalg,encrypted FROM oceanbase.V$OB_ENCRYPTED_TABLES;
+------------+---------------+-----------+
| table_name | encryptionalg | encrypted |
+------------+---------------+-----------+
| t1 | aes-256 | YES |
+------------+---------------+-----------+
1 row in set (0.048 sec)往表內插入一條值，並做大合併，使值落盤 SSTable。
# 插入值
obclient [sysbenchdb]> insert into t1 values (147852369,999999991);
Query OK, 1 row affected (0.005 sec)
# 做大合併
ALTER SYSTEM MAJOR FREEZE TENANT=ALL;
# 檢視合併進度
SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION\G建立一個不加密的表用以對比
普通使用者登入到資料庫的 MySQL 租戶，建立不指定加密空間的新表 ttttttt2。
同樣插入一條資料，並做大合併。
obclient [sysbenchdb]> CREATE TABLE ttttttt2 (id1 int, id2 int);
Query OK, 0 rows affected (0.076 sec)
obclient [sysbenchdb]> insert into ttttttt2 values (147852369,999999991);
Query OK, 1 row affected (0.005 sec)
# 做大合併
ALTER SYSTEM MAJOR FREEZE TENANT=ALL;
# 檢視合併進度
SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION\G加密驗證
驗證方式是藉助工具 ob_admin，其 dumpsst 功能可以顯示 block_file 檔案中的內容。
使用 dumpsst 來檢視加密表的內容，驗證是否加密。
使用前需要知道目標資料的 macro block id，接下來先找到上面資料對應的 macro block id。
查詢 macro block id
先根據 oceanbase.DBA_OB_TABLE_LOCATIONS 找到兩張表的 TABLET_ID，其中加密表 t1 的 TABLET_ID 為 200001，未加密表 ttttttt2 的 TABLET_ID 為 200002。
obclient [oceanbase]> select * from oceanbase.DBA_OB_TABLE_LOCATIONS where TABLE_NAME='t1';
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+-------------+----------+--------+--------------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+-------------+----------+--------+--------------+
| sysbenchdb | t1 | 500006 | USER TABLE | NULL | NULL | NULL | NULL | 200001 | 1001 | zone1 | 172.17.0.13 | 2882 | LEADER | FULL |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+-------------+----------+--------+--------------+
1 row in set (0.005 sec)
obclient [oceanbase]> select * from oceanbase.DBA_OB_TABLE_LOCATIONS where TABLE_NAME='ttttttt2';
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+-------------+----------+--------+--------------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+-------------+----------+--------+--------------+
| sysbenchdb | ttttttt2 | 500007 | USER TABLE | NULL | NULL | NULL | NULL | 200002 | 1001 | zone1 | 172.17.0.13 | 2882 | LEADER | FULL |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+-------------+----------+--------+--------------+
1 row in set (0.005 sec)拿著 TABLET_ID，根據合併時間，在 GV$OB_TABLET_COMPACTION_HISTORY[](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1... "GV$OB_TABLET_COMPACTION_HISTORY") 中找到 MACRO_ID_LIST，其中記錄的 ID 即是我們需要的 macro block id。
從輸出中，我們可以看到加密表 t1 對應的 macro block id 為 387，未加密表 ttttttt2 對應的 macro block id 為 718。
obclient [oceanbase]> select * from GV$OB_TABLET_COMPACTION_HISTORY where TABLET_ID=200001 and TYPE='MAJOR_MERGE' order by START_TIME \G
*************************** 1. row ***************************
SVR_IP: 172.17.0.13
SVR_PORT: 2882
TENANT_ID: 1004
LS_ID: 1001
TABLET_ID: 200001
TYPE: MAJOR_MERGE
COMPACTION_SCN: 1685093467526445446
START_TIME: 2023-05-26 17:31:22.478149
FINISH_TIME: 2023-05-26 17:31:22.482045
TASK_ID: YB42AC11000D-0005FC95091493EB-0-0
OCCUPY_SIZE: 432
MACRO_BLOCK_COUNT: 1
MULTIPLEXED_MACRO_BLOCK_COUNT: 0
NEW_MICRO_COUNT_IN_NEW_MACRO: 1
MULTIPLEXED_MICRO_COUNT_IN_NEW_MACRO: 0
TOTAL_ROW_COUNT: 1
INCREMENTAL_ROW_COUNT: 1
COMPRESSION_RATIO: 0.67
NEW_FLUSH_DATA_RATE: 100
PROGRESSIVE_COMPACTION_ROUND: 1
PROGRESSIVE_COMPACTION_NUM: 0
PARALLEL_DEGREE: 1
PARALLEL_INFO: -
PARTICIPANT_TABLE: table_cnt=4,[MAJOR]scn=1;[MINI]start_scn=1,end_scn=1685093478867382402;
MACRO_ID_LIST: 387
COMMENTS: serialize_medium_list:{cnt=1;1685093467526445446}|time_guard=EXECUTE=4.20ms|(0.79)|CREATE_SSTABLE=648us|(0.12)|total=5.32ms;
*************************** 2. row ***************************
SVR_IP: 172.17.0.13
SVR_PORT: 2882
TENANT_ID: 1004
LS_ID: 1001
TABLET_ID: 200001
TYPE: MAJOR_MERGE
COMPACTION_SCN: 1685094492266634220
START_TIME: 2023-05-26 17:48:27.276906
FINISH_TIME: 2023-05-26 17:48:27.282468
TASK_ID: YB42AC11000D-0005FC9509149878-0-0
OCCUPY_SIZE: 432
MACRO_BLOCK_COUNT: 1
MULTIPLEXED_MACRO_BLOCK_COUNT: 0
NEW_MICRO_COUNT_IN_NEW_MACRO: 1
MULTIPLEXED_MICRO_COUNT_IN_NEW_MACRO: 0
TOTAL_ROW_COUNT: 1
INCREMENTAL_ROW_COUNT: 1
COMPRESSION_RATIO: 0.67
NEW_FLUSH_DATA_RATE: 71
PROGRESSIVE_COMPACTION_ROUND: 1
PROGRESSIVE_COMPACTION_NUM: 0
PARALLEL_DEGREE: 1
PARALLEL_INFO: -
PARTICIPANT_TABLE: table_cnt=3,[MAJOR]scn=1685093467526445446;[MINI]start_scn=1685093467530410154,end_scn=1685094504683817069;
MACRO_ID_LIST: 718
COMMENTS: serialize_medium_list:{cnt=1;1685094492266634220}|time_guard=EXECUTE=5.92ms|(0.45)|CREATE_SSTABLE=5.94ms|(0.45)|total=13.10ms;
obclient [oceanbase]> select * from GV$OB_TABLET_COMPACTION_HISTORY where TABLET_ID=200002 and TYPE='MAJOR_MERGE' order by START_TIME \G
*************************** 1. row ***************************
SVR_IP: 172.17.0.13
SVR_PORT: 2882
TENANT_ID: 1004
LS_ID: 1001
TABLET_ID: 200002
TYPE: MAJOR_MERGE
COMPACTION_SCN: 1685094492266634220
START_TIME: 2023-05-26 17:48:27.277801
FINISH_TIME: 2023-05-26 17:48:27.284542
TASK_ID: YB42AC11000D-0005FC9509149879-0-0
OCCUPY_SIZE: 424
MACRO_BLOCK_COUNT: 1
MULTIPLEXED_MACRO_BLOCK_COUNT: 0
NEW_MICRO_COUNT_IN_NEW_MACRO: 1
MULTIPLEXED_MICRO_COUNT_IN_NEW_MACRO: 0
TOTAL_ROW_COUNT: 1
INCREMENTAL_ROW_COUNT: 1
COMPRESSION_RATIO: 0.61
NEW_FLUSH_DATA_RATE: 40
PROGRESSIVE_COMPACTION_ROUND: 1
PROGRESSIVE_COMPACTION_NUM: 0
PARALLEL_DEGREE: 1
PARALLEL_INFO: -
PARTICIPANT_TABLE: table_cnt=4,[MAJOR]scn=1685093467526445446;[MINI]start_scn=1,end_scn=1685094504683817070;
MACRO_ID_LIST: 718
COMMENTS: serialize_medium_list:{cnt=1;1685094492266634220}|time_guard=EXECUTE=10.20ms|(0.86)|total=11.87ms;
解析 block_file 檔案
安裝完 ob_admin，使用 dumpsst 解析上個步驟拿到的 macro block id。
注意：
- ob_admin dumpsst 必須在 ${path_to_oceanbase}/oceanbase 層級執行，原因是讀取
etc/observer.config.bin使用的是相對路徑。- 目前測試下來，必須指定
--macro-id，否則都會報錯（報錯內容需在 ob_admin.log 中檢視）。
介紹本次使用的幾個引數如下：
-f指定 data 目錄。-d宏塊型別，目前僅支援 macro_block。-a即macro-id，填寫上面步驟中獲取的值。-t指定tablet_id，進一步精確範圍。-i即micro block id，-1 表示所有 micro blocks。
解析 t1 表，即加密表
可以看到輸出中 tablet_id 為 200001，row_count 為 1，對應我們插入的那一條資料。
其中並未展示這行資料內容，驗證資料成功加密。
[admin@ob_4 oceanbase]$ ob_admin dumpsst -f /home/admin/oceanbase/store/obdemo/ -d macro_block -a 387 -t 200001 -i -1
succ to open, filename=ob_admin.log, fd=3, wf_fd=2
old log_file need close, old = ob_admin.log new = ob_admin.log
succ to open, filename=ob_admin.log, fd=3, wf_fd=2
succ to open, filename=ob_admin_rs.log, fd=4, wf_fd=2
------------------------------{Common Header}------------------------------
| header_size|24
| version|1
| magic|1001
| attr|1
| payload_size|952
| payload_checksum|-1027413104
--------------------------------------------------------------------------------
------------------------------{SSTable Macro Block Header}------------------------------
| header_size|208
| version|1
| magic|1007
| tablet_id|200001
| logical_version|1685093467526445446
| data_seq|0
| column_count|5
| rowkey_column_count|3
| row_store_type|1
| row_count|1
| occupy_size|432
| micro_block_count|1
| micro_block_data_offset|232
| data_checksum|2617981320
| compressor_type|6
| master_key_id|500004
--------------------------------------------------------------------------------
--------{column_index column_type column_order column_checksum collation_type}----------
| [0 ObUInt64Type ASC 3344869974 63]
| [1 ObIntType ASC 313654433 63]
| [2 ObIntType ASC 2388842353 63]
| [3 ObInt32Type ASC 2776795072 63]
| [4 ObInt32Type ASC 82537422 63]
--------------------------------------------------------------------------------解析 ttttttt2 表，即未加密的表
替換命令中 tablet_id 和 macro block id 為 ttttttt2 表的 id，進行解析。
對比加密表 t1，未加密表輸出資訊更豐富，並且可以看到具體的資料內容。
此處精簡展示，可以看到 Total Rows 中顯示了前面插入的那條資料{"INT":147852369}。
[admin@ob_4 oceanbase]$ ob_admin dumpsst -f /home/admin/oceanbase/store/obdemo/ -d macro_block -a 718 -t 200002 -i -1
succ to open, filename=ob_admin.log, fd=3, wf_fd=2
old log_file need close, old = ob_admin.log new = ob_admin.log
succ to open, filename=ob_admin.log, fd=3, wf_fd=2
succ to open, filename=ob_admin_rs.log, fd=4, wf_fd=2
------------------------------{Common Header}------------------------------
| header_size|24
| version|1
| magic|1001
| attr|1
| payload_size|892
| payload_checksum|-1696352947
--------------------------------------------------------------------------------
------------------------------{SSTable Macro Block Header}------------------------------
| header_size|208
| version|1
| magic|1007
| tablet_id|200002
| logical_version|1685094492266634220
| data_seq|0
| column_count|5
| rowkey_column_count|3
| row_store_type|1
| row_count|1
| occupy_size|424
| micro_block_count|1
| micro_block_data_offset|232
| data_checksum|725485397
| compressor_type|6
| master_key_id|0
--------------------------------------------------------------------------------
……
------------------------------{Total Rows[1]}------------------------------
|ROW[0]:trans_id=[{txid:0}],dml_flag=[N|INSERT],mvcc_flag=[]|[{"BIGINT UNSIGNED":1}][{"BIGINT":-1685094482154160502}][{"BIGINT":0}][{"INT":147852369}][{"INT":999999991}]
……
------------------------------{Encoding Column Header[4]}------------------------------
| type|0
| attribute|0
| is fix length|0
| has extend value|0
| is bit packing|0
| is last var field|0
| extend value index|65542
| store object type|0
| offset|0
| length|0
--------------------------------------------------------------------------------
------------------------------{Index Micro Block[0]}------------------------------
------------------------------{Total Rows[1]}------------------------------
|ROW[0]:trans_id=[{txid:0}],dml_flag=[N|INSERT],mvcc_flag=[]|[{"BIGINT UNSIGNED":1}][{"BIGINT":-1685094482154160502}][{"BIGINT":0}][{"VARCHAR":"
", collation:"binary", coercibility:"NUMERIC"}]
|Index Block Row Header|[{version:1, row_store_type:1, compressor_type:6, is_data_index:1, is_data_block:1, is_leaf_block:0, is_major_node:1, is_pre_aggregated:0, is_deleted:0, contain_uncommitted_row:0, is_macro_node:0, has_string_out_row:0, all_lob_in_row:1, macro_id:[-1](ver=0,mode=0,seq=0), block_offset:232, block_size:192, master_key_id:0, encrypt_id:0, encrypt_key:"data_size:16, data:00000000000000000000000000000000", row_count:1, schema_version:1685094464567160, macro_block_count:0, micro_block_count:1}]
------------------------------{Macro Meta Micro Block}------------------------------
------------------------------{Encoding Micro Header}------------------------------
| header_size|96
| version|2
| magic|1005
| column_count|4
| rowkey_column_count|3
| row_count|1
| row_store_type|2
| row_index_byte|0
| var_column_count|0
| row_data_offset|357
|column_chksum[ 0]|3344869974
|column_chksum[ 1]|1868627082
|column_chksum[ 2]|2388842353
|column_chksum[ 3]|1583982749
--------------------------------------------------------------------------------
……小結
本文主要是使用 ob_admin 工具的 dumpsst 功能解析 block_file，驗證了 OceanBase 資料透明加密功能。
使用 dumpsst 過程中碰到問題，建議多關注 ob_admin.log，對於排查比較有幫助。