인증, 매개변수화된 쿼리, 데이터 보호에 관한 모범 사례를 따라 mssql-python 애플리케이션을 안전하게 보호하세요.
가능하면 비밀번호 없는 인증부터 시작하세요. 로컬 .env 파일과 SQL 비밀번호는 임시 개발 도구로 간주하고, 코드가 공유 환경에 도달하기 전에 비밀을 관리 신원이나 비밀 저장소로 옮기세요.
인증 보안
SQL 인증 대신 Microsoft Entra 인증을 사용하세요
Microsoft Entra 인증은 저장된 비밀번호를 제거하고 관리되는 신원을 지원합니다. 모든 환경에서 SQL 인증보다 이 방식을 선호합니다.
Azure에서 호스팅되는 워크로드에는 ActiveDirectoryMSI와 함께 관리 ID를 사용하세요. 저장된 비밀이 필요 없고 자격 증명 체인을 거치지 않고도 연결됩니다:
import mssql_python
def connect_with_managed_identity():
return mssql_python.connect(
"Server=<server>.database.windows.net;"
"Database=<database>;"
"Authentication=ActiveDirectoryMSI;"
"Encrypt=yes;"
)
로컬 개발에는 Azure CLI 또는 다른 개발자 자격 증명을 자동으로 사용하는 ActiveDirectoryDefault를 사용하세요. 운영 환경에서는 피하세요. 첫 DefaultAzureCredential 연결에서 각 자격 증명 제공자를 순서대로 시도하기 때문에 운영 워크로드에는 필요하지 않은 지연 시간이 발생합니다:
import mssql_python
def connect_with_default_credential():
return mssql_python.connect(
"Server=<server>.database.windows.net;"
"Database=<database>;"
"Authentication=ActiveDirectoryDefault;"
"Encrypt=yes;"
)
피하세요: SQL 인증은 코드나 설정에 자격 증명을 저장하며 유출에 취약합니다.
conn = mssql_python.connect("Server=...;UID=user;PWD=password")
절대 자격 증명을 하드코딩하지 마세요
환경 변수는 지역 개발에만 사용하세요. 공유 환경에서는 비밀번호 없는 인증을 선호합니다. 레거시 SQL 인증 흐름이 불가피할 경우, 전체 연결 문자열을 소스 컨트롤로 확인하는 대신 런타임에 비밀 저장소에서 비밀을 가져오세요.
다음 접근법은 자격 증명을 하드코딩하며 절대 사용해서는 안 됩니다:
conn_str = "Server=<server>;UID=<login>;PWD=<password>"
로컬 개발을 위해 환경 변수에서 자격 증명을 읽으세요:
import os
conn_str = (
f"Server={os.environ['DB_SERVER']};"
f"Database={os.environ['DB_NAME']};"
)
공유 환경이 여전히 비밀이 필요한 경우, 런타임에 Azure Key Vault에서 이를 가져오세요:
from azure.keyvault.secrets import SecretClient
from azure.identity import DefaultAzureCredential
def get_connection_string():
credential = DefaultAzureCredential()
secret_client = SecretClient(
vault_url="https://myvault.vault.azure.net/",
credential=credential
)
return secret_client.get_secret("db-connection-string").value
SQL 인젝션 방지
항상 매개변수화된 쿼리를 사용하세요
매개변수화된 쿼리는 사용자 입력을 쿼리 구조와 분리하여 SQL 주입을 방지합니다. 항상 사용자 입력을 매개변수화하세요.
다음 문자열 형식의 쿼리는 SQL 인젝션에 취약합니다. 이런 식으로 쿼리를 만들지 마세요:
user_input = "'; DROP TABLE Person.Person; --"
cursor.execute(f"SELECT * FROM Person.Person WHERE FirstName = '{user_input}'")
다음 매개변수화된 쿼리는 드라이버가 쿼리 텍스트와 별도로 값을 전송하기 때문에 안전합니다:
user_input = "'; DROP TABLE Person.Person; --"
cursor.execute(
"SELECT * FROM Person.Person WHERE FirstName = %(name)s",
{"name": user_input}
)
모든 쿼리 구성 요소의 매개변수화
테이블과 컬럼 이름을 직접 매개변수화할 수는 없습니다. 사용자 입력에서 보간하면 앱이 SQL 인젝션에 취약해집니다:
table = user_input
cursor.execute(f"SELECT * FROM {table}")
대신, 동적 식별자를 허용 값 목록과 비교해 검증하고, 나머지 값들을 매개변수화하세요.
ALLOWED_TABLES = {"Person.Person", "Production.Product", "Sales.SalesOrderHeader"}
def query_table(cursor, table_name: str, conditions: dict):
"""Query with validated table name."""
if table_name not in ALLOWED_TABLES:
raise ValueError(f"Invalid table: {table_name}")
# Table name is safe, parameters are parameterized
where_clauses = [f"{k} = %({k})s" for k in conditions.keys()]
query = f"SELECT * FROM {table_name} WHERE {' AND '.join(where_clauses)}"
cursor.execute(query, conditions)
return cursor.fetchall()
입력을 검증하고 정제하세요
식별자를 사용하는 동적 SQL을 만들 때는 각 값을 사용하기 전에 엄격한 패턴에 대해 검증하세요:
import re
def validate_identifier(value: str) -> bool:
"""Validate SQL identifier (table/column name)."""
# Only allow alphanumeric and underscore
return bool(re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', value))
def safe_order_by(cursor, table: str, order_column: str, direction: str):
"""Order by with validation."""
if not validate_identifier(order_column):
raise ValueError(f"Invalid column name: {order_column}")
if direction.upper() not in ("ASC", "DESC"):
raise ValueError(f"Invalid direction: {direction}")
cursor.execute(f"""
SELECT * FROM {table}
ORDER BY {order_column} {direction.upper()}
""")
복잡한 연산에 저장 프로시저를 사용하세요
저장 프로시저는 애플리케이션 코드에 노출되는 SQL 표면적을 줄입니다:
employee_id = 5
cursor.execute("""
EXECUTE dbo.uspGetEmployeeManagers @BusinessEntityID = %(id)s
""", {"id": employee_id})
연결 보안
암호화 필요
항상 연결을 암호화하세요. Azure SQL은 기본적으로 암호화를 강제합니다. 온프레미스 SQL Server의 경우, 명시적으로 다음을 설정 Encrypt=yes 하세요:
conn = mssql_python.connect(
"Server=<server>.database.windows.net;"
"Database=<database>;"
"Encrypt=yes;"
"TrustServerCertificate=no"
)
최고 보안을 위해 TDS 8.0 엄격한 모드를 사용하세요
TDS 8.0은 다음을 제공합니다:
- 연결 시작 시 TLS 1.3
- 인증서 검증 필요
- 이전 프로토콜로의 후퇴 수단은 없습니다
conn = mssql_python.connect(
"Server=tcp:<server>.database.windows.net,1433;"
"Database=<database>;"
"Encrypt=strict"
)
서버 인증서 검증
운영 환경에서 서버 인증서를 항상 검증하여 중간 공격을 방지하세요. 를 설정 TrustServerCertificate=yes하지 마세요. 검증을 우회하기 때문입니다. 대신, CA 인증서와 대조하여 검증하도록 설정하고, 호스트명을 검증하도록 설정 TrustServerCertificate=noHostNameInCertificate 하세요:
conn = mssql_python.connect(
"Server=<server>;"
"Database=<database>;"
"Encrypt=yes;"
"TrustServerCertificate=no;"
"HostNameInCertificate=<server>.domain.com"
)
데이터 보호
서버 수준에서 민감한 데이터를 보호하세요
Always Encrypted는 현재 mssql-python 연결 문자열 키워드로 구성할 수 없습니다. 항상 암호화가 필요하다면, SQL Server용 ODBC 드라이버와 함께 pyodbc를 사용하세요. 비록 동일한 수준의 보호는 제공하지 않지만, 동적 데이터 마스킹과 행 단위 보안 같은 SQL Server 기능을 활용해 민감한 열을 보호할 수 있습니다.
employee_id = 1
cursor.execute("""
SELECT NationalIDNumber, LoginID
FROM HumanResources.Employee
WHERE BusinessEntityID = %(id)s
""", {"id": employee_id})
row = cursor.fetchone()
전송 중인 데이터 보호
- 연결 문자열에
Encrypt=yes을 사용합니다. - 온프레미스 연결은 VPN이나 프라이빗 엔드포인트를 사용하세요.
- Use Azure Private Link for Azure SQL.
최소 권한 원칙
데이터베이스 권한을 최소화하세요
애플리케이션 계정은 최소한의 권한을 가져야 합니다. 애플리케이션 연결에는 sa 또는 db_owner을 사용하지 마세요.
-
읽기 전용 보고:
GRANT SELECT ON SCHEMA::dbo TO ReportingApp; -
특정 테이블 접근:
GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor; -
저장 프로시저 전용:
GRANT EXECUTE ON ProcessOrder TO OrderProcessor;
다른 작업마다 다른 계정을 사용하세요
각 권한 레벨에 고유한 식별성을 부여하여 읽기 경로가 쓰기를 수행할 수 없도록 하세요. 이 예시는 클라이언트 ID에 의해 선택된 두 개의 사용자 지정 관리 신원을 사용합니다. 하나는 읽기 전용 권한이 부여되었고, 다른 하나는 쓰기 권한이 부여되었습니다:
readonly_client_id = os.environ["READONLY_IDENTITY_CLIENT_ID"]
readwrite_client_id = os.environ["READWRITE_IDENTITY_CLIENT_ID"]
def get_readonly_connection():
"""Connection for read-only operations."""
return mssql_python.connect(
f"Server={server};Database={db};"
f"Authentication=ActiveDirectoryMSI;UID={readonly_client_id};"
f"Encrypt=yes;ApplicationIntent=ReadOnly;"
)
def get_readwrite_connection():
"""Connection for write operations."""
return mssql_python.connect(
f"Server={server};Database={db};"
f"Authentication=ActiveDirectoryMSI;UID={readwrite_client_id};"
f"Encrypt=yes;"
)
애플리케이션 역할 비밀을 소스 컨트롤에서 벗어나게 하세요
애플리케이션 역할 비밀번호는 여전히 비밀입니다. 이들을 볼트나 비밀 주입된 환경 변수에 저장하고, 다른 자격 증명과 마찬가지로 신중하게 회전시키세요.
def execute_with_role(cursor, role: str, query: str, params: dict):
"""Execute query with specific application role."""
# Activate application role
cursor.execute(
"EXECUTE sp_setapprole @rolename = %(role)s, @password = %(pwd)s",
{"role": role, "pwd": os.environ[f"ROLE_{role.upper()}_PWD"]}
)
try:
cursor.execute(query, params)
return cursor.fetchall()
finally:
# Reset to original context
cursor.execute("EXECUTE sp_unsetapprole")
감사 및 로깅
보안 이벤트 기록
민감한 데이터는 기록하지 않고, 연결 실패, 권한 오류, 의심스러운 쿼리 같은 보안 관련 이벤트는 기록하세요.
import logging
logger = logging.getLogger("db_security")
def secure_connect(connection_string: str):
"""Connect with security logging."""
logger.info("Attempting database connection")
try:
conn = mssql_python.connect(connection_string)
logger.info("Database connection established")
return conn
except mssql_python.OperationalError as e:
logger.warning(f"Database connection failed: {type(e).__name__}")
raise
감사 민감 작업
민감한 테이블과 작업에 대해 데이터베이스 감사를 구성하세요. 중요한 작업에 대해 애플리케이션 수준 감사를 구현할 수도 있습니다.
def audit_data_access(cursor, user_id: str, action: str, resource: str):
"""Log data access for audit trail."""
cursor.execute("""
INSERT INTO AuditLog (UserID, Action, Resource, Timestamp, IPAddress)
VALUES (%(user)s, %(action)s, %(resource)s, GETUTCDATE(), %(ip)s)
""", {
"user": user_id,
"action": action,
"resource": resource,
"ip": get_client_ip()
})
민감한 데이터를 절대 기록하지 마세요
로그 메시지에는 매개변수 값을 포함하지 마세요. 데이터를 기록하지 말고 연산을 기록하세요.
피해야 함 - 값이 로그에 노출됨:
nid = "295847284"
logger.debug(f"Query: SELECT * FROM HumanResources.Employee WHERE NationalIDNumber = '{nid}'")
권장 - 값을 노출하지 않고 의도를 로그에 기록:
logger.debug("Executing employee lookup query")
nid = "295847284"
cursor.execute("SELECT * FROM HumanResources.Employee WHERE NationalIDNumber = %(nid)s", {"nid": nid})
오류 처리
내부 정보는 노출하지 마세요
내부적으로는 상세 오류를 기록하되, 데이터베이스 구조 유출을 방지하기 위해 사용자에게 일반적인 메시지만 반환합니다:
def safe_query(cursor, query: str, params: dict):
"""Execute query with safe error handling."""
try:
cursor.execute(query, params)
return cursor.fetchall()
except mssql_python.ProgrammingError as e:
# Log full error internally
logging.error(f"Query error: {e}")
# Return generic error to user
raise UserFacingError("An error occurred processing your request")
except mssql_python.IntegrityError:
raise UserFacingError("Invalid data provided")
오류 메시지 정제
구현 세부사항을 공개하지 않는 사용자 친화적인 메시지에 대한 데이터베이스 예외 매핑:
class UserFacingError(Exception):
"""Exception safe to show to users."""
pass
def handle_database_error(error: Exception) -> str:
"""Convert database errors to safe user messages."""
if isinstance(error, mssql_python.IntegrityError):
if "UNIQUE" in str(error):
return "A record with this value already exists"
if "FOREIGN KEY" in str(error):
return "Referenced item not found"
return "An error occurred. Please try again later."
보안 체크리스트
연결 보안 체크리스트
- [ ] 가능하면 Microsoft Entra 인증을 사용하세요.
- [ ] 암호화 활성화 (
Encrypt=yes). - [ ] 서버 인증서를 검증하세요.
- [ ] 신분증을 안전한 금고에 보관하세요.
- [ ] 파일은 로컬만 유지
.env하고, 공유 환경에서 대상 플랫폼을 통해 비밀을 주입합니다. - [ ] Azure SQL에는 TDS 8.0 strict 모드를 사용하세요.
쿼리 보안
- [ ] 항상 매개변수화된 쿼리를 사용하세요.
- [ ] 동적 식별자를 검증합니다.
- [ ] 복잡한 논리에는 저장 프로시저를 사용하세요.
- [ ] 쿼리 결과 크기를 제한하세요.
데이터 보안
- [ ] 서버 수준의 데이터 보호(마스킹, 행 수준의 보안)를 사용하세요.
- [ ] 적절한 경우 행 수준 보안을 사용하세요.
- [ ] 민감한 데이터는 로그에 숨기세요.
액세스 제어
- [ ] 최소의 특권 원칙을 사용하세요.
- [ ] 읽기/쓰기 계정을 따로 사용하세요.
- [ ] 권한을 정기적으로 점검하세요.
- [ ] 연결 타임아웃을 구현하세요.
모니터링
- [ ] 보안 이벤트를 기록하세요.
- [ ] 이상 현상 모니터링.
- [ ] 고장 알림을 설정하세요.
- [ ] 정기적인 보안 점검을 실시하세요.