针对第三方与己方共用 SQL Server 账户（包括 sa）且可能共用服务器的场景，以下是基于 Python 的业务数据加密与排他性功能方案，通过 “数据加密 + 身份识别 + 操作拦截” 三层防护实现安全隔离：
采用 “数据库证书 + 应用层对称密钥” 的双层加密体系，敏感字段加密存储，且解密密钥由 Python 程序控制（不存储在数据库），确保第三方即使获取数据也无法解密。
使用pyodbc库连接 SQL Server，在程序中动态管理对称密钥（密钥密码仅 Python 程序知晓，不写入数据库）：
Python实现SQL Server数据加密解密
import pyodbc
import datetime
from typing import Optional
class SQLDataSecurity:
def __init__(self, conn_str: str, sym_key_password: str):
"""
初始化数据库连接和加密配置
:param conn_str: SQL Server连接字符串
:param sym_key_password: 对称密钥保护密码（仅应用层持有）
"""
self.conn_str = conn_str
self.sym_key_name = "BizSymKey"
self.cert_name = "BizDataCert"
self.sym_key_password = sym_key_password # 关键：不存储在数据库，由程序管理
def _get_connection(self) -> pyodbc.Connection:
"""获取数据库连接"""
return pyodbc.connect(self.conn_str)
def encrypt_data(self, plaintext: str) -> Optional[bytes]:
"""
加密明文数据（如手机号）
:param plaintext: 明文数据
:return: 加密后的二进制数据
"""
conn = None
try:
conn = self._get_connection()
cursor = conn.cursor()
# 1. 打开对称密钥（需证书解密）
cursor.execute(f"""
OPEN SYMMETRIC KEY {self.sym_key_name}
DECRYPTION BY CERTIFICATE {self.cert_name};
""")
# 2. 加密数据（使用对称密钥）
cursor.execute(f"""
SELECT EncryptByKey(Key_GUID('{self.sym_key_name}'), ?) AS encrypted_data;
""", (plaintext,))
result = cursor.fetchone()
return result.encrypted_data if result else None
except Exception as e:
print(f"加密失败: {str(e)}")
return None
finally:
if conn:
# 关闭对称密钥
cursor.execute(f"CLOSE SYMMETRIC KEY {self.sym_key_name};")
conn.close()
def decrypt_data(self, encrypted_data: bytes) -> Optional[str]:
"""
解密密文数据
:param encrypted_data: 加密后的二进制数据
:return: 解密后的明文
"""
conn = None
try:
conn = self._get_connection()
cursor = conn.cursor()
# 1. 打开对称密钥
cursor.execute(f"""
OPEN SYMMETRIC KEY {self.sym_key_name}
DECRYPTION BY CERTIFICATE {self.cert_name};
""")
# 2. 解密数据
cursor.execute(f"""
SELECT CONVERT(NVARCHAR(100), DecryptByKey(?)) AS decrypted_data;
""", (encrypted_data,))
result = cursor.fetchone()
return result.decrypted_data if result else None
except Exception as e:
print(f"解密失败: {str(e)}")
return None
finally:
if conn:
cursor.execute(f"CLOSE SYMMETRIC KEY {self.sym_key_name};")
conn.close()
# 示例用法
if __name__ == "__main__":
# 连接字符串（注意：实际使用中从安全配置中心获取）
CONNECTION_STR = (
"DRIVER={ODBC Driver 17 for SQL Server};"
"SERVER=your_server;"
"DATABASE=YourDatabase;"
"UID=shared_user;" # 共用的数据库账户
"PWD=shared_password;"
)
# 对称密钥保护密码（仅己方程序知晓，不存储在数据库）
SYM_KEY_PASSWORD = "YourSecureKey123!" # 实际应使用更复杂的密钥
# 初始化加密工具
security = SQLDataSecurity(CONNECTION_STR, SYM_KEY_PASSWORD)
# 加密示例：存储手机号
plaintext_phone = "13800138000"
encrypted_phone = security.encrypt_data(plaintext_phone)
if encrypted_phone:
# 插入数据库
conn = pyodbc.connect(CONNECTION_STR)
cursor = conn.cursor()
cursor.execute("INSERT INTO Users (username, phone_encrypted) VALUES (?, ?);",
("张三", encrypted_phone))
conn.commit()
conn.close()
print(f"加密后存储: {encrypted_phone.hex()}")
# 解密示例：读取手机号
conn = pyodbc.connect(CONNECTION_STR)
cursor = conn.cursor()
cursor.execute("SELECT phone_encrypted FROM Users WHERE username = '张三';")
encrypted_data = cursor.fetchone()[0]
conn.close()
decrypted_phone = security.decrypt_data(encrypted_data)
print(f"解密后: {decrypted_phone}") # 输出：13800138000
加密方案核心优势：
- 第三方即使执行
SELECT * FROM Users，获取的phone_encrypted是二进制乱码，因无sym_key_password无法解密；
- 数据库管理员（包括 sa）无法直接查看明文，必须通过己方 Python 程序的解密逻辑；
- 密钥可通过配置中心动态下发，定期轮换，进一步降低泄露风险。
通过 “应用唯一标识 + 动态验证 + 操作审计” 机制，即使第三方使用相同账户和服务器，也能被系统识别并拦截关键操作。
Python实现应用身份验证与操作控制
import pyodbc
import datetime
import socket
import threading
import time
from typing import Optional, Dict
class AppSecurityManager:
def __init__(self, conn_str: str, app_id: str, app_secret: str, heartbeat_interval: int = 300):
"""
初始化应用安全管理器
:param conn_str: 数据库连接字符串
:param app_id: 己方应用唯一标识
:param app_secret: 应用密钥（与数据库中LegitimateApps匹配）
:param heartbeat_interval: 心跳间隔（秒），默认5分钟
"""
self.conn_str = conn_str
self.app_id = app_id
self.app_secret = app_secret
self.client_host = socket.gethostname() # 获取当前服务器主机名
self.heartbeat_interval = heartbeat_interval
self._start_heartbeat() # 启动心跳线程
def _get_connection(self) -> pyodbc.Connection:
return pyodbc.connect(self.conn_str)
def _start_heartbeat(self) -> None:
"""启动定时心跳线程，定期更新数据库中的最后活动时间"""
def heartbeat():
while True:
try:
conn = self._get_connection()
cursor = conn.cursor()
cursor.execute("""
UPDATE LegitimateApps
SET last_heartbeat = GETDATE()
WHERE app_id = ? AND app_secret = ?;
""", (self.app_id, self.app_secret))
conn.commit()
conn.close()
except Exception as e:
print(f"心跳失败: {str(e)}")
time.sleep(self.heartbeat_interval)
# 启动后台线程
threading.Thread(target=heartbeat, daemon=True).start()
print(f"心跳线程启动，间隔{self.heartbeat_interval}秒")
def is_authorized(self) -> bool:
"""验证当前应用是否为授权的合法程序"""
try:
conn = self._get_connection()
cursor = conn.cursor()
cursor.execute("""
SELECT 1
FROM LegitimateApps
WHERE app_id = ?
AND app_secret = ?
AND last_heartbeat >= DATEADD(MINUTE, -10, GETDATE()) -- 10分钟内有心跳
AND ',' + allowed_hosts + ',' LIKE '%,' + ? + ',%'; -- 验证主机名
""", (self.app_id, self.app_secret, self.client_host))
result = cursor.fetchone()
conn.close()
return result is not None
except Exception as e:
print(f"授权验证失败: {str(e)}")
return False
def audit_operation(self, operation: str, details: str, is_authorized: bool) -> None:
"""记录操作审计日志"""
try:
conn = self._get_connection()
cursor = conn.cursor()
cursor.execute("""
INSERT INTO OperationAudit
(app_id, client_host, operation, is_authorized, details)
VALUES (?, ?, ?, ?, ?);
""", (self.app_id, self.client_host, operation, is_authorized, details))
conn.commit()
conn.close()
except Exception as e:
print(f"审计日志记录失败: {str(e)}")
def execute_secure_operation(self, sql: str, params: Dict) -> Optional[pyodbc.Cursor]:
"""
执行安全操作（先验证身份，再执行SQL，最后记录审计）
:param sql: 要执行的SQL语句
:param params: SQL参数（防止注入）
:return: 执行结果游标，未授权则返回None
"""
# 1. 验证身份
if not self.is_authorized():
self.audit_operation(
operation=f"UNAUTHORIZED_{sql.split()[0]}", # 提取操作类型（如INSERT）
details=f"未授权操作: {sql}",
is_authorized=False
)
raise PermissionError("当前应用未授权，操作被拒绝")
# 2. 执行SQL
try:
conn = self._get_connection()
cursor = conn.cursor()
cursor.execute(sql, params)
conn.commit()
# 3. 记录授权操作日志
self.audit_operation(
operation=sql.split()[0],
details=f"授权操作: {sql}",
is_authorized=True
)
return cursor
except Exception as e:
self.audit_operation(
operation=f"FAILED_{sql.split()[0]}",
details=f"操作失败: {str(e)}",
is_authorized=True
)
raise
# 示例用法
if __name__ == "__main__":
# 连接字符串（共用账户）
CONNECTION_STR = (
"DRIVER={ODBC Driver 17 for SQL Server};"
"SERVER=your_server;"
"DATABASE=YourDatabase;"
"UID=shared_user;"
"PWD=shared_password;"
)
# 己方应用标识（第三方无法获取）
APP_ID = "OurBizApp_v1.0"
APP_SECRET = "a1b2c3d4-7890-efgh-ijkl-1234567890ab"
# 初始化安全管理器
security_manager = AppSecurityManager(CONNECTION_STR, APP_ID, APP_SECRET)
# 执行关键操作（如更新订单）
try:
# 安全执行SQL（自动验证身份并记录日志）
cursor = security_manager.execute_secure_operation(
sql="UPDATE Orders SET status = ? WHERE id = ?;",
params={"status": "completed", "id": 1001}
)
print(f"操作成功，影响行数: {cursor.rowcount}")
except PermissionError as e:
print(f"操作被拦截: {str(e)}") # 第三方程序会触发此异常
except Exception as e:
print(f"操作失败: {str(e)}")
在关键表上创建触发器，进一步拦截未授权操作（即使第三方绕过 Python 层直接执行 SQL）：
核心优势：
- 不依赖 SQL Server 的账户权限隔离（因账户共享），而是从业务层构建安全边界；
- 即使第三方获取数据库账户、服务器权限，也会因缺少 “加密密钥” 和 “应用标识” 而无法正常操作；
- 所有异常操作被完整记录，支持事后审计与追责。
实际部署时，需注意密钥的安全管理（如使用 Python 的keyring库存储，避免硬编码），并定期轮换密钥与应用标识。
posted on
浙公网安备 33010602011771号