DBAPI 實現不同角色控制查看表的不同列

場景說明

在數據庫管理系統中，對表進行列級別的權限控制是一項關鍵的安全措施，特別是在處理敏感數據或需要遵守特定數據訪問控制策略的情況下。合理的列權限控制不僅能保護敏感信息，還能幫助組織滿足合規性要求。

以下是一些典型的應用場景：

敏感數據保護

當表中包含敏感信息（如個人身份信息、財務數據、醫療記錄等）時，通過列權限控制，只允許特定角色或用戶訪問這些列，可以有效防止數據泄露，降低數據安全風險。

合規性要求

某些行業或地區有嚴格的數據保護法規（如 GDPR、HIPAA 等），要求對敏感數據的訪問進行嚴格控制。列權限控制可以幫助組織滿足這些合規性要求，避免因數據泄露而面臨的法律風險。

數據分層訪問

在大型企業中，不同部門或用戶角色可能只需要訪問表中的部分列。通過列權限控制，可以確保用戶只能訪問其工作所需的列，實現最小權限原則，減少不必要的數據訪問風險。

實現方案

DBAPI 提供了 API 編排、動態 SQL、數組傳參的功能，通過這些功能，可以實現列權限控制。
為了實現列權限可以動態配置，我們采用以下方案：

1. 動態權限配置：在數據庫中維護一張權限配置表，用于配置不同用戶可以訪問的列，便于權限信息的動態修改。
2. API 編排實現：創建兩個獨立的 API，并通過編排順序執行。第一個 API 查詢用戶擁有的權限列，第二個 API 根據這些權限列查詢表數據。

實施示例

1. 創建商品信息表

首先準備一張商品信息表 products，包含以下列：

字段名	類型	說明
id	int	商品 ID
name	varchar	商品名稱
description	text	商品描述
category	varchar	商品分類
cost	decimal	進貨成本（敏感數據）
supplier	varchar	供應商（敏感數據）

示例數據如下：

id	name	description	category	cost	supplier
1	iPhone 13 Pro	高性能手機	手機	9999	apple
2	MacBook Pro	性能 Strong	電腦	19999	apple
3	Mate 60	高性能手機	手機	6999	華為

其中，商品的進貨成本 cost 列和供應商 supplier 列為保密數據，需要根據用戶的角色進行權限控制。

2. 創建權限配置表

創建權限配置表 permission_config，用于記錄用戶對表列的權限信息：

字段名	類型	說明
user_id	bigint	用戶 ID
table_name	varchar	表名
column_name	varchar	列名

示例數據如下：

user_id	table_name	column_name
1	products	cost
1	products	supplier
2	products	supplier

3. 創建查詢用戶權限列的 API

創建 API，配置參數 userid，類型為 bigint，SQL 查詢語句如下：

SELECT column_name FROM permission_config WHERE user_id = #{userid} and table_name = 'products'

該 API 用于獲取指定用戶有權訪問的所有列名。

數據格式轉換

配置完成后，我們進行請求測試，發現返回的數據格式為：

{"data": [{ "column_name": "cost" }, { "column_name": "supplier" }],"msg": null,"success": true
}

但實際上我們期望的數據格式是簡潔的數組形式：["cost", "supplier"]。

為了實現這一目標，我們需要對 API 返回的數據進行格式轉換。在 API 配置中找到"全局插件 - 數據轉換"選項，填寫以下 Groovy 腳本：

data.data.collect { it.column_name }

這段 groovy 代碼的意思是將返回的數據中的 data 屬性進行循環， 提取每個元素的column_name 字段，并返回一個包含這些提取值的數組。

并將腳本語言選擇為 Groovy：

配置完成后保存 API，并再次進行請求測試。可以看到返回的數據格式已經轉換為我們期望的數組形式：

通過這樣的數據轉換處理，我們成功將原始的復雜 JSON 結構簡化為便于后續處理的字符串數組格式，為下一步的數據查詢操作提供了便利。

4. 創建查詢表數據的 API

創建 API，配置參數 authedColumns，類型為 Array<string>，SQL 查詢語句如下：

SELECT name, description,
<foreach collection="authedColumns" item="column" separator=",">${column}
</foreach>
FROM products

注意事項：

• 使用 <foreach> 標簽遍歷 authedColumns 數組，將每個列名拼接成 SQL 語句中要查詢的列名
• 必須使用 ${column} 來動態替換列名，不能使用 #{column}

請求測試 API，可以看到 API 正常返回數據：

5. 創建編排 API

按照以下步驟創建編排 API：

步驟 1：配置基礎參數

創建編排 API，配置參數 userid，類型為 bigint：

步驟 2：添加開始節點

點擊添加開始節點，作為流程的起點：

步驟 3：添加權限查詢節點

1. 點擊添加 API 節點，編輯 API 節點，選擇第一個 API（查詢用戶權限列）
2. 配置參數 userid 為 parameters.userid
3. 配置節點 ID 為 getAuthedColumns

parameters 表示整個 API 的參數，parameters.userid 表示取參數 userid 的值

步驟 4：添加數據查詢節點

1. 點擊添加 API 節點，選擇第二個 API（查詢表數據）
2. 配置參數 authedColumns 為 results.getAuthedColumns
3. 配置節點 ID 為 getData

results 表示所有 API 節點的返回結果，results.getAuthedColumns 表示取第一個 API 的返回結果

步驟 5：添加結束節點

1. 點擊添加結束節點
2. 配置數據腳本為 results.getData.data

results 表示所有 API 節點的返回結果，results.getData.data 表示取第二個 API 的返回結果中的 data 字段的值

步驟 6：連接節點

使用連線將所有節點按順序連接起來：開始節點 → getAuthedColumns 節點 → getData 節點 → end 節點

最后點擊保存并發布編排 API：

預期效果

通過以上配置，不同用戶將看到不同的數據列：

• 用戶 1：可以查看 cost 和 supplier 列

• 用戶 2：只能查看 supplier 列

這種實現方式既保證了數據的安全性，又提供了靈活的權限管理機制。

總結與優化

處理空列權限的 SQL 語法問題

對于第二個 API（查詢表數據的 API），當某個用戶沒有可訪問的敏感列權限時，會生成如下 SQL 語句：

SELECT name, description, FROM products

這個 SQL 語句存在語法錯誤（末尾多余的逗號），會導致查詢執行失敗。

解決方案

DBAPI 提供了類似 MyBatis 的動態 SQL 功能，其中的 trim 標簽可以優雅地解決這個問題。修改后的 SQL 如下：

SELECT
<trim prefix="" suffix="" suffixesToOverride="," prefixesToOverride="">name, description,<foreach collection="authedColumns" item="column" separator=",">${column}</foreach>
</trim>
FROM products

trim 標簽詳解

trim 標簽是 DBAPI 中一個強大的動態 SQL 處理工具，其主要屬性包括：

• prefix：在包裹內容前添加指定前綴
• suffix：在包裹內容后添加指定后綴
• suffixesToOverride：指定需要去除的后綴字符
• prefixesToOverride：指定需要去除的前綴字符

在這個場景中，我們將 suffixesToOverride 屬性設置為逗號,，表示當 trim 標簽包裹的內容以逗號結尾時，會自動去除結尾的逗號，從而避免 SQL 語法錯誤。

這種動態 SQL 處理機制確保了無論用戶擁有多少列權限，生成的 SQL 語句都是語法正確的，大大提高了系統的健壯性和用戶體驗。

根據客戶端身份獲取權限

基于用戶ID參數獲取權限存在安全風險，客戶端可能通過修改userid參數獲取未授權的數據。更安全的做法是根據客戶端身份（clientId）來確定數據訪問權限。

1. 創建并配置客戶端

首先創建兩個客戶端并授權其訪問相應的API分組：

2. 調整權限配置表結構

修改權限配置表 permission_config，使用client_id替代user_id來配置列權限：

字段名	類型	說明
client_id	varchar	客戶端 ID
table_name	varchar	表名
column_name	varchar	列名

示例數據：

client_id	table_name	column_name
CMlWBCb0v7U3goZG	products	cost
CMlWBCb0v7U3goZG	products	supplier
hTAOTQ5DmEb7IXMQ	products	supplier

3. 更新權限查詢API

調整權限查詢API配置：

• 移除userid參數
• 將API權限設置為私有
• 更新SQL語句使用系統內置變量__clientId：

SELECT column_name FROM permission_config 
WHERE client_id = #{__clientId} and table_name = 'products'

提示：SQL中可直接使用系統內置變量__clientId獲取當前請求的客戶端ID。

4. 驗證權限控制效果

使用不同客戶端訪問API，會得到不同的數據列權限：

注意：訪問私有API必須攜帶有效的token，系統通過token識別客戶端身份。